duckdb

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

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); SELECT count(*) FROM s.users"         # read an application database
duckdb -c "SUMMARIZE FROM 'people.parquet'"                                              # column stats in one call
sqlite3 app.sqlite ".schema" && sqlite3 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'}).
2. Keep queries in files: duckdb < analysis.sql or duckdb -f analysis.sql for anything longer than one line, committed next to the data description.
3. Persist derived data as Parquet: never commit .duckdb files (they change on every open); commit the SQL that rebuilds them.
4. Check results: row counts before and after joins, count(*) FILTER (WHERE x IS NULL) on keys, and a spot check against the source.
5. Export for the reader: -markdown for a report, -json for another tool, COPY ... TO 'out.csv' (HEADER) for a spreadsheet.