Transforming Data with dbt
Transforming Data with dbt is an intermediate-level course of 5 lessons (about 1 h 32 min), with 21 exercises and 20 quiz questions. Prerequisites: Data Warehouses and Analytical Modeling. The course needs a full project environment, planned for a later phase of the platform.
- Data engineering
- Intermediate
- 5 lessons
- 1 h 32 min
- 21 exercises
- Full project environment (phase 2)
The course in brief
This course teaches you to organize a warehouse's transformations with dbt. You will write models linked by ref() and source(), use Jinja and macros, declare data tests, build incremental models and snapshots, then document the project and integrate it into a continuous integration pipeline. The exercises have you write the compiled SQL, run and verified.
What you will be able to do
- Structure a dbt project in layers with sources, staging models and marts
- Choose a materialization and use Jinja and macros without hurting readability
- Declare generic and singular tests and interpret their failures
- Write an incremental model that is robust to updates and late-arriving data
- Historize a source with a snapshot
- Document a project, use lineage and verify it in continuous integration
Syllabus: 5 lessons
- Explain the role of dbt in an ELT chain
- Declare sources and write staging models with source()
- Link models together with ref() and understand the resulting graph
- 02
Jinja, Macros and Materializations
- Choose the right materialization for a model (view, table, ephemeral, incremental)
- Use Jinja variables and loops to generate SQL
- Write and call a reusable macro
- 03
Testing the Data
- Declare the generic tests unique, not_null, accepted_values and relationships in YAML
- Write a singular test in SQL
- Read a test as a query that returns the failing rows
- 04
Incremental Models and Snapshots
- Write an incremental model with is_incremental() and {{ this }}
- Choose a unique key and a strategy to handle updates
- Historize a source with a snapshot (SCD type 2)
Incremental modelis_incremental and thisunique_key and strategiesSnapshots
- 05
Documentation, Lineage and Best Practices
- Document models and columns and generate the documentation site
- Use the lineage graph to select and analyze the impact of a change
- Organize a dbt project and integrate it into a review and continuous integration process
DocumentationLineage and selectionExposuresContinuous integration
Final project
dbt Project for an Online Shop, from Source to Dashboard
A fictional shop loads its orders, customers and payments into a raw schema every night, and its customers regularly change segment. Build the complete dbt project that feeds a sales dashboard: staging, intermediate and marts layers, tests, customer history and continuous integration.
Deliverables
- A Git repository containing the dbt project (dbt_project.yml, sources, models, macros, tests, snapshots)
- The staging models of the three sources and the fct_orders and dim_customers marts
- Generic tests on all keys and at least two singular business tests
- fct_orders as an incremental model with unique_key and an overlap window
- A customer snapshot and a dim_customers that uses it
- The generated documentation, an exposure for the dashboard and a CI file using state:modified+
Assessment criteria
- No table built by dbt is hard-coded; each source is read by a single staging model
- All primary keys are tested and dbt build passes without errors
- The incremental model produces the same result as a --full-refresh on the same data
- Each marts model has a description that states its grain
- The compiled SQL stays readable and materialization choices are justified
Frequently asked questions
What is dbt?
dbt (data build tool) is an open-source tool that compiles SELECT queries enriched with Jinja and runs them in the data warehouse, managing dependency order, tests and documentation.
Do I need to know how to program to use dbt?
Mostly you need to know SQL well. Jinja is learned progressively, and a few notions of Git and the command line are enough to get started.
What is the difference between a dbt test and a unit test?
A data test checks the real data of a model (uniqueness, null values, relationships). A unit test, available since dbt 1.8, checks the logic of a model on small fixed input data.
What are the prerequisites for this course?
The course assumes advanced SQL and analytical modeling: star schema, warehouse layers and slowly changing dimensions, covered in the Data Warehouses and Analytical Modeling course.
This course, told for your job
Say what you want to be able to do: “Transforming Data with dbt” takes its place in your path, with examples from your field. The concepts stay those of the course.