Module 2: Sources And Staging Models

Staging models: one model per source table

Next →

Description

A staging model is the first real transformation dbt applies on top of a source: a .sql file that selects from exactly one source(), renames columns if needed, casts types explicitly, and nothing more. It does no JOIN against another table. It does no GROUP BY. It doesn't filter rows with a WHERE that decides what's "valid" and what isn't. Its only job is to leave each source table clean and correctly typed, in a predictable place in the project — the raw material, already washed and cut to standard size, but still not combined with any other ingredient.

This lesson defines that rule precisely — what belongs in a staging model and what doesn't — and puts it to the test with two real queries: one that respects it, and one that breaks it on purpose, so you see with your own eyes that dbt doesn't stop you if you decide to break it. The discipline, in this layer, is yours, not the engine's.

Connection to the module. Lessons 2 through 4 solved how raw data enters the project — declaring, understanding the function, connecting the physical file. This lesson solves a different question: once the data is already in, what shape does the first layer built on top of it take? Lesson 6 completes the answer with the naming convention; lesson 7 finally turns it into the project's real .sql files.

An analogy: mise en place, before cooking any dish

In a professional kitchen, before a single dish on the menu starts cooking, there's a required step called mise en place — "everything in its place": every raw ingredient gets washed, peeled, cut to the standard size the recipe needs, and placed in its own container, individually, ready to use. The washed, cut tomato lives in a bowl. The chopped onion lives in another. Nobody, at this stage, mixes the tomato with the onion — that's the job of the specific recipe that's going to combine them later, and different recipes on the same menu might combine them in completely different ways.

A staging model is that mise en place. stg_orders is the bowl with orders already washed — correct types, consistent names — ready for any recipe (any mart) to use it. If someone decided, during the mise en place step, to start mixing the tomato with the onion "to get ahead," they'd lose the ability to use each ingredient separately in another recipe that needs them combined differently. That, exactly, is what breaks when a staging model does a JOIN: it takes away any future model's ability to start from clean orders, with no stores already stuck to it.

Worked example: the correct form, and the form that breaks it

The correct form: one source, combined with nothing

You're still not going to save any permanent .sql file — that starts in lesson 7 — but you can preview the exact shape a real staging model is going to have, with dbt show --inline:

dbt show --inline "
  select
      store_id,
      store_name,
      city
  from {{ source('kiosko_raw', 'stores') }}
" --limit 5

What to expect.

Previewing inline node:
| store_id | store_name    | city     |
| -------- | ------------- | -------- |
| S01      | Kiosko Centro | Bogota   |
| S02      | Kiosko Norte  | Lima     |
| S03      | Kiosko Sur    | Santiago |

Notice the shape: a single FROM, a single source(), and an explicit list of columns (instead of SELECT *, which you already used in lesson 4 only to explore). That explicit list isn't decorative — it's where, in the next module, you're going to add cast(...) to every column that needs it. No other table shows up in this query, and none needs to: stores doesn't need to combine with anything to end up clean.

The form that breaks the rule — and that dbt lets through without complaint

Now the incorrect version, deliberately. You're going to write a real file inside models/staging/kiosko/ — the exact place the real staging model is going to live — that mixes orders with stores ahead of time:

-- models/staging/kiosko/stg_orders_with_store_name_BAD.sql (temporary, for this exercise)
select
    o.order_id,
    o.quantity,
    o.unit_price,
    s.store_name
from {{ source('kiosko_raw', 'orders') }} o
left join {{ source('kiosko_raw', 'stores') }} s
    on o.store_id = s.store_id

Run:

dbt run --select stg_orders_with_store_name_BAD

What to expect.

Found 5 models, 8 data tests, 4 sources, 500 macros

Concurrency: 4 threads (target='dev')

1 of 1 START sql view model main.stg_orders_with_store_name_BAD ................ [RUN]
1 of 1 OK created sql view model main.stg_orders_with_store_name_BAD ........... [OK in 0.05s]

Finished running 1 view model in 0 hours 0 minutes and 0.13 seconds (0.13s).

Completed successfully

Done. PASS=1 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=1

PASS=1. Zero errors. Read that output carefully, because it's this lesson's central point: dbt has no technical rule stopping a file inside models/staging/ from doing a JOIN. The model compiles, runs, and produces a perfectly valid, queryable view. The "one source per staging model, no joins" rule isn't in any dbt validator — it's documented as recommended practice by the dbt Labs team itself (see Resources), and you meet it through your own discipline, not because the engine forces you to.

Delete the temporary file before continuing:

rm models/staging/kiosko/stg_orders_with_store_name_BAD.sql

Going deeper: why the rule exists, even though nothing enforces it

The obvious question is worth answering: if dbt run doesn't complain, why bother following the rule? Three concrete reasons, none of them hypothetical in a project that grows:

One single clean place per source table. If stg_orders already brings store_name stuck to it, any future model that needs orders without stores — for example, to cross it with products instead of with stores — no longer has anywhere to start from without first undoing a JOIN that never should have been there. A clean staging model is reusable by any future mart, unconditionally.

Joins become impossible to trace. Imagine ten staging models, each with its own different JOIN added "for convenience." The dependency graph you're going to meet in module 3 (ref(), the DAG) stops reflecting reality: a table called stg_orders — a name that promises "just orders, clean" — actually depends on stores too, and nobody knows without opening the file and reading it whole. The name stops being trustworthy.

Real joins belong to a place with its own name: the marts. This guide's module 3 builds fact_orders, which really does need to combine orders with store_id and product_id — but that JOIN lives in a model whose name (fact_orders, a mart) already announces it combines several sources. The separation between staging (clean, 1 source) and marts (combined, several sources via ref()) is what makes each model's name, on its own, a trustworthy description of what it does.

Common mistakes

Justifying a JOIN in staging with "it's just to save a step." What happens: someone notices two tables are almost always queried together, and decides to merge them right in the staging model "to avoid repeating the join in every mart." Why it happens: in the moment, it seems like a reasonable optimization — less code, less repetition. How to spot it: any file inside models/staging/ with more than one FROM/JOIN is an immediate red flag, no matter how reasonable the justification sounds. How to fix it: if two tables almost always get combined together, that combination deserves its own mart with its own name (for example, something like orders_enriched), built with ref() from the clean staging models — never hidden inside a file that, by its name, promises to be just one source.

Adding a WHERE that filters "bad data" in staging. What happens: someone notices a row with a negative quantity or a store_id that doesn't exist in stores, and adds a WHERE quantity > 0 right in the staging model, to "clean up" the data early. Why it happens: it seems consistent with the idea that staging is where data gets "cleaned" — but cleaning types (casting) isn't the same as deciding which rows are valid. How to spot it: any WHERE in a staging model that isn't, say, a trivial technical filter (like excluding completely empty rows) deserves review — filtering by a business rule is a decision that should stay documented and visible, not silently hidden away in the project's earliest layer. How to fix it: this guide's module 4 gives you the right tool for this — tests (data_tests:) explicitly declare what's expected of a column, and fail visibly if a row doesn't comply, instead of disappearing without a trace inside a WHERE.

Confusing "casting a type" with "transforming the data's meaning." What happens: someone, inside a staging model, not only casts unit_price to decimal(10, 2) but also multiplies it by a currency conversion factor, thinking it's part of the "cleanup." Why it happens: casting types and transforming values feel similar — both modify a column — but they have a fundamental difference: casting preserves the data's meaning (0.55 is still 0.55, just with an explicit type), while multiplying by an exchange rate changes that meaning. How to spot it: ask yourself whether, after the transformation, the value still represents exactly the same thing it represented in the raw file. How to fix it: any transformation that changes a value's meaning — currency conversion, computing a margin, business rounding — belongs to a mart, never a staging model.

Exercises

Exercise 1 — Find the hidden JOIN. A teammate shows you this file, which they call stg_events.sql. Without running it, identify why it isn't, in fact, a valid staging model by this lesson's rule:

select
    e.event_id,
    e.event_type,
    e.session_id,
    e.event_ts,
    o.order_id
from {{ source('kiosko_raw', 'events') }} e
left join {{ source('kiosko_raw', 'orders') }} o
    on e.session_id = o.store_id
See solution

Even though the file is called stg_events.sql — a name that promises "just events, clean" — it actually combines two different sources (events and orders) with a LEFT JOIN. It breaks this lesson's rule exactly like the worked example's stg_orders_with_store_name_BAD: dbt would run it with no error at all — in fact, it doesn't even matter here that the JOIN's condition (e.session_id = o.store_id) compares two columns that don't represent the same thing, something no dbt validator catches — but it stops being a valid staging model in the sense this lesson defines.

Exercise 2 — Rewrite Exercise 1's example correctly. Split the previous exercise's file into the correct form: how many staging files would you need, and what would each one contain?

See solution

You'd need two separate files: stg_events.sql, with only event_id, event_type, session_id, event_ts, selected from source('kiosko_raw', 'events'); and stg_orders.sql, with orders's columns, selected from source('kiosko_raw', 'orders'), with no reference to events. If at some point the project needed to combine events with orders (something that, in fact, doesn't belong to this guide — session_id and store_id aren't directly comparable, as you saw in Exercise 1), that combination would deserve its own mart, built with ref('stg_events') and ref('stg_orders') once you know that function in module 3.

Exercise 3 — Argue in your own words why the absence of a dbt error isn't the same as "it's done right." Using the PASS=1 result from this lesson's worked example, explain in 2-3 sentences the difference between "the code is syntactically correct" and "the code respects the project's design."

See solution

dbt reporting PASS=1 only confirms the generated SQL is valid and DuckDB could run it with no syntax or type errors — it's a purely mechanical check. It says nothing about whether that model respects the architecture the team decided on for the project: the separation between staging (clean, one source) and marts (combined, via ref()) is a human convention, documented and agreed upon, not a rule dbt's compiler knows or can enforce. A project with PASS on every model can, even so, have a confusing architecture if nobody respects that convention — design discipline isn't replaced by the absence of errors.

Summary and next step

In this lesson you defined the staging layer's hardest rule: one model, one source, no JOIN or GROUP BY. You saw it respected in a simple query over stores, and saw it broken on purpose in a model that combines orders with stores — which ran perfectly, with no dbt error, demonstrating this rule is a design discipline, documented by dbt Labs, not a technical restriction from the engine. You also saw why a WHERE that filters "bad data," or a transformation that changes a value's meaning, don't belong to this layer either.

Before moving on you should be able to: explain, with no code run, why a staging model with a JOIN produces no dbt error; and name the three concrete reasons from the "going deeper" section for why that rule matters anyway.

Lesson 6 completes this layer's definition with the piece that gives it its visual identity inside the project: the stg_<entity> naming convention, and why this layer's default materialization is always view, never table.

Resources

← PreviousReading files directly with `external_location`Next →Naming staging models: the `stg_` prefix