DuckDB vs Pandas: Local Data Analysis Compared (2026)

The first time I ran a GROUP BY across 200 million rows on my laptop and got results in 1.4 seconds, I closed my terminal and re-ran it because I assumed something was broken. It wasn't. That was DuckDB doing what it was designed to do — running analytical SQL at warehouse speed inside a single Python process, no server, no Docker container, no cloud credentials.

I'd been using Pandas for this kind of work for years. The same aggregation on the same data in Pandas took 47 seconds and ate 12GB of RAM. DuckDB used 800MB and finished before I could switch to my browser tab.

This isn't a "Pandas is dead" post. I still use Pandas daily. But after two years of running both tools side by side, I have strong opinions about when each one belongs in your workflow.

What DuckDB actually is

DuckDB is an in-process OLAP database. The team at CWI Amsterdam (the same research lab that produced MonetDB) built it as "SQLite for analytics." That tagline undersells it.

Here's what makes it different from every other database you've used:

• No server. It runs inside your Python/R/Node.js process. import duckdb and you have a full analytical database engine.
• Columnar storage + vectorized execution. It processes data in batches of vectors rather than row-by-row. This is the same execution model that makes ClickHouse and BigQuery fast, except it's running on your laptop.
• Reads files directly. Point it at a Parquet file, a CSV, a JSON file, or a folder of them. No import step. No schema definition. SELECT * FROM 'sales_2024/*.parquet' WHERE region = 'EMEA' just works.
• Out-of-core processing. Your dataset is bigger than RAM? DuckDB spills to disk automatically. I've processed 80GB datasets on a machine with 16GB of memory.
• Zero dependencies. pip install duckdb gives you the entire engine. No C library conflicts, no system dependencies, no docker-compose.yml.

What Pandas actually is (and isn't)

Pandas is a DataFrame library. It gives you a Python object (pd.DataFrame) with methods for filtering, grouping, reshaping, and transforming tabular data. It's been the default data analysis tool in Python since around 2010.

But Pandas was never designed as a query engine. It was designed as a data structure with convenience methods. This distinction matters:

• It operates in memory. Your entire dataset has to fit in RAM (roughly 3-5x the file size after Pandas' internal representation overhead).
• It's single-threaded. That 16-core machine? Pandas uses one core.
• Operations are eager and row-oriented. Every .groupby().agg() materializes the full intermediate result before moving to the next step.

None of these are bugs. They're design choices that made Pandas flexible and easy to learn. But they become painful at scale.

The speed difference is not marginal

I ran these benchmarks on a 2024 MacBook Pro (M3 Pro, 36GB RAM) against a 50GB Parquet dataset of synthetic e-commerce events (1.2 billion rows):

# DuckDB: 2.1 seconds
duckdb.sql("""
    SELECT customer_id, 
           COUNT(*) as order_count,
           SUM(amount) as total_spent,
           AVG(amount) as avg_order
    FROM 'events/*.parquet'
    WHERE event_type = 'purchase' 
      AND event_date >= '2025-01-01'
    GROUP BY customer_id
    HAVING COUNT(*) > 5
    ORDER BY total_spent DESC
    LIMIT 1000
""")
 
# Pandas: crashed (OOM at 50GB)
# On a 5GB subset (120M rows): 34 seconds
df = pd.read_parquet('events/')
purchases = df[df['event_type'] == 'purchase']
purchases = purchases[purchases['event_date'] >= '2025-01-01']
result = (purchases.groupby('customer_id')
          .agg(order_count=('amount', 'count'),
               total_spent=('amount', 'sum'),
               avg_order=('amount', 'mean'))
          .query('order_count > 5')
          .sort_values('total_spent', ascending=False)
          .head(1000))

DuckDB processed the full 50GB. Pandas couldn't load it. On the subset Pandas could handle, DuckDB was 16x faster. On simpler operations (single-column filter), the gap shrinks to 5-10x. On complex multi-table joins, I've seen 100x differences.

The reason is architectural. DuckDB's vectorized columnar engine processes data the way analytical queries actually access it — scanning columns, not rows. It parallelizes across all available cores automatically. It pushes predicates down so it never reads data it doesn't need from Parquet files (predicate pushdown + column pruning).

Head-to-head comparison

Where DuckDB wins outright

Large file exploration. You get a 2GB CSV from a vendor. In Pandas, you load the whole thing into memory, wait 30 seconds, realize you need only three columns, and start over with usecols. In DuckDB:

import duckdb
 
# Instant schema inspection
duckdb.sql("DESCRIBE SELECT * FROM 'vendor_data.csv'")
 
# Query only what you need — reads only the relevant columns from disk
duckdb.sql("""
    SELECT product_id, SUM(quantity), AVG(unit_price)
    FROM 'vendor_data.csv'
    WHERE ship_date >= '2025-06-01'
    GROUP BY product_id
""")

Multi-file analysis. DuckDB treats a glob pattern as a table. SELECT * FROM 'logs/2025/**/*.parquet' unions all matching files automatically. No pd.concat([pd.read_parquet(f) for f in glob(...)]) dance.

Joins on large datasets. Joining two 500M-row tables is something DuckDB handles calmly. Pandas would need 60GB+ of RAM for the same operation.

Replacing "spin up a warehouse for this one query." I used to fire up a BigQuery console for ad-hoc analysis on exported data. Now I point DuckDB at the Parquet export locally. Faster feedback loop, no cloud costs, no IAM headaches.

Window functions. Running totals, rankings, lag/lead — these are one-liners in SQL window functions. In Pandas, they're achievable but verbose and easy to get wrong.

Where Pandas still wins

Complex row-level transformations. When you need to apply a regex extraction, call an API per row, or run custom business logic that doesn't map to SQL:

# This kind of thing is painful in SQL
df['extracted_domain'] = df['email'].str.extract(r'@(.+)$')
df['risk_score'] = df.apply(lambda row: custom_scoring_function(row), axis=1)
df['normalized_name'] = df['company_name'].str.lower().str.strip().str.replace(r'\s+', ' ', regex=True)

ML feature engineering. scikit-learn, XGBoost, PyTorch — they all expect NumPy arrays or Pandas DataFrames. Your feature engineering pipeline will likely live in Pandas regardless of where the data came from.

Quick prototyping on small data. If your CSV is 50,000 rows and you need to explore it interactively, Pandas is instant. The overhead of writing SQL doesn't pay off at this scale.

Reshaping operations. Pandas' .pivot_table(), .melt(), .unstack() methods handle complex reshaping concisely. DuckDB's SQL PIVOT works but is more verbose for multi-level operations.

Integration with plotting libraries. Matplotlib, Seaborn, Plotly — they all consume Pandas DataFrames natively. DuckDB results need a .df() call first (trivial, but it's an extra step in exploratory notebooks).

The "use both" pattern

Here's what my actual workflow looks like in 2026:

import duckdb
import pandas as pd
 
# DuckDB handles the heavy lifting
# Scans 40GB of Parquet, filters to 200K rows, joins with a dimension table
result = duckdb.sql("""
    SELECT 
        o.customer_id,
        c.segment,
        c.acquisition_channel,
        COUNT(*) as orders,
        SUM(o.revenue) as total_revenue,
        MAX(o.order_date) as last_order
    FROM 'orders/*.parquet' o
    JOIN 'customers.parquet' c ON o.customer_id = c.id
    WHERE o.order_date >= '2025-01-01'
      AND o.status = 'completed'
    GROUP BY o.customer_id, c.segment, c.acquisition_channel
    HAVING COUNT(*) >= 3
""").df()  # .df() converts to Pandas DataFrame
 
# Pandas handles the last mile
result['days_since_last_order'] = (pd.Timestamp.now() - result['last_order']).dt.days
result['ltv_bucket'] = pd.cut(result['total_revenue'], bins=[0, 100, 500, 2000, float('inf')], 
                               labels=['low', 'mid', 'high', 'whale'])
result['churn_risk'] = result['days_since_last_order'].apply(classify_churn_risk)
 
# Plot, export, feed to ML model — all standard Pandas territory

This pattern — DuckDB reduces billions of rows to thousands, Pandas handles the custom logic on the small result — is increasingly what I see in production notebooks. It plays to both tools' strengths.

DuckDB can also read Pandas DataFrames directly:

# Query an existing DataFrame with SQL
customers_df = pd.read_excel('customer_list.xlsx')
duckdb.sql("SELECT segment, COUNT(*) FROM customers_df GROUP BY segment")

No copying. DuckDB reads the DataFrame's underlying memory. This bidirectional interop is what makes the "use both" pattern feel natural rather than duct-taped.

DuckDB + Jupyter is replacing warehouse ad-hoc queries

The pattern I see spreading across data teams: instead of connecting to Snowflake/BigQuery for every ad-hoc exploration, analysts export the relevant tables to Parquet once, then run DuckDB in Jupyter locally. The feedback loop drops from seconds (network round-trip + queue time) to milliseconds. No credentials, no cost concerns, no shared cluster contention.

This doesn't replace the production warehouse. But for "let me quickly check if this hypothesis holds" — the most common data analysis motion — local DuckDB is faster end-to-end.

My recommendation

Start with this decision tree:

1. Dataset fits in memory AND you need complex Python transforms? Use Pandas.
2. Dataset is large OR your query is SQL-expressible (joins, aggregates, windows)? Use DuckDB.
3. Need both heavy aggregation AND custom Python logic? DuckDB first, Pandas second.
4. Building ML features from a large source? DuckDB to filter/aggregate to a manageable size, then Pandas for the feature engineering.

If you're a SQL person who's been forcing yourself to learn Pandas method chaining — DuckDB might be what you actually wanted. If you're a Pandas person who hits memory errors — DuckDB solves that without requiring you to set up a database server.

For teams that run local analysis as part of a larger analytics pipeline — connecting DuckDB queries to dashboards, databases, and AI-driven analysis — tools like Fastero wire these pieces together so the DuckDB-to-insight path doesn't dead-end in a notebook.

Further reading

If you're evaluating tools in this space, these related comparisons might help:

• Pandas vs Polars: DataFrames Compared — Polars is the other Pandas alternative worth knowing, built on Apache Arrow with lazy evaluation.
• ClickHouse vs TimescaleDB: Real-Time Analytics Compared — When you outgrow local analysis and need a dedicated analytics server.
• Streamlit vs Jupyter Notebooks: When to Use Each — Where your DuckDB/Pandas analysis lives: exploratory notebook or shared app.
• Grafana vs Python for Data Dashboards — Turning analytical results into monitoring and dashboards.

Try Fastero free — run Python analysis on your live data with built-in scheduling, triggers, and team sharing — no infrastructure to manage. No credit card required.