All roadmaps
Analytics engineer
Analytics engineer path
Turn raw tables into clean, tested models that analysts can trust, using SQL and dbt.
- about 13 weeks at an hour a day
- 5 stages, 52 steps
Strong SQL
about 3 weeks · 11 stepsAfter this stage: You write readable SQL with CTEs and windows.
- Subqueries and CTEsLessonLockedBreak a hard query into steps.01
- Window functionsLessonLockedRankings and running totals.02
- Advanced analytics patternsLessonLockedFunnels, retention and gaps.03
- SQL 50 practice sheetPracticeKeep the basics sharp.04
- Case study: do new members come back?Case studyA retention table, built step by step.05
- NULL handlingLessonLockedWhere wrong totals start.06
- Cleaning text and datesLessonLockedStandardise values in staging.07
- CASE expressionsLessonLockedBusiness rules inside a query.08
- Deduplication and incremental upsertsLessonLockedKeep one row per key.09
- Querying JSON columnsLessonLockedFlatten semi-structured sources.10
- Pivoting and unpivotingLessonLockedReshape for reporting.11
Model the data
about 3 weeks · 8 stepsAfter this stage: You can design facts and dimensions and say what one row means.
- OLTP vs OLAP mental modelLessonLockedWhy analytics tables look different.12
- Star schema: facts, dimensions, grainLessonLockedThe shape of analytics data.13
- Surrogate keys and conformed dimensionsLessonLockedKeys that survive change.14
- Slowly changing dimensionsLessonLockedKeep history when a value changes.15
- Capstone: model a star from ordersLessonLockedDo it once on real tables.16
- Additive, semi-additive and non-additive factsLessonLockedKnow which measures you can sum.17
- Normalization vs denormalizationLessonLockedChoose the right shape for analytics.18
- Views vs materialized viewsLessonLockedTrade freshness for speed.19
Build with dbt
about 3 weeks · 14 stepsAfter this stage: You can write models, tests and incremental builds.
- What is analytics engineering?LessonFreeThe role and the tools.20
- dbt project structureLessonFreeWhere models, tests and seeds live.21
- What dbt isLessonLockedThe idea in one page.22
- Jinja basicsLessonFreeTemplates that write SQL for you.23
- source() and ref()LessonLockedHow models depend on each other.24
- Staging modelsLessonLockedClean each source once.25
- not_null testsLessonLockedThe first test to add.26
- unique testsLessonLockedProtect the grain.27
- relationships testsLessonLockedCatch orphan keys.28
- Marts from staging CTEsLessonLockedBuild tables people query.29
- Views, tables and incrementalsLessonLockedChoose how a model is built.30
- Incremental vs full refreshLessonLockedBuild only what changed.31
- Snapshots and SCD Type 2LessonLockedKeep history of changing rows.32
- Capstone: staging, mart, testLessonLockedPut it together.33
Trust the numbers
about 2 weeks · 14 stepsAfter this stage: You can add checks that catch a bad load before users see it.
- What is data quality?LessonFreeWhat can go wrong and where.34
- Uniqueness on the grainLessonLockedThe check that catches duplicates.35
- Run a suiteLessonLockedGroup checks and read the result.36
- Case study: the duplicate click problemCase studyA number that was too high for a reason.37
- ProjectsProjectA dbt-shaped staging and tests project.38
- Testing strategies for dataLessonFreeWhat to test and where.39
- Why quality gatesLessonLockedStop bad data before it ships.40
- Not-null on keys and measuresLessonLockedCatch missing values.41
- Row count between boundsLessonLockedSpot a load that is too small or too big.42
- Accepted valuesLessonLockedCatch new or misspelt categories.43
- Freshness and numeric boundsLessonLockedKnow when data is late or off.44
- Data lineageLessonLockedTrace a number back to its source.45
- SLA versus SLILessonLockedPromise reliability and measure it.46
- Capstone: a publish suiteLessonLockedGate a release with checks.47
Run it on a schedule
about 2 weeks · 5 stepsAfter this stage: You can explain how models are scheduled, rerun and kept safe to repeat.