Analytics Engineering with dbt and DuckDB

Turn raw data into tested, documented models with dbt Core and DuckDB: sources, staging, marts, tests, snapshots, incremental models and CI.

Intermediate · Data Engineering · about 16 hours · 5 modules · certificate on completion

Taught by Valentinà Lopèz, Marisolò Garcíà, AI Learning Companions.

About this course

Analytics engineers turn raw tables into the clean, trusted models that dashboards, analysts and machine learning depend on, and dbt is the tool most of them use to do it. In this course you'll build a dbt project for a city bike-share scheme on DuckDB, a free database that runs on your laptop: load raw trips and members, declare them as sources with freshness checks, add stations as a seed, and layer staging, intermediate and mart models with ref, Jinja and macros.

Then you'll make the project trustworthy and ready for production: generic, singular and unit tests that catch the duplicated trip, the trip that ends before it starts and the vehicle type no one expected; documentation with a lineage graph; snapshots that keep members' history; an incremental fact table that picks up late data; and CI with separate targets, state selection and a GitHub Actions workflow. Every lab uses real output from dbt Core 1.12, and the capstone models a football club's ticket sales from scratch. Data Warehousing and Dimensional Modelling covers the design of marts in depth, and Orchestrating Data Pipelines with Apache Airflow shows how to schedule dbt alongside other jobs.

What you'll learn

  • Set up a dbt project on DuckDB, and build models that depend on each other with ref
  • Declare sources with freshness checks, load seeds, and write staging, intermediate and mart models
  • Write reusable SQL with Jinja and macros, and select parts of the graph to build
  • Test models with generic, singular and unit tests, and document them with a lineage graph
  • Keep history with snapshots and build large tables incrementally, safely
  • Run dbt in CI with targets, state selection and a GitHub Actions workflow

Skills you'll gain

dbt, Analytics engineering, SQL modelling, Data testing, Jinja and macros, Snapshots, Incremental models, CI for data, Data documentation

Tools

dbt Core, dbt-duckdb, DuckDB, Python 3, GitHub Actions

Before you start

SQL as in SQL for Data Analysis with DataGrip (joins, GROUP BY, CTEs and window functions) and Data Engineering Foundations: Batch and Streaming Pipelines (ELT and idempotent loads), or the same skills. Install Python 3.10 or later and, in a virtual environment, python -m pip install dbt-core dbt-duckdb (tested with dbt-core 1.12.6 and dbt-duckdb 1.11.0). Git and a GitHub account help with the CI lab.

Modules

  1. Analytics engineering and your first dbt project

    What analytics engineering is and where dbt fits, how a dbt project and its profile are set up, and how models, ref and materialisations work.

    • What analytics engineering is (10 min)
    • Projects, profiles and targets (12 min)
    • Models, ref and materialisations (12 min)
    • Hands-on lab: Build your first models on DuckDB

  2. Sources, seeds and staging models

    Declare raw tables as sources and check how fresh they are, load reference data as seeds, and write staging models that give everything downstream a clean start.

    • Sources and freshness (12 min)
    • Seeds (10 min)
    • Staging models (12 min)
    • Hands-on lab: Add sources, a seed and staging models

  3. Intermediate models, marts and Jinja

    Layer business logic into intermediate models and marts, write reusable SQL with Jinja and macros, and select parts of the graph to build.

    • Layers: staging, intermediate and marts (12 min)
    • Jinja and macros (12 min)
    • Selecting and running parts of the graph (12 min)
    • Hands-on lab: Build the intermediate model and the marts

  4. Testing and documenting

    Catch bad data with generic and singular tests, check SQL logic with unit tests, and document the project with descriptions and a lineage graph.

    • Generic data tests (12 min)
    • Singular tests and unit tests (12 min)
    • Documentation and lineage (12 min)
    • Hands-on lab: Test and document the project

  5. Snapshots, incremental models and CI

    Keep the history of changing rows with snapshots, build large tables incrementally, and run dbt in CI and production.

    • Snapshots (12 min)
    • Incremental models (14 min)
    • Running dbt in CI and production (12 min)
    • Hands-on lab: Track changes, load incrementally and add CI

Project: Model a football club's ticket sales

A football club wants trustworthy figures on ticket sales, attendance and its fans for each home match. Load its synthetic data with make_club_data.py (raw.matches, raw.ticket_sales and raw.fans in club.duckdb, plus a sections.csv seed), then build a dbt project from scratch: sources with freshness checks, the seed, staging models, an intermediate model for sales, and marts including an incremental fct_ticket_sales, dim_matches, dim_fans built on a snapshot of the fans, and a match-attendance mart with revenue net of refunds and the share of each section's capacity sold. The raw data has the usual problems: a sale delivered twice, a negative price, and refunds that arrive later as new rows. Load batch 2 to prove that the incremental model and the snapshot handle new sales, late refunds and fans changing tier, and add a CI workflow.

Part of

The products this course names are trademarks of their owners, who haven't made or endorsed it.

Start the course free