duckdb · diff
git:20260910.c429a71 to git:20261005.2bc0998
101 added, 47 removed. Audit A to A.
---
name: duckdb
- description: "Query local CSV and Parquet files with DuckDB SQL, aggregate and join datasets, export analytical results, and troubleshoot schema drift or memory limits."
+ description: "Run embedded OLAP SQL on Parquet/CSV locally; configure memory and thread limits; attach remote files safely; export with COPY; avoid multi-writer locks and treating DuckDB as a production server database."
category: scientific
risk: safe
source: self
source_type: self
date_added: "2026-08-26"
- tags: ["duckdb", "sql", "parquet", "analytics", "olap", "claude"]
+ tags: ["duckdb", "sql", "parquet", "olap", "embedded", "httpfs", "memory-limit", "analytics"]
tools: ["claude", "cursor", "gemini", "codex"]
---
# DuckDB Analytical SQL AI Skill Guide
## Overview & Engine Architecture
- DuckDB is an embedded OLAP database: columnar storage/execution, vectorized operators, and direct scans of Parquet/CSV/JSON without a server. Agents write SQL against files or attached databases, push filters into scans, and export results via `COPY` or DataFrame APIs instead of reinventing aggregations in Python.
+ 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/HTTP
- -> DuckDB engine (in-process)
- -> SQL (joins, window, aggregate)
- -> result / COPY / DataFrame
+ Parquet/CSV/S3 (httpfs)
+ -> DuckDB in-process
+ -> SQL (join/window/aggregate)
+ -> Arrow/DataFrame / COPY export
+ Optional .duckdb file for persistent views
```
- ## When to use this skill
+ ---
- - Ad-hoc SQL on local or lake files
- - Replacing heavy pandas groupbys with SQL
- - Lightweight warehouses for notebooks and CI checks
+ ## When to use / when not to
- ## Operational directives
+ **Use when**
- 1. Query files with `read_parquet` / `read_csv_auto` (or glob paths) before inventing loaders.
- 2. Create views for repeated logic; persist only when you need indexes/reuse.
- 3. Prefer `COPY ... TO 'out.parquet'` for large exports.
- 4. Set memory/thread limits for shared CI runners.
- 5. Never embed production secrets in attached remote extensions without review.
+ - Ad-hoc SQL on local or lake files; replacing heavy pandas groupbys
+ - CI validation of datasets (row counts, schema contracts)
+ - Prototyping transforms before `@dbt` on Snowflake/BigQuery
- ## Query Parquet example
+ **Do not use when**
+ - Many concurrent writers or multi-tenant serving layer needed (`@postgresql`, `@snowflake`)
+ - Data exceeds single-node RAM without spill tuning—consider `@spark` or warehouse
+ - Untrusted remote URLs without reviewing `httpfs` credential scope
+
+ ---
+
+ ## Operational Capabilities & Agent Directives
+
+ 1. **Connection safety**: One writer per `.duckdb` file; use `read_only` connections for parallel readers; avoid NFS locking surprises.
+ 2. **Remote access**: `INSTALL httpfs; LOAD httpfs;` only when needed; secrets via env vars (`AWS_*`), never committed keys.
+ 3. **Performance**: Project columns early; predicate pushdown on `read_parquet`; use `EXPLAIN ANALYZE` on slow queries.
+ 4. **Resource limits**: `PRAGMA threads=N; PRAGMA memory_limit='4GB';` on shared CI runners to prevent OOM kills.
+ 5. **Schema drift**: `union_by_name=true` on multi-file Parquet; explicit casts in views for stable contracts.
+ 6. **Backup/migration**: `.duckdb` is a file—copy while no writer or use export; Parquet lake remains source of truth for immutable pipelines.
+ 7. **Exactly-once myth**: DuckDB does not coordinate distributed exactly-once; downstream pipelines still need idempotent loads.
+
+ ---
+
+ ## Production examples
+
+ ### Parquet analytics
+
```sql
- INSTALL httpfs; LOAD httpfs; -- only if remote paths needed
+ 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')
+ 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;
```
- ## Python usage
+ ### Persistent views
- ```python
- import duckdb
+ ```sql
+ ATTACH 'analytics.duckdb' AS meta;
+ CREATE OR REPLACE VIEW meta.orders AS
+ SELECT * FROM read_parquet('data/orders/*.parquet');
+ ```
- con = duckdb.connect("analytics.duckdb")
- con.execute("CREATE OR REPLACE VIEW orders AS SELECT * FROM read_parquet('data/orders/*.parquet')")
- df = con.execute("""
+ ### Export
+
+ ```sql
+ COPY (
SELECT customer_id, sum(amount) AS revenue
- FROM orders
+ FROM read_parquet('data/orders/*.parquet')
GROUP BY 1
- ORDER BY 2 DESC
- LIMIT 20
- """).fetchdf()
+ ) TO 'out/revenue.parquet' (FORMAT PARQUET);
```
- ## Common failures
+ ### Python
- | Symptom | Cause | Fix |
- | --- | --- | --- |
- | Type mismatch on UNION | schema drift across files | `union_by_name` / cast |
- | Slow scan | reading all columns | select subset; predicate early |
- | OOM | huge hash join | filter; spill settings; sample |
- | Lock error | second writer on same file DB | one writer; use read_only |
+ ```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 a `.duckdb` file for curated views; keep raw lake files immutable.
- - Use `EXPLAIN ANALYZE` for unexpected runtime.
- - `PRAGMA threads=N` and `PRAGMA memory_limit='4GB'` in shared envs.
- - Hand off to `@pandas` / `@polars` only for the final slim result.
+ - Keep raw lake files immutable; version curated `.duckdb` views in git as SQL scripts, not binary blobs when possible.
+ - Hand off slim results to `@polars` / `@pandas` for visualization only.
+ - Use DuckDB for **correctness checks** before warehouse loads.
+ ---
+
## Limitations
- - Single-node; not a substitute for Snowflake/BigQuery at fleet scale.
- - Extension availability differs offline vs CI images.
- - Concurrent multi-writer patterns are limited compared to server DBs.
+ - 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` - expression DataFrames on the same files
- - `@spark` - distributed SQL when DuckDB no longer fits
- - `@dbt` - versioned SQL models wrapping DuckDB or warehouses
+ - `@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.
+
+ ---
+
+ ## Source anchors (research)
+
+ - [DuckDB SQL introduction](https://duckdb.org/docs/sql/introduction)
+ - [Reading multiple Parquet files](https://duckdb.org/docs/data/parquet/overview)
+ - [Pragmas (memory, threads)](https://duckdb.org/docs/configuration/pragmas)