DuckDB: a warehouse in a single file

DuckDB is to analytics what SQLite is to transactions: a single-file database that runs in-process and speaks real SQL. You can point it at a CSV, a parquet file, or a pandas dataframe and query all of them with one dialect. That is why we use it here.

setup
bash
uv add duckdb pandas datasets matplotlib jupyterlab

We install through uv, a fast Python package manager that records every dependency in pyproject.toml. If you prefer pip, swap uv add for pip install and the same list works. That is the entire setup cost for the warehouse side of this course.

notebooks/data_engineering.ipynb
python
import duckdb
import pandas as pd

con = duckdb.connect('hackerone.duckdb')

frame = pd.DataFrame({'severity': ['low', 'critical', 'critical']})
result = con.sql("SELECT severity, COUNT(*) AS n FROM frame GROUP BY severity").df()
print(result)

This opens a persistent warehouse file on disk and runs a SQL query against a pandas dataframe without any intermediate table. The dataframe itself is queryable because DuckDB can read Python locals.

Notice what just happened. No server. No network. No cluster. You queried a dataframe with SQL and got a dataframe back. Your medallion rehearsal will use this exact pattern: pandas for transformation code, DuckDB for SQL, one file on disk as the warehouse.

DuckDB in your notebook

One process, one file, SQL on top of pandas. Everything you rehearse here ports to a real warehouse with mostly cosmetic changes.

No server. No network. SQL and dataframes share one process.

No. DuckDB speaks mostly standard SQL. The model you design here, the fact and dimension tables, the surrogate keys, and the marts port to Snowflake or BigQuery with mostly mechanical changes. The big win of rehearsing on DuckDB is that you get the model right before you burn warehouse credits on a design you will regret.

Flashcards: Flashcards

Loading practice…

Quiz: Quiz

Loading practice…

Checkpoint: Checkpoint: you can justify the pattern

Loading practice…