What you will be able to do
- Explain how stacking models in layers lets each transformation live in exactly one place
- Decide which transformations belong in a staging model using the DRY litmus test
- Write and call a Jinja macro, quoting string arguments correctly
- Reuse package resources and qualify package macros with the package name
- Spot the anti-pattern of defining one business concept twice for different teams
Key concept
One transformation, one place — A modular dbt project stacks small models on top of each other so that every piece of logic is written once, upstream, and referenced everywhere else. DRY is the test, and layered models, macros and packages are how a project passes it.
1.Why modular layers keep logic in one place
A dbt project gets its modularity by building models out of other models. Without that, the same unit conversion or status mapping gets pasted into every query that needs it. When the business rule changes, someone has to find every copy, and the one they miss goes on producing a different number. In dbt you write that logic once in an upstream model, and every model that needs it selects from that model instead of working it out again.
The layers follow the data from raw to business-ready. The project starts with many narrow, source-conformed models that mirror the systems feeding it. It ends with fewer, wider business-conformed models that describe the entities the organisation cares about. Staging builds the atoms, intermediate models combine them for a specific purpose, and marts bring the pieces together into full entities. How you arrange those layers into a DAG is covered in [layered DAG design](lesson:1.5). Here, what matters is what each layer contributes to keeping the project DRY.
| Layer | Modular role | What gets written only once here |
|---|---|---|
| Staging | Atoms: one model per source table, the entry point for that table | Renaming, type casting, basic computations such as cents to dollars |
| Intermediate | Molecules: purpose-built steps that prepare staging models for marts | Complex logic such as re-graining or fanning out rows |
| Marts | Cells: one wide model per business entity | The single agreed definition of each business concept |
The ref() function holds the stack together. It is how one model selects from another, and dbt uses those references to build the dependency graph and run models in the right order (see [ref() and raw object dependencies](lesson:1.1)). Because of this, reusing an upstream model costs one line, select * from {{ ref('stg_orders') }}, rather than a copy of its SQL.
Checkpoint 1 of 8· Check yourself
Four marts each contain the same expression that converts order amounts from cents to dollars. Which refactor follows dbt's modular approach?
Stacking models means the transformation is applied in one upstream place and referenced downstream. Merging marts removes the duplicate code but destroys the entity-per-mart design.
“stacking our transformations in optimized, modular layers means we can apply each transformation in only one place”Source: docs.getdbt.com
2.Staging models and the DRY litmus test
Each source table gets exactly one staging model, and that model is its only entry point into the project. Where ref() points at another model, seed or snapshot, source() selects from a raw table that the project's source YAML declares, as in source('ecom', 'raw_orders'). Staging is the only layer that calls source(). Everything downstream refs the staging model. So a fix made in stg_orders reaches every model that uses orders, and no downstream model touches the raw table directly.
Transformations you will always want go into staging. If the source system stores money as integer cents and every consumer wants dollars, divide and cast once in staging. Otherwise every downstream model repeats the same code and the same compute. The four standard staging transformations are renaming, type casting, basic computations, and categorizing (grouping values into buckets or booleans with conditional logic). In the Jaffle Shop stg_orders model, the cents columns are kept and dollar versions are added next to them:
---------- numerics
subtotal as subtotal_cents,
tax_paid as tax_paid_cents,
order_total as order_total_cents,
{{ cents_to_dollars('subtotal') }} as subtotal,
{{ cents_to_dollars('tax_paid') }} as tax_paid,
{{ cents_to_dollars('order_total') }} as order_total,DRY also decides what stays out of staging. To be placed there, a transformation has to be something every downstream model wants and has to remove repeated code. Joins usually fail that test: they cause duplicated computation and confusing relationships that ripple downstream. Aggregations fail it as well, because grouping changes the grain, and the source rows that later models need are gone. The exception is when a concept can't be staged cleanly without a join, such as joining a separate delete table or unioning symmetrical sources. In that case, build base models in a subdirectory and join them in the staging model.
Checkpoint 2 of 8· Check yourself
Which of these transformations passes the DRY litmus test for a staging model?
Casting cents to dollars is an always-want transformation that removes repeated code downstream. Joins and aggregations are called out as things to keep out of staging, and a team-specific figure isn't wanted by every consumer.
“do the division and type casting as early as possible so that we can reference it rather than redo it repeatedly downstream”Source: docs.getdbt.com
Checkpoint 3 of 8· Exam question
A staging model performs light column renaming and type casting on a source table, and only one downstream intermediate model references it via `ref()`. The team wants to avoid creating a permanent database object for this lightweight logic. Which materialization best fits this staging model?
Correct answer: A — Ephemeral
- A. This is correct because the ephemeral materialization interpolates the model's SQL as a CTE directly into the one model that calls it, avoiding a permanent database object for logic that only a single downstream consumer needs.
- B. This is incorrect for this exact scenario because a view still creates a database object that dbt rebuilds and the warehouse must resolve on every query, which is unnecessary overhead for logic used by only one consumer.
- C. This is incorrect because a table materializes the full result set to storage on every run, which adds build time and storage cost that this lightweight, single-consumer transformation does not need.
- D. This is incorrect because incremental materializations are built for large, frequently appended datasets, not for a simple renaming and casting step consumed by a single downstream model.
Sources3
3.Macros: reusable SQL written once in Jinja
Layers stop repeated transformations of the same *data*. Macros stop repeated *code*: the same expression applied to different columns or models. The cents_to_dollars calls in stg_orders are a macro, used three times in one model, and it can be called the same way in any other staging model that holds cents. Macros work like functions in other languages. They are defined in .sql files, usually in the macros directory, and one file can hold several macros.
{% macro cents_to_dollars(column_name, scale=2) %}
({{ column_name }} / 100)::numeric(16, {{ scale }})
{% endmacro %}Calling the macro from a model replaces the call with the SQL it renders. {{ cents_to_dollars('amount') }} compiles to (amount / 100)::numeric(16, 2), using the default scale. The two kinds of delimiters do different jobs. Expressions {{ ... }} output a string, which is why the call uses them. Statements {% ... %} output nothing and handle control flow and macro definitions. Comments are {# ... #}, not --.
select
id as payment_id,
{{ cents_to_dollars('amount') }} as amount_usd,
...
from app_data.paymentsQuoting is the most common way a macro call goes wrong. Inside {{ ... }} or {% ... %}, a string argument needs quotes, single or double. Without them, Jinja reads the word as a variable name. A missing variable renders as nothing, so the mistake doesn't show up until you read the compiled SQL. Run dbt compile and open the file under target/compiled/ to see exactly what the macro produced (more in [troubleshooting with compiled code](lesson:3.2)).
Checkpoint 4 of 8· Fill the gap
Complete the call so the macro converts the amount column correctly.
select
id as payment_id,
{{ cents_to_dollars( ? ) }} as amount_usd,
...
from app_data.paymentsThe column name is passed as a string, so it needs quotes. Unquoted, it's an undefined Jinja variable, and nesting curlies inside an expression is wrong.
Source: docs.getdbt.comSources4
4.Packages: reusing logic that is already written
Some code is repeated across organisations, not just inside one project. Generating SQL to union two relations, pivoting columns, building a surrogate key, and turning Segment or Snowplow pageviews into sessions are problems that thousands of teams share. dbt packages work like software libraries for these. A package is a standalone dbt project, and adding it to your project makes all of its resources part of yours.
After a package is installed, its resources behave like your own:
- its models are materialized when you dbt run
- your models can ref its models
- your models can call source on its sources
- your models can call its macros
Package macros must be qualified with the package name. The dimensions macro from dbt-utils is called as {{ dbt_utils.dimensions(5) }}, not as a bare dimensions(5). For models, the two-argument form ref('project_or_package', 'model_name') is recommended whenever the model is defined in another package. It isn't always required, but it tells every reader where the model comes from. Declaring and installing packages is covered in [dbt packages](lesson:1.7).
Checkpoint 5 of 8· Match them up
Match each package resource to how your project uses it once the package is added
Tap a term, then the definition that fits it.
Adding a package merges its resources into your project. Models build with yours, ref and source can point at its nodes, and its macros are called under the package's namespace.
“by adding a package to your project, all of the package's resources will become part of your own project.”Source: docs.getdbt.com
Checkpoint 6 of 8· Exam question
A dbt project has near-identical SQL blocks for calculating customer lifetime value duplicated across four mart models. The analytics engineer wants to apply DRY principles project-wide, not just fix this one duplication. Which of the following practices would reduce this kind of repetition across the project?(Select 3)
Correct answers: A, B, C — Extract the calculation into a parameterized macro under `macros/` and call it from each of the four models with `{{ calculate_ltv(...) }}`.; Install a shared package such as `dbt-utils` via `packages.yml` and reuse its existing surrogate-key and pivot macros instead of writing bespoke SQL.; Define the shared filter logic once as a Jinja variable in `dbt_project.yml` under `vars` and reference it with `{{ var('ltv_window_days') }}` in each model.
- A. This is correct because a parameterized macro centralizes the calculation logic in one place, and every model that needs it calls the same compiled Jinja instead of maintaining its own copy of the SQL.
- B. This is correct because installing a package gives the project access to already-written, tested macros, letting the team reuse vetted logic instead of re-implementing similar SQL helpers by hand in each model.
- C. This is correct because a shared `vars` entry lets every model pull the same configuration value from one definition, so changing the window only requires editing `dbt_project.yml` instead of four separate files.
- D. This is incorrect because docs blocks only store descriptive text for documentation pages; they are not compiled into SQL, so pasting the calculation from one still leaves four separate, independently maintained copies of the logic.
- E. This is incorrect because a shared naming prefix only changes how the models are labeled in documentation and search; it does not consolidate or reduce the duplicated SQL that each model still executes.
- F. This is incorrect because raising the thread count only affects how many models build concurrently; the duplicated calculation logic still exists in four separate files and still needs to be maintained separately.
5.Intermediate models, marts and one definition per concept
Past staging, modularity is about breaking complexity into named, reusable pieces. A mart that needs ten joins is easier to read and test as a join of two intermediate models, each combining a few related entities. Logic that is hard to follow, such as fanning out orders into one row per item based on a quantity column, belongs in its own intermediate model. There it can be debugged and tested on its own, and later models reference it by a clear name instead of repeating it.
For marts, DRY means a single source of truth. Finance and marketing should not each have their own orders model. finance_orders and marketing_orders are an anti-pattern because they are two definitions of one concept, and they will drift apart. Sometimes a team really does need something different, for example revenue as reported to the government versus revenue as the company measures it day to day. In that case, model two distinct concepts with names that say so: tax_revenue and revenue.
DRY doesn't mean storing data only once. Marts are denormalized on purpose: the customers mart carries order data that the orders mart also holds. With cheap storage and expensive compute, building the same data in several places costs less than joining the concepts again on every query. Repeated data is fine. Repeated definitions of a concept are not.
Folder structure has limits too. Beyond staging, the intermediate and marts layers can be split into subdirectories by business area, such as finance or marketing. With fewer than about ten marts that are working well, keep those layers flat and don't split and optimize too early. Staging is the exception: give each source system its own subdirectory as you add it.
Modular structure usually comes from refactoring, not from building the DAG top to bottom. A typical sequence: mock the output in a spreadsheet with stakeholders, write the SQL that produces it, stage the atomic pieces it needs, assemble the mart, and then move parts of its logic back upstream into intermediate models.
Checkpoint 7 of 8· Put it in order
Put these steps for developing a new mart in their usual order
- 1.Mock out the output design in a spreadsheet with stakeholders
- 2.Write the SQL that generates that output and identify the tables involved
- 3.Bring the pieces together to build the mart
- 4.Stage all the necessary atomic pieces into the project
- 5.Refactor by moving parts of the logic upstream into intermediate models
Development starts from the agreed output and works back to the sources. Splitting logic into intermediate models comes last, once the mart works.
“By splitting the logic up and moving parts back upstream into intermediate models, we ensure all of our models are clean and readable”Source: docs.getdbt.com
Checkpoint 8 of 8· Exam question
A model contains `FROM analytics.raw.orders` written directly instead of `{{ source('raw', 'orders') }}`, and joins to `FROM analytics.staging.stg_customers` instead of `{{ ref('stg_customers') }}`. Which consequences follow from hardcoding these references instead of using `ref()`/`source()`?(Select 2)
Correct answers: A, C — The hardcoded objects will not appear as nodes in the DAG, so dbt cannot infer the correct build order between this model and `stg_customers`.; Environment promotion from dev to prod breaks unless the hardcoded database and schema names are manually rewritten for each target.
- A. This is correct because dbt builds its DAG from `ref()` and `source()` calls; a hardcoded identifier is invisible to dependency resolution, so dbt has no way to know this model must run after `stg_customers`.
- B. This is incorrect because dbt does not block schema-qualified table names in a model's SQL; the query will still compile and run, it just loses dbt's dependency tracking and environment awareness.
- C. This is correct because `ref()` and `source()` resolve to the correct database and schema for whichever target is active, while a hardcoded identifier points at one fixed environment and has to be edited by hand for every target.
- D. This is incorrect because dbt never rewrites a model's file contents based on the tables it queries; a hardcoded reference stays exactly as written until someone edits it manually.
- E. This is incorrect because dbt's source freshness command reads freshness config attached to the declared `source()` entry in the manifest, not raw text scanning of arbitrary SQL, so a hardcoded reference is not tracked by that check.
- F. This is incorrect because even if the compiled SQL text looks similar, hardcoding still removes the DAG edge and environment portability that `ref()`/`source()` provide, so the effect is not negligible.
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.Joining related source tables in staging is DRY, because every mart then gets a pre-joined table.Why is that wrong?
Staging keeps source-conformed concepts separate. Joins there create duplicated computation and confusing relationships downstream. Use base models only when a concept truly can't be staged without a join.
Covered in Staging models and the DRY litmus test
2.Passing a column name to a macro without quotes, as in cents_to_dollars(amount), makes dbt use that column.Why is that wrong?
Inside Jinja curlies, an unquoted word is a variable. With no such variable, the argument renders as nothing, so string arguments must be quoted.
Covered in Macros: reusable SQL written once in Jinja
3.Building finance_orders and marketing_orders is good modular design, since each team gets a model tailored to it.Why is that wrong?
Two models for one concept break the single source of truth. Genuinely different needs should be modelled as clearly named, separate concepts.
Covered in Intermediate models, marts and one definition per concept
4.Denormalized marts violate DRY, because the same order data appears in both the orders and customers marts.Why is that wrong?
DRY targets repeated logic. Storing the same data in several marts is deliberate and cheaper than joining the concepts again repeatedly.
Covered in Intermediate models, marts and one definition per concept
Practise it for real
Replace a repeated cents-to-dollars expression with a macro and confirm the SQL it compiles to
1.Create macros/cents_to_dollars.sql containing the cents_to_dollars macro with arguments column_name and scale=2
Why: Macros are defined in .sql files, typically in the macros directory
You should see: The macro file sits alongside your models and is picked up when the project is parsed
2.In a staging model, replace each hand-written cents division with {{ cents_to_dollars('column_name') }}, quoting each column name
Why: String arguments inside curlies need quotes, or they render as nothing
You should see: The model contains one macro call per money column and no repeated arithmetic
3.Run dbt compile
Why: Compiling renders the Jinja into the SQL dbt would actually execute
You should see: The command finishes and writes compiled SQL files
4.Open the model's file under target/compiled/ and read the converted columns
Why: The compiled SQL is the only place to confirm what the macro produced
You should see: Each call appears as (column / 100)::numeric(16, 2)
Stuck? Get a nudge
If a converted column compiles to an empty pair of parentheses, check the quotes around the argument.
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“stacking our transformations in optimized, modular layers means we can apply each transformation in only one place”
↩︎ Why modular layers keep logic in one place“stacking our transformations in optimized, modular layers means we can apply each transformation in only one place”
↩︎ Key concept - 2.
“it is using these references between models to automatically build the dependency graph”
↩︎ Why modular layers keep logic in one place - 3.
“The DRY principle is ultimately the litmus test for whether transformations should happen in the staging layer.”
↩︎ Staging models and the DRY litmus test“Staging models help us keep our code DRY.”
↩︎ Staging models and the DRY litmus test“Staging models are the only place we'll use the source macro”
↩︎ Staging models and the DRY litmus test“joins are almost always a bad idea here”
↩︎ Exam trap 1“do the division and type casting as early as possible so that we can reference it rather than redo it repeatedly downstream”
↩︎ Checkpoint“By splitting the logic up and moving parts back upstream into intermediate models, we ensure all of our models are clean and readable”
↩︎ Checkpoint - 4.https://docs.getdbt.com/docs/build/jinja-macrosOfficial docs
“extremely useful if you find yourself repeating code across multiple models”
↩︎ Macros: reusable SQL written once in Jinja“Macros are defined in .sql files, typically in your macros directory”
↩︎ Macros: reusable SQL written once in Jinja“make sure you qualify the macro by prefixing it with the package name”
↩︎ Packages: reusing logic that is already written“Without the quotes, the Jinja parser will look for a variable named amount.”
↩︎ Exam trap 2“Without the quotes, the Jinja parser will look for a variable named amount.”
↩︎ Prediction - 5.https://docs.getdbt.com/docs/build/packagesOfficial docs
“dbt packages are in fact standalone dbt projects, with models, macros, and other resources that tackle a specific problem area.”
↩︎ Packages: reusing logic that is already written“by adding a package to your project, all of the package's resources will become part of your own project.”
↩︎ Checkpoint - 6.
“We don't want finance and marketing operating on separate orders models”
↩︎ Intermediate models, marts and one definition per concept“It's helpful to move any particularly complex or difficult to understand pieces of logic into their own intermediate models.”
↩︎ Intermediate models, marts and one definition per concept“don't split and optimize too early”
↩︎ Intermediate models, marts and one definition per concept - 7.
“grouping by departments (marketing, finance, etc.) is the most common structure at this stage”
↩︎ Intermediate models, marts and one definition per concept“finance_orders and marketing_orders is typically considered an anti-pattern.”
↩︎ Exam trap 3“is more efficient in this paradigm than repeatedly rejoining these concepts”
↩︎ Exam trap 4