DuckDB: SQL on Your Laptop for Modern Data Science

Published August 2026 · Data Science · ~1,800 words

If you work with data in Python, you probably reach for pandas first. Load a file, filter rows, compute a group-by, export the result. It works. But when the file is large, the workflow breaks down: you must load everything into RAM, parse every column, and then process it in Python.

DuckDB changes this workflow. Instead of loading data into a DataFrame, you query the files directly — using SQL. The engine reads only the columns you need, applies filters at the storage level, and streams results back to Python.

Featured image: articles/081/featured-image.svg

Architecture diagram: articles/081/duckdb-workflow.svg

What Is DuckDB?

DuckDB is an in-process analytical database. Think of it as SQLite for analytics. Key characteristics:

• In-process — no server to start, no configuration file, runs inside your Python process
• Columnar engine — optimized for analytical queries, not row-by-row processing
• Reads files directly — CSV, Parquet, JSON, and more — no loading step required
• Standard SQL — window functions, CTEs, joins, subqueries, aggregations
• Python integration — results come back as pandas DataFrames, lists, or Arrow tables
• Zero dependencies — just pip install duckdb

import duckdb

con = duckdb.connect()
result = con.execute("SELECT COUNT(*) FROM 'data.parquet'").fetchone()
print(result[0])  # Works immediately — no load step

The Core Problem: Loading Everything Into RAM

The traditional pandas workflow:

import pandas as pd

# Step 1: Load entire file into RAM
df = pd.read_csv('sales_data.csv')  # 2 GB file = 2+ GB RAM

# Step 2: Parse every column (even ones you don't need)
# Step 3: Filter in Python
filtered = df[df['region'] == 'north']

# Step 4: Aggregate
result = filtered.groupby('category')['revenue'].mean()

Three problems:

1. Memory — the entire file must fit in RAM
2. Columns — pandas parses all columns, even if you only need two
3. Speed — filtering happens in Python, not at the storage level

The DuckDB Solution: Query Files Directly

With DuckDB, you skip the load step entirely:

import duckdb

con = duckdb.connect()

# Query CSV directly — no pd.read_csv() needed
result = con.execute("""
    SELECT category, AVG(revenue) as avg_revenue
    FROM read_csv_auto('sales_data.csv')
    WHERE region = 'north'
    GROUP BY category
    ORDER BY avg_revenue DESC
""").fetchdf()  # Returns a pandas DataFrame

print(result)

What happened:

• DuckDB read the CSV directly — no intermediate DataFrame
• It applied the WHERE filter at the storage level
• It computed the average without loading the full dataset into Python's memory
• The result is a small pandas DataFrame — only the aggregated output

Real Benchmark Numbers

All numbers in this article come from a reproducible benchmark on a 500,000-row × 8-column dataset.

Environment: Python 3.13.14, pandas 3.0.5, DuckDB 1.5.5, pyarrow 25.0.1

Count and Aggregation

Column Selection and Filtering

* When reading the entire dataset into a DataFrame, pandas with Parquet is often faster due to pyarrow's optimized reader. DuckDB's advantage appears in analytical queries — not full loads.

Window Functions and Joins

** For small in-memory joins, pandas merge is faster. DuckDB's advantage grows with dataset size and query complexity.

End-to-End Pipeline

The pipeline benchmark is especially meaningful: DuckDB reads Parquet, filters, aggregates, and writes the result — all without creating an intermediate pandas DataFrame.

Why DuckDB Is Fast: Three Optimizations

1. Column Pruning

When you select 2 columns, DuckDB reads only those 2 columns from disk. The other 6 are never touched.

# DuckDB reads only category and revenue
con.execute("""
    SELECT category, AVG(revenue)
    FROM 'data.parquet'
    GROUP BY category
""")

# pandas reads ALL columns, then discards 6
df = pd.read_csv('data.csv', usecols=['category', 'revenue'])

2. Predicate Pushdown

Filters are applied at the storage level, not after loading:

# DuckDB filters at storage — reads only matching rows
con.execute("""
    SELECT region, SUM(revenue)
    FROM 'data.parquet'
    WHERE category = 'alpha'  -- Applied BEFORE reading data
    GROUP BY region
""")

# pandas loads full dataset, THEN filters in Python
df = pd.read_csv('data.csv')
result = df[df['category'] == 'alpha'].groupby('region')['revenue'].sum()

3. Vectorized Execution

DuckDB processes data in batches (vectors), not one row at a time. This makes efficient use of CPU cache and enables parallel processing.

Practical Workflow: CSV to Parquet with DuckDB

import duckdb

con = duckdb.connect()

# Convert CSV to Parquet (one-time)
con.execute("""
    COPY (
        SELECT * FROM read_csv_auto('sales_data.csv')
    ) TO 'sales_data.parquet' (FORMAT PARQUET, COMPRESSION ZSTD)
""")

# Now query the Parquet file directly
top_products = con.execute("""
    SELECT product_name, SUM(revenue) as total_revenue
    FROM 'sales_data.parquet'
    WHERE sale_date >= '2026-01-01'
    GROUP BY product_name
    ORDER BY total_revenue DESC
    LIMIT 10
""").fetchdf()

print(top_products)

Query Multiple Parquet Files at Once

DuckDB can query multiple Parquet files as if they were a single table — a common pattern in data lake workflows:

# Query all Parquet files in a directory
result = con.execute("""
    SELECT month, SUM(revenue) as total
    FROM 'sales/2026/*.parquet'
    GROUP BY month
    ORDER BY month
""").fetchdf()

This works without loading all files into memory. DuckDB reads and processes each file, applying filters and aggregation as it goes.

Using DuckDB with pandas DataFrames

DuckDB can also query existing pandas DataFrames in memory:

import pandas as pd
import duckdb

# Load data with pandas
df = pd.read_parquet('large_data.parquet')

# Query the DataFrame using SQL
con = duckdb.connect()
result = con.execute("""
    SELECT category,
           COUNT(*) as cnt,
           AVG(revenue) as avg_rev
    FROM df
    WHERE flag = true
    GROUP BY category
""").fetchdf()

# result is a pandas DataFrame
print(type(result))  # pandas.core.frame.DataFrame

This is useful when you want SQL syntax for complex operations on data that's already in memory.

DuckDB vs pandas: When to Use Each

Installation and Setup

# Install
pip install duckdb

# That's it — no server, no config file
python -c "import duckdb; print(duckdb.__version__)"

DuckDB includes everything in a single package. No separate database server, no connection strings, no environment variables.

Common DuckDB SQL Patterns for Data Science

import duckdb
con = duckdb.connect()

# Summary statistics
con.execute("""
    SELECT
        COUNT(*) as n_rows,
        AVG(revenue) as mean_rev,
        STDDEV(revenue) as std_rev,
        MIN(revenue) as min_rev,
        MAX(revenue) as max_rev
    FROM 'data.parquet'
""").fetchdf()

# Percentiles
con.execute("""
    SELECT
        category,
        quantile_cont(revenue, 0.25) as p25,
        quantile_cont(revenue, 0.50) as median,
        quantile_cont(revenue, 0.75) as p75
    FROM 'data.parquet'
    GROUP BY category
""").fetchdf()

# Cross-tabulation
con.execute("""
    SELECT
        category,
        region,
        COUNT(*) as cnt,
        SUM(revenue) as total_rev
    FROM 'data.parquet'
    GROUP BY category, region
    ORDER BY category, total_rev DESC
""").fetchdf()

DuckDB in the Data Science Ecosystem

• Polars — DuckDB can query Polars DataFrames directly
• Apache Arrow — Zero-copy data exchange between DuckDB and Arrow
• PySpark — DuckDB can query PySpark DataFrames
• SQLAlchemy — DuckDB works as a SQLAlchemy backend
• Jupyter — Results display naturally in notebooks
• Cloud storage — S3, GCS, Azure Blob via httpfs extension

When DuckDB Is Not the Answer

DuckDB excels at analytical queries on structured data. It is not the best choice when:

• You need row-by-row processing — pandas apply() and generators are more natural
• You need complex data transformations — melt, pivot, explode are pandas strengths
• Your data is tiny — the SQL overhead isn't worth it for a 100-row DataFrame
• You need real-time streaming — DuckDB is batch-oriented
• You need transactional writes — DuckDB is optimized for reads

Best Practices

Further Reading

• Parquet vs CSV: Why Data Scientists Should Care — file format comparison
• Python Data Science Optimization for 16GB RAM — DuckDB as a memory-efficient alternative
• Local Python Docker Workspace for Students — setting up the environment
• Feature Engineering in the Age of AI — DuckDB in feature pipelines
• Why ML Models Fail in Production — DuckDB for data quality checks
• Imbalanced Datasets — SQL-based class distribution analysis
• Build a Private Local AI Assistant — DuckDB for local data queries

Conclusion

DuckDB is not a replacement for pandas — it is a complement. Pandas excels at data manipulation, shape transformations, and exploratory analysis. DuckDB excels at analytical queries on files that are too large to comfortably load into memory.

The practical workflow is simple: convert your data to Parquet once, query it with DuckDB for aggregations and filtering, and use pandas for the final result manipulation. You get SQL power without a database server, memory efficiency without distributed computing, and speed without complexity.