duckdb git:20260919.4491844A

Immutable. This exact content is served forever at /api/v1/blob/b2afa2a83ea7c066.

---
name: duckdb
description: "Query, transform, and export files or databases with DuckDB and SQLite."
license: MIT
metadata:
  kind: connector
  author: Médéric HURIER (Fmind)
  source: github.com/fmind/dot/tree/main/skills/duckdb
  created: "2026-09-02"
  updated: "2026-09-19"
---

# DuckDB and SQLite

DuckDB is the default engine for local analysis: it reads CSV, Parquet, and JSON directly, attaches SQLite files, and writes any of them back. SQLite stays the embedded store for applications; DuckDB is the tool you point at data.

## Commands

```bash
duckdb -c "SELECT name, score FROM 'people.csv' WHERE score > 80 ORDER BY score DESC"   # files are tables
duckdb -json -c "SELECT * FROM 'events/*.json'"                                          # JSON output for agents and jq
duckdb lab.duckdb -c "CREATE OR REPLACE TABLE people AS FROM 'people.csv'"               # persist into a database file
duckdb -c "COPY (FROM 'people.csv') TO 'people.parquet'"                                 # convert; Parquet is the durable format
duckdb -c "ATTACH 'app.sqlite' AS s (TYPE sqlite, READ_ONLY); SELECT count(*) FROM s.users"         # read an application database
duckdb -c "SUMMARIZE FROM 'people.parquet'"                                              # column stats in one call
sqlite3 -readonly app.sqlite ".schema" && sqlite3 -readonly app.sqlite "PRAGMA integrity_check"              # inspect the app store itself
```

The interactive shells load `~/.duckdbrc` and `~/.sqliterc` (box mode, headers, timer, `∅` for NULL); scripts pass `-json`, `-csv`, or `-markdown` explicitly so output does not depend on the rc file.

## Workflow

1. **Look before querying**: `DESCRIBE FROM '<file>'` and `SUMMARIZE` reveal types, nulls, and ranges; fix a wrong inference with `read_csv('<file>', types={'id': 'BIGINT'})`.
1. **Keep queries in files**: `duckdb < analysis.sql` or `duckdb -f analysis.sql` for anything longer than one line, committed next to the data description.
1. **Persist derived data as Parquet**: keep rebuildable `.duckdb` files out of Git and commit the SQL that produces them; use `-readonly` for inspection of an existing database.
1. **Check results**: row counts before and after joins, `count(*) FILTER (WHERE x IS NULL)` on keys, and a spot check against the source.
1. **Export for the reader**: `-markdown` for a report, `-json` for another tool, `COPY ... TO 'out.csv' (HEADER)` for a spreadsheet.

Use [data-migration](../data-migration/SKILL.md) when changing an application schema or persisted format; analysis and export alone do not establish migration or recovery safety.

## Gotchas

- **Do not open a live SQLite database with DuckDB while the app writes to it**: use a consistent SQLite backup or `sqlite3 -readonly` directly. A plain copy of the main file can omit committed WAL data; use SQLite's backup API or `.backup` for a snapshot.
- **Glob paths quote as strings**: `'events/*.parquet'` works, unquoted paths do not.
- **Memory**: large joins spill to disk automatically; set `SET memory_limit='4GB'` and `SET threads=4` on a shared machine.
- **Extensions load on demand**: `httpfs`, `spatial`, `postgres` install once with `INSTALL <ext>; LOAD <ext>;` and need network the first time.
- **Secrets**: `CREATE SECRET` from environment variables or Application Default Credentials, never literal credentials in SQL.

## Official Skills

Upstream: `duckdb/duckdb-skills`, with separate selections for querying, file formats, attached databases, spatial, S3, and documentation lookup. Follow the shared [vendor-skill policy](../agent-project/references/vendor-skills.md).

## Documentation

- [DuckDB CLI](https://duckdb.org/docs/stable/clients/cli/overview) · [SQLite CLI](https://sqlite.org/cli.html)
- Releases: [DuckDB](https://github.com/duckdb/duckdb/releases) · [SQLite changes](https://sqlite.org/changes.html)
- Companion skills: [python-script](../python-stack/references/python-script/GUIDE.md) (a one-file pipeline when SQL is not enough), [python-stack](../python-stack/references/foundation/GUIDE.md) (typed application data access and embedded SQLite).