← All Cheat Codes Library / Cheat Codes / DuckDB

🦆 DuckDB Cheat Codes

DuckDB Cheat Codes

DuckDB Cheat Codes for in-process analytical SQL, direct Parquet reading, Pandas zero-copy, and SUMMARIZE. 20 essential cheat codes ready to copy and execute.

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

# 1 SELECT DQL

Query local in-memory or file datasets.

Syntax:

SELECT col1, col2 FROM tbl;

Working Example:

SELECT country, amount FROM sales;

# 2 WHERE DQL

Filter output rows.

Syntax:

SELECT * FROM tbl WHERE col = val;

Working Example:

SELECT * FROM orders WHERE status = 'shipped';

# 3 JOIN Joins

Join datasets in vectorized execution engine.

Syntax:

SELECT * FROM t1 JOIN t2 ON t1.id = t2.fk;

Working Example:

SELECT u.name, s.amount FROM users u JOIN sales s ON u.id = s.user_id;

# 4 GROUP BY Aggregations

Vectorized aggregation grouping.

Syntax:

SELECT col, AVG(x) FROM tbl GROUP BY col;

Working Example:

SELECT category, AVG(price) FROM products GROUP BY category;

# 5 read_parquet() File Access

Query Parquet files directly using SQL without import.

Syntax:

SELECT * FROM read_parquet('file.parquet');

Working Example:

SELECT country, COUNT(*) FROM read_parquet('data/sales_2026.parquet') GROUP BY country;

# 6 COPY TO (.parquet) Export

Export query results directly into compressed Parquet file.

Syntax:

COPY (SELECT ...) TO 'output.parquet' (FORMAT PARQUET);

Working Example:

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

# 7 read_csv_auto() File Access

Auto-detect CSV schema and query file directly.

Syntax:

SELECT * FROM read_csv_auto('file.csv');

Working Example:

SELECT * FROM read_csv_auto('logs/*.csv') WHERE status = 500;

# 8 SUMMARIZE EDA Stats

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

Syntax:

SUMMARIZE SELECT * FROM tbl;

Working Example:

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

# 9 QUALIFY SQL Extensions

Filter window function output directly without subquery wrappers.

Syntax:

SELECT col, ROW_NUMBER() OVER(PARTITION BY grp ORDER BY val DESC) as rn FROM tbl QUALIFY rn = 1;

Working Example:

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

# 10 PIVOT / UNPIVOT Analytics

Rotate quarterly row values into reporting columns.

Syntax:

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

Working Example:

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

# 11 Pandas DataFrame Zero-Copy Ecosystem

Query Python Pandas DataFrame directly in DuckDB.

Syntax:

import duckdb; duckdb.query('SELECT * FROM df');

Working Example:

import duckdb, pandas as pd; df = pd.DataFrame({'a': [1,2]}); duckdb.query('SELECT AVG(a) FROM df').df();

# 12 INSTALL httpfs / LOAD httpfs Cloud Access

Enable HTTP and S3 remote file querying.

Syntax:

INSTALL httpfs; LOAD httpfs;

Working Example:

INSTALL httpfs; LOAD httpfs; SELECT * FROM 's3://my-bucket/data.parquet' LIMIT 10;

# 13 CREATE SECRET (S3) Security

Store S3 access key and secret token.

Syntax:

CREATE SECRET s3_dev (TYPE S3, KEY_ID '...', SECRET '...');

Working Example:

CREATE SECRET s3_dev (TYPE S3, KEY_ID 'AKIA...', SECRET 'wJal...', REGION 'us-east-1');

# 14 EXPLAIN ANALYZE Performance

Display vectorized query execution plan and timing metrics.

Syntax:

EXPLAIN ANALYZE SELECT ...;

Working Example:

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

# 15 PRAGMA enable_profiling Performance

Output JSON query profiling timings to file.

Syntax:

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

Working Example:

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

# 16 CREATE TABLE AS SELECT DDL

Create table from Parquet or CSV query result.

Syntax:

CREATE TABLE tbl AS SELECT * FROM read_parquet('file.parquet');

Working Example:

CREATE TABLE orders_cache AS SELECT * FROM read_parquet('s3://bucket/orders.parquet');

# 17 read_json_auto() File Access

Query JSON or NDJSON files directly.

Syntax:

SELECT * FROM read_json_auto('file.json');

Working Example:

SELECT * FROM read_json_auto('events/*.json') WHERE type = 'signup';

# 18 DENSE_RANK() OVER Window

Rank rows without rank gaps.

Syntax:

SELECT col, DENSE_RANK() OVER(ORDER BY val DESC) FROM tbl;

Working Example:

SELECT player, score, DENSE_RANK() OVER(ORDER BY score DESC) FROM leaderboard;

# 19 UNION ALL Queries

Combine result rows from multiple queries preserving duplicates.

Syntax:

SELECT col FROM t1 UNION ALL SELECT col FROM t2;

Working Example:

SELECT email FROM leads UNION ALL SELECT email FROM customers;

# 20 ALTER TABLE RENAME COLUMN DDL

Rename column inside table schema.

Syntax:

ALTER TABLE tbl RENAME COLUMN old_name TO new_name;

Working Example:

ALTER TABLE users RENAME COLUMN fname TO first_name;