Skip to content
Tutorials Center
My learning

Topics

DuckDB Tutorial

Learn DuckDB step by step: 38 lessons in 9 chapters with examples, quizzes, diagrams and projects.

Start learning →

of lessons completed

Getting Started

  1. 1 What Is DuckDB? Learn what DuckDB is, why an in-process analytical SQL database is useful, how it differs from SQLite and PostgreSQL, and when it is the right tool for your data.
  2. 2 Installing DuckDB and Running Your First Query Install DuckDB with the CLI, Python, Node.js, R or Java, start a session, run your first SQL queries and learn the one-line ways to query data from the shell.
  3. 3 In-Memory and Persistent Databases Understand where DuckDB keeps data: transient in-memory databases, single-file persistent databases, read-only mode, views over files, and how to choose between them.
  4. 4 Your First Analysis with DuckDB Walk through a complete first analysis in DuckDB style SQL: look at the data, filter, group, join, rank and summarize, with runnable examples on a small shop data set.

Concepts and CLI

  1. 5 A Tour of the DuckDB Command Line Learn the DuckDB command-line shell: dot commands, output modes, reading scripts, timing queries, exporting results, command-line flags and handy shell habits.
  2. 6 Columnar Storage and Vectorized Execution Understand why DuckDB is fast: column-oriented storage, compression, vectorized execution, row groups, zone maps and parallel query processing, with runnable comparisons.
  3. 7 Transactions, Files and Attached Databases Learn DuckDB ACID transactions, the single-writer rule, ATTACH for several databases including SQLite and Postgres, and EXPORT and IMPORT DATABASE for portable backups.
  4. 8 Reading Query Plans with EXPLAIN Learn to read DuckDB query plans with EXPLAIN and EXPLAIN ANALYZE, recognize scans, filters, hash joins and aggregates, and use profiling to find slow queries.

SQL Dialect and Types

  1. 9 Friendly SQL Learn DuckDB's friendly SQL extensions: FROM-first queries, SELECT * EXCLUDE and REPLACE, COLUMNS, GROUP BY ALL, ORDER BY ALL, QUALIFY, alias reuse and trailing commas.
  2. 10 Data Types and Casting in DuckDB Learn DuckDB's rich type system: integers, DECIMAL, floating point, VARCHAR, BOOLEAN, dates, ENUM and UUID, plus CAST, the :: operator, TRY_CAST and division rules.
  3. 11 Lists, Structs and Maps Work with DuckDB nested data: LIST, ARRAY, STRUCT, MAP and UNION values, UNNEST, list comprehensions, lambda functions, list aggregates and dot access into nested columns.
  4. 12 Strings and Regular Expressions Clean and parse text in DuckDB: concatenation, slicing, trimming, LIKE and ILIKE, regexp_matches, regexp_extract, regexp_replace, string_split, format and fuzzy matching.
  5. 13 Dates, Times and Intervals Master DuckDB date and time handling: DATE, TIMESTAMP and INTERVAL types, date_trunc, date_part, date_diff, strptime and strftime parsing, series generation and time zones.
  6. 14 PIVOT, Sampling, Sequences and Macros Reshape and reuse SQL in DuckDB: PIVOT and UNPIVOT, USING SAMPLE, CREATE SEQUENCE and generated columns, scalar and table macros, plus portable stand-ins using conditional aggregation.

Reading Files and Formats

  1. 15 Reading CSV Files Query CSV files directly in DuckDB with read_csv: dialect sniffing, header and delimiter options, explicit types, date formats, error handling, COPY into tables and fixing messy files.
  2. 16 Parquet Files Learn why Parquet is the ideal partner of DuckDB: columnar layout, compression, row groups, statistics, read_parquet, parquet_metadata, projection and filter pushdown, and writing Parquet.
  3. 17 Reading JSON Query JSON and newline-delimited JSON with DuckDB: read_json auto-detection, nested STRUCT columns, the JSON type, json_extract and arrow operators, json_transform and flattening arrays.
  4. 18 Many Files, Globs and Hive Partitions Query hundreds of CSV, Parquet or JSON files as one table in DuckDB: glob patterns, file lists, union_by_name, the filename column, Hive-partitioned folders and partition pruning.
  5. 19 Writing Files and Remote Data Export DuckDB results with COPY to CSV, Parquet and JSON, then reach remote data over HTTPS and S3 with httpfs, use secrets, and read SQLite, Excel and PostgreSQL sources.

Python and Embedded Use

  1. 20 The DuckDB Python API Basics Use DuckDB from Python: import duckdb, run SQL with duckdb.sql and connections, fetch tuples, lists and DataFrames, and bind parameters safely.
  2. 21 DataFrames, Arrow and Zero-Copy Scans Query pandas, Polars and Arrow data directly with SQL in DuckDB, write results back to DataFrames, and understand replacement scans and zero-copy reads.
  3. 22 Method Chaining with the Relational API Build DuckDB queries with Python method chaining: filter, project, aggregate, join, order and limit on lazy relations, and mix them freely with SQL.
  4. 23 Persistence, Connections and Concurrency Learn how DuckDB stores data in a single file, what in-memory means, how cursors and threads share a database, and the one-writer process rule.

Window Functions and Analytics

  1. 24 Window Functions: The Basics Understand window functions in DuckDB: OVER, PARTITION BY and ORDER BY, how a window differs from GROUP BY, and the aggregate functions you can run over a window.
  2. 25 Ranking, Offsets and Running Totals Use ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD and cumulative sums in DuckDB to rank rows, find top-N per group and compare each row with its neighbours.
  3. 26 QUALIFY, Named Windows and Frames Filter window results with DuckDB's QUALIFY, name and reuse windows with the WINDOW clause, and control ROWS, RANGE and GROUPS frames with EXCLUDE and FILTER.
  4. 27 Time Series: Gaps, Buckets and ASOF Joins Analyze time series in DuckDB: bucket timestamps, build a date spine to fill gaps, compute moving averages and match events to the latest prior value with ASOF JOIN.

Extensions and Performance

  1. 28 Extensions: The Plugin System Learn how DuckDB extensions work: INSTALL and LOAD, autoloading, the core extensions (json, parquet, httpfs, icu, fts, spatial) and attaching other databases.
  2. 29 Remote Data: HTTP, S3 and Cloud Storage Query Parquet and CSV files over HTTP and S3 with DuckDB httpfs: range requests, projection and filter pushdown, secrets, globs and writing results back to buckets.
  3. 30 Performance: The Vectorized Engine Why DuckDB is fast: columnar storage, vectorized execution, parallelism and compression, plus practical tuning of threads, memory limits, types and file formats.
  4. 31 Reading Plans: EXPLAIN and Profiling Read DuckDB query plans with EXPLAIN and EXPLAIN ANALYZE, find the slow operator, use the profiler and JSON output, and fix common problems such as bad joins and scans.

Patterns and Pipelines

  1. 32 The ETL Pipeline Pattern Design a small DuckDB data pipeline with raw, staging and mart layers: land files, clean and type columns with SQL, build tables, and export Parquet for others.
  2. 33 Incremental Loads and Partitioned Output Load only new data with watermarks, upsert with INSERT OR REPLACE and ON CONFLICT, MERGE-style updates, and write Hive-partitioned Parquet with COPY PARTITION_BY.
  3. 34 Data Quality and Testing Pipelines Protect a DuckDB pipeline with SQL data-quality checks: nulls, duplicates, ranges, referential integrity, row-count reconciliation, constraints and pytest unit tests.

Projects and Practice

  1. 35 Project: Sales Analytics Build a sales analytics report step by step: revenue by month, top products per category, customer ranking, month-over-month growth and a final mart, ready for DuckDB.
  2. 36 Project: Web Log Analysis Parse and analyze web server access logs: traffic per hour, error rates, slowest endpoints with percentiles, top visitors and sessions, using SQL that scales to GBs in DuckDB.
  3. 37 Project: A Parquet Data Lake Pipeline Build a small data lake with DuckDB: land daily files, validate and dedupe, write partitioned Parquet, compact small files, expose a view and run quality checks daily.
  4. 38 Practice: Analytics Query Workout Ten analytics SQL exercises with solutions: aggregates, joins, windows, top-N per group, gaps, deduplication and cohorts, plus DuckDB-only variations for each.