GuideBeginner
Dbt Analytics Engineering
Takes the dimensional warehouse that `data-modeling-for-analytics-guide` designed and built by hand for Kiosko — star schema, a historized `dim_product` with SCD type 2, an accumulating snapshot, a wide table for BI — and turns it into a real software project with dbt-core + dbt-duckdb. You learn the anatomy of a dbt project (sources, staging, marts), the dependency graph via `ref()` and materializations (view, table, incremental), how to write declarative tests (the four built-in generic tests, a custom generic test, and singular tests for business rules), how to automate SCD type 2 with dbt snapshots (instead of a hand-written `MERGE INTO`), how to build idempotent incremental models, and how to generate documentation and lineage automatically with `dbt docs generate`. Everything runs locally with dbt-core and dbt-duckdb, with no dbt Cloud account and no cloud provider required. It closes with a capstone that ports the rest of Kiosko's marts into the project and runs `dbt build` end to end: sources, snapshot, every model in DAG order, and every test, with a literal `PASS/WARN/ERROR` report.
- 64
- lessons
- 8
- modules
- English · Spanish
- available in
- Yes
- certificate
- Free
- access
Outcomes
What you'll be able to do
- Install dbt-core + dbt-duckdb and understand the minimal anatomy of a dbt project (`dbt_project.yml`, `profiles.yml`, `models/`)
- Declare Kiosko's raw files as `source()` and build staging models (`stg_orders`, `stg_events`, `stg_stores`, `stg_products`) reading CSV/JSONL directly with no prior load step
- Use `ref()` to build the project's dependency graph, and choose between `view` and `table` materializations with real criteria
- Write declarative tests: the four built-in generic tests (`unique`, `not_null`, `accepted_values`, `relationships`), a custom generic test, and singular tests for business rules
- Automate `dim_product`'s SCD type 2 with a dbt snapshot (`dbt snapshot`, `timestamp` strategy), replacing the manual `MERGE INTO`
- Convert a model to incremental (`materialized: incremental`, `is_incremental()`) and prove idempotency by running it twice on the same date
- Write a reusable Jinja macro, document models and columns, and generate automatic documentation and lineage with `dbt docs generate`
- Assemble a complete dbt project and run it end to end with `dbt build`, integrating sources, snapshot, models, and tests in the correct DAG order
Before you start
What you need to bring
It's for you if...
- Anyone who already completed `data-modeling-for-analytics-guide` (or has an equivalent dimensional model) and needs to turn it into a versioned, tested project
- Analytics engineers and data engineers who run transformation SQL by hand, in loose scripts, with no version control or tests
- Devs preparing for roles where dbt is a core requirement (data consulting, roles built around Snowflake/BigQuery)
- Anyone who needs to automate SCD type 2 without hand-writing `MERGE INTO` on every schema change
Requirements and materials
- `data-modeling-for-analytics-guide` completed (or equivalent: a working dimensional model with a star schema and SCD type 2)
- Intermediate SQL: `SELECT`, `JOIN`, `GROUP BY`, basic CTEs
- A personal laptop with Python 3 and the ability to install dbt-core + dbt-duckdb (`pip install dbt-core dbt-duckdb`) — everything runs locally at $0
Content
The syllabus, module by module
Open any of them to see its lessons.
- Module introduction: from a toy model to Kiosko's real data
- Declaring Kiosko's raw data as sources
- The `source()` function and why it matters
- Reading files directly with `external_location`
- Staging models: one model per source table
- Naming staging models: the `stg_` prefix
- Building `stg_orders` and `stg_events`
- Mini-project: Kiosko's complete staging layer
- Module overview: from staging views to the star schema with `ref()`
- The `ref()` function and the DAG it builds
- Materializations: `view` versus `table`
- Rebuilding `dim_store` and `dim_date`
- Rebuilding `fact_orders` with `ref()`
- Choosing materializations for Kiosko
- Inspecting the DAG with `dbt ls`
- Mini-project: Kiosko's star schema in dbt
- Module overview: from a star schema that runs to a star schema you trust
- What a data test actually checks
- Generic tests: `unique` and `not_null` on `fact_orders`
- Generic tests: `accepted_values` and `relationships`
- Writing your own generic test: `test_is_positive`
- Singular tests for business rules
- Reading `dbt test`'s report
- Mini-project: Kiosko's test suite
- Module overview: from a star schema you trust to a star schema that remembers its past
- What a dbt snapshot actually does
- `timestamp` vs `check`: a snapshot's two strategies
- Configuring `dim_product_snapshot`
- Running the first snapshot
- Changing the source and snapshotting again
- Querying the historized `dim_product`
- Mini-project: Kiosko's SCD type 2 in dbt
Common questions
What people usually ask
No limit. It's a free guide: come in whenever you like, as often as you like.
No. Modules run from easier to harder, but you can jump to the one you need. Progress is saved per lesson.
Whatever is needed is listed under “What you need to bring”, above. If nothing is listed there, you can start from zero.
In the Club's WhatsApp group, and every two weeks there's a live with an instructor where questions get worked through.
Yes. It's issued automatically once you finish every lesson, with a verifiable code you can share on LinkedIn.
Start whenever you like
What students say
These reviews are from enrolled students who completed at least 50% of the course. We moderate reviews only on content grounds (spam, offensive language, personal data), never for being critical or negative.
No approved reviews yet.
Be the first to share your experience!