dbt in the AI Era: Migrating from Legacy ETL

What's inside

1. Level 1: Meet the Legacy Pipeline We Will MigrateFree
   Learners take apart the course's worked example, a nightly SQL Server stored-procedure pipeline that turns raw orders, customers and products into a sales mart using temp tables, cursors, MERGE statements and an Agent job schedule, and list what it does, what it hides and where it breaks.
2. Level 2: What dbt Replaces and What It Leaves to OthersFree
   Learners place dbt in the data stack as a transformation framework that compiles SQL SELECTs into warehouse objects, and see that ingestion, orchestration, reverse ETL and alerting still belong to other tools.
3. Level 3: The dbt Landscape in October 2026: Engines and Licences
   Learners get a dated, plain account of where dbt stands now: the Fivetran and dbt Labs merger completed on 1 June 2026, dbt v2 (the Rust engine) generally available since 16 September 2026 in two distributions, 'dbt' and the Apache 2.0 'dbt OSS', dbt Core 1.x moving to critical-fixes-only support, and the commercial dbt platform, with open questions stated plainly.
4. Level 4: Setting Up a Project and Its Warehouse Connection
   Learners install dbt, connect it to the course's assumed warehouses (DuckDB locally for labs, with notes for Snowflake as the production target), and learn the project folder layout and profiles that every later level builds on.
5. Level 5: Your First Model: A SELECT Becomes a Table
   Learners write their first model by turning one step of the legacy procedure into a plain SELECT, run it, and read the compiled SQL and DDL that dbt actually sends to the warehouse.
6. Level 6: Sources: Declaring Raw Data and Its Freshness
   Learners declare the worked example's raw tables as sources, refer to them with source(), and add freshness checks so stale loads are caught before transformation.
7. Level 7: ref() and the DAG: Letting dbt Work Out the Order
   Learners connect models with ref() so dbt builds the dependency graph itself, replacing the legacy procedure's hand-ordered steps, and use node selection to run parts of that graph.
8. Level 8: Layering Models: Staging, Intermediate and Marts
   Learners rebuild the worked example as staging, intermediate and mart layers with consistent naming, and weigh the benefits of layering against the cost of too many thin models.
9. Level 9: Jinja and Macros Without the Spaghetti
   Learners use Jinja templating and macros to remove repeated SQL from the worked example, and learn the point at which templating makes models harder to read and review than plain SQL.
10. Level 10: Materialisations: View, Table and Ephemeral
    Learners choose between view, table and ephemeral materialisations for each model in the worked example, based on query cost, freshness, build time and how easy each is to debug.
11. Level 11: Incremental Models: Processing Only What Changed
    Learners turn the worked example's large order fact table into an incremental model using is_incremental() and a unique key, replacing the legacy procedure's high-watermark logic.
12. Level 12: Incremental Strategies and Late-Arriving Data
    Learners compare the incremental strategies (append, merge, delete+insert, insert_overwrite, microbatch) and lookback windows for handling late or corrected records, weighing correctness against warehouse cost.
13. Level 13: Seeds, Configuration Hierarchy and Variables
    Learners move the legacy pipeline's hard-coded lookup lists into seeds, and learn where configuration can be set (project, folder, YAML, in-model) and which setting wins when they conflict.
14. Level 14: Rebuilding the Worked Example End to End
    Learners finish phase one by rebuilding the whole legacy sales pipeline as a layered dbt project, running it with dbt build and doing a first rough comparison against the stored procedure's output.
15. Level 15: Generic Tests: Encoding Data Assumptions
    Learners add the built-in generic tests (unique, not_null, accepted_values, relationships) to the worked example and set their severity, thresholds and failure storage so tests catch real problems without becoming noise.
16. Level 16: Singular and Custom Tests for Business Rules
    Learners turn the legacy procedure's buried validation checks and business rules into singular SQL tests and reusable custom generic tests.
17. Level 17: Unit Tests: Proving Logic Before It Touches Data
    Learners write dbt unit tests with mocked input rows and expected outputs to prove tricky transformation logic, such as discount rules and edge-case dates, before it runs on real data.
18. Level 18: Model Contracts, Constraints and Versions
    Learners enforce model contracts on the sales mart so its column names and data types are guaranteed, add constraints, and use model versions to change a contracted model without breaking its consumers.
19. Level 19: Documentation That People and Machines Can Use
    Learners write model and column descriptions, reusable docs blocks and ownership metadata for the worked example, generate the documentation site, and learn what makes documentation stay accurate.
20. Level 20: Packages: Reusing Community Code Safely
    Learners install and pin packages such as dbt_utils and audit_helper, use them in the worked example, and weigh dependency, maintenance and compatibility risk against writing the code themselves, including compatibility with the v2 engine.
21. Level 21: Environments, Targets and Profiles
    Learners set up separate dev, CI and prod environments for the worked example, using profiles.yml targets, per-developer schemas and environment variables, and see where dbt Core and the commercial platform handle connections and secrets differently.
22. Level 22: Code Review for dbt Projects
    Learners set up a pull-request workflow for the worked example, with a review checklist covering naming, grain, join fan-out, test coverage and materialisation choices, plus linting with SQLFluff so reviewers spend their time on logic rather than formatting.
23. Level 23: CI Builds with State Selection and Deferral
    Learners build a CI job that compares against the production manifest, rebuilds and tests only the changed models and their children with state:modified+, and uses --defer so that unchanged parents are read from production instead of being rebuilt.
24. Level 24: CI Cost and Safety: Slim Data and Schema Hygiene
    Learners keep CI fast and cheap with the --empty flag, limited-data patterns and per-PR schemas that are cleaned up afterwards, and protect production from CI jobs with credentials and permissions scoped to each role.
25. Level 25: Deploying dbt Core with an External Orchestrator
    Learners deploy the worked example with dbt Core under an orchestrator such as Airflow, Dagster or a plain scheduled container, choosing between running one dbt build per job and one task per model, and handling retries, artifacts and run results.
26. Level 26: Deploying on the Platform: Core vs Commercial Trade-offs
    Learners deploy the same project on dbt's commercial platform with its jobs, environments and CI, and compare it honestly with self-hosted Core on cost, lock-in, licensing, the Fusion engine and what is still changing after the Fivetran merger (all claims dated October 2026).
27. Level 27: Inventorying the Legacy Estate
    Learners start the migration playbook by building a complete inventory of the legacy estate, covering stored procedures, scheduled SQL jobs, graphical ETL packages, their schedules, owners, inputs, outputs and run history, using system catalogs and scheduler metadata rather than tribal memory.
28. Level 28: Mapping Hidden Dependencies
    Learners uncover the dependencies the inventory misses, such as dynamic SQL, cross-database references, views used by reports, spreadsheet and BI extracts, and timing-based coupling between jobs, then assemble a lineage graph of the legacy estate.
29. Level 29: Triage: Move, Retire or Leave
    Learners sort every legacy job into migrate, rebuild, retire or leave-in-place using usage evidence, business criticality and migration effort, and agree the decision with stakeholders before writing any dbt code.
30. Level 30: Sequencing the Migration into Waves
    Learners split the triaged estate into migration waves ordered by dependency and risk, choosing between building outward from the sources and starting from one high-value output, and set the definition of done for each wave.
31. Level 31: Reading a Stored Procedure for Its Intent
    Learners pull apart the worked example's stored procedure to separate business rules from procedural mechanics such as temp tables, cursors, staging truncates and error handling, and write down the real rules before translating anything.
32. Level 32: Temp Tables and Multi-Step Logic to a Model Chain
    Learners turn a procedure's sequence of temp tables and UPDATE passes into a chain of staging, intermediate and mart models joined by refs, deciding where each step should become a CTE, an ephemeral model or a model of its own.
33. Level 33: Row-by-Row Logic to Set-Based SQL
    Learners rewrite cursors, WHILE loops and scalar functions as set-based SQL using window functions, recursive CTEs and joins, and test that the rewrites give the same answers on edge cases.
34. Level 34: Dynamic SQL, Control Flow and Jinja Limits
    Learners deal with legacy dynamic SQL and branching control flow, deciding when Jinja macros and loops are the right translation and when they are a warning sign that the logic should be reshaped or moved out of dbt.
35. Level 35: Translating Graphical ETL Packages
    Learners translate packages from graphical ETL tools (lookups, conditional splits, derived columns, slowly changing dimension components) into dbt models, including how to pull logic out of tools that hide it in GUI configuration.
36. Level 36: Incremental Legacy Loads to Incremental Models
    Learners move legacy watermark, delete-insert and MERGE load patterns to dbt incremental models, matching the legacy behaviour for late-arriving data, deletes and reprocessing windows.
37. Level 37: Slowly Changing Dimensions with Snapshots
    Learners rebuild the worked example's type 2 customer dimension using dbt snapshots, choosing between the timestamp and check strategies and handling hard deletes, and see where snapshots stop being enough.
38. Level 38: Migrating Existing SCD History
    Learners carry years of legacy type 2 history into the dbt world by seeding it once into the new model and joining it to snapshot-driven changes from the cutover date, keeping surrogate keys and validity ranges consistent.
39. Level 39: Running Legacy and dbt in Parallel
    Learners set up a parallel run where legacy and dbt build from the same source snapshot into separate schemas on aligned schedules, with the timing, cost and data-freshness controls that make comparison meaningful.
40. Level 40: Reconciling Results at Row and Aggregate Level
    Learners compare legacy and dbt outputs using aggregate checks, key-level diffs and column-level comparisons (for example with audit_helper), sort the differences into bugs, legacy defects and intended changes, and agree tolerance thresholds.
41. Level 41: Row-Level Reconciliation of Legacy and dbt Output
    Learners compare the legacy fact table from the worked example with its dbt replacement row by row. They match on business keys and classify each difference as missing, extra or changed, using EXCEPT queries and the audit_helper package on the warehouse the course uses.
42. Level 42: Aggregate Reconciliation, Tolerances and Sign-Off
    Learners reconcile totals, counts and distributions across dimensions and over time. They agree tolerances with business owners and turn the results into a written sign-off record. They also learn to tell a legacy bug apart from a translation bug.
43. Level 43: Planning and Running the Cutover
    Learners plan the switch from legacy to dbt as a sequenced runbook. The runbook covers freeze windows, the final parallel run, repointing downstream consumers through views or synonyms, and the go/no-go criteria agreed in advance.
44. Level 44: Rollback Plans and Retiring the Legacy Pipeline
    Learners design a rollback they have actually rehearsed and decide how long to keep the legacy jobs available. They then decommission the stored procedures, schedules and tables safely, without leaving orphaned dependencies.
45. Level 45: Warehouse Load and Cost of a dbt Project
    Learners measure what the migrated project actually costs to run by attributing warehouse spend to individual models through query tags and run metadata. They also see why a dbt rebuild can cost more than the procedure it replaced.
46. Level 46: Incremental Models That Drift
    Learners diagnose incremental models whose output slowly diverges from a full rebuild because of late-arriving data, deletes in the source, schema changes or lookback windows set too small. They then put scheduled checks and periodic full refreshes in place to catch it.
47. Level 47: Failure Modes from Real Migrations
    Learners work through a casebook of migration failures and the early signal each one would have given: silent fan-out joins, timezone shifts, NULL semantics, changed source contracts, ownership gaps and orphaned downstream consumers.
48. Level 48: Alerting on Stale Sources and Failed Runs
    Learners configure source freshness checks and route run failures, test failures and freshness breaches to the right owners with sensible severity, so the team does not ignore alerts because there are too many of them.
49. Level 49: Growing the Project: Structure and Ownership
    Learners restructure a project that has grown beyond its original layout. They set folder and naming conventions, groups and owners, access modifiers and model versions, so many teams can work in it without breaking each other's models.
50. Level 50: Splitting Across Teams: Multi-Project Setups
    Learners weigh one large project against several projects that reference each other across project boundaries. They compare what dbt Core supports with what the commercial platform supports for cross-project refs, and note where this is still changing (dated as of October 2026).
51. Level 51: Review and CI Habits After Go-Live
    Learners set up the day-to-day habits that keep the project healthy. These include pull request templates, review checklists for SQL and YAML, required tests and documentation, linting, and slim CI that stays fast as the project grows.
52. Level 52: Upgrades and Engine Changes Without Drama
    Learners plan dbt version and engine upgrades, including the move from dbt Core v1 to the Rust-based Core v2/Fusion engine (alpha as of October 2026). They use deprecation warnings, compatibility checks and a parallel build, and treat claims about the new engine as provisional until it is generally available.
53. Level 53: Naming and Structure an Agent Can Read
    Learners audit the migrated models from an AI agent's point of view. They rename unclear models and columns, make grain and entity obvious from names, and hide intermediate models that an agent should never query.
54. Level 54: Descriptions and Contracts as Machine-Readable Truth
    Learners write model and column descriptions that answer the questions an agent will actually ask. They enforce contracts on public models so the schema an agent relies on cannot change silently.
55. Level 55: Building Semantic Models
    Learners define semantic models on top of the worked example's fact and dimension tables, with entities, dimensions and measures, so that joins and aggregations follow governed rules instead of being guessed by an LLM.
56. Level 56: Defining Metrics Agents Can Trust
    Learners define simple, ratio, cumulative and derived metrics matching the business definitions agreed during reconciliation. They test metric output against known figures and note where metrics can be queried in dbt Core versus the commercial platform.
57. Level 57: Tests as Guardrails for Agent Answers
    Learners add tests aimed at the failure modes that would make an agent's answer wrong: freshness, uniqueness at the stated grain, accepted values for categorical filters and metric sanity bounds. They also learn how to expose test status so an agent can see when data should not be trusted.
58. Level 58: Connecting an Agent Through dbt's MCP Server
    Learners set up dbt's MCP server against the project, choose which toolsets to enable (discovery, Semantic Layer, SQL, CLI), and connect an MCP-capable agent client. Every feature they use is dated and checked against current dbt documentation.
59. Level 59: Governing Agent Access: Permissions, Scope and Audit
    Learners restrict what an agent can do. They use least-privilege service tokens and warehouse roles, turn off write-capable and arbitrary-SQL tools where they are not needed, and log every query. The level also names what dbt's tooling does not govern.
60. Level 60: Final Challenge: Legacy Estate to Agent Answer Exam
    Learners produce a complete migration plan for a realistic legacy estate of stored procedures, scheduled SQL and graphical ETL jobs. The plan runs from inventory, triage, translation, snapshots, parallel run, reconciliation, cutover and rollback through to cost and CI, and ends with an agent answering a business question from governed, tested models through dbt's MCP server.

Access

The first 2 levels are free with a free account. Every level, the podcast edition and the AI tutor come with All Access at £4.99/month or any Creator plan — see pricing.

Start free →