DuckDB Analytics skill for Codex

Done well means: files are queried in place without a load step unless reuse justifies one, types are explicit wherever inference is shaky, outputs are Parquet with sensible partitions, and every answer is reproducible from a single .sql file or script.

Before running anything

1. Read AGENTS.md. Check how DuckDB is available: duckdb --version (CLI) or python -c "import duckdb; print(duckdb.__version__)". This skill targets DuckDB 1.x.
2. Find the data and its shape before writing the query:

DESCRIBE SELECT * FROM 'data/orders/*.parquet';
SUMMARIZE SELECT * FROM 'data/orders/*.parquet';      -- min, max, nulls, approx distinct per column
SELECT * FROM read_csv('raw/events.csv', sample_size = -1) LIMIT 5;
SELECT * FROM parquet_metadata('data/orders/part-0.parquet');  -- row groups, stats

3. Decide where results live:

   Situation	Choice
   One-off question over files	In-memory connection, query files directly
   Repeated queries, joins across many sources	Persistent analytics.duckdb file with tables or views
   Output consumed by other tools	COPY ... TO Parquet
   Concurrent writers from several processes	Not DuckDB’s model (single writer per file); use a server database

Reading files

-- CSV with explicit schema when auto-detection guesses wrong
SELECT *
FROM read_csv('raw/events_*.csv',
    header = true,
    delim = ',',
    columns = {'event_id': 'VARCHAR', 'user_id': 'VARCHAR', 'ts': 'TIMESTAMP', 'value': 'DOUBLE'},
    timestampformat = '%Y-%m-%d %H:%M:%S',
    filename = true,            -- adds a column with the source file
    ignore_errors = false);     -- keep false until you know why rows fail

-- Parquet folders with key=value directories, schemas that drift between files
SELECT *
FROM read_parquet('lake/orders/**/*.parquet', hive_partitioning = true, union_by_name = true)
WHERE year = 2025 AND month = 9;      -- partition pruning on directory keys

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

• store_rejects = true on read_csv collects bad rows into reject_errors instead of failing or silently skipping them. Report the reject count.
• IDs with leading zeros: force VARCHAR in columns or types.

Remote storage

httpfs handles S3, GCS, R2 and HTTPS and autoloads on first use; it may need network for the first INSTALL, which in a Codex sandbox requires approval.

CREATE SECRET aws (TYPE s3, PROVIDER credential_chain);   -- env vars, ~/.aws, SSO
SELECT count(*) FROM read_parquet('s3://bucket/events/date=2025-09-*/*.parquet');

Never paste access keys into a SQL file that is committed. Use credential_chain or environment variables.

Analytical SQL patterns

-- GROUP BY ALL and QUALIFY keep queries short
SELECT customer_id, date_trunc('month', created_at) AS month, sum(amount) AS revenue
FROM orders
GROUP BY ALL;

-- latest row per key
SELECT * FROM orders
QUALIFY row_number() OVER (PARTITION BY order_id ORDER BY updated_at DESC) = 1;

-- pivot months into columns
PIVOT monthly ON month USING sum(revenue) GROUP BY customer_id;

-- as-of join: price in effect at order time
SELECT o.*, p.price
FROM orders o ASOF LEFT JOIN prices p
  ON o.sku = p.sku AND o.created_at >= p.valid_from;

-- reuse expressions by alias in the same SELECT, exclude columns
SELECT * EXCLUDE (raw_payload), amount * fx AS amount_eur, amount_eur * 0.19 AS vat
FROM orders;

Writing outputs

COPY (SELECT * FROM cleaned_orders)
TO 'out/orders' (FORMAT parquet, PARTITION_BY (year, month), COMPRESSION zstd,
                 OVERWRITE_OR_IGNORE true);

COPY (SELECT * FROM summary ORDER BY month) TO 'out/summary.csv' (HEADER, DELIMITER ',');

• Partition by low-cardinality columns that queries filter on (date parts, region). Thousands of tiny files are worse than no partitioning.
• ORDER BY the main filter column before writing a single Parquet file so row-group min/max statistics allow skipping.

Python client

import duckdb

con = duckdb.connect("analytics.duckdb")          # or duckdb.connect() for in-memory
con.execute("SET memory_limit = '4GB'; SET threads = 4;")
rel = con.sql("SELECT status, count(*) AS n FROM 'data/orders/*.parquet' GROUP BY ALL")
df = rel.df()          # pandas; rel.pl() for Polars, rel.arrow() for Arrow
con.sql("SELECT * FROM df WHERE n > 100")         # DataFrames in scope are queryable by name

# parameters, never f-strings with user input
con.execute("SELECT * FROM orders WHERE customer_id = ?", ["C-1042"]).fetchall()
con.close()

Memory and performance

Symptom	Fix
Out of memory on a big join or aggregate	SET memory_limit = '...' below container limit; SET temp_directory = '/tmp/duck' so operators spill
Still OOM with ORDER BY on huge output	SET preserve_insertion_order = false when order is not needed
Slow scans over many small files	Compact into fewer larger Parquet files (target 100 MB to 1 GB)
Partition filter ignored	Filter on the hive column itself, not on a function of it
Slow repeated reads of CSV	Convert to Parquet or a table once

Use EXPLAIN ANALYZE <query> to see per-operator time and cardinality. Check that filters appear inside the scan node (pushdown) rather than above it.

Gotchas

• A .duckdb file opened read-write by one process cannot be opened by another; use duckdb.connect(path, read_only=True) for concurrent readers.
• Database files are tied to storage format versions; old CLIs may not open newer files. Exchange data as Parquet, not .duckdb files.
• read_csv auto-detection samples rows; a bad value late in the file errors at query time. sample_size = -1 scans everything for detection.
• Integer division: / returns a double in DuckDB 1.x; use // for integer division.
• Timestamps without time zone stay naive. Use TIMESTAMPTZ and set TimeZone explicitly when the source carries offsets.
• SUMMARIZE distinct counts are approximate.

Verification

• Row counts: SELECT count(*) on source and output, plus counts per partition.
• Spot-check totals against a known figure (finance report, dashboard).
• Re-run the saved .sql file from a clean shell: duckdb analytics.duckdb < queries/report.sql.
• Check output files: SELECT count(*), min(created_at), max(created_at) FROM 'out/orders/**/*.parquet';

Report template

**Question:** monthly revenue by country, Jan-Sep 2025
**Sources:** lake/orders (hive, 212 files, 48.1M rows), customers.csv (88k rows)
**Query:** queries/monthly_revenue.sql (runs in 4.2 s, memory_limit 4GB)
**Output:** out/monthly_revenue.parquet (243 rows)
**Checks:** row counts match source; September total equals finance export within 0.01
**Caveats:** 311 CSV rows rejected (malformed timestamps), listed in out/rejects.csv