DuckDB Analytics
DuckDB is an embedded analytical database — SQLite for OLAP. It reads Parquet/CSV directly, runs columnar vectorized SQL, and ships as a single binary. For most "I'd reach for pandas" or "I'd spin up a warehouse for this" tasks, DuckDB is faster and simpler.
When to use
- Querying Parquet/CSV/JSON without loading into a database first.
- A pandas pipeline that's slow or won't fit in memory.
- Local data marts for dashboards (read-heavy, single-writer).
- Joining S3 data in place without ETL.
- Embedding analytical SQL into an app (CLI tool, Jupyter, Electron).
Core capabilities
Read Parquet directly
-- Single file
SELECT * FROM read_parquet('/data/events.parquet') LIMIT 10;
-- Glob over many files (single dataset)
SELECT count(*) FROM read_parquet('/data/events/*.parquet');
-- Hive-partitioned dataset
SELECT region, count(*) AS n
FROM read_parquet('/data/events/year=2026/month=*/day=*/*.parquet',
hive_partitioning = true)
GROUP BY region;
DuckDB pushes predicates and column projections into the Parquet reader — only the columns and row groups you need are read off disk.
Read S3
INSTALL httpfs;
LOAD httpfs;
SET s3_region='us-west-2';
SET s3_access_key_id='...';
SET s3_secret_access_key='...';
-- Or for IAM roles in EC2/ECS, AWS_PROFILE works.
SELECT * FROM read_parquet('s3://my-bucket/events/*.parquet') LIMIT 10;
For Cloudflare R2, use the S3-compatible endpoint: SET s3_endpoint='<account>.r2.cloudflarestorage.com';.
CSV with type inference
SELECT * FROM read_csv_auto('/data/exports/*.csv', sample_size = 100000);
-- Or explicit
CREATE TABLE orders AS
SELECT * FROM read_csv('/data/orders.csv',
header = true,
delim = ',',
columns = {'id': 'BIGINT', 'created_at': 'TIMESTAMP', 'total': 'DECIMAL(10,2)'}
);
read_csv_auto is great for exploration; lock down types in read_csv for production pipelines.
JSON
SELECT * FROM read_json_auto('/data/events.ndjson') LIMIT 10;
-- Specific path inside nested JSON
SELECT json_extract(payload, '$.user.id') AS user_id, count(*)
FROM read_json_auto('/data/events.ndjson')
GROUP BY 1;
Attach Postgres/MySQL/SQLite
INSTALL postgres;
LOAD postgres;
ATTACH 'host=db.example.com user=ro password=... dbname=app' AS pg (TYPE postgres);
-- Now query Postgres tables alongside Parquet.
SELECT u.name, count(*) AS events
FROM read_parquet('s3://events/*.parquet') e
JOIN pg.public.users u ON u.id = e.user_id
GROUP BY u.name;
This is a common pattern: production data in Postgres, log/event data in S3 Parquet, joins for analytics.
Export
COPY (SELECT * FROM big_query)
TO '/data/output/result.parquet' (FORMAT PARQUET, COMPRESSION ZSTD);
-- Partitioned
COPY (SELECT *, date_trunc('day', ts) AS day FROM events)
TO '/data/events_partitioned'
(FORMAT PARQUET, PARTITION_BY (day), OVERWRITE_OR_IGNORE);
ZSTD is the right default — better compression than snappy, similar speed.
Python integration
import duckdb
import pandas as pd
# Inline DataFrame querying — DuckDB sees pandas DataFrames as tables.
df = pd.read_csv('orders.csv')
result = duckdb.sql("SELECT region, SUM(total) FROM df GROUP BY region").df()
# Persistent file-backed database.
con = duckdb.connect('/data/marts/sales.duckdb')
con.sql("CREATE TABLE IF NOT EXISTS daily AS SELECT date_trunc('day', ts) AS day, count(*) AS n FROM read_parquet('s3://e/*') GROUP BY 1")
DuckDB+pandas is the common "throw out the warehouse" combo for analyst notebooks.
Aggregations and window functions
-- Top-3 products per region by revenue
SELECT * FROM (
SELECT
region, product, SUM(total) AS revenue,
ROW_NUMBER() OVER (PARTITION BY region ORDER BY SUM(total) DESC) AS rn
FROM orders
GROUP BY region, product
) WHERE rn <= 3;
-- 7-day moving average
SELECT
day,
AVG(orders) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7
FROM daily_orders
ORDER BY day;
Full window-function support; same SQL works in Postgres.
Performance — vectorized columnar
DuckDB processes data in vectors of ~1024 values at a time, columnar layout. Aggregations and projections are dramatically faster than row-store pandas equivalents on wide tables.
For a 100M-row aggregation that takes pandas 30s, DuckDB often does it in 2-5s with no special tuning.
MotherDuck (cloud sync)
-- Hybrid local + cloud DuckDB
ATTACH 'md:my_database' AS md (TYPE motherduck);
SELECT * FROM md.events LIMIT 100;
MotherDuck is hosted DuckDB with cloud storage. Useful for sharing a dataset with collaborators without exporting.
Anti-patterns
Using DuckDB for OLTP
Symptom: Concurrent writes corrupt or hang.
Diagnosis: DuckDB is single-writer. It locks the database file for write transactions.
Fix: OLTP belongs in Postgres/MySQL/SQLite-WAL. DuckDB is for analytics.
read_csv_auto in production
Symptom: Pipeline silently mistypes a column when input shape changes.
Diagnosis: Auto-detection is convenient but unstable across data shifts.
Fix: Lock types with read_csv(..., columns = {...}) in production. Auto for exploration.
Reading the entire file when you need one column
Symptom: Job slow despite Parquet being columnar.
Diagnosis: SELECT * reads all columns; predicate pushdown can't skip the columns you don't use.
Fix: Project only what you need: SELECT user_id, count(*) FROM read_parquet('...').
Loading 100M rows into pandas first
Symptom: OOM or 30+ minute runtimes.
Diagnosis: Pulling everything into memory before filtering.
Fix: Push the filter into DuckDB: duckdb.sql("SELECT * FROM read_parquet('...') WHERE …").df() only materializes the result.
Forgetting LOAD httpfs after INSTALL
Symptom: "IO Error: Could not access HTTP" on s3:// URL.
Diagnosis: INSTALL downloads the extension; LOAD activates it for this session.
Fix: Both, in order. Or use a persistent database: extensions persist across sessions.
Mixing CSV and Parquet without explicit casts
Symptom: Aggregations give wrong answers; types coerce silently.
Diagnosis: CSV is all strings unless you tell it otherwise; Parquet types are fixed.
Fix: Explicit CAST(col AS BIGINT) where types differ across sources.
Quality gates
NOT for
- OLTP — concurrent transactional workload.
- Distributed analytics at petabyte scale — Spark, Trino, BigQuery, Snowflake.
- Vector search — pgvector, Lance, Chroma; DuckDB has VSS but it's early.
- Streaming ingestion — DuckDB is batch-friendly; streaming requires external pipelines.
- Real-time multi-user dashboards — write contention; use a managed warehouse.