DuckDB Query Cookbook

In-process analytical database engine built for lightning-fast vectorized SQL queries over local files. 10 production-tested query recipes ready to copy and execute.

🐘 PostgreSQL 🐬 MySQL 🪶 SQLite 🔴 Oracle Database 💻 Microsoft SQL Server 🍃 MongoDB 🔴 Redis ❄️ Snowflake 🍒 ClickHouse 🦆 DuckDB

1. Query Parquet File Directly with SQL

Execute SQL queries directly over local or remote Parquet files.

SELECT country, COUNT(*), AVG(amount)
FROM read_parquet('data/sales_2026.parquet')
WHERE status = 'completed'
GROUP BY country;

#parquet#read_parquet#file

2. Export Query Results to Parquet File

Write compressed Parquet file directly from SQL query.

COPY (
  SELECT * FROM orders WHERE order_date >= '2026-01-01'
) TO 'orders_2026.parquet' (FORMAT PARQUET, COMPRESSION SNAPPY);

#copy#export#parquet

3. Query Wildcard CSV Files (Globbing)

Query multiple CSV files matching a path wildcard pattern.

SELECT * FROM read_csv_auto('logs/2026-*.csv')
WHERE response_code = 500;

#csv#glob#read_csv

4. Query Python Pandas DataFrame Zero-Copy

Query in-memory Python DataFrames natively inside DuckDB.

-- In Python:
-- import duckdb, pandas as pd
-- df = pd.DataFrame({'a': [1, 2], 'b': [3, 4]})
-- result = duckdb.query('SELECT AVG(b) FROM df').df()

#python#pandas#zero_copy

5. Filter Window Functions with QUALIFY

Filter window outputs directly without CTE wrappers.

SELECT customer_id, order_date, amount,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) as rn
FROM read_parquet('orders.parquet')
QUALIFY rn = 1;

#qualify#window#duckdb

6. Query Remote S3 Parquet Data Lake

Attach S3 credentials and query remote cloud files.

INSTALL httpfs; LOAD httpfs;
SET s3_region='us-east-1';

SELECT * FROM 's3://my-bucket/events/*.parquet'
LIMIT 100;

#s3#httpfs#cloud

7. Inspect Column Summary Statistics (SUMMARIZE)

Generate statistical summary (nulls, min, max, avg) for all table columns.

SUMMARIZE SELECT * FROM read_parquet('dataset.parquet');

#summarize#eda#stats

8. Dynamic PIVOT Query

Pivot quarterly row totals into analytical columns.

PIVOT (SELECT year, quarter, sales FROM quarterly_sales)
ON quarter IN ('Q1', 'Q2', 'Q3', 'Q4')
USING SUM(sales);

#pivot#analytics#transform

9. Manage S3 Credentials with CREATE SECRET

Securely store cloud authentication tokens in DuckDB.

CREATE SECRET s3_dev (
  TYPE S3,
  KEY_ID 'AKIAIOSFODNN7EXAMPLE',
  SECRET 'wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY',
  REGION 'us-east-1'
);

#secret#security#s3

10. Profile Query Execution Plan & Timings

Enable query profiling to inspect execution stage durations.

PRAGMA enable_profiling = 'json';
PRAGMA profiling_output = 'profile.json';

SELECT COUNT(*) FROM read_parquet('large_file.parquet');

#profiling#performance#benchmark

Test your DuckDB architectural knowledge!

Practice key concepts, memory structures, and indexing questions with flashcards.

Study DuckDB Flashcards →