DuckDB Cheatsheet

Q: How is DuckDB different from SQLite?

DuckDB is optimized for analytical (OLAP) workloads — columnar storage, vectorized execution, parallel queries. SQLite is optimized for transactional (OLTP) workloads. DuckDB excels at aggregate queries over large datasets, while SQLite excels at single-row operations. Use DuckDB for data analysis, SQLite for application storage.

Q: Can DuckDB query files without importing them?

Yes! Use `read_csv_auto('file.csv')`, `read_parquet('file.parquet')`, or `read_json_auto('file.json')` to query files directly without importing. Use glob patterns like `read_csv_auto('data/*.csv')` for multiple files. Create a table with `CREATE TABLE t AS SELECT * FROM read_csv_auto('f.csv')` if you need repeated queries.

Q: What are the most useful DuckDB extensions?

Install with `INSTALL name; LOAD name;`. Key extensions: `httpfs` (S3/HTTP file access), `spatial` (GIS/geospatial), `json` (advanced JSON), `parquet` (Parquet I/O), `postgres_scanner` (query PostgreSQL), `sqlite_scanner` (query SQLite), `delta` (Delta Lake), `iceberg` (Apache Iceberg), `excel` (Excel files).

DuckDB is a high-performance embedded analytical database designed for OLAP workloads. It is serverless and supports direct SQL queries on CSV, Parquet, and JSON files, with a rich extension ecosystem.

Updated: 2026-07-20·46 commands

Quick Start

bash
duckdb mydb.duckdb                                  # Open/create database
duckdb -c "SELECT version()"                        # One-shot query
SELECT * FROM read_csv_auto('data.csv')             # Query CSV directly
CREATE TABLE t AS SELECT * FROM read_parquet('f.parquet')  # Import Parquet
SUMMARIZE SELECT * FROM t                           # Profile data

Install & Setup(5)

Command	Level	Example
`brew install duckdb` Install DuckDB via Homebrew	Basic	`brew install duckdb`
`pip install duckdb` Install DuckDB Python package via pip	Basic	`pip install duckdb`
`curl -fsSL https://install.duckdb.org \| sh` Install DuckDB CLI via install script	Basic	`curl -fsSL https://install.duckdb.org \| sh`
`duckdb --version` Check DuckDB version	Basic	`duckdb --version`
`duckdb --help` Show DuckDB help	Basic	`duckdb --help`

Basic Usage(7)

Command	Level	Example
`duckdb` Start DuckDB CLI (in-memory database)	Basic	`duckdb`
`duckdb mydb.db` Open/create a persistent database file	Basic	`duckdb mydb.duckdb`
`duckdb -c "SELECT 1"` Execute a single SQL query and exit	Basic	`duckdb -c "SELECT current_database(), current_version();"`
`.tables` List all tables in current database	Basic	`.tables`
`.schema TABLE` View CREATE TABLE statement for a table	Basic	`.schema employees`
`.databases` List all attached databases	Basic	`.databases`
`.quit` Exit DuckDB CLI	Basic	`.quit`

Query & Analysis(8)

Command	Level	Example
`SELECT * FROM read_csv_auto('file.csv')` Auto-infer schema and read CSV file	Basic	`SELECT * FROM read_csv_auto('data.csv')`
`SELECT * FROM read_csv_auto('*.csv')` Read multiple CSV files (glob pattern)	Intermediate	`SELECT * FROM read_csv_auto('data/*.csv')`
`SELECT * FROM read_parquet('file.parquet')` Query Parquet files directly	Basic	`SELECT * FROM read_parquet('sales.parquet')`
`SELECT * FROM read_parquet('*.parquet')` Query multiple Parquet files	Intermediate	`SELECT count() FROM read_parquet('partitions/.parquet')`
`SUMMARIZE TABLE` Generate statistical summary of a table	Basic	`SUMMARIZE SELECT * FROM employees`
`EXPLAIN ANALYZE SQL` Analyze query execution plan and performance	Intermediate	`EXPLAIN ANALYZE SELECT count(*) FROM orders WHERE amount > 100`
`SELECT * FROM read_json_auto('file.json')` Auto-infer schema and read JSON file	Basic	`SELECT * FROM read_json_auto('events.json')`
`SELECT * FROM delta_scan('path/to/delta/table')` Query Delta Lake tables directly	Expert	`SELECT * FROM delta_scan('s3://bucket/delta-table/')`

Table Ops(8)

Command	Level	Example
`CREATE TABLE t AS SELECT * FROM read_csv_auto('f.csv')` Create table from CSV	Basic	`CREATE TABLE employees AS SELECT * FROM read_csv_auto('employees.csv')`
`CREATE TABLE t AS SELECT * FROM read_parquet('f.parquet')` Create table from Parquet	Basic	`CREATE TABLE sales AS SELECT * FROM read_parquet('sales.parquet')`
`INSERT INTO t SELECT * FROM read_csv_auto('f.csv')` Append CSV data into existing table	Intermediate	`INSERT INTO employees SELECT * FROM read_csv_auto('new_employees.csv')`
`.import FILE TABLE` Import data from file into table	Basic	`.import data.csv my_table`
`COPY t TO 'output.csv' (HEADER, DELIMITER ',')` Export table to CSV file	Intermediate	`COPY employees TO 'employees_export.csv' (HEADER, DELIMITER ',')`
`COPY t TO 'output.parquet'` Export table to Parquet file	Intermediate	`COPY orders TO 'orders.parquet' (FORMAT PARQUET)`
`DROP TABLE t` Drop a table	Basic	`DROP TABLE temp_data`
`DESCRIBE TABLE` View table column information	Basic	`DESCRIBE employees`

Format & Export(8)

Command	Level	Example
`duckdb -csv -c SQL` Output query results in CSV format	Basic	`duckdb -csv -c "SELECT * FROM employees"`
`duckdb --json -c SQL` Output query results in JSON format	Basic	`duckdb --json -c "SELECT * FROM employees"`
`duckdb -list -c SQL` List format output (no borders, pipe-friendly)	Basic	`duckdb -list -c "SELECT name FROM employees"`
`duckdb -readonly mydb.db` Open database in read-only mode	Intermediate	`duckdb -readonly production.db -c "SELECT count(*) FROM users"`
`.mode markdown` Set output format to Markdown table	Intermediate	`.mode markdown`
`.mode box` Set output format to box table	Intermediate	`.mode box`
`.maxrows N` Set maximum rows to display	Basic	`.maxrows 50`
`.separator SEP` Set CSV separator	Intermediate	`.separator '\|'`

Advanced(10)

Command	Level	Example
`INSTALL EXTENSION` Install a DuckDB extension	Intermediate	`INSTALL httpfs; LOAD httpfs;`
`LOAD EXTENSION` Load an installed extension	Intermediate	`LOAD spatial;`
`SELECT * FROM read_csv_auto('s3://bucket/file.csv')` Query CSV directly from S3	Expert	`SELECT * FROM read_csv_auto('s3://my-bucket/data.csv')`
`ATTACH 'other.db' AS other` Attach another database file	Intermediate	`ATTACH 'archive.duckdb' AS archive`
`PRAGMA table_info('t')` View detailed column info for a table	Intermediate	`PRAGMA table_info('employees')`
`PRAGMA database_list` List all attached databases	Intermediate	`PRAGMA database_list`
`SELECT version()` View DuckDB version info	Basic	`SELECT version()`
`SELECT * FROM duckdb_extensions()` List installed and available extensions	Intermediate	`SELECT * FROM duckdb_extensions()`
`SELECT * FROM duckdb_tables()` List metadata for all tables in database	Intermediate	`SELECT * FROM duckdb_tables()`
`SELECT * FROM duckdb_columns()` List metadata for all columns in database	Intermediate	`SELECT * FROM duckdb_columns()`

FAQ

How is DuckDB different from SQLite?

Can DuckDB query files without importing them?

What are the most useful DuckDB extensions?

This cheatsheet is compiled from official tool documentation. Last updated: 2026-07-20.