Anaiis Duckdb

DuckDB Analytics

Run ad hoc SQL analytics on local and remote structured data files using the DuckDB CLI.

When to use this skill

• Analyzing, querying, or exploring local parquet, CSV, Excel, JSON, Avro, or SQLite files
• Inspecting schema, row counts, or column distributions
• Joining, aggregating, or filtering across multiple files or formats
• Data exported from BigQuery or other systems for local analysis
• Any structured data query where Python would require loading the file into memory

Tool selection rules

Never load a multi-GB file into pandas with pd.read_parquet() or pd.read_csv(). DuckDB reads all supported formats natively with streaming/out-of-core execution.

Before using the duckdb Python package, check if it is installed:

pip list | grep duckdb

If not installed, default to the CLI rather than installing without asking.

Performance context

DuckDB is the default engine regardless of file size. For single-file CSV/parquet under ~50MB, Python's csv module is marginally faster (3x on a 1.7MB file), but that difference is noise compared to LLM round-trip latency. Use DuckDB for SQL ergonomics, multi-format support, and out-of-core execution on large files.

Use Python only for plotting, ML pipelines, or tasks that genuinely need control flow.

Standard workflow

1. Check file size

ls -lh path/to/file

• Under ~100MB: safe to preview with SELECT * LIMIT 10
• Over 100MB: use column projections, filters, and aggregations; avoid SELECT * without LIMIT
• Multiple files: use glob patterns (see below)

2. Introspect schema before querying

Parquet:

duckdb -c "DESCRIBE SELECT * FROM 'path/to/file.parquet';"
duckdb -c "SELECT COUNT(*) FROM 'path/to/file.parquet';"

CSV (auto-detect schema):

duckdb -c "DESCRIBE SELECT * FROM read_csv_auto('path/to/file.csv');"
duckdb -c "SELECT * FROM read_csv_auto('path/to/file.csv') LIMIT 5;"

JSON:

duckdb -c "DESCRIBE SELECT * FROM read_json_auto('path/to/file.json');"
duckdb -c "SELECT * FROM read_json_auto('path/to/file.json') LIMIT 5;"

Excel:

duckdb -c "INSTALL spatial; LOAD spatial;"
duckdb -c "SELECT * FROM st_read('path/to/file.xlsx') LIMIT 5;"

Avro:

duckdb -c "SELECT * FROM 'path/to/file.avro' LIMIT 5;"

SQLite (attach and query):

duckdb -c "ATTACH 'path/to/file.db' AS src (TYPE SQLITE); SHOW TABLES;"
duckdb -c "ATTACH 'path/to/file.db' AS src (TYPE SQLITE); SELECT * FROM src.table_name LIMIT 5;"

3. Query patterns

Prefer heredoc multi-statement blocks over multiple sequential -c calls. Each -c invocation is a subprocess round-trip; batching into a single heredoc runs all statements in one pass and minimizes latency.

One-shot query:

duckdb -c "SELECT col, COUNT(*) FROM 'file.parquet' GROUP BY col ORDER BY 2 DESC LIMIT 20;"

JSON output for piping to jq:

duckdb -json -c "SELECT col, COUNT(*) FROM 'file.parquet' GROUP BY col;" | jq '.[]'

Multiple files via glob:

duckdb -c "SELECT * FROM 'data/*.parquet' LIMIT 10;"
duckdb -c "SELECT COUNT(*) FROM 'exports/2024-*.parquet';"

Multi-statement heredoc (preferred for 2+ queries -- single subprocess, no round-trips):

duckdb <<'SQL'
CREATE TABLE t AS SELECT * FROM 'file.parquet';
SELECT column_name, column_type FROM information_schema.columns WHERE table_name = 't';
SELECT COUNT(*), AVG(value_col) FROM t WHERE condition;
SQL

Cross-format join:

duckdb <<'SQL'
CREATE TABLE a AS SELECT * FROM 'file.parquet';
CREATE TABLE b AS SELECT * FROM read_csv_auto('file.csv');
SELECT a.id, a.col1, b.col2 FROM a JOIN b ON a.id = b.id LIMIT 20;
SQL

4. Export results

duckdb -c "COPY (SELECT col, COUNT(*) FROM 'file.parquet' GROUP BY col) TO 'output.csv' (HEADER, DELIMITER ',');"
duckdb -c "COPY (SELECT * FROM 'file.parquet' WHERE condition) TO 'filtered.parquet' (FORMAT PARQUET);"

Guardrails

• Do not create persistent .duckdb database files unless the user explicitly asks. Use in-memory mode (no db path argument).
• Do not install Python packages (duckdb, pandas, pyarrow) without asking.
• Do not use SELECT * without LIMIT on files over 100MB.
• Prefer heredoc batching over multiple -c calls whenever 2 or more queries target the same data.
• Query discipline (purpose classification, column selection, re-query prevention): follow rules/duckdb.md.

Output format

• Small result sets (under 20 rows): render as a markdown table.
• Large result sets: show the query used and a prose summary of findings.
• Schema introspection: show as a table with column name, type, and nullable.