DuckDB Cheat Sheet

Every example below uses one small shop: an orders.csv file, an events.parquet file, and a shop.duckdb database holding orders and customers.

Reading Files

DuckDB queries files in place. A quoted path works anywhere a table name does.

Example: revenue by order status since September 1, queried straight from orders.csv.

SELECT status, count(*) AS orders, sum(amount) AS revenue
FROM read_csv('orders.csv')
WHERE created_at >= DATE '2026-09-01'
GROUP BY ALL
ORDER BY revenue DESC;

In 1bench: connect straight to a Parquet, CSV or JSON file at an S3 or HTTPS URL and query it as a table, with S3, MinIO and R2 credentials in the connection form. See the DuckDB GUI.

CLI Commands

Shell flags first, then the dot commands you type inside the duckdb prompt.

Example: export customers ranked by total spend to a CSV file with one shell command.

duckdb shop.duckdb -csv -c "
  SELECT customer_id, sum(amount) AS total
  FROM orders GROUP BY ALL ORDER BY total DESC" > top_customers.csv

In 1bench: open shop.duckdb and the sidebar lists every table and view with its row count, and each table's Schema tab shows its columns and types, so you skip .tables and DESCRIBE. See the DuckDB GUI.

Functions

The functions that come up in almost every query. The full list belongs on its own page.

Example: order count, revenue and refunds per month from the orders table.

SELECT strftime(date_trunc('month', created_at), '%Y-%m') AS month,
       count(*) AS orders,
       round(sum(amount), 2) AS revenue,
       count(*) FILTER (WHERE status = 'refunded') AS refunds
FROM orders
GROUP BY ALL
ORDER BY month;

Filtering and Sorting

Example: each customer's two largest non-refunded orders, using QUALIFY instead of a subquery.

-- each customer's two largest orders
SELECT customer_id, order_id, amount
FROM orders
WHERE status <> 'refunded'
QUALIFY row_number() OVER (PARTITION BY customer_id ORDER BY amount DESC) <= 2
ORDER BY customer_id, amount DESC;

Tables and Writes

Example: load new orders into a temp table, then MERGE them to update statuses and add new rows.

CREATE TEMP TABLE staging AS FROM 'orders_new.csv';

MERGE INTO orders o
USING staging s ON o.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET status = s.status
WHEN NOT MATCHED THEN INSERT BY NAME;

Joins and Reshaping

Example: pivot orders into one row per month with a revenue column for each status.

PIVOT (
  SELECT strftime(created_at, '%Y-%m') AS month, status, amount FROM orders
)
ON status USING sum(amount)
GROUP BY month
ORDER BY month;

Exporting

Example: write the orders table to Parquet folders partitioned by year and month.

COPY (
  SELECT *, year(created_at) AS year, month(created_at) AS month
  FROM orders
) TO 'lake/orders' (FORMAT parquet, PARTITION_BY (year, month), OVERWRITE_OR_IGNORE);

In 1bench: export any table to CSV, JSON, NDJSON or SQL from a dialog, keeping your current filter, sort and visible columns, no COPY needed. See the DuckDB GUI.

ATTACH

ATTACH opens another database file, or a live SQLite, PostgreSQL or MySQL database, next to the current one.

Example: attach a live Postgres database read-only and join its customers to local Parquet events.

ATTACH 'dbname=shop host=localhost user=app' AS pg (TYPE postgres, READ_ONLY);

-- join live Postgres customers to local Parquet events
SELECT c.email, count(*) AS events
FROM pg.public.customers c
JOIN 'events.parquet' e ON e.user_id = c.customer_id
GROUP BY ALL
ORDER BY events DESC;

Extensions

Core extensions autoload on first use. Install them ahead of time for offline machines, or by hand when autoload fails.

Example: count the last 30 days of S3 Parquet events by type, signed in with your AWS credentials.

CREATE SECRET (TYPE s3, PROVIDER credential_chain);

SELECT event, count(*) AS n
FROM 's3://shop-data/events/*.parquet'
WHERE ts >= now() - INTERVAL 30 DAY
GROUP BY ALL
ORDER BY n DESC;

Data Types

Example: find the rows in orders.csv whose amount or timestamp would fail a typed load.

-- find the rows that would break a typed load
SELECT order_id, amount, created_at
FROM read_csv('orders.csv', all_varchar = true)
WHERE TRY_CAST(amount AS DECIMAL(10,2)) IS NULL
   OR TRY_CAST(created_at AS TIMESTAMP) IS NULL;

Python API

pip install duckdb (install failing?). Deeper coverage goes on a separate Python page.

Example: fetch the top 10 paid customers as a pandas DataFrame and save them to Parquet.

import duckdb

with duckdb.connect("shop.duckdb") as con:
    top = con.execute(
        "SELECT customer_id, sum(amount) AS total FROM orders "
        "WHERE status = ? GROUP BY ALL ORDER BY total DESC LIMIT 10",
        ["paid"],
    ).df()

    con.sql("FROM top").write_parquet("top_customers.parquet")

Gotchas

• Only one process can open a .duckdb file for writing. A second writer, or a reader while a writer holds it, fails with a lock error. Open readers with -readonly or read_only=True, and close the Python connection when done.
• duckdb with no file name is in-memory. Everything you create is lost on .exit unless you passed a path or ran ATTACH.
• COPY ... TO overwrites an existing file without asking, and CREATE OR REPLACE TABLE drops the old table first. Partitioned writes refuse a non-empty folder unless you add OVERWRITE, which deletes what was there.
• The CSV sniffer reads the first 20,480 rows. A bad value further down, like N/A in a number column, fails the whole read with a cast error. Use sample_size = -1, set types, or load with all_varchar = true and TRY_CAST.
• The arrow lambda x -> x + 1 is deprecated in 1.5 and prints a warning. Write lambda x: x + 1.
• INSERT OR REPLACE fails when a table has both a primary key and a separate UNIQUE column. Use ON CONFLICT (column) DO UPDATE and name the key.