DuckDB SQL cheat sheet
Querying Parquet and CSV directly, friendly SQL shorthand, extensions, and using DuckDB as a local transformation engine.
Query files directly
select * from 'data/events/*.parquet'- No loading step — DuckDB reads Parquet (with pushdown) straight off disk. Globs work.
select * from read_csv('raw.csv', auto_detect = true)- Sniffs delimiter, header, and types. Add types = {'id':'BIGINT'} to override bad guesses.
select * from read_json_auto('api_dump.json')- Infers a schema from nested JSON, including lists and structs.
select filename, count(*) from read_parquet('s3://bucket/t/*.parquet', filename = true) group by 1- filename=true exposes the source file per row — great for debugging a bad partition.
copy (select * from t where d = '2026-08-01') to 'out.parquet' (format parquet, compression zstd)- Write results back to Parquet. ZSTD is the sane default codec.
Friendly SQL shorthand
select * exclude (raw_payload) from t- All columns except the listed ones.
select * replace (round(amount, 2) as amount) from t- Keep all columns, transform one inline.
select columns('sales_.*') from t- Select columns by regex — handy on wide auto-generated tables.
from t select id, amount where amount > 0- FROM-first syntax; a bare `from t` is a valid full query.
group by all / order by all- Groups by every non-aggregated column — kills the "group by 1,2,3,4" boilerplate.
Remote data and extensions
install httpfs; load httpfs;- Enables reading from s3://, gcs://, and https:// URLs. Most extensions autoload on first use in recent versions.
create secret (type s3, provider credential_chain);- Picks up AWS credentials from env/profile the same way the AWS SDK does.
attach 'postgres://user@host/db' as pg (type postgres);- Query live Postgres tables from DuckDB — join them against local Parquet in one statement.
attach 'md:' as cloud;- MotherDuck attach — hybrid local/cloud execution with the same SQL.
install iceberg; select * from iceberg_scan('s3://lake/db/t');- Read Iceberg tables directly, no Spark required.
Transformation idioms
create table clean as select distinct on (id) * from 'raw/*.parquet' order by id, updated_at desc- Postgres-style DISTINCT ON works — latest record per key in one line.
unpivot t on jan, feb, mar into name month value amount- Wide-to-long without a stack of UNION ALLs. PIVOT does the reverse.
select unnest(items, recursive := true) from t- Explodes lists and flattens nested structs into columns in one go.
summarize t;- Instant profile — min, max, nulls, approx uniques for every column.
describe select * from 'file.parquet';- Schema of any query or file without reading the data.
CLI and operational habits
duckdb warehouse.duckdb -c "select count(*) from events"- Run a query against a persistent database file and exit — ideal in Makefiles and CI.
duckdb -c ".mode markdown" -c "select * from 'x.parquet' limit 5"- Output modes (markdown, csv, json, line) make DuckDB a Swiss-army formatter.
set memory_limit = '8GB'; set threads = 4;- DuckDB spills to disk when it must, but capping memory keeps it from starving co-located processes.
export database 'backup_dir' (format parquet);- Dump every table to Parquet — a portable, versionable backup of a local warehouse.
From DataLane — tutorials at/blog, practice SQL live in theplayground.