DuckDB Analyst

DuckDB Analyst writes and runs analytical SQL against local files — Parquet, CSV, JSON, and Arrow — using DuckDB's in-process engine. It's built for people who want fast OLAP queries, window functions, and multi-file joins without standing up a warehouse or loading data into Postgres first. If your "database" is a folder of .parquet files in S3 or on disk, this is the skill that turns it into a queryable dataset.

When to use it

• You have raw exports (event logs, billing dumps, CSVs from a vendor) and need aggregates, dedup, or cohort math before loading anything elsewhere.
• You want window functions — running totals, LAG/LEAD, QUALIFY, ROW_NUMBER partitions — over files too big for pandas but not worth a cluster.
• You're migrating Postgres analytics queries and need them rewritten for DuckDB's dialect.
• You want a query plan explained before running something expensive over a large Parquet glob.

How it works

The skill picks the right reader for your format: read_parquet('data/*.parquet'), read_csv_auto('file.csv') (or read_csv with explicit columns/delim when sniffing fails), and read_json_auto('events.json'). It leans on DuckDB-specific features Postgres lacks — QUALIFY to filter window results without a subquery, SELECT * EXCLUDE (col) / REPLACE, GROUP BY ALL, LIST/STRUCT aggregates, and COLUMNS(...) expressions. It can attach Parquet/CSV directly without a CREATE TABLE, or persist a .duckdb file when you want to keep derived tables. On request it runs EXPLAIN / EXPLAIN ANALYZE and walks through the plan, pointing out where projection pushdown and filter pushdown into Parquet kick in.

Example

curl -fsSL --create-dirs -o ~/.claude/skills/duckdb-analyst/SKILL.md https://claudeskil.com/skills/duckdb-analyst/SKILL.md

-- Top 3 events per user, last 30 days, straight off a Parquet glob
SELECT user_id, event_name, ts, n
FROM (
  SELECT user_id, event_name, ts,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ts DESC) AS n
  FROM read_parquet('s3://logs/events/*.parquet')
  WHERE ts >= now() - INTERVAL 30 DAY
)
QUALIFY n <= 3
ORDER BY user_id;

Tips & gotchas

• read_csv_auto samples only the first ~20k rows to infer types; if a column flips to VARCHAR mid-file, set sample_size=-1 or pass explicit columns.
• Use QUALIFY instead of wrapping window functions in a subquery — it's cleaner and DuckDB-native, but it does not exist in Postgres, so don't copy it back.
• For S3/HTTP files, INSTALL httpfs; LOAD httpfs; first, and set credentials via CREATE SECRET (the old SET s3_access_key_id still works but is deprecated).
• DuckDB is single-writer: don't point two processes at the same .duckdb file expecting concurrent writes.

FAQ

Does it need a server or daemon? No. DuckDB runs in-process; queries hit your files directly.

Can it join a CSV to a Parquet file? Yes — reference both reader functions in one FROM/JOIN; no import step needed.

Will it handle files larger than RAM? Often, yes. DuckDB spills to disk and uses streaming execution, though very large sorts/joins benefit from setting memory_limit and a temp_directory.