Layering dbt models into a clean DAG

1.Why a dbt project flows in layers

A dbt project is a graph before it is a set of files. Every ref() adds an edge, and dbt uses those edges to decide the order in which models run. That graph is the DAG. In a clean DAG, the shape itself tells you how the data moves: raw, source-conformed tables come in on the left, and a smaller number of wide, business-conformed entities come out on the right.

Layering is what gives a DAG that shape. The recommended structure has three layers, and each one builds on the layer before it. Staging turns source data into atomic building blocks. Intermediate adds logic, one clear purpose per model, to get those blocks ready to join. Marts combine them into the entities the organisation cares about. The benefit is that each transformation lives in one place, so a definition change happens once instead of in every model that copied it.

You can see the structure in three places: the DAG, the folder tree, and the objects dbt creates in the warehouse. The folder layout counts as an interface to the project, just like the DAG. It should follow the same step-by-step flow, from many source-conformed models to fewer, richer business-conformed ones.

Sources12

2.Staging: one clean model per source table

Staging is the way into the project. Each source table gets exactly one staging model, and staging models are the only ones that call source(). Every later layer reads staging through ref(). [Raw object dependencies](lesson:1.1) explains how the two functions differ. Keeping source() inside one layer gives each raw table a single entry point.

A staging model does only a few kinds of work: renaming columns, casting types, simple computations such as converting cents to dollars, and sorting values into buckets or booleans. Joins and aggregations do not belong here. A join creates duplicated computation and confusing relationships that spread downstream. An aggregation changes the grain, which cuts off access to source rows you will probably need later. To decide whether something belongs in staging, apply the DRY test: if every downstream model would want it, do it once here. The principle is covered in [modularity and DRY](lesson:1.3).

        ---------- 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,

        ---------- timestamps
        {{ dbt.date_trunc('day', 'ordered_at') }} as ordered_at

    from source

)

select * from renamed

Base models are the approved exception to the no-joins rule. Sometimes a concept can't be staged cleanly without a join, for example a separate table of deleted records, or several symmetrical sources that need a union. In that case, create base models in a subdirectory. They read the raw data and do the non-joining work, and the staging model then joins them.

Folders and file names follow the source system. A transactional database, Stripe and Snowplow each get their own staging subdirectory. Grouping by loader (Fivetran, Stitch) is too broad to be useful. Grouping by business area (marketing, finance) invites conflicting definitions before any modelling has started. Files are named stg_[source]__[entity]s.sql. The double underscore keeps google_analytics__campaigns unambiguous, and the plural reflects a table with many rows. Staging models are usually materialised as views. They are building blocks rather than tables people query, so views avoid wasting warehouse space; the trade-offs are covered in [core materializations](lesson:1.2).

-- stg_orders.sql

with

source as (

    select * from {{  ? ('ecom', 'raw_orders') }}

),

Sources1

3.Intermediate: purpose-built steps between staging and marts

Small projects can skip the intermediate layer. The Jaffle Shop example goes straight from staging to marts. Larger projects add it so their marts stay readable. Every intermediate model has a single job: preparing staging models for marts.

From this layer on, the project is organised around the business. Subdirectories group models by area of business concern instead of by source system. File names describe what the model does, using verbs: int_[entity]s_[verb]s.sql, as in int_order_items_summed_to_orders. The double underscore drops out because the model now refers to one unified entity, not a system plus an entity. It stays only when an intermediate model still works at source-system level, as in int_shopify__orders_summed.

Intermediate models are used in three common ways:

- Structural simplification: join a manageable number of inputs, typically 4 to 6, into a model that is later joined with a similar one. The mart then joins two models instead of ten. - Re-graining: expand or collapse rows to the right grain. One example is turning each order into one row per item based on its quantity, so you can confirm the grain is correct before mixing it with anything else. - Isolating complex operations: move hard-to-follow logic into its own model, where it is easier to debug and test, and where later models can reference it by a clear name.

Intermediate models are not meant for end users. They should generally stay out of the main production schema. A common default is to make them ephemeral. That is simple, but harder to troubleshoot, because an ephemeral model is inserted into the models that ref it instead of existing on its own. The sturdier option is to build them as views in a custom schema with special permissions.

Sources3

4.Marts: one model per business entity

Marts are the first layer built for end users. Each mart represents one entity or concept at its own grain, such as an order, a customer or a payment, with one row per instance. Name the file after that entity in plain English: customers, orders.

Marts are wide on purpose. In a project without the Semantic Layer, the guidance is to denormalise heavily. Storage is cheap and compute is expensive, so copying order data into the customers mart costs less than re-joining it every time someone asks a question.

Each concept gets one mart. Building finance_orders and marketing_orders is an anti-pattern, because two teams end up with two different answers, which is exactly what layering is meant to prevent. Sometimes a department really does need a different concept, for example when finance reports revenue to the government differently from how the company measures it day to day. In that case, name it as a separate concept (tax_revenue alongside revenue) rather than as one department's version of the same thing. Department subfolders are fine once the layer grows, but with fewer than about ten marts you rarely need them.

Sources4

5.Reading DAG shape: the arrowhead and the cycle

Up to the marts layer, a healthy DAG looks like an arrowhead pointing right: many narrow source-conformed models narrowing into fewer, wider ones. For a single model, the rule of thumb is many arrows in, few arrows out. Several inputs to a post-staging model are normal. A model that feeds several downstream models is a warning sign worth questioning, although it is not an absolute ban.

dbt rejects a cycle outright. It uses ref() to work out the run order by building an acyclic graph. If two models reference each other, there is no valid order, and dbt stops with an error that names the loop.

Encountered an error:
Found a cycle: model.jaffle_shop.customers --> model.jaffle_shop.stg_customers --> model.jaffle_shop.customers

Your dbt DAG is not acyclic, and needs to be fixed!

To fix a cycle, change the ref calls so the dependency runs in one direction only. In a layered project, a staging model never refs a mart. When a model really does need to read its own existing relation, use {{ this }} instead of a ref to itself. this is equivalent to a ref to the current model, and it avoids the circular dependency.

Clean structure also helps when you select what to run. Because folders match the layers, dbt build --select staging+ rebuilds the staging layer and everything downstream of it after fresh data arrives. The command itself is covered in [dbt commands](lesson:1.4).

Sources3561

6.Building the flow: start from the mart, refactor upstream

The order you read a DAG in, left to right, is not the order you write it in. Development usually starts from the output and works back, and the clean layering comes at the end, as a refactor. The usual sequence is:

1. Mock up the desired output in a spreadsheet so stakeholders agree on the goal. 2. Write the SQL that produces that output, and identify the tables it uses. 3. Make sure every atomic piece it needs is staged in the project. 4. Combine the staged pieces into the mart using the logic you wrote. 5. With the mart working, refactor by splitting its logic and moving parts back upstream into intermediate models.

The last step is where the DAG gets clean. A mart that works but holds every join and calculation in one file is hard to read and has only one place to test. Moving pieces into intermediate models keeps each model readable, makes the DAG's story clear, and gives you more places to add tests.

Sources1

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.Staging subfolders should be split by business area, such as marketing and finance, so each team owns its own models.Why is that wrong?

   Staging is grouped by source system. Splitting by business area this early leads to overlapping and conflicting definitions of the same entity.

   Covered in Staging: one clean model per source table

2. 2.Joining two source tables in a staging model is fine, because staging is where data gets cleaned up.Why is that wrong?

   Staging does renaming, casting, simple computations and categorising. Joins are almost always wrong there; base models are the narrow exception.

   Covered in Staging: one clean model per source table

3. 3.Intermediate models should be built in the main production schema so analysts can query them directly.Why is that wrong?

   Intermediate models are not intended for end users. They are made ephemeral or kept in a separate custom schema with special permissions.

   Covered in Intermediate: purpose-built steps between staging and marts

4. 4.A model that feeds many downstream models is a sign of good reuse.Why is that wrong?

   The rule of thumb is many inputs, few outputs. Several arrows coming out of a post-staging model are a warning sign to examine.

   Covered in Reading DAG shape: the arrowhead and the cycle