The Problem: Three Bottlenecks of Pandas ETL

If you process millions of rows of CSV data daily with Pandas, you’ve hit these walls:

1. Memory blowups — pd.read_csv() loads everything into RAM. A 16GB machine processing 200M rows? OOM.
2. Slow performance — groupby().agg() on millions of rows takes minutes, not seconds.
3. Multi-source chaos — Merging MySQL tables, CSV files, and API data requires repetitive read/write cycles, with 50+ lines of glue code.

DuckDB’s solution: Zero-copy queries (no full data loading), vectorized execution engine (10-50x faster than Pandas), and native cross-source JOINs (MySQL + Parquet + CSV in one query).

1. ETL Data Cleaning: Line-by-Line Replacement

1.1 Reading and Filtering

Pandas approach:

import pandas as pd
df = pd.read_csv('orders_2026.csv')
df = df[df['amount'] > 100]
df = df.dropna(subset=['user_id'])
result = df.groupby('category')['amount'].sum()

DuckDB replacement (3 lines of SQL):

import duckdb
result = duckdb.sql("""
 SELECT category, SUM(amount)
 FROM read_csv_auto('orders_2026.csv')
 WHERE amount > 100 AND user_id IS NOT NULL
 GROUP BY category
""").fetchdf() # Returns a Pandas DataFrame

💡 Key difference: DuckDB doesn’t load the entire CSV into memory. It streams the data, loading only what’s needed. Benchmark: 120M row CSV — Pandas needed 64GB RAM and crashed → DuckDB used only 4.3GB, completing in 0.8 seconds versus 42 seconds.

1.2 Multi-Table JOIN Cleaning

Pandas multi-table merging creates multiple intermediate DataFrames:

users = pd.read_sql("SELECT id, name FROM users", conn)
orders = pd.read_csv('orders.csv')
products = pd.read_csv('products.csv')
merged = users.merge(orders, left_on='id', right_on='user_id')
merged = merged.merge(products, left_on='product_id', right_on='pid')
result = merged.groupby('name').agg({'amount': 'sum', 'qty': 'count'})

DuckDB cross-source JOIN:

SELECT u.name, SUM(o.amount), COUNT(o.id)
FROM 'mysql://user:pass@host/db?table=users' AS u
JOIN read_csv_auto('orders.csv') AS o ON u.id = o.user_id
JOIN read_csv_auto('products.csv') AS p ON o.product_id = p.id
GROUP BY u.name;

One SQL statement handles all cross-source data merging. Zero temp files. Zero memory explosions.

1.3 Operation Comparison

2. Cross-Source Queries: Zero-ETL Best Practices

One of DuckDB’s most powerful features is querying across data sources directly, without pre-loading.

2.1 Reading Remote Files

-- Read Parquet from S3
SELECT region, SUM(revenue)
FROM read_parquet('s3://my-bucket/sales/*.parquet')
WHERE date >= '2026-01-01'
GROUP BY region;

-- Read CSV over HTTP
SELECT * FROM read_csv_auto('https://data.example.com/daily_report.csv');

2.2 MySQL + Parquet + CSV Federated Query

CREATE VIEW monthly_sales AS
SELECT 
 u.region,
 DATE_TRUNC('month', o.order_date) AS month,
 SUM(o.amount) AS total_revenue,
 COUNT(DISTINCT u.id) AS active_users
FROM postgres_db.public.users AS u
JOIN read_parquet('s3://orders/2026/*.parquet') AS o
 ON u.id = o.user_id
WHERE o.amount > 0
GROUP BY u.region, DATE_TRUNC('month', o.order_date);

This single SQL query directly reads PostgreSQL user tables + S3 Parquet order files. No ETL pipeline. No data sync. No intermediate storage.

2.3 Exporting Results

-- Write directly to Parquet (10x better compression than CSV)
COPY (
 SELECT * FROM monthly_sales
 WHERE total_revenue > 10000
 ORDER BY total_revenue DESC
) TO 'monthly_sales_report.parquet' (FORMAT PARQUET, COMPRESSION ZSTD);

-- Or export as CSV
COPY monthly_sales TO 'report.csv' (HEADER, DELIMITER ',');

3. Step-by-Step Migration from Pandas to DuckDB

3.1 Gradual Migration Strategy

Don’t rewrite everything at once. Follow this phased approach:

Week 1: Replace data reading and simple filtering

# Before
df = pd.read_csv('data.csv')
df_filtered = df[df['col'] > 100]

# After
df_filtered = duckdb.sql("SELECT * FROM read_csv_auto('data.csv') WHERE col > 100").fetchdf()

Week 2: Replace groupby aggregation

# Before
result = df.groupby('category').agg({'sales': 'sum', 'count': 'size'}).reset_index()

# After
result = duckdb.sql("""
 SELECT category, SUM(sales) AS total_sales, COUNT(*) AS order_count
 FROM df
 GROUP BY category
""").fetchdf()

Week 3: Replace multi-table merges

# Before
merged = pd.merge(orders, users, on='user_id')
merged = pd.merge(merged, products, left_on='product_id', right_on='id')

# After
result = duckdb.sql("""
 SELECT *
 FROM orders o
 JOIN users u ON o.user_id = u.id
 JOIN products p ON o.product_id = p.id
""").fetchdf()

Week 4: Enable cross-source queries, eliminate intermediate files entirely

One SQL query reads MySQL + Parquet + CSV. No more ETL middleware.

3.2 FAQ

Q: How much data can DuckDB handle? A: Terabyte-scale on a single machine (via spilling to disk). Benchmark: 100GB Parquet files, 4-core 8GB machine, aggregation query under 5 seconds.

Q: I use custom functions in Pandas. What now? A: DuckDB supports Python UDFs and lambda registration:

duckdb.create_function('my_func', lambda x: x * 2, [bigint, bigint])
duckdb.sql("SELECT my_func(amount) FROM orders")

Q: Can I still use Pandas for visualization after migration? A: Yes! fetchdf() returns a Pandas DataFrame, so Matplotlib/Seaborn work seamlessly.

Q: How do I monitor performance? A: Use EXPLAIN ANALYZE:

EXPLAIN ANALYZE
SELECT category, SUM(amount)
FROM read_csv_auto('large_file.csv')
GROUP BY category;

5. Summary

Your action plan:

1. Open your slowest Pandas script. Find the groupby or merge operation
2. Replace it with duckdb.sql() pattern from this guide, run a time comparison
3. Post the benchmark screenshot on LinkedIn/Reddit — people will ask how
4. Quote $30 to start, close your first client in under an hour

DuckDB replacing Pandas isn’t a technical challenge — it’s an arbitrage opportunity. Spend 2 days learning it, and you can save your clients thousands.

📺 Video tutorials: youtube.com/@DuckDBLab 🦆 More strategies: duckdblab.org

本文信息

如发现错误，欢迎通过 GitHub Issue 或邮件 contact@duckdblab.org 反馈。