Lesson 09 — DuckDB Locally

The analytical engine that lives next to your Parquet.

Where Module 4 fits

What DuckDB is

• An embedded analytical (OLAP) database — no daemon, no port, no config file.
• A columnar, vectorised engine that processes data in chunks for CPU-cache efficiency.
• Runs in-process (same way SQLite does for OLTP) — one shared object, loaded on import duckdb.
• Reads Parquet, CSV, JSON, Iceberg, and Postgres tables natively — no ETL step.
• Ships as a single pip install duckdb. No daemon, no port, no pg_ctl start.

Why it matters for pipelines

• Replaces the "spin up Postgres / Redshift / Snowflake just to GROUP BY" anti-pattern in dev and CI.
• Lets you write the same SQL in unit tests, in scratch notebooks, and in Airflow tasks — one dialect everywhere.
• Pushes filter and projection predicates down into Parquet’s row groups — often orders of magnitude faster than loading the whole file into pandas first.
• No dollars-per-developer-per-month cloud cost during development.

Hello world — query Parquet directly

import duckdb

con = duckdb.connect(":memory:")
con.execute("""
    SELECT type, count(*) AS n
    FROM read_parquet('data/parquet/date=*/*.parquet',
                      hive_partitioning=true)
    GROUP BY type
    ORDER BY n DESC
""").fetchall()

No LOAD, no COPY, no DDL. DuckDB reads the partition files in place and respects the Hive-style date=YYYY-MM-DD/ directory layout for predicate pushdown.

Predicate pushdown in 2 minutes

SQL: WHERE date = '2024-01-01'
        |
        v
  DuckDB partition discovery
  (reads directory listing only)
        |
        v
  Opens ONLY date=2024-01-01/*.parquet
        |
        v
  Reads Parquet row-group statistics
  (min/max per column per chunk)
        |
        v
  Decompresses ONLY matching pages
  for the columns in SELECT

The whole-file-scan alternative reads ∼1000× more bytes. Partition pruning + row-group skipping + column projection stack together.

DuckDB vs SQLite vs pandas

The takeaway: DuckDB is the SQLite-of-analytics. Same install footprint, OLAP semantics.

The pipeline-embedding pattern

The shared helper

from sample_pipeline.duckdb.query import open_pipeline_db

with open_pipeline_db() as con:
    rows = con.execute(
        "SELECT type, count(*) FROM events GROUP BY type"
    ).fetchall()

The same open_pipeline_db() pattern is used in count_by_type.py and top_repos.py in the sample pipeline. One view definition, tested once, reused everywhere.

What DuckDB is not

• Not a transactional database. Concurrent writers with low-latency commits: use Postgres / MySQL / SQLite. DuckDB supports one writer at a time on a .duckdb file.
• Not a distributed query engine. When one machine runs out of RAM, the answer is Spark or Trino — not “add DuckDB nodes” (no such thing). DuckDB’s design constraint is explicitly single-node.
• Not a metastore. No CREATE TABLE that persists across runs unless you point it at a .duckdb file. The L10 Iceberg lesson is the persistence answer.

Concurrency notes

The in-memory pattern used in query.py is always safe in multi-worker Airflow: each task process gets its own Python interpreter and its own DuckDB connection object.

Tying it to the pipeline

• L09 adds sample-pipeline/duckdb/ with query.py, count_by_type.py, top_repos.py — the CLI scripts you run in the lab today.
• The L10 Iceberg lesson layers an Iceberg metastore over the same Parquet root and exposes a DuckDB-on-Iceberg reader (query_duckdb.py). Same engine, richer table format.
• The L11 cloud-warehouse lesson keeps DuckDB as the Track B fallback for readers without a paid cloud account — so nothing you learn today is discarded.

Recap & what's next

• DuckDB is the in-process OLAP engine — one-line install, no daemon, no port.
• Queries Parquet with predicate pushdown — no DDL needed; filters prune partitions before any file is opened.
• Embed it via open_pipeline_db() — one helper, one view definition, used identically in CLI scripts, tests, and Airflow tasks.