My progress
Menu

Data Analyst / Engineer · Focus area 2: Data warehousing

DT7: dbt: transformations as tested code

Goal: build a warehouse's models with dbt: SQL files that know their dependencies, tests that run on every build, documentation that stays next to the code, materialisations that fit each table, and a workflow where every change is reviewed and checked.

You'll need: DT5, DT6, Module 9 (Introduction to Git), Module 56 (Advanced Queries), Module 71 (CI/CD), and the SabziSathi practice kit.

Where this is used: many companies, from startups to large banks, keep their warehouse's SQL in a dbt project in Git. Analysts and engineers change it through pull requests, and every change is built and tested before it reaches a dashboard.

How it differs: SQL pasted into a BI tool or a scheduled query is hard to review, test or reuse. dbt treats SQL like software: versioned, tested, documented and built in order.

Time: reading about 2½ hours · practice (the Try it tasks) about 3 hours · assignment about 3 hours.

Lessons

  1. 1Models, `ref` and `source`Reading 15–25 minutes, plus practice✓
  2. 2Tests that run on every buildReading 15–25 minutes, plus practice✓
  3. 3Documentation and lineageReading 15–25 minutes, plus practice✓
  4. 4Materialisations: view, table, incrementalReading 15–25 minutes, plus practice✓
  5. 5dbt in a teamReading 15–25 minutes, plus practice✓

🛠️Assignment: SabziSathi's dbt project, tested and documented

This is the third part of Project 2.

  1. Declare every pipeline table as a source, with loaded_at_field and freshness on order_lines and tests on the source keys.
  2. Add generic tests: unique and not_null on the fact's grain and dimension keys, accepted_values on status and store, and relationships from the fact to every dimension.
  3. Add a singular test that reconciles mart_daily_sales with the fact table.
  4. Make fct_order_lines incremental with a unique_key and a 3-day look-back.
  5. Write descriptions for every mart model and column, and generate the docs site.

Self-check before you finish:

✅Module check

Explain in a few sentences, in your own words:

Why build a warehouse with dbt instead of scheduled queries?

Show a good answer

A good answer mentions: one select per model linked with ref and source, so dbt builds in dependency order; macros for shared logic; generic and singular tests on grain, keys and business rules, run on every build; descriptions and lineage kept with the code; views, tables and incremental models chosen for each case, with unique keys and look-backs; development environments, pull requests and CI; freshness checks in the scheduled job; and secrets from environment variables.

📚Official references

Official documentation and guidance for this module's topics, chosen on 2 November 2026. Tools, laws and standards change, so check the current version before you rely on a detail. If a link has moved, search the same site.

📝Module quiz

10 questions · 10 marks each · 100 marks in all · pass mark 70% · retry as often as you like ·

1. A dbt model has from marts.dim_products written directly instead of using ref(). What can go wrong?

Why each option is right or wrong

  • “The model can't be queried by BI tools” (Incorrect): BI tools read the built table either way.
  • “dbt refuses to build models that contain schema names” (Incorrect): dbt builds it; it just doesn't know the dependency.
  • “The model becomes a view instead of a table” (Incorrect): Materialisation is set by config, not by how tables are named.
  • “dbt may build it before dim_products, reading old data” is correct.

    Without ref(), dbt can't see the dependency, so it can't put the models in order.

    • Also: the reference won't follow development schemas.

📖 Revise: Lesson 1: Models, `ref` and `source`

Question ID sp-data-07-q1 · Report a mistake in this question
2. A change to a booking model's join silently creates two rows for some bookings. Which test would have caught it on the pull request?

Why each option is right or wrong

  • “not_null on the booking date” (Incorrect): Duplicated rows still have dates.
  • “accepted_values on the booking channel” (Incorrect): The channels are still valid values.
  • “A test that the table isn't empty” (Incorrect): The table has more rows, not fewer.
  • “unique on the booking id” is correct.

    A unique test fails as soon as any booking id appears twice.

    • Add it to: the grain column of every fact table.
    • CI: runs it on every pull request.

📖 Revise: Lesson 2: Tests that run on every build

Question ID sp-data-07-q2 · Report a mistake in this question
3. A team marks all dbt tests as warn so builds never stop. A key test starts failing every day. What's the likely outcome?

Why each option is right or wrong

  • “dbt fixes the duplicate keys automatically after three warnings” (Incorrect): dbt reports problems; it doesn't fix data.
  • “The dashboards hide the affected rows until the warning clears” (Incorrect): Dashboards show whatever the marts contain.
  • “The warnings eventually turn into errors on their own” (Incorrect): Severity doesn't change by itself.
  • “Wrong numbers reach dashboards while the warnings go unread” is correct.

    Rules that make figures wrong should be errors, so a failure stops the build before publishing.

    • Use error: for grain, key and revenue rules.
    • Use warn: for things to look at, such as an unusual row count.

📖 Revise: Lesson 2: Tests that run on every build

Question ID sp-data-07-q3 · Report a mistake in this question
4. A column's description says "revenue: the revenue". What's missing for someone using it?

Why each option is right or wrong

  • “The name of the person who wrote the model” (Incorrect): Ownership helps, but it doesn't explain the number.
  • “The SQL that computes it, pasted into the description” (Incorrect): Code is already in the model; the description should explain meaning.
  • “A chart showing the revenue over time” (Incorrect): Charts belong in dashboards, not column descriptions.
  • “What's included and left out, and the unit” is correct.

    "Delivered lines only; cancelled and refunded excluded; in paise" tells people how to use the number correctly.

    • Example: "Delivered lines only; cancelled and refunded excluded; in paise."
    • Keep: the dashboard's definition the same.

📖 Revise: Lesson 3: Documentation and lineage

Question ID sp-data-07-q4 · Report a mistake in this question
5. A source system's team wants to drop a column. How can the data team quickly see which models and dashboards would be affected?

Why each option is right or wrong

  • “Search every dashboard by hand for the column name” (Incorrect): Slow and easy to miss something.
  • “Drop the column in a test environment and wait for complaints” (Incorrect): Complaints arrive late and incomplete.
  • “Ask each analyst whether they use it” (Incorrect): People forget what their reports depend on.
  • “Use dbt's lineage graph, with exposures for dashboards” is correct.

    Lineage shows every model that reads the source, and exposures link those models to the reports that use them.

    • Command: dbt docs generate, then the lineage graph.
    • Or: dbt ls --select source:...+ to list every model downstream.

📖 Revise: Lesson 3: Documentation and lineage

Question ID sp-data-07-q5 · Report a mistake in this question
6. An incremental fact model has no unique_key. The pipeline reloads yesterday's file, and dbt runs again. What happens?

Why each option is right or wrong

  • “dbt detects the reload and skips it” (Incorrect): Without a key, dbt has no way to recognise rows it already has.
  • “The model is rebuilt in full automatically” (Incorrect): Incremental runs don't switch to full rebuilds on their own.
  • “dbt raises an error about the missing key” (Incorrect): It builds without complaint.
  • “Yesterday's rows are added again” is correct.

    Without a unique key, the incremental run appends what its where clause selects.

    • Fix: unique_key on the grain, so re-processed rows replace old ones.

📖 Revise: Lesson 4: Materialisations: view, table, incremental

Question ID sp-data-07-q6 · Report a mistake in this question
7. A team changes how refunds are counted in an incremental model and runs dbt build. Last month's figures don't change. Why?

Why each option is right or wrong

  • “dbt caches last month's results for a week” (Incorrect): dbt doesn't cache results like that.
  • “Refunds can only be counted in new months” (Incorrect): Nothing in dbt limits that.
  • “The warehouse rejected the change because of a contract” (Incorrect): A contract would fail the build, not quietly keep old figures.
  • “Only new rows ran; old rows need --full-refresh” is correct.

    Incremental runs touch only the new window. A logic change needs a full rebuild to apply to old rows.

    • Rule: run a full refresh after any change to an incremental model's logic.
    • Check: compare last month before and after.

📖 Revise: Lesson 4: Materialisations: view, table, incremental

Question ID sp-data-07-q7 · Report a mistake in this question
8. An analyst fixes an urgent number by editing a production model directly, and two other marts break overnight. What process would

Why each option is right or wrong

  • “A rule that only senior analysts may edit production” (Incorrect): Seniority doesn't test anything.
  • “Running dbt docs generate after every change” (Incorrect): Docs describe models; they don't test them.
  • “Editing production only on weekends” (Incorrect): Breakages would still reach production, just on weekends.
  • “A pull request with CI building the change and what depends on it” is correct.

    CI runs the changed model and everything downstream with tests, so the breakage shows before production changes.

    • Command: dbt build --select state:modified+ against the production manifest.
    • Plus: development schemas, so edits stay out of production.

📖 Revise: Lesson 5: dbt in a team

Question ID sp-data-07-q8 · Report a mistake in this question
9. SabziSathi's pipeline stops running. Which check, run in the scheduled job, alerts someone first?

Why each option is right or wrong

  • “A unique test on the fact table” (Incorrect): Old data stays unique; nothing fails.
  • “dbt docs generate” (Incorrect): It documents; it doesn't check data age.
  • “A not_null test on order_date in the fact table” (Incorrect): Old rows still have dates.
  • “dbt source freshness, with time limits” is correct.

    It compares the newest loaded_at with the limits, so stale data raises a warning and then an error.

    • Settings: loaded_at_field with warn_after and error_after in sources.yml.
    • Kit: warns after 30 hours, errors after 54.

📖 Revise: Lesson 5: dbt in a team

Question ID sp-data-07-q9 · Report a mistake in this question
10. A developer commits profiles.yml with the cloud warehouse's password written in it, and pushes to the team's repository. What

Why each option is right or wrong

  • “Delete the file in the next commit, and carry on” (Incorrect): The password stays in Git history for anyone with the repository.
  • “Make the repository private for a few days” (Incorrect): Everyone who already has access, and every clone, still has it.
  • “Rename the file so dbt can't find it” (Incorrect): The password is still in the history, and dbt can't connect.
  • “Change the password, and read it with env_var() from now on” is correct.

    Once pushed, treat it as exposed. Reading it from the environment keeps the next one out of the files.

    • Store it in: the CI secret store or a secrets manager.
    • Check: search the repository history for other credentials.

📖 Revise: Lesson 5: dbt in a team

Question ID sp-data-07-q10 · Report a mistake in this question

ProLearners Assist