Topolearn

Learn DuckDB

In-process analytical SQL database for files, DataFrames and laptops

15 lessons · 3 hours · 4 levels · covers DuckDB 1.5

DuckDB is an embedded, columnar SQL database built for analytics: it runs inside your Python process, CLI or app with no server, and queries CSV, Parquet, JSON and DataFrames directly. It is the fastest way to do serious SQL analysis on a single machine, from a few megabytes to hundreds of gigabytes. Learn it to replace slow scripts and heavy clusters for everyday data work.

Sign in free to track progressOfficial docs ↗

Before you start

DuckDB builds on these; the tree links to the exact lessons you need.

SQLEssentials level

How deep do you want to go?

1. EssentialsRead and write simple code, do the everyday basics4 lessons · 38 min in total
2. PracticalUse it productively at work, with the usual idioms9 lessons · 2 hours in total
3. AdvancedThe less common features, performance and trade-offs12 lessons · 2 hours in total
4. ExpertInternals and the edge cases experts know15 lessons · 3 hours in total

The DuckDB tree

DuckDBWhat DuckDB isWhat DuckDB isCLI, files and persistenceCLI, files andpersistenceQuerying files directlyQuerying filesdirectlyThe Python APIThe Python APIFriendly SQL extensionsFriendly SQLextensionsReading messy CSV filesReading messy CSVfilesParquet, globs and partitionsParquet, globs andpartitionsExporting with COPYExporting with COPYSQL on pandas, Polars and ArrowSQL on pandas,Polars and ArrowSELECT: reading dataSELECT: reading dataCreating and altering tablesCreating and alteringtablesGROUP BY and HAVINGGROUP BY andHAVINGWindow functionsWindow functionsReading query plans with EXPLAINReading query planswith EXPLAINTransactions and ACIDTransactions andACID
Essentials and Practical shown (9 of 15 lessons); the full list is below. Click a lesson to read it.

All 15 lessons, in order

Getting started

  1. What DuckDB is · Essentials · 8 min

    An in-process, columnar SQL engine for analytics: no server, one library, very fast on a single machine.

  2. CLI, files and persistence · Essentials · 10 min

    Use the duckdb shell, open a persistent .duckdb file and create tables that survive restarts.

Querying files

  1. Querying files directly · Essentials · 10 min

    Run SQL straight over CSV, Parquet and JSON files by putting the path in the FROM clause.

  2. Reading messy CSV files · Practical · 12 min

    Control read_csv's auto-detection: delimiters, headers, types, dates and bad rows.

  3. Parquet, globs and partitions · Practical · 12 min

    Query many files at once with glob patterns and Hive-partitioned folders, and let Parquet skip data.

  4. Exporting with COPY · Practical · 10 min

    Write tables and query results to Parquet, CSV or JSON, optionally partitioned, with COPY ... TO.

  5. Extensions, S3 and other databases · Advanced · 14 min

    Install extensions to read S3 and HTTPS, attach PostgreSQL, MySQL or SQLite, and use spatial, full-text and more.

Python and DataFrames

  1. The Python API · Essentials · 10 min

    Run queries with duckdb.sql or a connection, pass parameters safely and fetch results as tuples.

  2. SQL on pandas, Polars and Arrow · Practical · 12 min

    Query DataFrames by their variable name and get results back as pandas, Polars or Arrow without copying rows.

DuckDB SQL

  1. Friendly SQL extensions · Practical · 12 min

    Write shorter queries with GROUP BY ALL, SELECT * EXCLUDE/REPLACE, FROM-first syntax and COLUMNS().

  2. Lists, structs and maps · Advanced · 14 min

    Store and query nested data with LIST, STRUCT and MAP types, unnest them, and transform lists with lambdas.

  3. QUALIFY, ASOF joins and PIVOT · Advanced · 15 min

    Use DuckDB's analytics features: filter window results with QUALIFY, match time series with ASOF JOIN, reshape with PIVOT.

Performance and internals

  1. Vectorized execution and tuning · Expert · 15 min

    How DuckDB executes queries (columnar vectors, morsel parallelism, spilling) and how to read EXPLAIN ANALYZE and tune memory and threads.

  2. Storage format and concurrency · Expert · 14 min

    Inside the .duckdb file: row groups, compression, zone maps, MVCC transactions and the single-writer rule.

  3. How the optimizer rewrites queries · Expert · 14 min

    See what DuckDB's optimizer does to your SQL: pushdown, join ordering, build-side choice, Top-N and statistics propagation.