Python Doc & Data Automation

Query Large CSV Files with DuckDB

Answering "revenue by region for September, excluding cancelled orders" from a 9 GB CSV means writing a chunk loop: read 500,000 rows, filter, group, keep partial results, combine at the end, and hope the combination logic is right for every aggregation. Each new question means another loop. Loading the file once into pandas is not an option — it needs more memory than the machine has — and moving it into a database means provisioning one, defining a schema and maintaining a load job for a file that is regenerated every night anyway.

Root Cause

pandas is an in-memory engine: it must materialise what it reads, so a file larger than memory forces manual chunking, and every chunked aggregation reimplements a piece of a query planner. The chunk loop is not just tedious — it is where correctness errors live, because some aggregations do not combine by simple addition (averages, medians, distinct counts). What the task actually needs is a query engine that streams data from disk, applies projections and filters while reading, and handles the combination of partial aggregates itself. DuckDB provides exactly that in-process: it reads CSV, Parquet and JSON directly from the file system, pushes filters and column selection into the scan, spills to disk when a query exceeds memory, and returns results as pandas DataFrames or Arrow tables.

Minimal Diagnostic

Measure the two approaches on a real question before rewriting anything: peak memory and wall time for the chunked pandas loop versus the same query in DuckDB.

# pip install duckdb "pandas>=2.2"
import time
import tracemalloc
from pathlib import Path
import duckdb
import pandas as pd

SOURCE = Path("in/transactions-2026.csv")

def pandas_chunks() -> pd.DataFrame:
    parts = []
    for chunk in pd.read_csv(SOURCE, chunksize=500_000, usecols=["region", "status", "booked_at", "amount"],
                             parse_dates=["booked_at"]):
        chunk = chunk[(chunk["status"] != "cancelled") & (chunk["booked_at"].dt.month == 9)]
        parts.append(chunk.groupby("region", observed=True)["amount"].sum())
    return pd.concat(parts).groupby(level=0).sum().rename("revenue").reset_index()

def duckdb_query() -> pd.DataFrame:
    return duckdb.sql(f"""
        SELECT region, sum(amount) AS revenue
        FROM read_csv_auto('{SOURCE}')
        WHERE status <> 'cancelled' AND month(booked_at) = 9
        GROUP BY region ORDER BY revenue DESC
    """).df()

for name, fn in (("pandas chunks", pandas_chunks), ("duckdb", duckdb_query)):
    tracemalloc.start()
    start = time.perf_counter()
    result = fn()
    peak = tracemalloc.get_traced_memory()[1] / 1e6
    tracemalloc.stop()
    print(f"{name:<14} {time.perf_counter() - start:6.1f}s  peak python memory {peak:7.1f} MB  rows {len(result)}")

pandas chunks    186.4s  peak python memory  1180.3 MB  rows 5
duckdb            21.7s  peak python memory     1.9 MB  rows 5

DuckDB reads the same file roughly eight times faster and returns a five-row result without materialising anything in Python. The memory figure is only Python's allocations — DuckDB manages its own buffer pool, which respects a configurable limit.

Chunk loop versus SQL over the file The pandas approach reads every chunk, filters in Python, aggregates per chunk, concatenates the partial results and re-aggregates, taking 186 seconds and over a gigabyte of Python memory. The DuckDB approach expresses the same question as one SQL statement that pushes the column selection and filter into the CSV scan, taking 22 seconds and returning five rows directly into pandas. pandas chunk loop for chunk in read_csv(chunksize): filter, groupby, keep partial concat + re-aggregate 186 s, 1.2 GB peak combination logic per aggregate DuckDB SQL SELECT region, sum(amount) FROM read_csv_auto(file) WHERE ... GROUP BY region 22 s, 2 MB peak engine handles combination

Fix: Query the Files Directly, with Types Declared

Point DuckDB at the file, declare the column types that matter, set a memory limit, and hand the small result to pandas. Changed lines carry comments.

# pip install duckdb "pandas>=2.2"
from pathlib import Path
import duckdb
import pandas as pd

SOURCE = Path("in/transactions-2026.csv")

def connect(memory_limit: str = "4GB", threads: int = 4) -> duckdb.DuckDBPyConnection:
    con = duckdb.connect()                                     # in-process, no server
    con.execute(f"SET memory_limit='{memory_limit}'")          # changed: bound memory, spill to disk
    con.execute(f"SET threads={threads}")
    con.execute("SET preserve_insertion_order=false")          # changed: lets large scans stream freely
    return con

def revenue_by_region(con: duckdb.DuckDBPyConnection, path: Path, month: int) -> pd.DataFrame:
    return con.execute(
        """
        SELECT region,
               count(*)                       AS orders,
               sum(amount)                    AS revenue,
               count(DISTINCT customer_ref)   AS customers      -- correct across the whole file
        FROM read_csv(?, header=true, types={'customer_ref': 'VARCHAR', 'amount': 'DOUBLE'},
                      timestampformat='%Y-%m-%d %H:%M:%S')      -- changed: declared types, no guessing
        WHERE status <> 'cancelled' AND month(booked_at) = ?
        GROUP BY region
        ORDER BY revenue DESC
        """,
        [str(path), month],                                     # changed: parameters, not string formatting
    ).df()                                                      # changed: small result into pandas

if __name__ == "__main__":
    with connect() as con:
        print(revenue_by_region(con, SOURCE, 9).to_string(index=False))

        region  orders     revenue  customers
        EMEA   812043  18904221.55      44120
North America   611209  14220118.02      31877
         APAC   402118   9014772.10      20441

count(DISTINCT customer_ref) is the aggregation that chunk loops get wrong most often — distinct counts cannot be summed across chunks — and DuckDB computes it correctly across the whole file without extra code. Declaring customer_ref as VARCHAR prevents the sampling-based type detection from deciding it is an integer because the first rows look numeric. Parameter binding keeps file paths and values out of the SQL string, which matters as soon as any part of the query comes from configuration.

What DuckDB does with the query DuckDB parses the SQL and determines that only four of the twenty columns are referenced, so the CSV scan reads and parses only those. The filter on status and month is applied during the scan, so rejected rows never reach the aggregation. Partial aggregates are computed per thread over ranges of the file and combined by the engine, including distinct counts. The five-row result is converted to a pandas DataFrame. Projection pushdown only the four referenced columns are parsed from each row Filter pushdown status and month filters applied during the scan Parallel partial aggregates per-thread hash aggregation over file ranges Correct combination sums add, distinct counts merge sketches, averages recombine Small result to pandas df() materialises only the final rows

Variant Fix 1: A Folder of Files, and Joins Between Them

DuckDB globs paths, so a folder of monthly exports is one table, and files of different formats join directly:

# pip install duckdb
import duckdb

with duckdb.connect() as con:
    con.execute("SET memory_limit='4GB'")
    result = con.execute("""
        WITH tx AS (
            SELECT * FROM read_csv('in/exports/transactions-*.csv',
                                   union_by_name=true,          -- tolerate column order changes
                                   filename=true)               -- keep the source file name
        ),
        customers AS (
            SELECT * FROM read_parquet('reference/customers.parquet')
        )
        SELECT c.segment,
               date_trunc('month', tx.booked_at) AS month,
               sum(tx.amount) AS revenue
        FROM tx
        JOIN customers c ON c.customer_ref = tx.customer_ref
        WHERE tx.status <> 'cancelled'
        GROUP BY ALL
        ORDER BY month, revenue DESC
    """).df()
print(result.head())

union_by_name=true matches columns by header rather than position, which keeps a supplier's reordered export from silently shifting data. filename=true adds the source path as a column — invaluable when one file in a folder turns out to be malformed. Joining a CSV against a Parquet reference table needs no import step for either side.

Variant Fix 2: Results Too Large for Memory

When the result itself is large — a cleaned, filtered extract rather than a summary — write it straight to Parquet or CSV without passing through Python:

# pip install duckdb
import duckdb

with duckdb.connect() as con:
    con.execute("SET memory_limit='4GB'")
    con.execute("""
        COPY (
            SELECT customer_ref, booked_at, amount, region
            FROM read_csv('in/transactions-2026.csv',
                          types={'customer_ref': 'VARCHAR'})
            WHERE status <> 'cancelled'
        ) TO 'out/clean-transactions.parquet' (FORMAT parquet, COMPRESSION zstd)
    """)

For iterative work, con.execute(sql).fetch_record_batch() streams Arrow batches into Python so a large result can be processed incrementally. Persisting intermediate results into a DuckDB database file (duckdb.connect("analysis.duckdb")) also avoids re-scanning the CSV for every question during exploration — and once the data lives in Parquet, subsequent queries are faster still, as covered in convert a large CSV to Parquet with Python.

Which tool for which job pandas in memory suits files that fit comfortably, offers full flexibility and no setup, and computes aggregations correctly. pandas chunk loops handle larger files but require manual combination logic that is error-prone for distinct counts and medians. DuckDB handles files far larger than memory with SQL, needs only a pip install, and combines aggregates correctly. A database server suits shared, concurrent, long-lived data but requires provisioning and a load process. Approach Bigger than memory Setup Aggregation correctness pandas in memory no none correct pandas chunk loop yes none manual combination DuckDB yes pip install engine handles it Database server yes provision and load correct

Using DuckDB Alongside pandas

DuckDB reads pandas DataFrames as tables by name, so existing pandas code can stay where it is useful and hand heavy steps to SQL:

# pip install duckdb "pandas>=2.2"
import duckdb
import pandas as pd

regions = pd.DataFrame({"region": ["EMEA", "APAC"], "manager": ["J. Patel", "M. Chen"]})

with duckdb.connect() as con:
    summary = con.execute("""
        SELECT r.manager, sum(t.amount) AS revenue
        FROM read_csv('in/transactions-2026.csv', types={'customer_ref': 'VARCHAR'}) t
        JOIN regions r ON r.region = t.region        -- the pandas DataFrame, referenced by variable name
        WHERE t.status <> 'cancelled'
        GROUP BY r.manager
    """).df()
print(summary)

The DataFrame is read in place, without a copy, which makes DuckDB a practical way to join a small reference table held in pandas against a file too large to load. Keep the split clear: SQL for the heavy scan, filter, join and aggregate; pandas for the small result, formatting and export, as in Building Pivot Tables and Summaries for Excel.

Verification

Check DuckDB's answers against a pandas computation on a sample small enough to load, and assert that row counts and totals reconcile with the file.

# pip install duckdb "pandas>=2.2"
from pathlib import Path
import duckdb
import pandas as pd

def verify_against_pandas(path: Path, sample_rows: int = 200_000) -> None:
    sample = pd.read_csv(path, nrows=sample_rows, dtype={"customer_ref": "string"},
                         parse_dates=["booked_at"])
    expected = (sample[(sample["status"] != "cancelled") & (sample["booked_at"].dt.month == 9)]
                .groupby("region", observed=True)["amount"].sum().sort_index())
    with duckdb.connect() as con:
        actual = con.execute("""
            SELECT region, sum(amount) AS revenue
            FROM (SELECT * FROM read_csv(?, types={'customer_ref': 'VARCHAR'}) LIMIT ?)
            WHERE status <> 'cancelled' AND month(booked_at) = 9
            GROUP BY region ORDER BY region
        """, [str(path), sample_rows]).df().set_index("region")["revenue"]
    pd.testing.assert_series_equal(expected, actual, check_names=False, rtol=1e-9)

    with duckdb.connect() as con:
        rows = con.execute("SELECT count(*) FROM read_csv(?)", [str(path)]).fetchone()[0]
    with path.open("rb") as fh:
        line_count = sum(chunk.count(b"\n") for chunk in iter(lambda: fh.read(1 << 24), b""))
    assert abs(rows - (line_count - 1)) <= 1, f"DuckDB counted {rows} rows, file has ~{line_count - 1} lines"
    print(f"verified: {rows:,} rows, sample aggregation matches pandas")

The line-count check catches the case where a quoting or delimiter misconfiguration makes DuckDB see a different number of rows than the file contains — the same class of problem as a CSV dialect mismatch on export. Values containing embedded newlines make the counts differ legitimately, so treat a small difference as a prompt to inspect rather than an automatic failure.

FAQ

Does DuckDB need a server or a schema? No. It runs in the Python process and reads files directly; a persistent database file is optional.

Can it read compressed CSVs? Yes — .gz, .zst and .bz2 are detected automatically, including inside globs.

What if type detection gets a column wrong? Pass types={...} for those columns, or all_varchar=true to read everything as text and cast explicitly in SQL.

Is the result exactly what pandas would produce? Floating-point sums can differ in the last bits because of different summation order; compare with a tolerance rather than exact equality.

Part of Handling Large CSV Files with Chunking.