🦆 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.

Four things DuckDB does that matter
  • 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 DATA step 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
Mental model: SQLite is the database your phone app uses to persist user settings. DuckDB is the database your data pipeline uses to answer "what was yesterday's total transaction volume by city?" — fast.

⚖️ 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
Rule of thumb at BoraPay

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.

pip install duckdb
terminal Lesson 10 · install
verified output
Successfully installed duckdb-1.0.0

Verify the install:

python3 -c "import duckdb; print('duckdb', duckdb.__version__)"
terminal Lesson 10 · verify
verified output
duckdb 1.0.0
Why no JVM?

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.

borapay_duckdb_demo.py Lesson 10 · full demo · Python
verified output
=== BoraPay DuckDB Demo === -- Daily Transaction Volume -- date count total_kes failures 2024-01-15 3 18300.0 1 2024-01-16 3 51200.0 0 2024-01-17 3 48200.0 1 2024-01-18 1 7200.0 0 -- Top Senders -- sender txns sent_kes ACC101 3 78000.0 ACC102 2 31250.0 ACC106 1 7200.0 -- Running Total (Window Function) -- txn_id date amount_kes running_total TXN001 2024-01-15 5000.0 5000.0 TXN002 2024-01-15 12500.0 17500.0 TXN004 2024-01-16 32000.0 49500.0 TXN006 2024-01-16 18750.0 68250.0 TXN007 2024-01-17 6300.0 74550.0 TXN009 2024-01-17 41000.0 115550.0 TXN010 2024-01-18 7200.0 122750.0 Full-table scan (10 rows): 0.4 ms ← DuckDB is fast 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 OVER clause 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.

read_parquet_example.py Lesson 10 · Parquet query · Python
DuckDB treats Parquet files as virtual tables. No 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:

s3_parquet_example.py Lesson 10 · S3 / MinIO · Python

🐼 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_pandas.py Lesson 10 · Pandas interop · Python
Zero-copy: DuckDB reads from Pandas memory

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:

4
Reporting layer
Pandas → CSV export → Google Sheets (shared with finance team)
3
Transform layer
DuckDB SQL — aggregations, window functions, joins across Parquet files
2
Storage layer
Parquet files on local disk (mirrored from MinIO nightly via mc mirror)
1
Runtime
Python 3.12 + duckdb 1.0 + pandas — nothing else needed on the laptop

🔁 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
dbt + DuckDB workflow at BoraPay

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.

Single machine — fits in RAM or it fails
DuckDB processes data on one machine. All data must fit in RAM — or swap, slowly. On a 16 GB laptop with a 12 GB Parquet dataset, DuckDB will spill to swap and query time can jump from 10 seconds to 10 minutes. For datasets over 10 GB, use Spark or a cloud warehouse instead.
No fault tolerance — if the process dies, nothing is saved
DuckDB has no checkpointing or recovery mechanism. If a long-running query crashes (OOM, power loss, SIGKILL), there is no way to resume it. Spark, by contrast, can retry failed tasks from the last successful shuffle. For multi-hour jobs on large data, Spark's resilience is worth the overhead.
No streaming — batch only
DuckDB has no concept of micro-batching, watermarks, or streaming sources. It works entirely on data at rest. For real-time analytics — Kafka events, live transaction feeds, sub-second dashboards — use Kafka + Spark Structured Streaming. DuckDB is for historical and daily-batch analysis.

🚫 Common mistakes

Running DuckDB on a 50 GB dataset without checking available RAM
DuckDB will start the query confidently, then start spilling to swap disk around the 16 GB mark. Queries that took 8 seconds on 4 GB now take 45 minutes. Check df -h and your dataset size before running. If it's borderline, sample down to 10 % and time the query first.
Using DuckDB for concurrent write-heavy workloads
DuckDB supports one writer at a time. If two processes try to open the same DuckDB file for writing simultaneously, the second will get a file lock error. DuckDB is designed for OLAP reads and single-process writes — not for multi-writer OLTP patterns. Use PostgreSQL for that.
Forgetting to close the connection
An open DuckDB connection holds a file lock on the database file. Other processes cannot write to it until the connection is closed. Always call 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

Question 1 of 3
DuckDB is best suited for datasets of what size?
Question 2 of 3
DuckDB can query Parquet files directly without loading them into a table first. True or False?
Question 3 of 3
What is the main limitation of DuckDB compared to Spark?
0
/ 3
DuckDB fundamentals solid.

Next: learn Spark performance tuning for when DuckDB is not enough.

🎤 Interview Q&A

Easy When would you use DuckDB instead of Spark?
+

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.

Medium How does DuckDB's columnar storage compare to SQLite's row-based storage?
+

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.

Medium How would you use DuckDB in a dbt pipeline?
+

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 — no CREATE 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.
Africa & India's Best AI Academy
🤖 AI-first 🎓 Placement Assistance

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 →