Home › DuckDB › DuckDB analytics Cursor rules

DuckDB analytics Cursor rules

Cursor rules for DuckDB analytics in SQL and Python: query files in place, parameterized queries, columnar results, extensions, and one-writer concurrency.

CursorDuckDB

Copy duckdb.mdc

How to use

Save this as .cursor/rules/duckdb.mdc in your repository root, commit it, and your agent will pick it up automatically. Edit the commands to match your project.

Note: Review and test before use. Provided as is, without warranty. See Terms.

---
description: DuckDB SQL and client conventions
globs: ["**/*.sql", "**/*.py", "**/*.ipynb"]
alwaysApply: false
---

# DuckDB

- Query files where they live: `FROM read_parquet('data/events/*.parquet')`, `read_csv('x.csv', header = true, columns = {...})` when inference is wrong, `read_json_auto`. Don't load files into pandas first just to query them.
- Write results with `COPY (SELECT ...) TO 'out/x.parquet' (FORMAT parquet, COMPRESSION zstd)`; partition big outputs with `PARTITION_BY (year, month)`.
- SQL style: CTEs with clear names, explicit column lists in final selects (no `SELECT *` in saved queries), `GROUP BY ALL`/`ORDER BY ALL` are fine for exploration only.
- Python: one connection per process (`duckdb.connect('analytics.duckdb')` or in-memory). Always parameterize: `con.execute('SELECT * FROM t WHERE id = ?', [id])`. Never f-string values into SQL.
- Return columnar results: `.df()`, `.pl()` (Polars) or `.arrow()`/`.fetch_arrow_table()`. Don't loop over `fetchall()` rows in Python to aggregate; push the aggregation into SQL.
- Pandas/Polars frames in scope can be queried by name; register them explicitly with `con.register('name', df)` in library code so it is obvious.
- Remote data: `INSTALL httpfs; LOAD httpfs;` and credentials with `CREATE SECRET` (never hardcode keys in SQL files). Select only needed columns and filter early so Parquet pushdown works.
- Concurrency: a database file has one writer process at a time. Readers from other processes open with `read_only=True`. For multi-writer apps use a server database instead.
- Use `EXPLAIN ANALYZE` before optimizing; set `SET memory_limit`/`threads` only when measured.
- Schema: create tables with explicit types; use `TIMESTAMPTZ` for instants, `DECIMAL` for money.

## Tests
Run queries against small fixture files in `tests/fixtures/`; assert on row counts and key aggregates.