Master DuckDB: From Your First SQL Query to Analyzing Ten Million E‑Commerce Rows

Why DuckDB?

Many users start with Pandas and quickly hit memory limits as CSV files grow from a few hundred megabytes to several gigabytes. The article asks the fundamental question: why must a tens‑of‑gigabyte dataset be fully loaded into a Pandas DataFrame before any analysis?

DuckDB solves this by allowing direct scanning, filtering, joining, and aggregating of CSV, Parquet, and JSON files without loading the entire file into memory. Only the rows needed for the final result are materialized, dramatically reducing memory usage.

Installation and First Query

pip install duckdb

import duckdb
duckdb.sql("SELECT 42 AS answer").show()

The output shows a simple SQL engine running inside the Python process with no server, port, or credentials required.

Basic SQL Workflow

Start by reading a CSV as a table: SELECT * FROM read_csv('orders.csv'); Use DESCRIBE to verify column types before analysis. Then apply standard SQL clauses:

SELECT columns

WHERE filters (e.g., amount > 1000)

GROUP BY for aggregation

HAVING to filter aggregated results

ORDER BY and LIMIT for sorting and sampling

Example: City Revenue

SELECT city,
       SUM(amount) AS revenue
FROM read_csv('orders.csv')
GROUP BY city
ORDER BY revenue DESC;

This query aggregates sales by city directly on the CSV file.

GROUP BY ALL

DuckDB supports GROUP BY ALL, which automatically groups by every non‑aggregated column, reducing boilerplate when many columns are involved.

WHERE vs HAVING

WHERE

filters raw rows before aggregation, while HAVING filters after GROUP BY. The article illustrates both with concise examples.

CASE WHEN

Use CASE WHEN for conditional labeling, such as categorizing order value tiers.

SELECT order_id, amount,
       CASE WHEN amount >= 5000 THEN 'High'
            WHEN amount >= 1000 THEN 'Medium'
            ELSE 'Low' END AS order_level
FROM read_csv('orders.csv');

Date Truncation

Aggregate by month using DATE_TRUNC('month', order_date) to produce monthly metrics.

Working with Parquet and JSON

Parquet files are columnar and support projection and filter push‑down, making them ideal for large‑scale analysis. DuckDB can read them directly: SELECT * FROM read_parquet('orders.parquet'); JSON files can be queried with read_json('events.json'), useful for semi‑structured logs.

Analyzing Large Directories

DuckDB can glob multiple files: SELECT * FROM read_parquet('data/*.parquet'); It can also expose the filename column to diagnose problematic files.

Performance Benchmark vs Pandas

The article generates a synthetic ten‑million‑row dataset in DuckDB, exports it to Parquet, and runs an identical aggregation in both DuckDB and Pandas. Results on the author's machine:

DuckDB runtime: 0.018 s, peak memory ≈ 165 MB

Pandas runtime: 0.263 s, peak memory ≈ 1.8 GB

Key takeaways emphasize fair benchmarking: include file‑read time, use the same data source, warm‑up runs, and report hardware, software versions, and data size.

EXPLAIN and EXPLAIN ANALYZE

Use EXPLAIN to view the logical plan (e.g.,

PARQUET_SCAN → FILTER → PROJECTION → HASH_GROUP_BY → RESULT

) and EXPLAIN ANALYZE to obtain runtime statistics for each operator.

Memory and Thread Tuning

DuckDB allows setting the number of threads and a memory limit:

SET threads = 4;
SET memory_limit = '4GB';

More threads can increase parallelism but also raise memory pressure; the article warns that over‑allocating threads may cause swapping and slower performance.

Common Pitfalls

Assuming DuckDB can replace Pandas for all tasks—small datasets are still fine in Pandas.

Blindly trusting automatic type inference on CSV files; always run DESCRIBE and specify types when needed.

Treating IDs as integers when they are actually strings.

Using DOUBLE for monetary values instead of DECIMAL.

Writing value = NULL instead of value IS NULL.

Reading a Parquet file into Pandas before aggregating—DuckDB can aggregate directly.

Materializing huge intermediate results with .df() before aggregation.

Assuming more threads always yields better performance.

Believing DuckDB never OOM because it can spill; memory limits still matter.

Mixing one‑off analysis with persistent databases without clear intent.

Comparing only runtime without considering the full workflow (I/O, data preparation, etc.).

Writing monolithic 200‑line SQL queries instead of using CTEs, views, and clear naming.

When to Use DuckDB

Ideal scenarios: large CSV/Parquet files (hundreds MB to tens GB), heavy GROUP BY / JOIN workloads, multi‑file datasets, Jupyter notebooks, and as a pre‑processing layer before Pandas.

Less suitable: tiny datasets, high‑concurrency OLTP workloads, analyses that rely heavily on custom Python logic, or environments that already have a mature data warehouse.

One‑Page Cheat Sheet

Installation: pip install duckdb
Import: import duckdb
First query: duckdb.sql("SELECT 42").show()
Read CSV: SELECT * FROM read_csv('data.csv');
Read Parquet: SELECT * FROM read_parquet('data.parquet');
Glob Parquet: SELECT * FROM read_parquet('data/*.parquet');
Filter: WHERE amount > 1000
Sort: ORDER BY amount DESC
Group: GROUP BY city
Friendly: GROUP BY ALL
Join: FROM orders o LEFT JOIN users u ON o.user_id = u.user_id
CTE: WITH base AS (...) SELECT * FROM base;
Window: ROW_NUMBER() OVER (PARTITION BY city ORDER BY amount DESC)
Qualify: QUALIFY ranking <= 3
CSV → Parquet: COPY (SELECT * FROM read_csv('data.csv')) TO 'data.parquet' (FORMAT PARQUET);
Result to Pandas: duckdb.sql("...").df()
Query Pandas: duckdb.sql("SELECT * FROM df")
Persistent DB: con = duckdb.connect('analytics.duckdb')
View: CREATE VIEW orders AS SELECT * FROM read_parquet('orders/*.parquet');
Explain: EXPLAIN SELECT ...;
Explain Analyze: EXPLAIN ANALYZE SELECT ...;
Threads: SET threads = 4;
Memory limit: SET memory_limit = '4GB';

Final Thoughts

The key habit to adopt is to ask, “Do I really need to load the entire file into memory?” DuckDB lets you push the heavy lifting—scanning, filtering, joining, aggregating—into the query engine, returning only the small result set that Pandas or other tools can consume for visualization or modeling.