The Pain: Three Bottlenecks of Pandas

If you process millions of rows of data daily with Pandas, you’ve experienced these pain points:

1. Memory explosions — pd.read_csv() loads the entire file into memory. A 16GB machine processing 200M rows? OOM crash.
2. Slow performance — .groupby().agg() takes minutes on millions of rows. .apply(axis=1) is a performance killer.
3. Multi-source data integration hell — Merging CSV + MySQL + 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).

Why DuckDB is Faster Than Pandas

The core reason is simple: columnar storage + vectorized execution.

Pandas processes row by row — every operation traverses all columns. DuckDB processes column by column, reading only what you need. Plus, DuckDB’s C++ vectorized execution makes memory layouts naturally suitable for analytical queries.

The gap grows larger with more data:

• Columnar storage: Only reads needed columns, skips irrelevant content
• Vectorized parsing: C++ implementation is orders of magnitude faster than Python’s row-by-row loops
• Zero-copy: Parsed results store directly in columnar format, no intermediate conversion

Five Core Scenarios: Pandas to DuckDB Comparison

Scenario 1: Reading CSV with Auto Type Inference

Pandas approach:

import pandas as pd
df = pd.read_csv('orders.csv')
df['amount'] = df['amount'].astype(float)
df['date'] = pd.to_datetime(df['date'])

DuckDB approach:

import duckdb
df = duckdb.sql("SELECT * FROM read_csv_auto('orders.csv')").df()

One line. read_csv_auto() automatically infers every column’s type — no manual type conversion needed.

Scenario 2: Group Aggregation (Replaces groupby)

Pandas approach:

result = (df.groupby('category')
          .agg(total_amount=('amount', 'sum'),
               avg_amount=('amount', 'mean'),
               order_count=('order_id', 'count'))
          .reset_index()
          .sort_values('total_amount', ascending=False))

DuckDB approach:

result = duckdb.sql("""
    SELECT category,
           SUM(amount) AS total_amount,
           ROUND(AVG(amount), 2) AS avg_amount,
           COUNT(*) AS order_count
    FROM read_csv_auto('orders.csv')
    GROUP BY category
    ORDER BY total_amount DESC
""").df()

Same logic, DuckDB uses just 7 lines of SQL. And the DuckDB version is faster and more memory-efficient.

Scenario 3: Multi-Table Joins (Replaces merge)

Pandas approach:

orders = pd.read_csv('orders.csv')
customers = pd.read_csv('customers.csv')
result = orders.merge(customers, on='customer_id', how='left')

DuckDB approach:

result = duckdb.sql("""
    SELECT o.*, c.name, c.tier
    FROM read_csv_auto('orders.csv') o
    LEFT JOIN read_csv_auto('customers.csv') c
        ON o.customer_id = c.id
""").df()

Key insight: DuckDB can read multiple CSV files directly in SQL for JOINs — no need to load files into memory first. For large files, this means streaming reads with minimal memory footprint.

Scenario 4: Conditional Computation (Replaces apply)

Pandas approach:

def classify_order(row):
    if row['amount'] > 1000:
        return 'high'
    elif row['amount'] > 100:
        return 'medium'
    else:
        return 'low'

df['level'] = df.apply(classify_order, axis=1)

DuckDB approach:

result = duckdb.sql("""
    SELECT *,
           CASE WHEN amount > 1000 THEN 'high'
                WHEN amount > 100  THEN 'medium'
                ELSE 'low' END AS level
    FROM read_csv_auto('orders.csv')
""").df()

.apply(axis=1) is Pandas’ performance killer — it本质上 loops in Python. DuckDB’s CASE WHEN executes at the C++ layer, orders of magnitude faster.

Scenario 5: Deduplication and Unique Values

Pandas approach:

unique_customers = df['customer_id'].unique()
clean_df = df.drop_duplicates(subset=['order_id'])

DuckDB approach:

unique_customers = duckdb.sql("""
    SELECT DISTINCT customer_id
    FROM read_csv_auto('orders.csv')
""").df()

clean_df = duckdb.sql("""
    SELECT DISTINCT ON (order_id) *
    FROM read_csv_auto('orders.csv')
""").df()

DISTINCT and DISTINCT ON are native DuckDB syntax with highly optimized execution plans.

Complete Example: Rewriting a Pandas Data Pipeline

Here’s a typical task:

1. Read sales.csv and products.csv
2. Join to get product names for each order
3. Aggregate sales by category and month
4. Find the top 10 combinations

Pandas version (~15 lines):

import pandas as pd
sales = pd.read_csv('sales.csv')
products = pd.read_csv('products.csv')
merged = sales.merge(products, left_on='product_id', right_on='id')
merged['month'] = pd.to_datetime(merged['sale_date']).dt.to_period('M')
result = (merged.groupby(['category', 'month'])
          .agg(total_sales=('amount', 'sum'),
               avg_order=('amount', 'mean'),
               order_count=('amount', 'count'))
          .reset_index()
          .sort_values('total_sales', ascending=False)
          .head(10))

DuckDB version (just 1 SQL statement):

import duckdb
result = duckdb.sql("""
    SELECT
        p.category,
        strftime(s.sale_date, '%Y-%m') AS month,
        COUNT(*) AS order_count,
        ROUND(SUM(s.amount), 2) AS total_sales,
        ROUND(AVG(s.amount), 2) AS avg_order_value
    FROM read_csv_auto('sales.csv') s
    JOIN read_csv_auto('products.csv') p
        ON s.product_id = p.id
    GROUP BY p.category, strftime(s.sale_date, '%Y-%m')
    ORDER BY total_sales DESC
    LIMIT 10
""").df()
print(result)

No .groupby(), no .merge(), no .apply(). Everything expressed in SQL. And whether the data is 1 million rows or 10 billion rows, the code doesn’t change.

Three Migration Tips: From Pandas to DuckDB

Tip 1: Write SQL First, Then Convert to Python

Write your Pandas logic in SQL first — the logic is clearer and performance is better. Then use .df() to convert results back to DataFrames for any downstream processing.

# Write complete logic in DuckDB first
sql = """
    SELECT ... FROM ... WHERE ... GROUP BY ...
"""
# Then convert to Pandas for visualization
df = duckdb.sql(sql).df()
df.plot()

Tip 2: Use duckdb.sql() Instead of pd.read_csv()

Anywhere you previously used pd.read_csv(), switch to duckdb.sql("SELECT * FROM read_csv_auto('file.csv')"). Everything else stays the same.

Tip 3: Fall Back to Pandas Only When Needed

DuckDB covers 90% of data processing scenarios (reading, filtering, aggregation, joining, window functions). Only fall back to Pandas when you truly need its advanced features (time series resampling, complex plotting, custom algorithms).

# DuckDB handles data preparation
df = duckdb.sql("""
    SELECT * FROM read_csv_auto('large_data.csv')
    WHERE date >= '2026-01-01'
    GROUP BY category
    HAVING SUM(amount) > 10000
""").df()

# Pandas handles what it's good at (visualization)
df.groupby('category').plot(kind='bar', y='amount')

Pandas → DuckDB Quick Reference Table

Performance Benchmark: Real-World Comparison

We tested with an e-commerce analytics scenario: 10 million order records (orders.csv, ~4.2GB).

Memory usage comparison:

• Pandas full pipeline: Peak 12.8GB
• DuckDB full pipeline: Peak 2.1GB

Monetization: What Can You Earn With DuckDB Skills?

1. Data Analysis Service Upgrade

Help companies migrate their existing Pandas data processing pipelines to DuckDB, achieving 10-50x performance improvements. Single project fee: $700-2,800, with monthly maintenance at $280-700.

2. Automated Reporting Systems

Build automated reporting systems using DuckDB + FastAPI + schedule. Small and medium businesses have a strong need for monthly data summaries. Monthly fee: $140-420.

3. High-Performance ETL Services

Provide big data ETL services for enterprise clients. DuckDB’s columnar processing and vectorized execution are naturally suited for batch data processing. Charge by data volume or project.

4. Technical Consulting and Training

Many companies still use Pandas for large data but face performance and memory issues. Offer DuckDB migration consulting and team training. Single training session: $420-1,400.

5. SaaS Product Backend

Use DuckDB as the analytics engine backend for SaaS products, replacing the heavy Pandas + PostgreSQL architecture. DuckDB’s embedded nature makes deployment costs nearly zero.

Core logic: Pandas users hit performance walls as data grows, and DuckDB provides a seamless upgrade path.

Summary

DuckDB isn’t about completely replacing Pandas — it’s about providing a better choice for core data analysis scenarios (reading, filtering, aggregation, joining). Remember:

1. read_csv_auto() replaces pd.read_csv() in one line
2. GROUP BY + aggregates replace .groupby().agg()
3. JOIN replaces .merge(), supporting cross-source queries
4. CASE WHEN replaces .apply(axis=1), avoiding Python loops
5. DISTINCT replaces .drop_duplicates()

Pandas is a Python library. DuckDB is a database engine. When you use Pandas for data analysis, you’re essentially simulating a database in memory. DuckDB was born for analysis — it doesn’t need to simulate, it simply is your analysis engine.

Next time you get data, ask yourself: Can I do this in one line of SQL?

💡 More DuckDB tutorials → duckdblab.org