---
name: duckdb-analyst
description: "Local analytics on Parquet/CSV/JSON with DuckDB. Window functions, joins, no-server data prep. Writes DuckDB SQL for analytical workloads. Knows the differences between DuckDB and Postgres, picks the right read function for the format, and explains query plans on demand."
license: MIT
---

# 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

```sql
-- 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`.