🦆 What is DuckDB?
DuckDB is an in-process analytical database. It runs entirely inside your Python process — no server to start, no daemon to manage, no Docker container needed. You import duckdb and start querying.
- Embeds directly in Python — no server, no connection string, no
host:port - Uses a columnar storage engine — reads only the columns your query needs, skipping the rest
- Supports full SQL including window functions, CTEs, and FILTER aggregates (PostgreSQL-compatible)
- Reads Parquet, CSV, and JSON natively from disk or S3 — no
LOAD DATAstep required
DuckDB vs SQLite — not the same thing
Both embed in your process and need no server. That is where the similarity ends.
| Dimension | SQLite | DuckDB |
|---|---|---|
| Storage layout | Row-oriented (B-tree pages) | Columnar (vectorised chunks) |
| Workload | OLTP — many small reads/writes | OLAP — few large scans with aggregations |
| Aggregate speed | Slow on wide tables — reads every column even if unused | Fast — reads only the columns referenced in your query |
| Window functions | Limited | Full SQL:2003 window functions |
| Parquet support | None (extension required) | Native — read_parquet() built-in |
| Ideal for | Mobile apps, config storage, embedded state | Data analysis, local ETL, BI queries on flat files |
⚖️ When to use DuckDB vs Spark
The single most important factor is data size. DuckDB processes data in-process on one machine. Spark distributes work across many executors. Both are excellent tools — used at the wrong scale, both are painful.
| Data size | DuckDB | Spark |
|---|---|---|
| < 1 GB | ✅ Fast — sub-second queries | Overkill — cluster startup adds 30–60 s overhead |
| 1 – 10 GB | ✅ Still fast — typically 1–15 s per query | Possible but unnecessary — single machine is fine |
| 10 – 100 GB | Memory-dependent — may spill to swap and slow down | ✅ Spark shines — distributes shuffle across executors |
| > 100 GB | ❌ Likely runs out of RAM — process crash or swap thrash | ✅ Spark — designed for this scale |
If the data fits on Wanjiku's laptop (16 GB RAM, 4 GB free for query processing), run DuckDB. If it doesn't, escalate to the shared Spark cluster. Most daily reconciliation jobs are well under 10 GB and run in DuckDB.
📦 Installing DuckDB
One pip command. No Java, no Hadoop configuration, no cluster provisioning.
Verify the install:
DuckDB is written in C++ and ships as a native Python extension (.so / .pyd). The Python interpreter links it directly — no subprocess, no socket, no JVM heap to tune. This is why it starts in milliseconds instead of the 20–30 seconds Spark needs to initialise a JVM.
💻 BoraPay DuckDB demo
The script below creates a BoraPay transactions table in memory, then runs three progressively complex queries: a daily volume summary with FILTER aggregates, a top-senders ranking, and a running total window function. A timing call at the end shows how fast DuckDB is on columnar data.
Three query patterns explained
FILTER aggregate
COUNT(*) FILTER (WHERE status='failed')counts only rows matching the filter — no subquery needed- PostgreSQL-compatible syntax — same SQL works on DuckDB, PostgreSQL, and modern SQLite
- More readable than
SUM(CASE WHEN ...)
Window function
SUM(amount_kes) OVER (ORDER BY date, txn_id)computes a running cumulative total- The
OVERclause means "apply this function across the whole result set, ordered by these columns" - No
GROUP BY— each row keeps its individual values plus the computed window result
📄 Reading Parquet directly
DuckDB's most powerful feature for data engineering is read_parquet(). It treats Parquet files on disk (or on S3) as virtual tables — no load step, no staging table, no ETL preamble. You query the file as if it were already in a database.
CREATE TABLE, no ETL.The glob pattern *.parquet scans all matching files in the directory as a single logical table. DuckDB uses Parquet's column metadata to skip column groups it does not need — reading only the data your SELECT references. On a 4 GB Parquet dataset selecting 2 of 20 columns, DuckDB may read less than 500 MB from disk.
S3 support
With the httpfs extension (bundled in DuckDB 1.0), the same read_parquet() call works against S3-compatible endpoints including MinIO:
🐼 DuckDB + Pandas interop
DuckDB and Pandas work together seamlessly. You can query a Pandas DataFrame with SQL directly — DuckDB sees the DataFrame as a virtual table by the same name as the Python variable.
DuckDB reads the Pandas DataFrame's memory buffer directly — no serialisation, no copy. This means you can load data with Pandas (rich CSV/Excel/API support) and then analyse it with DuckDB (fast columnar SQL) without any data movement cost. The .df() at the end returns the result as a new Pandas DataFrame for further manipulation or plotting.
Common interop patterns
- Load with Pandas (
pd.read_csv,pd.read_excel) → analyse with DuckDB SQL → back to Pandas for matplotlib/seaborn plots - Query Parquet with DuckDB → convert to Pandas for scikit-learn feature engineering
- Build a Pandas DataFrame from an API response → filter/aggregate with DuckDB → export to Parquet with
COPY ... TO ... (FORMAT PARQUET)
🏦 Kwame's local analytics stack at BoraPay
Real-world story: DuckDB replaces a Spark job
At BoraPay, Kwame runs daily reconciliation scripts with DuckDB. 4 GB of Parquet files, 12 SQL queries, total runtime: 8 seconds. Previously the same pipeline took 6 minutes on Spark on a shared cluster — including 2 minutes of cluster startup, JVM initialisation, and job scheduling overhead.
The switch to DuckDB also removed a dependency on the cluster team's availability during off-hours. Kwame now runs the reconciliation from his laptop the moment the nightly Parquet files land in MinIO.
The BoraPay local analytics stack Kwame uses for sub-10 GB workloads:
mc mirror)🔁 DuckDB in the modern data stack
One of the most popular uses of DuckDB outside of direct scripting is pairing it with dbt (data build tool). The dbt-duckdb adapter lets you run your dbt models locally with DuckDB as the execution engine, then deploy the same models to a cloud warehouse (BigQuery, Snowflake, Redshift) in production.
| Environment | dbt adapter | Use case |
|---|---|---|
| Local development | dbt-duckdb |
Fast iteration — run all models in seconds on a 500 MB sample dataset |
| CI pipeline | dbt-duckdb |
Run full model + test suite in GitHub Actions without a cloud warehouse |
| Production | dbt-bigquery or dbt-snowflake |
Full dataset — same SQL models, different adapter |
Wanjiku writes a new dbt model (e.g. fct_daily_reconciliation.sql). She runs dbt run --target dev locally against a 1-day Parquet sample using DuckDB — takes 3 seconds. Once the model looks correct, she pushes to CI where the same dbt run runs against a 30-day sample on DuckDB (30 seconds). On merge to main, the deploy pipeline runs dbt run --target prod against the full BigQuery dataset. One SQL file, three environments, zero rewriting.
⚠️ Limitations of DuckDB
DuckDB is excellent at what it does. It is not a general-purpose replacement for every data system. Know the limits before you reach them in production.
🚫 Common mistakes
df -h and your dataset size before running. If it's borderline, sample down to 10 % and time the query first.con.close() when done, or use a context manager: with duckdb.connect('mydb.duckdb') as con: — the connection closes automatically when the with block exits.🧠 Quiz — 3 questions
Next: learn Spark performance tuning for when DuckDB is not enough.
🎤 Interview Q&A
Use DuckDB when your dataset fits on a single machine — typically under 10 GB. DuckDB starts in milliseconds (no JVM, no cluster), runs SQL including window functions and CTEs, and reads Parquet natively. A query that takes 5 seconds in DuckDB on 4 GB of Parquet would take 2–3 minutes in Spark on the same data — most of that time is cluster startup, JVM initialisation, and task scheduling overhead.
Use Spark when your dataset exceeds the RAM of a single machine (roughly 10+ GB for typical laptops), when you need fault tolerance (Spark can retry failed tasks), or when you need to process streaming data with Structured Streaming. Spark's value is distributed execution — it earns its overhead only when data is too large to process on one node.
At BoraPay, the decision rule is simple: if it fits on Kwame's laptop, use DuckDB. If it doesn't, use Spark.
SQLite stores data in row order on B-tree pages: all columns of row 1, then all columns of row 2, and so on. When you run SELECT SUM(amount) FROM transactions, SQLite must read every page — even columns like sender and status that your query does not reference. On a wide table (many columns), most of the disk I/O is wasted reading irrelevant data.
DuckDB stores data in column order: all values of amount_kes together in one column group, all values of date together in another, and so on. The same SELECT SUM(amount_kes) query reads only the amount_kes column group from disk and skips every other column entirely. On a 20-column table where your query only touches 2 columns, DuckDB reads 10× less data.
Columnar storage also compresses far better than row storage: a column of 1 million integers of similar magnitude can be dictionary-encoded or delta-encoded down to a fraction of its raw size. SQLite's B-tree pages mix all column types together, making compression much less effective. These two properties — selective I/O and better compression — are why DuckDB is 10–100× faster than SQLite for analytical aggregations.
Install dbt-duckdb and configure a profiles.yml with type: duckdb pointing to a local .duckdb file or an in-memory database. Write your dbt models as standard SQL .sql files — the same SQL you would write for BigQuery or Snowflake. Run dbt run --target dev to execute all models locally in DuckDB.
The key workflow advantage: dbt-duckdb reads Parquet files directly as sources (using DuckDB's read_parquet() under the hood), so you can point your local dbt sources at a sample Parquet file on disk and develop against real data structures without needing a cloud warehouse. A full dbt model run that takes 20 minutes on BigQuery can take 10 seconds locally on a 500 MB sample in DuckDB.
For production, you switch to a different dbt adapter (dbt-bigquery, dbt-snowflake, etc.) via --target prod. The SQL models do not change — only the execution engine changes. This makes DuckDB an excellent local development and CI layer for dbt projects targeting cloud warehouses.
✅ Key takeaways
- DuckDB embeds in Python with no server, no JVM, and no configuration —
import duckdb; con = duckdb.connect()is all you need to start querying. - DuckDB's columnar storage engine reads only the columns your query references, making aggregations and scans 10–100× faster than row-based SQLite on analytical workloads.
read_parquet('path/*.parquet')queries Parquet files as virtual tables — noCREATE TABLE, no ETL, no load step; DuckDB pushes column pruning into the Parquet reader itself.- DuckDB and Spark serve different scales: DuckDB wins on single-machine datasets under 10 GB; Spark wins when data exceeds available RAM, when fault tolerance is required, or when you need streaming.
The foundation our data engineers stand on
Join the Best AI Program: The AI-Era Data Engineer — 5 months, fully online, with placement assistance. Week 1 is free.
Explore The AI-Era Data Engineer →