DuckDB: The Fastest Way to Build Local Data Pipelines in 2026

DuckDB is SQLite for analytics: a full SQL engine inside your Python process, with no server to manage. For local work and single-VM jobs — files on disk, a slice of S3, a Postgres table you need to join — it has become the default I reach for before pandas or Spark.

See it yourself: the SQL Playground runs SQLite compiled to WebAssembly in the browser. Same engine family, no install.

This post is the laptop-to-CI pattern I actually use: query files, write Parquet, optionally wrap the SQL in dbt. It is not a Spark replacement and it is not a multi-writer warehouse.

flowchart LR
  files[CSV / Parquet files] --> duck[DuckDB in-process]
  duck --> sql[SQL / Python]
  sql --> out[Local result or warehouse]

Query files directly — no loading step

# pip install duckdb
import duckdb

con = duckdb.connect()  # in-memory. Use a path for a persistent file.

result = con.sql("""
    select
        date_trunc('day', ordered_at) as day,
        country,
        sum(amount) as revenue
    from 'data/orders/*.parquet'
    where status = 'completed'
    group by all
    order by day
""").df()

No cluster, no CREATE TABLE before you can SELECT, no schema file unless you want one. DuckDB reads Parquet footers, pushes predicates down, and uses your cores. .df() is an optional exit into pandas. Stay in DuckDB if the next step is more SQL.

# Stay lazy-ish: a relation, not a pandas copy
rel = con.sql("select * from 'data/orders/*.parquet' where country = 'US'")
rel.show()
rel.write_parquet("data/us_orders.parquet")

It reads almost anything

select * from 'events.csv';
select * from 'logs/*.json.gz';
select * from read_parquet('s3://bucket/data/*.parquet');
select * from postgres_scan('host=db dbname=app', 'public', 'users');

The last two lines make DuckDB a small integration engine: join S3 files to a live Postgres table in one query. That is a legitimate pattern for a backfill laptop job. It is not a replacement for a stable CDC pipeline.

-- Explicit readers when inference is wrong
select *
from read_csv(
    'landing/orders.csv',
    header = true,
    columns = {
        'order_id': 'varchar',
        'amount': 'decimal(12,2)',
        'ordered_at': 'timestamp'
    }
);

Infer once, then pin types. The same lesson as Spark inferSchema: convenience on day one, silent type drift on day thirty.

A pipeline, not a notebook souvenir

Treat DuckDB like a tiny warehouse: extract (files), transform (SQL), load (Parquet or COPY).

from pathlib import Path
import duckdb

WAREHOUSE = Path("warehouse.duckdb")
OUT = Path("out/daily_revenue.parquet")


def run(day: str) -> None:
    con = duckdb.connect(str(WAREHOUSE))
    con.execute("""
        create or replace table daily_revenue as
        select
            date_trunc('day', ordered_at)::date as day,
            country,
            sum(amount) as revenue,
            count(*) as orders
        from read_parquet('data/orders/*.parquet')
        where ordered_at >= $1
          and ordered_at <  $1::date + interval '1 day'
          and status = 'completed'
        group by all
    """, [day])

    OUT.parent.mkdir(parents=True, exist_ok=True)
    con.execute(
        "copy daily_revenue to $1 (format parquet, overwrite true)",
        [str(OUT)],
    )
    con.close()


if __name__ == "__main__":
    run("2026-08-28")

Idempotent: same day, same output path, overwrite. Put run() behind Airflow or a CI job. Do not pass the DataFrame through XCom; pass the path. That habit is the same one as the Airflow tutorial.

flowchart LR
  src[Local CSV / Parquet] --> scan[DuckDB scan]
  scan --> xform[SQL transform]
  xform --> pq[COPY / write_parquet]
  pq --> lake[Partition folder]
  lake --> wh[Optional warehouse load]

Scan files, transform in SQL, write Parquet. The warehouse is an optional last hop, not the runtime.

-- Production batch: prune the glob, pin types, overwrite one output
copy (
    select
        order_id,
        customer_id,
        amount::decimal(12, 2) as amount,
        ordered_at::timestamp as ordered_at,
        country
    from read_parquet(
        'data/orders/dt=2026-08-30/*.parquet',
        hive_partitioning = true
    )
    where status = 'completed'
      and amount > 0
) to 'out/dt=2026-08-30/orders.parquet' (
    format parquet,
    overwrite true,
    compression zstd
);

A path filter is still a path filter. *.parquet at the lake root will scan last year. dt=2026-08-30/** will not.

Persistent file vs in-memory

duckdb.connect() is memory. duckdb.connect("warehouse.duckdb") is a file you can reopen tomorrow. Use the file when:

• dbt will materialize models into it.
• You want CREATE TABLE once and many ad-hoc queries.
• CI should leave an artifact for debugging a failed test.

Use in-memory when the process is short-lived and the source of truth is still the Parquet directory.

-- Useful once you have a file
pragma database_list;
checkpoint;

Copy or replace tables; do not invent a hand-rolled WAL story. DuckDB is not Postgres HA.

S3 and secrets (keep them out of SQL files)

con.execute("""
    create secret lake (
        type s3,
        provider credential_chain
    );
""")
# Or set key/secret from env in the job, never in git
con.sql("select count(*) from 's3://lake/orders/*.parquet'")

Credential chain / env vars. If you paste an access key into a .sql file, you will rotate it after the first intern clone.

dbt-duckdb: tests without a warehouse bill

pip install dbt-duckdb

# profiles.yml
my_project:
  target: local
  outputs:
    local:
      type: duckdb
      path: warehouse.duckdb
      threads: 4

Staging and marts follow the same rules as the dbt tutorial. You get unique / not_null tests, docs, and a DAG. Teams run this in CI containers and on a single VM for pipelines that do not need a cloud warehouse.

-- models/staging/stg_orders.sql
select
    id as order_id,
    customer_id,
    amount_cents / 100.0 as amount,
    created_at::timestamp as ordered_at
from read_parquet('../data/orders/*.parquet')

When you later move marts to Snowflake or BigQuery, the SQL mostly comes along. File-path sources become source() tables. That is the point of keeping logic in SQL.

Quality checks still apply

DuckDB will happily aggregate garbage. Add the same volume / uniqueness / null / range checks you would in pandas — in SQL or via dbt tests. The Python framework in data quality checks can run on .df() if you must; I prefer tests next to the models.

-- Fail the job if the grain broke
select
    case
        when count(*) = count(distinct order_id) then 1
        else 1 / 0
    end
from stg_orders;

Ugly, effective. dbt unique is the grown-up version.

Why it feels faster than pandas (no fake bake-off)

I am not going to paste a made-up “45 seconds vs 1.8 seconds” table. Your files, your cores, and your columns decide.

What is consistently true:

• DuckDB is columnar and vectorized. It does not build a 20-column pandas DataFrame to sum one column.
• It scans Parquet in place. pandas usually pays a load into RAM first.
• Parallelism is default, not a multiprocessing side quest.

If you need a number, time your query:

import time
t0 = time.perf_counter()
con.sql("select count(*) from 'data/orders/*.parquet'").fetchone()
print(time.perf_counter() - t0)

Compare that to the same aggregation after pd.read_parquet. Then believe your disk, not a blog chart.

Interop: Arrow, pandas, Spark (one way)

# Zero-copy-ish Arrow when the next tool speaks it
arrow_table = con.sql("select * from daily_revenue").arrow()

# Spark can read the Parquet you wrote. Do not expect to embed
# DuckDB inside every executor as your compute fabric.

DuckDB as a pre-agg on a sample, Spark as the cluster job, is a sane split. DuckDB as a hidden engine inside a giant Spark UDF is a science project.

Pitfalls

• One writer. Concurrent writes to the same .duckdb file will disappoint you. CI jobs get their own file or in-memory connection.
• SELECT * from wide Parquet. Column pruning works when you name columns. Habits from notebooks leak.
• Holding .df() on a 40 GB result. You just bought pandas RAM problems again. Aggregate first, or write Parquet.
• Using DuckDB as the system of record for multi-team gold tables. No catalog, no warehouse RBAC, no concurrency story that matches Snowflake / BigQuery.
• Globbing * on a lake prefix with years of data. You will scan more than you meant. Partition path filters are still your job (dt=2026-08-28/**).

When NOT to use DuckDB

• Many concurrent writers or a BI fleet hitting one file.
• Data that does not fit on one machine’s disk / RAM working set in a way you can partition down. Graduate to Spark or a warehouse — PySpark tutorial.
• You need governed sharing, roles, and time travel as a product feature. Use the warehouse or Delta / Iceberg on object storage.
• The “pipeline” is already three SQL queries inside BigQuery. Stay there. DuckDB is for local and edge compute, not for relocating a happy warehouse.

FAQ

Should analysts point a BI tool at warehouse.duckdb? No. One writer, no warehouse RBAC, no concurrency story. Export Parquet or load a real warehouse if humans will query it all day.

In-memory or a file in CI? In-memory if the job is short and Parquet is the source of truth. A file if dbt needs to materialize and you want an artifact after a failed test. Do not share one file across parallel jobs.

Can two Airflow tasks write the same .duckdb file? They can try. You will get lock errors or a corrupt file. One writer, or each task gets its own file or in-memory connection.

Does DuckDB replace Spark at 200 GB? Only if 200 GB is a partition you can prune to a single-node working set. A full shuffle-shaped join is still a cluster job. Take the SQL with you.

Why did a glob of *.parquet scan last year? You asked it to. Partition path filters are your job (dt=2026-08-30/**). DuckDB will not invent Hive prune from a comment in the DAG.

Production checklist

• SQL in files or dbt models, not only a notebook.
• Types pinned on CSV / JSON. Parquet schemas reviewed once.
• Output path overwritten per batch / day. Retries are safe.
• Secrets via env or a secret manager, never committed.
• Tests: grain uniqueness, not-null on keys, a freshness or volume assertion if this feeds anyone.
• Persistent .duckdb only if you have a backup / rebuild story from Parquet.
• A written “we will move to X when Y” — disk full, second writer, or SLA that needs a cluster.

DuckDB replaced pandas in my local loops because I already think in SQL and I was tired of loading files just to filter them. Keep that honesty: it is an engine in a process. When the process is not enough, take the SQL with you and leave the .duckdb file behind.