DuckDB Analytics skill for Claude Code

DuckDB work is done well when queries read files directly (no ad-hoc import step unless data is reused), the scan reads only the needed columns and partitions, types are checked rather than trusted from auto-detection, results are written as Parquet, and memory limits are set so large jobs spill to disk instead of crashing.

Setup

duckdb --version                       # CLI; current stable line is 1.x (1.4 LTS in late 2025)
python3 -c "import duckdb; print(duckdb.__version__)"
duckdb analysis.duckdb                 # persistent DB file; `duckdb` alone is in-memory

In the CLI: .timer on, .mode box (or markdown for reports), .maxrows 50.

Workflow

- [ ] 1. Inspect the files: schema, row counts, types, nulls
- [ ] 2. Fix types and parsing explicitly where detection is wrong
- [ ] 3. Write the analysis as SQL (views or CTEs), reading only needed columns
- [ ] 4. Persist reusable intermediate results as Parquet or tables
- [ ] 5. Verify counts and totals against the source; export the result

1. Inspect before analyzing

DESCRIBE SELECT * FROM 'data/events/*.parquet';
SUMMARIZE SELECT * FROM 'data/events/*.parquet';      -- min/max/nulls/approx distinct per column
SELECT count(*) FROM read_parquet('data/events/*.parquet');
SELECT * FROM parquet_metadata('data/events/part-0.parquet') LIMIT 5;   -- row groups, stats
SELECT * FROM sniff_csv('raw/orders.csv');                 -- what CSV auto-detection decided

2. Read files correctly

-- Parquet with hive partitioning (path like year=2026/month=10/file.parquet)
SELECT * FROM read_parquet('s3://lake/events/**/*.parquet', hive_partitioning = true)
WHERE year = 2026 AND month = 10;                           -- prunes directories

-- Schema drift across files: match columns by name, fill missing with NULL
SELECT * FROM read_parquet('data/*.parquet', union_by_name = true, filename = true);

-- CSV with explicit types where detection guesses wrong
SELECT * FROM read_csv('raw/orders.csv',
    header = true,
    delim = ',',
    dateformat = '%d/%m/%Y',
    types = {'order_id': 'BIGINT', 'zip': 'VARCHAR', 'amount': 'DECIMAL(18,2)'},
    ignore_errors = false);

-- JSON / NDJSON
SELECT * FROM read_json('logs/*.ndjson', format = 'newline_delimited');

• Leading-zero codes (zip, SKU, phone) must be read as VARCHAR; auto-detection turns them into integers and drops the zeros.
• Ambiguous dates (03/04/2026) need an explicit dateformat.
• Prefer failing loudly on bad rows. If you use ignore_errors = true, count how many rows were dropped and report it (store_rejects = true collects them in a table).

Remote files:

INSTALL httpfs; LOAD httpfs;                 -- autoloaded in most builds when needed
CREATE SECRET lake (TYPE s3, PROVIDER credential_chain);   -- uses AWS env/profile/SSO
-- or explicit: CREATE SECRET (TYPE s3, KEY_ID '...', SECRET '...', REGION 'us-east-1');

Never paste credentials into SQL files that get committed; use credential_chain or environment variables. Use CREATE PERSISTENT SECRET only on a trusted machine.

3. Analysis SQL

DuckDB’s friendly extensions make analysis shorter; use them:

CREATE OR REPLACE VIEW orders AS
SELECT * EXCLUDE (raw_payload), amount_cents / 100.0 AS amount
FROM read_parquet('data/orders/*.parquet');

SELECT customer_id, date_trunc('month', placed_at) AS month, sum(amount) AS revenue
FROM orders
GROUP BY ALL
ORDER BY ALL;

-- Latest order per customer
SELECT * FROM orders
QUALIFY row_number() OVER (PARTITION BY customer_id ORDER BY placed_at DESC) = 1;

-- Pivot months into columns
PIVOT (SELECT customer_id, strftime(placed_at, '%Y-%m') AS ym, amount FROM orders)
ON ym USING sum(amount) GROUP BY customer_id;

-- Point-in-time join: price in effect at each trade
SELECT t.*, p.price
FROM trades t ASOF JOIN prices p
  ON t.symbol = p.symbol AND t.ts >= p.ts;

-- Columns by pattern
SELECT COLUMNS('revenue_.*') FROM monthly;
SELECT max(COLUMNS(* EXCLUDE (id))) FROM metrics;

Other useful pieces: list/struct types with unnest(), string_split, regexp_extract, arg_max(value, ts), approx_count_distinct, histogram, generate_series, FROM tbl shorthand (no SELECT needed), SELECT ... USING SAMPLE 1%.

4. Persist and export

-- Single Parquet file with zstd
COPY (SELECT * FROM orders WHERE placed_at >= DATE '2026-01-01')
TO 'out/orders_2026.parquet' (FORMAT parquet, COMPRESSION zstd);

-- Partitioned dataset
COPY (SELECT *, year(placed_at) AS year, month(placed_at) AS month FROM orders)
TO 'out/orders' (FORMAT parquet, PARTITION_BY (year, month), OVERWRITE_OR_IGNORE);

-- CSV for spreadsheet users
COPY (FROM monthly_report) TO 'out/report.csv' (HEADER, DELIMITER ',');

-- Materialize a reused intermediate in the DB file
CREATE OR REPLACE TABLE customers_clean AS SELECT ... ;

• Convert large CSVs to Parquet once; every later query will be 10-100x faster and read only needed columns.
• Sort data by common filter columns before writing Parquet so row-group min/max stats allow skipping.
• OVERWRITE_OR_IGNORE / OVERWRITE replace output; confirm before overwriting files the user did not create in this session.

Python integration

import duckdb
import pandas as pd

con = duckdb.connect("analysis.duckdb")        # or duckdb.connect() for in-memory
con.execute("SET memory_limit = '8GB'")
con.execute("SET threads = 8")

df = pd.read_parquet("small_lookup.parquet")
# DataFrames, Polars frames, and Arrow tables in scope are queryable by variable name
result = con.sql("""
    SELECT o.customer_id, l.segment, sum(o.amount) AS revenue
    FROM read_parquet('data/orders/*.parquet') o
    JOIN df l USING (customer_id)
    GROUP BY ALL
""")
out_pd = result.df()          # pandas
out_pl = result.pl()          # Polars
out_arrow = result.arrow()    # Arrow table

# Parameters: never format values into SQL strings
con.execute("SELECT * FROM orders WHERE customer_id = ? AND placed_at >= ?", [cid, since]).fetchall()

• A DuckDBPyConnection is not safe to share across threads; give each thread con.cursor().
• Only one process can open a database file for writing at a time. Multiple processes can open it read_only=True. For multi-writer needs, write Parquet files instead or use a server database.
• Prefer letting DuckDB read files directly over loading them into pandas first; it streams and parallelizes.

Performance and memory

SET memory_limit = '8GB';              -- default is ~80% of RAM
SET threads = 8;
SET temp_directory = '/fast-disk/duckdb_tmp';    -- spill location for larger-than-memory work
SET preserve_insertion_order = false;  -- lets large exports/aggregations use less memory
EXPLAIN ANALYZE SELECT ... ;           -- per-operator timings and cardinalities

Symptom	Fix
Out of memory on big join/aggregate	Set memory_limit and temp_directory; preserve_insertion_order = false; reduce columns early
Slow scans of CSV	Convert to Parquet once
Remote S3 queries slow	Filter on hive partition columns; avoid SELECT *; fewer, larger files (100 MB-1 GB)
Many tiny Parquet files	Compact with COPY (SELECT * FROM read_parquet('in/*.parquet')) TO ...
Window function heavy	Partition on low-cardinality keys; pre-filter

Gotchas

• Integer division: 1 / 2 returns 0.5 in DuckDB (float division since 0.8); use // for integer division. Do not assume Postgres semantics.
• Timestamps: TIMESTAMP has no zone, TIMESTAMPTZ is displayed in the session TimeZone. Set SET TimeZone = 'UTC'; for reproducible output.
• Globs over S3 list every object; deep prefixes with millions of files are slow to plan. Point at the narrowest prefix.
• CREATE OR REPLACE TABLE and COPY ... OVERWRITE destroy previous contents without a prompt.
• Auto-detected CSV schemas are sampled; a column that is numeric in the first rows and text later fails mid-query. Pin types for production pipelines.
• DuckDB file format is stable from 1.0 onward with backward compatibility, but older 0.x files need migration via export/import.

Verification

1. Row counts: count(*) of source files equals rows in the output (or the difference is explained by documented filters).
2. Totals: a checksum metric (sum of amount, count distinct of IDs) matches between source and result.
3. Types: DESCRIBE the output; no numeric IDs that should be strings, no VARCHAR dates.
4. Re-run the script from a clean session (duckdb -c ".read analysis.sql" or the Python script) to confirm it reproduces.