DuckDB Cheat Sheet
Run fast in-process analytical SQL queries directly on CSV, Parquet, and pandas data with DuckDB's embedded OLAP engine.
Query Files Directly with SQL
Run SQL over CSV and Parquet files without any loading step.
-- query a CSV directlySELECT customer_id, SUM(amount) AS totalFROM 'orders.csv'GROUP BY customer_idORDER BY total DESCLIMIT 10;-- query multiple partitioned parquet files with a globSELECT status, COUNT(*) FROM 'data/orders/*.parquet' GROUP BY status;
Embedded in Python
Use DuckDB as an in-process analytical engine directly against pandas or Polars data.
import duckdbimport pandas as pddf = pd.read_csv("orders.csv")# DuckDB can query a pandas DataFrame by variable name, no import stepresult = duckdb.sql("SELECT status, AVG(amount) FROM df GROUP BY status").df()# or via a persistent connectioncon = duckdb.connect("analytics.duckdb")con.execute("CREATE TABLE orders AS SELECT * FROM df")
Read from S3 with httpfs
Install the httpfs extension and query remote object storage directly.
INSTALL httpfs;LOAD httpfs;SET s3_region='us-east-1';SET s3_access_key_id='...';SET s3_secret_access_key='...';SELECT * FROM read_parquet('s3://my-bucket/events/*.parquet') LIMIT 100;
Export Query Results
Write query output to Parquet or CSV using the COPY statement.
COPY ( SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id) TO 'summary.parquet' (FORMAT PARQUET);COPY orders TO 'orders.csv' (HEADER, DELIMITER ',');
CLI Essentials
Common ways to start and use the DuckDB command-line shell.
- duckdb- launches an in-memory database shell
- duckdb mydb.duckdb- opens or creates a persistent on-disk database file
- .mode csv / .mode markdown- changes the shell's output format
- .import file.csv table- loads a CSV into a table from the shell
- EXPLAIN ANALYZE <query>- shows the physical query plan with timings
Window Functions with QUALIFY
Filter on a window function result without wrapping the query in a subquery, using DuckDB's QUALIFY clause.
SELECT customer_id, order_id, amount, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rnFROM ordersQUALIFY rn <= 3; -- top 3 orders per customer, no subquery needed
Reusable Macros and Sequences
Define a SQL macro for reusable logic and a sequence for generating surrogate keys.
CREATE MACRO pct_of(part, total) AS part * 100.0 / total;SELECT status, COUNT(*) AS n, pct_of(COUNT(*), SUM(COUNT(*)) OVER ()) AS pctFROM orders GROUP BY status;CREATE SEQUENCE order_seq START 1;SELECT nextval('order_seq') AS surrogate_id, * FROM staging_orders;
ATTACH Postgres/SQLite and Query Across Engines
Federate a live query across a Postgres table and local Parquet files without an ETL step.
INSTALL postgres;LOAD postgres;ATTACH 'host=localhost dbname=app user=app_ro' AS pg (TYPE postgres);SELECT p.customer_id, p.email, SUM(o.amount) AS lifetime_valueFROM pg.customers pJOIN read_parquet('warehouse/orders/*.parquet') o USING (customer_id)GROUP BY p.customer_id, p.email;
Parameterized Queries and Arrow Interop from Python
Safely bind parameters and exchange data with pyarrow without an intermediate copy through pandas.
import duckdbimport pyarrow as pacon = duckdb.connect("analytics.duckdb")# parameter binding avoids SQL injection and lets DuckDB cache the planrows = con.execute( "SELECT * FROM orders WHERE status = ? AND amount > ?", ["completed", 100]).fetchall()# zero-copy-ish hand-off to/from Arrowarrow_table = con.execute("SELECT * FROM orders").arrow()con.register("arrow_view", arrow_table)con.execute("SELECT COUNT(*) FROM arrow_view").fetchone()
Advanced Concepts
Engine features that matter once you go beyond ad-hoc single-file queries.
- PRAGMA threads=n- caps the number of threads DuckDB uses, useful when co-located with other workloads
- PRAGMA memory_limit='4GB'- bounds working memory so DuckDB spills to disk instead of exhausting RAM
- COPY ... FROM 'data.csv' (UNION_BY_NAME)- merges files with differing column order/subsets by name instead of position
- read_json_auto('file.json')- infers schema and reads nested JSON directly into relational rows
- CREATE VIEW ... AS SELECT ...- persists a named query for reuse without materializing data
- EXPORT DATABASE 'dir' (FORMAT PARQUET)- dumps every table in the database to Parquet files plus a schema script in one command
Reach for duckdb.sql("... FROM df ...") instead of pandas groupby chains on anything over a few million rows — DuckDB's vectorized engine will usually finish in a fraction of the time with far less peak memory.
