duckdb
Query local CSV and Parquet files with DuckDB SQL, aggregate and join datasets, export analytical results, and troubleshoot schema drift or memory limits.
SKILL.mdskills/duckdb/SKILL.md
GitHubDuckDB Analytical SQL AI Skill Guide
Overview & Engine Architecture
DuckDB is an embedded columnar OLAP engine: runs in-process (Python/R/JDBC/CLI), vectorized execution, reads Parquet/CSV/JSON directly without a server. Ideal for notebooks, CI data checks, and local lake queries—not a substitute for fleet-scale warehouse serving.
Parquet/CSV/S3 (httpfs)
-> DuckDB in-process
-> SQL (join/window/aggregate)
-> Arrow/DataFrame / COPY export
Optional .duckdb file for persistent views
When to use / when not to
Use when
- Ad-hoc SQL on local or lake files; replacing heavy pandas groupbys
- CI validation of datasets (row counts, schema contracts)
- Prototyping transforms before
@dbton Snowflake/BigQuery
Do not use when
- Many concurrent writers or multi-tenant serving layer needed (
@postgresql,@snowflake) - Data exceeds single-node RAM without spill tuning—consider
@sparkor warehouse - Untrusted remote URLs without reviewing
httpfscredential scope
Operational Capabilities & Agent Directives
- Connection safety: One writer per
.duckdbfile; useread_onlyconnections for parallel readers; avoid NFS locking surprises. - Remote access:
INSTALL httpfs; LOAD httpfs;only when needed; secrets via env vars (AWS_*), never committed keys. - Performance: Project columns early; predicate pushdown on
read_parquet; useEXPLAIN ANALYZEon slow queries. - Resource limits:
PRAGMA threads=N; PRAGMA memory_limit='4GB';on shared CI runners to prevent OOM kills. - Schema drift:
union_by_name=trueon multi-file Parquet; explicit casts in views for stable contracts. - Backup/migration:
.duckdbis a file—copy while no writer or use export; Parquet lake remains source of truth for immutable pipelines. - Exactly-once myth: DuckDB does not coordinate distributed exactly-once; downstream pipelines still need idempotent loads.
Production examples
Parquet analytics
INSTALL httpfs; LOAD httpfs;
SELECT
customer_id,
date_trunc('month', created_at) AS month,
sum(amount) AS revenue,
count(*) AS orders
FROM read_parquet('data/orders/*.parquet', union_by_name=true)
WHERE created_at >= DATE '2025-01-01'
GROUP BY 1, 2
ORDER BY revenue DESC;
Persistent views
ATTACH 'analytics.duckdb' AS meta;
CREATE OR REPLACE VIEW meta.orders AS
SELECT * FROM read_parquet('data/orders/*.parquet');
Export
COPY (
SELECT customer_id, sum(amount) AS revenue
FROM read_parquet('data/orders/*.parquet')
GROUP BY 1
) TO 'out/revenue.parquet' (FORMAT PARQUET);
Python
import duckdb
con = duckdb.connect("analytics.duckdb", read_only=False)
con.execute("PRAGMA memory_limit='4GB'")
df = con.execute("SELECT count(*) FROM read_parquet('data/orders/*.parquet')").fetchdf()
Technical troubleshooting matrix
| Failure signature | Root cause | Diagnostic & fix |
|---|---|---|
Binder Error on UNION files |
Schema drift | union_by_name; cast in view |
| OOM on join | Large hash join | Filter first; raise memory_limit; sample |
| Database locked | Second writer | Single writer; read_only readers |
| Slow full scan | Reading all columns | Select subset; partition filters on paths |
| S3 403 | Missing creds/role | Fix env; scope IAM read-only |
| Wrong row counts vs prod | Stale local files | Pin snapshot paths; checksum inputs |
Best practices
- Keep raw lake files immutable; version curated
.duckdbviews in git as SQL scripts, not binary blobs when possible. - Hand off slim results to
@polars/@pandasfor visualization only. - Use DuckDB for correctness checks before warehouse loads.
Limitations
- Single-node; no built-in HA serving tier.
- Extension availability differs in air-gapped CI images.
- Not a replacement for row-level security products unless implemented in app layer.
Related skills
@polars— DataFrame engine on same files@snowflake— promote vetted SQL to warehouse@dbt— versioned transform layer
Agent Operational Directive
MANDATORY: Set memory/thread pragmas in CI scripts. Never embed cloud secrets in SQL files. Do not propose DuckDB as the production OLTP or multi-tenant serving database without explicit requirements.