Agent Skills: DuckDB

DuckDB

SQL over local files with no import step. Standard SQL and DuckDB's file-reading functions work as documented; this file covers tool selection, local database locations, and the Python API traps.

When to reach for it

Yes: aggregations, GROUP BY, window functions, joins across datasets, percentiles and summary statistics, profiling, transformations before export.

No: viewing a file (xsv, xlsx, cat), single-row lookups (grep, jq), simple column filtering (xsv is less ceremony).

Local databases in this workspace

• areas/staffing/staffing.duckdb — org headcount (use the staffing skill)
• data/slack_qa.duckdb — Slack Q&A pipeline storage
• data/*.duckdb — project-specific pipeline databases

Reading files

Query paths directly — globs, and format detection, both work:

SELECT * FROM 'data.csv' LIMIT 10;
SELECT * FROM 'data/*.parquet';
DESCRIBE SELECT * FROM 'data.csv';   -- inspect inferred types before trusting them

Parquet is fastest (columnar, predicate pushdown). Convert large CSVs once if you'll query them repeatedly: COPY (SELECT * FROM 'big.csv') TO 'big.parquet' (FORMAT PARQUET);

Piping

DuckDB reads stdin via /dev/stdin, so it chains with the rest of the toolchain:

xsv search -s status "active" data.csv | duckdb -c "SELECT AVG(amount) FROM read_csv('/dev/stdin')"
jq -c '.' events.json | duckdb -c "SELECT COUNT(*) FROM read_json_auto('/dev/stdin')"
conform extract messy_report.pdf --output invoices.csv && duckdb -c "SELECT vendor, SUM(amount) FROM 'invoices.csv' GROUP BY 1"
duckdb -c "COPY (SELECT * FROM 'local.csv' WHERE important) TO 'important.csv'" && bigquery insert dataset.table important.csv

Concurrency: a read-write connection takes an exclusive file lock

Opening a .duckdb file in read-write mode (the default for duckdb.connect(path) and any wrapper class that always opens rw) takes an exclusive OS-level lock on that file — a second process cannot open any connection to it concurrently, not even a read-only one, until the first connection closes. This bit a multi-agent dispatch plan in usps-local-route-detection: GeocodeCache opened a shared data/geocode_cache.duckdb read-write on every connection, so running cache-import/backtest steps for multiple metros in parallel (as the original task dependency graph assumed) would have hit lock conflicts. Caught by reading the accessor's code before dispatching, not from any DuckDB doc.

Before parallelizing sub-agent tasks that touch a shared .duckdb file, check whether the accessor opens it read-write unconditionally — if so, serialize those specific steps instead of running them concurrently, even if the rest of the plan's tasks are otherwise independent. Open with read_only=True explicitly wherever a step only needs to read.

Python API gotchas

rowcount is always negative for UPDATE/DELETE

con.execute("UPDATE ...") then .rowcount returns -1 (or a negative multiple in a loop), not the affected row count. The statement did run. To report a count, run the equivalent SELECT COUNT(*) with the same WHERE clause first:

affected = con.execute(
    "SELECT COUNT(*) FROM users WHERE last_login < '2024-01-01'"
).fetchone()[0]
con.execute("UPDATE users SET active = false WHERE last_login < '2024-01-01'")

.fetchall() / .df() on SELECT are unaffected — they return real results.

.df().to_dict(orient="records") coerces types

SQL NULLs arrive as typed pandas NA, not None, and several types are not JSON-serializable:

| DuckDB type | pandas representation | symptom | |---|---|---| | NULL VARCHAR/INTEGER | float('nan') | AttributeError: 'float' object has no attribute 'lower' | | NULL TIMESTAMP | pd.NaT | fromisoformat: argument must be str; not JSON serializable | | TIMESTAMP | pd.Timestamp | not JSON serializable — needs .isoformat() | | ARRAY/LIST | numpy.ndarray | not JSON serializable — needs .tolist() |

Sanitize before passing rows downstream:

import math, numpy as np, pandas as pd

row = {
    k: (None if (isinstance(v, float) and math.isnan(v)) or v is pd.NaT
        else v.isoformat() if isinstance(v, pd.Timestamp)
        else v.tolist() if isinstance(v, np.ndarray)
        else v)
    for k, v in row.items()
}

Pass allow_nan=False to json.dumps as a safety net — it raises on a leftover NaN rather than silently emitting invalid NaN.