Skip to Content
View all events

Transforming and Analyzing Data With DuckDB

Published by O'Reilly Media, Inc.

Beginner to intermediate content levelBeginner to intermediate

From zero to analytical power in just 4 hours

What you’ll learn and how you can apply it

  • Understand DuckDB’s different data formats and their advantages/disadvantages
  • Build efficient SQL statements for DuckDB
  • Develop Jupyter notebooks in Python interfacing with DuckDB
  • Overcome restrictions of pandas with DuckDB
  • Design scalable data analysis pipelines for large heterogeneous data

Course description

DuckDB, the open source, fast, embedded analytics engine, is transforming how data analysts and engineers work with CSV, Parquet, and JSON files; and no server or ingestion pipeline is required. Join Christian Winkler to get this technology into your tool kit.

You’ll go from installing DuckDB and writing your first query against a local file, to combining real-world datasets, optimizing performance, and querying data from different file formats, working directly in DuckDB or alongside pandas and Polars in Jupyter notebooks. Whether you’re a data scientist, a data analyst with some SQL knowledge, or a Python practitioner looking for a faster, simpler alternative to heavyweight databases while struggling with slow data frames, you’ll leave with practical skills, a working cheat sheet, and a capstone project under your belt.

This live event is for you because...

  • You’re a data analyst, data scientist, or a Python programmer with a data focus.
  • You work with different data formats and want to query or consolidate them.
  • You want to become an expert in online analytical processing (OLAP).
  • You struggle with pandas being too slow or taking up too many resources in your daily work.
  • You want to speed up your data-driver Jupyter notebooks.

Prerequisites

Prerequisites:

  • DuckDB installed on your computer
  • Python and Jupyter, including the duckdb package, installed
  • Clone the GitHub repository, including the data (link to come), or work directly in Google Colab
  • Basic SQL skills
  • Basic Python skills
  • Basic knowledge about data formats (helpful but not required)
  • No prior DuckDB experience needed

Recommended follow-up:

Schedule

The time frames are only estimates and may vary according to how the class is progressing.

What is DuckDB? (30 minutes)

  • Presentation: SQLite for analytics; OLAP database; how it’s different from PostgreSQL/MySQL and Spark
  • Group discussion: Who has problems like this?
  • Hands-on exercise: Install DuckDB and run first query on CLI and web interface
  • Q&A

Getting data in and out (30 minutes)

  • Presentation: Querying CSV, JSON, and Parquet, using glob syntax (*.json); schema inference; persistent and in-memory databases
  • Group discussion: What are use cases for persistent and transient databases?
  • Hands-on exercise: Query multiple *.json files, transform to Parquet format, and query Parquet
  • Q&A
  • Break

SQL in DuckDB (40 minutes)

  • Presentation: SQL refresh; special features of DuckDB like GROUP BY ALL, and PIVOT; special datatypes
  • Group discussion: Which features are most important to you?
  • Hands-on exercise: Try complex queries on sample dataset
  • Q&A

DuckDB integration in Python (30 minutes)

  • Presentation: duckdb.sql(); querying pandas or polars DataFrames; converting results; working with temporary tables
  • Group discussion: What would be use cases in your daily work for that?
  • Hands-on exercise: Run Jupyter notebook
  • Q&A
  • Break

Performance (20 minutes)

  • Presentation: Vectorized execution; columnar storage; good match with Parquet; EXPLAIN and EXPLAIN ANALYZE; early filter; the fields you’ll need
  • Group discussion: Which of your data would be suited for columnar storage?
  • Hands-on exercise: Run different queries and observe performance
  • Q&A

Real-life data analytics example (30 minutes)

  • Presentation: Tackling a complicated data analysis project with large amounts of data
  • Group discussion: How would you do that in pure Python?
  • Hands-on exercise: Perform the analysis as a hybrid Jupyter-DuckDB project
  • Q&A

Your Instructor

  • Christian Winkler

    Christian Winkler has worked with large amounts of data for several decades, and specializes in advanced analytics. As a professor at the Technical University of Applied Science in Nuremberg, he concentrates his research on optimizing user experience using modern data-based approaches. He researches and publishes articles about data management and large language models and is a regular contributor to machine learning conferences.

Skills covered

  • Apache Spark
  • Dask
  • Pandas