Topic: Part 10. dbt Staging Models
Difficulty level: Medium
Estimated study time: 2.5–3 hours (theory ~1 h, practice ~1.5 h, case walkthrough ~30 min)
Prerequisites: Confident SQL skills (SELECT, JOIN, CAST, COALESCE, CASE)
Basic understanding of dbt architecture (sources, models, ref, tests)
Familiarity with ELT principles and the layered architecture of data marts (raw → staging → intermediate → marts)
Concept of Specification-Driven Development (SDD) and Schema Manifest
Minimal experience working with a dbt project (dbt run, dbt test)
Learning objectives: Distinguish allowed and forbidden operations in the dbt staging layer and justify the boundary between technical normalization and business logic.
Correctly apply type casting, empty-value handling, and null semantics, taking into account source facts and the specification.
Design dbt tests for staging models (not_null, unique, accepted_values, singular) and distinguish them from the verification facts of the mart layer.
Detect hidden business logic in staging and formulate requirements for moving it into the specification or the mart.
Document decisions regarding PII, keys, and contract drift in staging in the SDD style.
Overview: Staging models in dbt are the first layer in which raw data from sources is turned into stable technical models with clear typing, consistent naming, and explicit null-semantic handling. The chapter formulates the staging philosophy: "boring is useful here." Staging must not answer product questions, choose the mart grain, aggregate customers, or hide source-contract drift. Its task is to prepare the data so that the SQL further down the chain is predictable, and a reviewer can quickly separate technical normalization from product meaning. The section examines the boundaries of allowed and forbidden actions, type-casting patterns (including handling empty strings before CAST to date), the basic set of dbt tests, requirements for handling PII, typical mistakes like "hiding business logic in staging," as well as review questions and a Qwen prompt that helps the agent work with staging in the spirit of SDD.
Key concepts: Staging as a technical layer: Staging is the first transformation layer where data "begin to speak the language of the platform." Stable column names, explicit types, empty-value handling, and base keys appear here, but no product decisions are made. If a staging model becomes a "small business mart," the layer has become too smart.
Boundary of allowed actions: In staging, the following are permitted: bringing field names into line with the project convention, type casting (date, integer, decimal), explicit handling of empty-value and null semantics, preserving source keys, marking PII fields (but not hiding them without reason). These operations are technical; they do not answer consumer questions.
Boundary of forbidden actions: In staging, the following are forbidden: aggregating customers, calculating a risk score, choosing the final mart grain, deleting rows "for the sake of appearance," hiding source-contract drift. Any such logic must either be explicitly written down in the specification/manifest or moved into the intermediate/mart layer.
Type casting and null semantics: A typical pattern: try_cast(nullif(cast(revoked_at as varchar), '') as date) as revoked_at. This line turns an empty value into null, but the meaning "null in revoked_at = active consent" is fixed in the specification and taken into account in the mart. A technical transformation is always accompanied by a product comment.
Dbt tests in staging: The minimum set: not_null and unique for source keys; not_null for required amounts and dates; accepted_values where the domain is small; singular tests for complex constraints. Staging tests catch technical source problems but do not replace data-product verification.
SDD and decision documentation: Specification-Driven Development requires: record that an empty value occurs in raw; explain why it becomes null in staging; verify that the mart counts active and revoked consents consistently. A technical fix turns into a documented decision.
PII in staging: PII fields remain visible in staging only where it is needed to demonstrate the security policy. Hiding them "by default" is an anti-pattern: it masks contract drift and gets in the way of verification.
Qwen prompt for staging: The canonical agent prompt: "Read the Schema Manifest and the raw sources. Create or verify staging models. Do not add business aggregations. For each type cast, explain the source fact it is based on." Such a prompt fixes the boundary and reduces the risk of "smart" models.
SQL style for the agent: Staging models should be small, read top to bottom, and use ref() and source() where appropriate. If a staging model is hard to read, the models further down the chain will be even worse.
Typical mistake: risk policy in staging: Hiding business logic in staging — for example, case when amount_rub > 100000 then 1 else 0 end as risk_event — is only acceptable if the rule is already written down in the specification or in the source manifest. Otherwise the agent invents a risk policy, which violates SDD.
Important dates: 2020: dbt Labs officially formalized the staging → intermediate → marts layer terminology in the documentation, simplifying the standardization of architectures.
2021: The release of dbt-utils and the popularization of tests/generic_tests macros, which simplified writing accepted_values and singular tests.
2022: The emergence of dbt-meta-testing and contract tests, strengthening control over source-schema drift precisely in the staging layer.
2023: The introduction of dbt Contracts (model versions) — staging became the first line of defense against source-contract drift.
2024: The wide spread of SDD practices and Schema Manifest as a mandatory artifact before AI agents generate staging models.
Practice exercises: Name: Staging audit: separate the technical from the product
Problem: Given a staging model stg_consents.sql. The model body:
select
user_id,
cast(consented_at as date) as consented_at,
try_cast(nullif(cast(revoked_at as varchar), '') as date) as revoked_at,
case when revoked_at is null then 'active' else 'revoked' end as status,
case when amount_rub > 100000 then 1 else 0 end as risk_event,
count(*) over (partition by user_id) as consents_per_user
from {{ source('raw', 'consents') }}Task:
- Label each transformation: rename, type cast, null handling, business logic.
- For each line, indicate which source facts justify it.
- Find any business logic that has no explicit specification.
- Rewrite the model so that only technical transformations remain in staging, and product logic is moved out or flagged as requiring a specification.
Solution: Step 1 — label each transformation:
- cast(consented_at as date) — type cast (technical; fact: the field is string in raw, domain is date).
- try_cast(nullif(cast(revoked_at as varchar), '') as date) — null/empty-string handling (technical; fact: empty values occur in raw and break cast to date).
- case when revoked_at is null then 'active' else 'revoked' end as status — BUSINESS LOGIC. The 'active' semantics belong to the specification, not to staging. They must be moved to the mart or fixed in the Schema Manifest.
- case when amount_rub > 100000 then 1 else 0 end as risk_event — BUSINESS LOGIC. The 100,000 rule is a risk policy that does not belong in staging. Hiding it here is a typical mistake.
- count(*) over (partition by user_id) as consents_per_user — AGGREGATION. Forbidden in staging.
- user_id — preserving the source key (technical; fact: the key exists in raw and must be preserved).
Step 2 — the correct version of staging:
select
user_id,
cast(consented_at as date) as consented_at,
try_cast(nullif(cast(revoked_at as varchar), '') as date) as revoked_at,
amount_rub
from {{ source('raw', 'consents') }}Step 3 — comments in the model or in the Schema Manifest:
- revoked_at: "an empty value in raw → null in staging; the active/revoked semantics are defined in the mart per specification S-001".
- amount_rub: "field is preserved as is; business rules (for example, risk_event) are computed in the mart per the specification".
Conclusion: the model has become "boring" and predictable, and the business logic has been moved to where it belongs.
Complexity: intermediate
Name: A set of dbt tests for staging on consents
Problem: The source is the raw.consents table with fields: user_id (string), consented_at (string, format YYYY-MM-DD), revoked_at (string, may be empty), source_system (string, domain: 'web', 'mobile', 'partner_api'). Create a schema.yml YAML file with tests for stg_consents. Justify why exactly these tests are technical rather than product.
Solution: ```yaml version: 2
models:
- name: stg_consents
description: "Technical normalization of consents from raw.consents. The active/revoked semantics are defined in the mart per specification S-001." columns:
- name: user_id
description: "User identifier from the source. Key of the staging model." tests:
- not_null
- unique
- name: consented_at
description: "Consent issue date. Source: a string field, cast to date in staging." tests:
- not_null
- name: revoked_at
description: "Consent revocation date. An empty value in raw → null in staging." tests:
- not_null
# Alternatively: not_null is only acceptable if revoked_at is contractually always populated; # otherwise replace with a singular test that verifies empty values have been turned into null.
- name: source_system
description: "Source through which the consent was issued." tests:
- accepted_values:
values: ['web', 'mobile', 'partner_api']
Justification of the technical nature of the tests:
- not_null + unique on user_id — this is a source-key integrity check, not a product hypothesis.
- not_null on consented_at — fixes the contract that the issue date is always present.
- accepted_values on source_system — catches the appearance of a new channel that has not been onboarded and signals contract drift.
- Product tests (for example, "no user with revoked_at later than consented_at," "no two active consents of the same type") are written in the mart or in a tests/ folder with singular tests for the data product.
Complexity: intermediate
Name: Handling an empty revoked_at: from a source fact to a documented decision
Problem: In raw.consents, the revoked_at field arrives as a string and in 30% of rows contains an empty string. The current code: cast(revoked_at as date) as revoked_at. With an empty string, dbt returns an error or unexpected values. Rewrite the transformation and prepare a comment for the Schema Manifest.
Solution: Correct transformation:try_cast(nullif(cast(revoked_at as varchar), '') as date) as revoked_at
Logic:
1. cast(revoked_at as varchar) — normalize the type (in case it arrives differently in the source).
2. nullif(..., '') — turn an empty string into null. This is a technical step, not a product decision.
3. try_cast(... as date) — safely cast to date; if the string does not parse, we get null.
Schema Manifest entry (fragment):fields:
- name: revoked_at
raw_type: string raw_semantics: "Consent revocation date. Empty strings occur in raw (~30% of rows) and mean 'the consent has not been revoked'." staging_type: date staging_semantics: "An empty string from raw is cast to null. The 'null = active consent' semantics is fixed in specification S-001 and implemented in the mart." mart_usage: "mart.consent_status computes active/revoked by the rule: revoked_at is null → active, otherwise → revoked."
Conclusion: a technical fix (try_cast + nullif) becomes part of a documented decision that is consistent between staging, the specification, and the mart.
Complexity: intermediate
Case studies:
Name: Fintech startup case: "risk score in staging broke the regulatory report"
Scenario: The analytics team of a fintech startup introduced an AI agent that generated staging models based on the Schema Manifest. The agent created stg_transactions, in which, in addition to technical transformations, the column risk_event appeared: case when amount_rub > 100000 then 1 else 0 end. The 100,000 threshold rule was not fixed in the specification — the agent "made it up" on its own, relying on general AML compliance practices.
Challenge: Three months later, the regulator requested a report on suspicious operations. The team assembled the mart.suspicious_transactions data mart based on risk_event from staging and sent it to the regulator. An internal audit found that: (1) the 100,000 rule was documented nowhere; (2) another team (compliance) was using a 250,000 threshold in parallel per the latest version of the policy; (3) the mart data mart differed from the compliance regulatory report by 12% of rows. The team urgently had to revisit the architecture and justify the source of the rule to the regulator.
Solution: The team conducted a retrospective and took the following steps:
1. Removed risk_event from stg_transactions, leaving only technical transformations: type casting, null handling, and renaming of fields.
2. Described the risk_event rule in the Schema Manifest with an indication of the source (compliance policy v2.3, threshold 250,000) and a reference to the regulatory document.
3. Moved the logic to mart.suspicious_transactions, where the rule became explicit, versioned, and signable by the product owner.
4. Added a dbt test in the mart: a singular test that verifies that all rows with risk_event = 1 have amount_rub >= 250000 and the field policy_version = 'v2.3'.
5. Added an explicit constraint to the agent prompt: "Do not add business aggregations or thresholds in staging. If you see that a rule is not fixed in the Schema Manifest, flag it as a TODO and do not generate the column".
Result: The regulatory report was reassembled in 2 days, and the discrepancy with compliance was eliminated. The team formalized the rule: staging accepts only the decisions listed in the chapter (names, types, null, keys, PII marking). Business logic is allowed in staging only when there is a reference to the specification. A quarter later, a similar situation with a 1,000,000 threshold was caught by the agent at the generation stage — it emitted a TODO instead of a column, and the team updated the Schema Manifest in time.
Lessons learned:
Staging is not the place for thresholds and business classifications: even a "harmless" case when generates regulatory risks.
Any numeric rule in staging must have a reference to the specification or the source manifest; otherwise it is a policy invented by the agent.
The agent prompt must explicitly forbid business aggregations and require a specification reference for any non-standard column.
Documenting null semantics and thresholds in the Schema Manifest makes auditing and reassembly of reports cheap.
Related concepts:
Boundary of allowed/forbidden actions in staging
SDD and Schema Manifest
Typical mistake: risk policy in staging
Qwen prompt for staging
The difference between staging tests and mart verification facts
Name: E-commerce platform case: "PII was hidden in staging, and debugging broke"
Scenario: A data engineer at an e-commerce platform decided to "secure" the data already in staging and masked PII fields (email, phone) with hashes right in stg_customers. This was done "just in case," without coordination with the security team and without an entry in the Schema Manifest. Six months later, the fraud-analytics team was unable to reproduce an incident: the investigation needed the original email, but staging already stored only a hash, and the raw.customers source had been rotated and was unavailable.
Challenge: The fraud team was unable to investigate the reported incident, and the regulator (with regard to anti-fraud reporting) requested confirmation that a specific email had been seen in a suspicious transaction. Reconstructing the chain "email → user → transaction" became impossible without re-extracting from the source, which required separate approval and took 5 business days.
Solution: The team conducted an audit and fixed the new rules:
1. PII remains visible in staging by default; masking is performed only in the mart layer for specific consumers (for example, for the marketing mart).
2. In the Schema Manifest, each PII field indicates the sensitivity level (low/medium/high) and a reference to the security policy.
3. In stg_customers, the email and phone columns were returned in their original form, and masking was implemented in mart.marketing_customers as a view that applies the mask_pii() macro.
4. A separate mart.fraud_investigation was created for the fraud team, where PII is preserved in clear text, and access is regulated at the warehouse-grant level rather than at the transformation level.
Result: The incident investigation was completed in 1 day after the changes were rolled out. The security team received an explicit registry of PII fields in the Schema Manifest and was able to build an access policy by layers rather than by engineers' "intuition". A quarter later, an analogous story with phone was prevented: a junior analyst asked the agent to "mask phone in staging" — the agent refused with a reference to the policy and suggested creating a separate mart.
Lessons learned:
PII in staging must remain visible: masking is a product decision, and it belongs to the mart layer.
Hiding PII in staging masks source-contract drift: if a field disappears or changes format, you will notice it too late.
The policy for accessing PII must be regulated at the grant and role level, not at the transformation level in staging.
The Schema Manifest is the mandatory place to record which PII fields are present in the source and where they are masked.
Related concepts:
PII in staging
Schema Manifest
Boundary of allowed actions (marking, but not hiding)
Documenting decisions in SDD
Study tips:
Before writing a staging model, open the Schema Manifest and underline the fields that have no specification. If there are more than two of them — stop and request a specification from the product owner.
The "boring staging" rule: if the model is pleasant to read top to bottom without branches and CASE — most likely it is in the right layer. If you catch yourself thinking "a comment is needed here to explain the logic" — the logic does not belong in staging.
Always apply the try_cast(nullif(cast(... as varchar), '') as date) pattern for fields that may arrive empty in raw. This eliminates 90% of type-cast errors.
Before committing a staging model, go through each line and label it: "rename," "type cast," "null handling," "business logic." If the last category has no reference to a specification — delete the line.
For a Qwen/AI agent, use the canonical prompt from the chapter as a template and add explicit prohibitions: "do not add case when with thresholds," "do not compute window functions except for technical row_number".
dbt tests in staging should be technical: not_null, unique, accepted_values. Product tests (for example, "no two active consents of the same type") belong in the mart.
If PII needs to be hidden — create a separate mart rather than touching staging. Staging is where contract drift must be visible.
Periodically (once a quarter) audit staging: look for lines with business logic that have no reference to a specification. This is the most reliable way to keep the layer from getting "smarter".
Compare staging and mart by the principle "staging answers the question 'what came from the source,' and the mart answers 'what does it mean for the business'." If staging starts answering the second question — it is in the wrong layer.
Additional resources:
Dbt documentation — staging models: https://docs.getdbt.com/best-practices/how-we-structure/1-staging — canonical description of the staging layer from dbt Labs with examples and anti-patterns.
Dbt-utils (tests and macros): https://github.com/dbt-labs/dbt-utils — a set of macros for not_null, unique, accepted_values, expression_is_true, and singular tests.
Dbt contracts (model versioning): https://docs.getdbt.com/docs/collaborate/govern/model-contracts — documentation on model contracts that strengthen protection against drift in staging.
The book "analytics engineering with dbt": https://www.amazon.com/Analytics-Engineering-dbt-Airflow-BigQuery/dp/1098142370 — a chapter on layering and the staging/marts boundary.
How we structure our dbt projects (discourse): https://discourse.getdbt.com/t/how-we-structure-our-dbt-projects/355 — the classic article by the dbt Labs team on layer structure.
Specification-driven development (sdd): https://martinfowler.com/articles/replaceThrowWithException.html — general SDD principles adapted to analytics in articles from the dbt community.
Qwen documentation (prompt engineering): https://qwen.readthedocs.io/en/latest/ — Qwen documentation for composing effective prompts for analytics agents.
Dbt-project-evaluator: https://github.com/dbt-labs/dbt-project-evaluator — automated project-structure tests that catch staging models with unexpected logic.
Summary: Staging models in dbt are a technical layer that turns raw data into stable, typed, documented models without making product decisions. In staging, the following are allowed: renaming and type casting, explicit handling of null and empty values, preserving source keys, and marking PII. In staging, the following are forbidden: aggregations, business filters, choosing the mart grain, computing a risk score, deleting rows "for the sake of appearance," and hiding contract drift. The key pattern — try_cast(nullif(cast(... as varchar), '') as date) — safely turns empty strings into null, while the meaning "null = active consent" is fixed in the specification and implemented in the mart. dbt tests in staging are technical in nature (not_null, unique, accepted_values, singular for complex constraints) and catch source problems, but do not replace data-product verification. The main trap is hiding business logic in staging: case when amount_rub > 100000 then 1 else 0 end as risk_event is only acceptable with a reference to a specification; otherwise the agent invents a policy. PII remains visible in staging; masking is a mart task. Good staging is "boring": small, read top to bottom, uses ref()/source(), and is easy to review. If staging becomes complex, the models further down the chain become even worse; if staging is clear, a reviewer can quickly separate technical normalization from product meaning. After mastering the chapter, you should have: staging models for the main sources, keys covered by dbt tests, null decisions recorded in the Schema Manifest, and PII remaining visible where it is needed to demonstrate the policy.