Introduction: The Nightmare of 200 CSV Files

Every morning at 9 AM, the operations team exports data from a dozen channels—Google Ads, Facebook Ads, WeChat Ads, Douyin Ads, and more. Each channel produces one CSV file with different formats, column orders, and naming conventions. This is your daily reality.

The traditional approach looks like this:

import pandas as pd
import glob
import os

files = glob.glob('data/2026-09-16/*.csv')
dfs = []
for f in files:
    try:
        df = pd.read_csv(f)
        # Manual data cleaning
        df = df[df['amount'] > 0]
        dfs.append(df)
    except Exception as e:
        print(f"Error reading {f}: {e}")

# Merge all DataFrames
result = pd.concat(dfs, ignore_index=True)
# Continue processing...

This takes 5 minutes, consumes 3GB of memory, and crashes if any file has inconsistent columns. With DuckDB, it’s just one SQL statement.

Why Traditional Solutions Are So Painful

Before diving into solutions, let’s understand the root causes. Multi-file CSV merging has several classic pain points:

The core issue: Pandas is a row-oriented general tool, while DuckDB is a columnar database designed for analytical queries. When you need to merge, clean, and aggregate large numbers of structured files, the tool you choose directly determines your efficiency.

Sword One: union_by_name — A Revolution in Column Alignment

Suppose you have 10 CSV files, each representing data from a different advertising channel:

data/
├── google_ads_2026-09-16.csv
├── facebook_ads_2026-09-16.csv
├── wechat_ads_2026-09-16.csv
├── douyin_ads_2026-09-16.csv
└── ...

Each file has slightly different column structures:

• Google Ads: campaign_id, impressions, clicks, spend, date
• Facebook Ads: adset_id, clicks, spend, impressions (missing date, different column order)
• WeChat Ads: campaign_id, date, spend, ctr (only partial columns)

Traditional Pandas approach: You need to manually read each file, unify column names, handle missing values, then concat. That’s over 100 lines of code.

DuckDB approach:

SELECT 
    campaign_id,
    date,
    SUM(impressions) AS total_impressions,
    SUM(clicks) AS total_clicks,
    SUM(spend) AS total_spend
FROM read_csv_auto('data/*.csv',
    union_by_name = true,    -- Align by column name, missing columns filled with NULL
    header = true)
WHERE spend > 0             -- Filter invalid data
GROUP BY 1, 2
ORDER BY total_spend DESC;

The key is the union_by_name = true parameter. It tells DuckDB: merge data based on column names, not column positions. This means:

1. Even if 30 CSVs have completely different column orders, they align correctly
2. If a file is missing a column, that column is automatically filled with NULL
3. No need to know the complete Schema of all files in advance

This is truly “declarative” data processing—you describe what you want, not how to do it.

Sword Two: filename — Traceable Data Sources

In production environments, merely merging data is not enough. You need to know which file each record came from—for data lineage, debugging, and incremental updates.

DuckDB’s filename = true parameter was built for this:

SELECT 
    filename,                          -- Original file name
    regexp_extract(filename, '(\d{4}-\d{2}-\d{2})', 1) AS dt,  -- Extract date from filename
    channel,
    campaign_id,
    amount
FROM read_csv_auto('data/*/*.csv',
    union_by_name = true,
    filename = true)
WHERE amount > 0;

With the filename parameter, you can:

1. Trace data sources: Which channel produced that anomalous value?
2. Incremental processing: Only process today’s new files
3. Partitioned output: Organize Parquet files by date

Complete Production ETL Pipeline

Now, let’s combine both techniques to build a complete daily data pipeline:

Step 1: Read, Clean, and Merge

-- Core ETL: read all CSVs, clean, and merge
COPY (
  SELECT
    regexp_extract(filename, '(\d{4}-\d{2}-\d{2})', 1) AS dt,
    channel,
    campaign_id,
    CAST(order_time AS TIMESTAMP) AS order_time,
    amount,
    -- Preserve original filename for traceability
    filename
  FROM read_csv_auto('data/*/*.csv',
    filename = true,        -- Key: preserve source filename
    union_by_name = true)   -- Key: align by column name
  WHERE amount > 0           -- Clean dirty data
) TO 'output/clean_orders.parquet'
  (FORMAT PARQUET, PARTITION_BY dt);

What does this SQL do?

1. Recursively reads all CSV files under the data/ directory
2. Extracts the date from filenames as the partition key
3. Cleans invalid data (records with amount ≤ 0)
4. Outputs as Parquet format, partitioned by date

Step 2: Python Integration — Scheduled Tasks

import duckdb
from pathlib import Path
from datetime import datetime

def daily_etl_pipeline():
    """Daily data ETL pipeline"""
    con = duckdb.connect('etl.duckdb')
    today = datetime.now().strftime('%Y-%m-%d')
    
    # Create or replace the orders table
    con.execute(f"""
        CREATE OR REPLACE TABLE orders AS
        SELECT 
            regexp_extract(filename, '(\\d{{4}}-\\d{{2}}-\\d{{2}})', 1) AS dt,
            channel,
            campaign_id,
            CAST(order_time AS TIMESTAMP) AS order_time,
            amount,
            filename
        FROM read_csv_auto('data/{today}/*.csv',
            union_by_name = true,
            filename = true)
        WHERE amount > 0
    """)
    
    # Incremental write to Parquet lake
    con.execute(f"""
        COPY orders TO 'lake/orders'
        (FORMAT PARQUET, PARTITION_BY (dt), APPEND)
    """)
    
    # Verify results
    count = con.execute("SELECT count(*) FROM orders").fetchone()[0]
    print(f"✅ Processed {count} records today")
    
    con.close()

if __name__ == '__main__':
    daily_etl_pipeline()

Step 3: Automation Scheduling

Add the above Python script to crontab to run automatically at 2 AM daily:

# crontab -e
0 2 * * * cd /home/user/etl && python3 daily_pipeline.py >> /var/log/etl.log 2>&1

Or use GitHub Actions for cloud scheduling:

# .github/workflows/daily-etl.yml
name: Daily ETL Pipeline
on:
  schedule:
    - cron: '0 2 * * *'  # Daily at 02:00 UTC
  workflow_dispatch:  # Also supports manual trigger

jobs:
  etl:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - name: Run DuckDB ETL
        run: python3 daily_pipeline.py

Performance Comparison: Real Benchmarks

Let’s compare three approaches using a real scenario:

Test Data: 200 CSV files, 500K rows each, total ~10GB Test Environment: 16GB RAM, SSD storage, 8-core CPU

Key Insight: DuckDB’s parallel reading and columnar execution makes it 30+ times faster than Pandas for this “read multiple files → clean → aggregate” pattern, and much lighter than Spark (no cluster needed).

Advanced Techniques: Benefits of Partitioned Parquet

What’s the benefit of adding PARTITION_BY dt when outputting Parquet?

1. Query Performance Improvement

-- Query only the last 7 days of data (avoid scanning everything)
SELECT * FROM read_parquet('lake/orders/')
WHERE dt >= '2026-09-10';

DuckDB automatically uses partition pruning to read only relevant date files.

2. Simpler Incremental Updates

-- Append today's data (don't overwrite history)
COPY (
  SELECT * FROM read_csv_auto('data/2026-09-17/*.csv', ...)
) TO 'lake/orders'
  (FORMAT PARQUET, PARTITION_BY dt, APPEND);

3. Data Lake Compatibility

Partitioned Parquet files can be read directly by Spark, Trino, ClickHouse, and other engines, making future expansion easier.

Why Not Spark?

Many developers ask: isn’t Spark more professional for this problem?

The answer depends on your data scale:

For most small-to-medium enterprise data processing needs, DuckDB’s performance is more than adequate, with far lower operational costs than Spark.

Monetization Suggestions: From Skill to Income

After mastering this skill, you can develop the following paid products:

Option A: SaaS Data Pipeline Service

Provide automated data integration services for e-commerce clients:

• Connect data from Shopify, Amazon, TikTok Shop, and other platforms
• Daily automatic cleaning, merging, and report generation
• Monthly pricing: $299-$999/client/month

Option B: Customized ETL Solutions

Help enterprises build data pipelines:

• One-time project fee: $3,000-$15,000
• Includes: requirement analysis, code development, deployment & debugging, training documentation
• Ongoing maintenance: $500-$2,000/month

Option C: Paid Knowledge Courses

Create a DuckDB ETL practical course:

• Udemy/Coursera pricing: $19.99-$49.99/student
• Estimated students: 500-2,000
• Potential revenue: $10,000-$100,000

Revenue Estimates

Summary

DuckDB’s union_by_name + filename two parameters solve 90% of multi-file CSV merging pain points:

1. No need to care about column order — auto-aligned by name
2. Preserve data source — filename parameter tracks each record’s origin
3. Streaming processing — doesn’t consume excessive memory
4. One SQL statement — replaces dozens of lines of Pandas code

Start deleting those for-loops in your scripts today.

Article Information

If you find any errors, please report via GitHub Issue or email [email protected].