DuckDB vs Pandas: When SQL Beats DataFrames

Pandas gives you DataFrames. DuckDB gives you SQL. Both operate on the same tabular data, both run inside a Python process, and — here's the part most people miss — DuckDB can query Pandas DataFrames directly, so you don't have to pick one. But if your analytical queries involve GROUP BY, JOIN, or window functions on anything past a few hundred megabytes, DuckDB will finish while Pandas is still chewing through memory.

How do the two actually differ?

Pandas loads your entire dataset into RAM as a DataFrame and processes it row by row on a single CPU core. DuckDB runs a columnar, vectorized engine that reads only the columns each query touches, parallelizes across all cores, and spills to disk if memory runs low.

That architectural gap barely matters at 10,000 rows. At 10 million, it's the difference between 0.3 seconds and 45 seconds.

Same task, two approaches

The fastest way to feel the difference is to write the same query both ways.

Task: monthly revenue by product category, ranked.

# — Pandas —
import pandas as pd
 
df = pd.read_parquet('orders.parquet')
result = (
    df.assign(month=df['order_date'].dt.to_period('M'))
    .groupby(['month', 'category'])
    .agg(revenue=('amount', 'sum'), orders=('amount', 'count'))
    .sort_values(['month', 'revenue'], ascending=[True, False])
    .reset_index()
)

-- DuckDB --
SELECT
    date_trunc('month', order_date) AS month,
    category,
    SUM(amount)  AS revenue,
    COUNT(*)     AS orders
FROM 'orders.parquet'
GROUP BY 1, 2
ORDER BY 1, revenue DESC;

On a 200MB Parquet file, Pandas takes about 4 seconds. DuckDB finishes in 0.15 seconds. Both produce identical output. The DuckDB version also never loads the full file — it reads only order_date, category, and amount columns from disk.

Where does DuckDB pull ahead?

Joins across big tables. Joining two DataFrames with 20M+ rows each makes Pandas sweat — it needs RAM for both frames plus the merge result. DuckDB's hash join is built for exactly this.

SELECT
    o.customer_id,
    c.segment,
    SUM(o.amount) AS lifetime_value
FROM 'orders.parquet' o
JOIN 'customers.parquet' c ON o.customer_id = c.id
GROUP BY 1, 2;

The Pandas equivalent — pd.merge(orders, customers, ...) followed by a groupby — allocates a third DataFrame for the merge output. On a 5GB orders table joined with a 200MB customers table, I've seen Pandas consume 18GB of RAM. DuckDB used 1.2GB.

Window functions. Running totals, row numbering, percentile ranks — these are one-liners in SQL and DuckDB processes them in vectorized batches. The Pandas equivalents (cumsum(), rank()) work but each produces a separate Series, and at 50M rows the cumulative memory cost is steep.

Querying files you just received. Someone drops a 3GB CSV on you. DuckDB queries it in place:

SELECT department, COUNT(*), AVG(salary)
FROM 'q2_headcount_export.csv'
GROUP BY department;

Pandas has to load the whole file first, infer dtypes (slowly, often incorrectly for mixed columns), and hold the result. DuckDB reads the file in a streaming pass and only materializes the grouped result.

When do DataFrames still win?

Not every workload is a GROUP BY.

Row-level transformations with arbitrary Python. When each row needs a custom function, regex extraction, or API call, Pandas is the natural home:

df['domain'] = df['email'].str.extract(r'@(.+)$')
df['risk_score'] = df.apply(score_customer, axis=1)

You can write DuckDB UDFs in Python, but the ergonomics don't match native DataFrame operations for this kind of work.

ML feature pipelines. scikit-learn, XGBoost, and PyTorch expect NumPy arrays or DataFrames. Your feature engineering step will be in Pandas regardless.

Small data, fast iteration. A 5,000-row CSV you're exploring in a Jupyter notebook? Pandas is instant and the overhead of writing SQL buys you nothing. The crossover point where DuckDB's speed advantage starts to matter is roughly 500MB of data or 5M+ rows, depending on query complexity.

How fast is DuckDB, really?

Benchmarks vary by hardware and query shape, but these ratios are consistent across the tests I've run on a laptop (M3 Pro, 36GB RAM) against Parquet files:

                  10M rows    50M rows    200M rows
GROUP BY + SUM
  DuckDB:        ██           ██           ██          ~0.1-0.4s
  Pandas:        ████████     █████████████████████████ ~2-45s
 
JOIN (2 tables)
  DuckDB:        ███          ███          ████        ~0.3-1.2s
  Pandas:        ██████████   ████████████████████████  ~4-60s (OOM >150M)
 
Window function
  DuckDB:        ██           ███          ████        ~0.2-1.5s
  Pandas:        ███████      ██████████████████████    ~3-40s

At 200M rows, Pandas often can't finish — it runs out of memory before the computation completes. DuckDB's out-of-core execution means it keeps going even when the dataset exceeds available RAM.

Can you use both together?

Yes, and this is the workflow I'd recommend once your data hits the crossover point. DuckDB handles the heavy aggregation and filtering, then hands a small result to Pandas for custom logic or plotting.

import duckdb
import pandas as pd
 
# DuckDB: scan 40GB, aggregate down to ~50K rows
summary = duckdb.sql("""
    SELECT
        customer_id,
        COUNT(*) AS orders,
        SUM(amount) AS total_revenue,
        MAX(order_date) AS last_order
    FROM 'orders/*.parquet'
    WHERE order_date >= '2025-01-01'
    GROUP BY customer_id
    HAVING COUNT(*) >= 3
""").df()
 
# Pandas: custom logic on the small result
summary['days_since_last'] = (pd.Timestamp.now() - summary['last_order']).dt.days
summary['churn_risk'] = summary['days_since_last'].apply(classify_risk)

It works the other direction too. DuckDB reads existing DataFrames without copying — pass the variable name as a table reference (SELECT * FROM users_df GROUP BY region) and it queries the underlying Arrow buffers directly.

What does this look like in a real stack?

The "DuckDB for aggregation, Pandas for last mile" pattern works great locally. Once you need to join data from multiple sources — a Postgres database, a Stripe export, a HubSpot CSV — the coordination overhead grows.

Fastero uses DuckDB as its cross-source analytical store. You connect Postgres, Stripe, HubSpot, or upload files, and the data syncs into DuckDB where you can run SQL joins across all of them. The AI agent writes the queries, builds dashboards from the results, and schedules refreshes — so the DuckDB-to-insight path doesn't dead-end in a notebook.

If you're evaluating how DuckDB fits into a broader analytics workflow, these related posts dig into specifics:

• DuckDB vs SQLite for Analytics — when embedded SQL needs a columnar engine underneath
• How to Analyze Excel and CSV Files with SQL — the file-querying workflow in practice
• How to Run SQL Across Multiple Databases Without a Warehouse — cross-source queries without standing up Snowflake

FAQ

Is DuckDB a replacement for Pandas? Not entirely. DuckDB replaces the subset of Pandas work that's expressible as SQL — aggregations, joins, filtering, window functions. For row-level Python transformations, ML feature engineering, and tight integration with plotting libraries, Pandas is still the right tool. Most teams end up using both.

Can DuckDB read Pandas DataFrames? Yes. DuckDB reads a DataFrame's underlying memory directly — no serialization, no copying. You pass the DataFrame variable name as a table reference in SQL: SELECT * FROM my_dataframe WHERE amount > 100.

How much faster is DuckDB than Pandas? On analytical queries (GROUP BY, JOIN, windows), DuckDB is typically 10-50x faster at millions of rows and 50-100x faster at hundreds of millions. On small datasets under a few hundred thousand rows, the difference is negligible.

Do I need to learn SQL to use DuckDB? Yes, DuckDB's primary interface is SQL. If you already know SQL from working with Postgres, MySQL, or BigQuery, DuckDB's dialect will feel familiar — it adds modern extensions like QUALIFY, EXCLUDE, and direct file queries.

What's the memory crossover point? Roughly 500MB of source data, or 5 million rows depending on column count and query complexity. Below that, Pandas' in-memory approach is fast enough and SQL adds unnecessary ceremony. Above it, DuckDB's columnar engine and out-of-core execution start to dominate.

Try Fastero free — connect your databases and files, query across them with DuckDB under the hood, and build dashboards without a warehouse. No credit card required.