DuckDB Locally

Lesson 09 · Data Engineering with Python · ~15 min read

1. The local-analytical-engine niche

For OLTP work the standard answer for two decades has been SQLite — embedded, file-based, zero-config, “good enough for 99% of small databases.” There was no equivalent for OLAP. If you wanted columnar or vector semantics with pushdown you stood up Postgres with plugins, ran Spark on a laptop, or loaded a CSV into pandas and hoped your RAM held out. DuckDB closes that gap: an in-process columnar engine with a Postgres-flavoured SQL dialect, zero-config install, and native readers for Parquet, CSV, JSON, and Iceberg.

For data engineering it is the right default for everything from ad-hoc exploration to per-task Airflow scans to dbt model materialisation locally. The same pip-installable library that answers a one-off question in a notebook also powers the transformation layer in a production DAG — identical SQL, identical semantics, no spin-up cost and no dollars-per-developer cloud bill during development.

DuckDB was created at CWI Amsterdam and first released publicly in 2019. It is deliberately narrow in scope: one machine, one process, OLAP only. That constraint is a feature — simplicity is what makes it reliable and embeddable.

2. What “embedded” actually means

No daemon. No port. No pg_ctl start. The entire setup is:

import duckdb
con = duckdb.connect(":memory:")

The library loads as a shared object into your Python process; query results materialise as Python lists, Arrow tables, or pandas DataFrames; the engine never crosses a process boundary unless you explicitly point it at a .duckdb file or a remote source. Contrast with the “spin up Redshift in dev/test” workflow, which adds minutes of setup per CI run and dollars per developer per month.

The .duckdb file form (a persisted on-disk database) exists and is useful — it persists tables across Python sessions the way SQLite persists OLTP state — but it is not what this lesson uses. Pipelines almost always want the in-memory form: cheap to create, isolated per task, deterministic across re-runs. Reach for the file form only when you are building a long-lived local analyst dashboard or a catalog that must survive process restarts.

3. Reading Parquet directly

The most-used DuckDB feature for data engineering is the read_parquet table function. Three call shapes you will use across this course:

-- Single file
SELECT * FROM read_parquet('data/parquet/date=2024-01-01/part-0.parquet');

-- Glob across partitions, Hive-aware
SELECT * FROM read_parquet('data/parquet/date=*/*.parquet',
                            hive_partitioning = true);

-- View shortcut so downstream SQL stays clean
CREATE OR REPLACE VIEW events AS
SELECT * FROM read_parquet('data/parquet/date=*/*.parquet',
                            hive_partitioning = true);

The hive_partitioning = true flag makes DuckDB parse the directory layout (date=YYYY-MM-DD/) and expose date as a real column. Predicates against date then prune partitions before any file is opened. Without the flag DuckDB still reads the data but loses partition-pruning and has to open every Parquet file to discover row-group statistics.

The CREATE OR REPLACE VIEW is a thin name alias — it costs almost nothing to create and the underlying scan is deferred to actual query time. Using a view means the rest of your SQL never mentions the path glob; it just says FROM events.

4. Predicate pushdown and why it’s the whole point

The reason DuckDB routinely outperforms “load this Parquet into pandas and filter” by orders of magnitude is layered pushdown:

1. Partition pruning — WHERE date BETWEEN X AND Y skips entire directories; no file in the excluded partitions is even opened.
2. Row-group skipping — Parquet stores min/max statistics per row group; DuckDB reads them first and skips groups outside the filter range without decompressing the data.
3. Page-level decoding — only the columns named in SELECT are decompressed; unused columns never leave disk.
4. Vector execution — the engine processes ∼2 048-row vectors at a time so CPU caches stay hot and SIMD instructions can fire.

5. Embedding DuckDB in pipeline scripts

The pattern this lesson teaches and the sample-pipeline implements is a thin helper module:

# sample-pipeline/duckdb/query.py
from pathlib import Path
import duckdb

PARQUET_ROOT = (Path(__file__).resolve().parent.parent / "data" / "parquet").as_posix()

def open_pipeline_db() -> duckdb.DuckDBPyConnection:
    con = duckdb.connect(":memory:")
    con.execute(
        f"""
        CREATE OR REPLACE VIEW events AS
        SELECT *
        FROM read_parquet('{PARQUET_ROOT}/date=*/*.parquet',
                          hive_partitioning = true);
        """
    )
    return con

Every pipeline script that needs analytical access imports open_pipeline_db() and runs SQL against the events view. The benefits:

• Test parity. Tests can monkeypatch PARQUET_ROOT to a fixture directory and the rest of the SQL is unchanged.
• One source of truth for the schema view. If you rename a column, you fix query.py once instead of every Airflow task.
• Airflow-friendly. Each task instance gets its own in-memory connection — no cross-task contention, no .duckdb file lock to manage.
• dbt-friendly. The dbt-duckdb adapter from L08 uses the same SQL dialect; a snippet that runs in count_by_type.py will run in a dbt model with minor wrapping.

6. What DuckDB is not

Three “do not use for” patterns to internalise:

• OLTP. Concurrent writers with low-latency transactions: use Postgres / MySQL / SQLite. DuckDB’s MVCC supports a single writer at a time on a .duckdb file.
• Distributed scale. When one machine’s RAM cannot fit a working set, the answer is Spark, Trino, BigQuery, or Snowflake — not “DuckDB cluster” (no such thing exists). DuckDB’s roadmap explicitly calls single-node the design constraint.
• Metastore. DuckDB does not maintain a long-lived table catalog across runs the way Iceberg or Hive Metastore do. The events view in query.py is rebuilt every script invocation. Lesson 10 introduces Iceberg specifically to close that gap — a persistent table format DuckDB can read.

7. Common pitfalls

References

• DuckDB documentation — duckdb.org/docs/. The Reading Parquet, Pragmas, and Performance Guide pages are essential.
• DuckDB: An Embeddable Analytical Database — Raasveldt & Mühleisen, SIGMOD 2019. The original paper describing the design rationale.
• L02 of this course — data-engineering/lesson02-parquet-and-columnar/theory.html — the Parquet layout this lesson reads.
• L08 of this course — data-engineering/lesson08-dbt-intro/theory.html — dbt + DuckDB pairing; the same SQL dialect.
• L10 of this course — data-engineering/lesson10-iceberg-tables/theory.html — the table format layer that adds schemas, snapshots, and ACID over the same Parquet root.