Say Goodbye to Manual Reconciliation: Auto-Merge Multi-Source CSVs and Detect Anomalies with One DuckDB Query

Do You Also Experience This Every Month-End?

Every month, finance colleagues open their computers with tired faces, beginning a dreaded “reconciliation war”:

• Export transaction records from Alipay — Format A
• Export billing from WeChat Pay — Format B
• Download bank credit card statements — Format C
• Three Excel files with different column names, manually copying and pasting into one spreadsheet
• Using VLOOKUP to reconcile, and the results are often wrong anyway

This entire process takes 2-3 hours and is highly error-prone. When the data volume grows slightly, Excel crashes outright.

What if there was a tool that could, like magic, read multiple CSVs with different formats in one click, automatically align fields, and even help you spot anomalous transactions?

Welcome to the world of DuckDB.

DuckDB: The Underrated Swiss Army Knife for Data Processing

DuckDB is an embedded OLAP database designed for analytical queries, rapidly gaining popularity among data engineers and data analysts in recent years. Its core advantages include:

• Zero deployment: No server installation needed — call it directly from Python, R, or the command line
• Extreme speed: Columnar storage + SIMD vectorized execution, processing millions of rows in seconds
• SQL-native: All operations use standard SQL, no language switching required
• Rich data sources: Read CSV, JSON, Parquet, PostgreSQL, S3, and more directly

In this article, we’ll use DuckDB to solve a real-world scenario: automated multi-source bill reconciliation and anomaly detection.

Scenario Setup: Three Sources, Three Formats

Assume you have three transaction record CSV files from different payment channels:

Our goals:

1. Auto-read three files regardless of field name differences
2. Normalize fields: Unify different names to standard names
3. Smart categorization: Automatically classify by description into “Dining”, “Transport”, “Shopping”, etc.
4. Anomaly detection: Find “刺客” (刺客 = ambush assassin, slang for unexpectedly expensive purchases) transactions exceeding 3x the average
5. Export results: Output as Parquet format for downstream BI analysis

Complete Solution

Step 1: Installation and Initialization

import duckdb

# Create DuckDB connection
con = duckdb.connect(':memory:')

# Install httpfs extension (for reading remote files, optional)
con.install('httpfs')
con.load('httpfs')

Step 2: One-Click Read of Multiple Heterogeneous CSVs

DuckDB’s read_csv_auto() function is the core of this solution. It supports glob pattern matching and can read all CSV files in a directory at once:

CREATE TABLE raw_transactions AS
SELECT * FROM read_csv_auto('/data/*.csv',
                             header=true,
                             union_by_name=true,
                             filename=true);

Key parameters explained:

• header=true: Automatically recognizes the first row as column names
• union_by_name=true: Merges by column name automatically — columns from different files align themselves, missing columns are filled with NULL
• filename=true: Adds an extra filename column marking which file each record came from

This is DuckDB’s biggest advantage over pandas: no need for Python loops to read and merge files one by one. DuckDB handles everything at the C++ level — faster and with lower memory usage.

Step 3: Field Normalization and Smart Categorization

CREATE TABLE normalized_transactions AS
SELECT
    filename AS source,
    
    -- Normalize amount field
    COALESCE(CAST(交易金额 AS DOUBLE),
             CAST(金额 AS DOUBLE),
             CAST(amount AS DOUBLE)) AS amount,
    
    -- Normalize item description field
    COALESCE(商品说明, 商户全称, description) AS item_desc,
    
    -- Smart categorization
    CASE
        WHEN COALESCE(商品说明, 商户全称, description) ILIKE '%外卖%' 
          OR COALESCE(商品说明, 商户全称, description) ILIKE '%餐饮%' 
          OR COALESCE(商品说明, 商户全称, description) ILIKE '%饿了么%' 
          OR COALESCE(商品说明, 商户全称, description) ILIKE '%美团%' 
            THEN 'Dining'
        WHEN COALESCE(商品说明, 商户全称, description) ILIKE '%滴滴%' 
          OR COALESCE(商品说明, 商户全称, description) ILIKE '%地铁%' 
          OR COALESCE(商品说明, 商户全称, description) ILIKE '%公交%' 
            THEN 'Transport'
        WHEN COALESCE(商品说明, 商户全称, description) ILIKE '%淘宝%' 
          OR COALESCE(商品说明, 商户全称, description) ILIKE '%京东%' 
          OR COALESCE(商品说明, 商户全称, description) ILIKE '%拼多多%' 
            THEN 'Shopping'
        ELSE 'Other'
    END AS category,
    
    -- Original amount (for further analysis)
    COALESCE(CAST(交易金额 AS DOUBLE),
             CAST(金额 AS DOUBLE),
             CAST(amount AS DOUBLE)) AS raw_amount
    
FROM raw_transactions;

Key points:

• COALESCE(): Tries multiple field names in sequence, returning the first non-NULL value. This is the standard approach for handling heterogeneous data sources.
• ILIKE: Case-insensitive LIKE pattern matching, suitable for mixed Chinese and English scenarios.
• Extensible categorization rules: You can add more classification conditions as business needs evolve.

Step 4: Anomaly Transaction Detection

-- Calculate global average spending
WITH stats AS (
    SELECT AVG(amount) AS avg_amount, 
           STDDEV(amount) AS std_amount
    FROM normalized_transactions
),

-- Flag anomalous transactions (beyond 3 standard deviations or 3x mean)
flagged AS (
    SELECT *,
           CASE 
               WHEN amount > 3 * (SELECT avg_amount FROM stats) 
               OR amount > (SELECT avg_amount + 3 * std_amount FROM stats)
               THEN TRUE 
               ELSE FALSE 
           END AS is_anomaly
    FROM normalized_transactions
)

-- Output top 10 anomalous transactions
SELECT 
    source,
    category,
    item_desc,
    ROUND(amount, 2) AS amount,
    ROUND((SELECT avg_amount FROM stats), 2) AS avg_spend,
    ROUND(amount / (SELECT avg_amount FROM stats), 2) AS ratio_to_avg
FROM flagged
WHERE is_anomaly = TRUE
ORDER BY amount DESC
LIMIT 10;

This SQL uses two CTEs (Common Table Expressions):

1. stats: Calculates the overall spending average and standard deviation
2. flagged: Marks each transaction as anomalous using two criteria: absolute threshold and statistical threshold

Step 5: Export Analysis Results

-- Export as Parquet (columnar compressed format, ideal for BI tools)
COPY (
    SELECT * FROM normalized_transactions
    ORDER BY amount DESC
) TO '/output/transactions_clean.parquet' (FORMAT PARQUET);

-- Export anomaly transaction details
COPY (
    SELECT source, category, item_desc, amount, raw_amount
    FROM flagged
    WHERE is_anomaly = TRUE
) TO '/output/anomaly_report.csv' (HEADER, DELIMITER ',');

Parquet format has three major advantages over CSV:

Performance Comparison: DuckDB vs Traditional Approaches

💡 Key Insight: For small-to-medium scale data processing tasks (under 1M rows), DuckDB achieves 5-10x faster processing than pandas with minimal code and low resource consumption, while avoiding Excel’s performance bottlenecks.

Advanced: Cloud Data Sources

If your bill files are stored on S3 or other cloud storage, simply replace the path:

-- Read CSV from S3
CREATE TABLE s3_raw AS
SELECT * FROM read_csv_auto('s3://my-bucket/bills/*.csv',
                             header=true,
                             union_by_name=true,
                             filename=true,
                             ACCESS_KEY_ID='...',
                             SECRET_ACCESS_KEY='...');

Combined with scheduled tasks (e.g., cron + DuckDB CLI), you can achieve a fully automated monthly reconciliation process with zero manual intervention.

Monetization Suggestions

After mastering this skill, you can convert it into actual income streams:

Path 1: Enterprise Reconciliation SaaS

• Package the above solution into a web app (Streamlit / FastAPI)
• Support uploading multi-source bills, auto-reconcile and generate difference reports
• Monthly subscription: Small businesses $99/month, medium businesses $299/month
• Target customers: E-commerce companies, cross-border e-commerce, chain stores

Path 2: Financial Automation Consulting Service

• Customize “bill auto-reconciliation systems” for enterprises
• One-time project fee: ¥3,000-¥10,000
• Ongoing maintenance: ¥500-¥2,000/month
• Suitable for freelancers and small consulting firms

Path 3: Open Source Tool + Paid Support

• Open-source the code as a GitHub project
• Offer Pro versions with advanced features (e.g., intelligent classification models, multi-language support)
• Earn passive income through GitHub Sponsors or Patreon

Summary

This article introduced how to use DuckDB’s read_csv_auto() function, combined with advanced parameters like union_by_name and filename, to auto-merge heterogeneous CSVs from multiple sources, normalize fields, categorize transactions, and detect anomalies — all in roughly 10 lines of SQL.

This approach is far more efficient than traditional Excel manual reconciliation or pandas programming. DuckDB’s columnar engine and zero-deployment characteristics make it an ideal choice for personal data analysis and small-to-medium enterprise data pipelines.

Next month-end, try this approach — give your bills a “health check”.