Builder session 10 · Capstone: add a subject area - learn analytics engineering with phoebe

Part 0

The brief, and what "done" means

Bazaar now accepts returns. The behaviour, as the product team describes it: a shopper requests a return on a single order line and gives a reason - damaged, wrong_item, changed_mind or not_as_described. The merchant approves or rejects the request. An approved return triggers a refund transaction, which is money leaving. The returned unit is either resellable or written off.

Read that paragraph twice before you write anything, because four modeling decisions are hiding in it. A return has its own date, separate from the sale. A return can be rejected, so it is not the same population as a refund. A return is at return-line grain, which is not order-line grain. And the refund is already visible in a fact you have - there are 37 refunded transactions in fct_transactions today, with no explanation attached to any of them. Your job is to build the explanation.

Live - presented in session Self-study - read after class ▶ Live SQL - real DuckDB, editable Sources covered

★ Acceptance criteria - agree these before you start 1 · A source declared for the raw returns table, and one staging model doing four jobs only: rename, recast, light clean, no joins. 2 · One intermediate model joining returns to the order lines they belong to. 3 · dim_return_reasons with a modelled reason_group, and fct_returns as a NEW fact at return-line grain - not columns on fct_order_items. 4 · Three tests: two generic on the new key and the new enum, one singular reconciling refund value against fct_transactions. 5 · A mart at merchant-day grain carrying returns and return value beside sold units as the correct denominator, plus a model contract on it. 6 · dbt build green, which means every model built, every test passing, and zero SKIPs.

Part 1 · the whole build, on one page

Five checkpoints, five traps 5 min live

Every checkpoint has one deliverable and one trap. The traps are not exotic - each one is the mistake a competent person makes when they are moving quickly, which is why they are worth naming out loud before you begin.

🔍 Click to zoom - the whole capstone, deliverable and trap per checkpoint

LiveHow the browser labs relate to the real project3 min▶

There is no Python in a browser, so no Jinja is rendered here and no dbt build runs. What the boxes below do is execute the SQL each model would compile to, on real DuckDB, against the real Bazaar warehouse - so the logic, the joins, the keys and the numbers are genuine even though the orchestration is not.

Two consequences worth understanding before you start:

There are no returns tables in the browser database. The project as shipped has eight sources and none of them is returns. So each box that needs returns creates the raw table first, deriving a handful of rows from the 37 refunded transactions that really are in raw.transactions. That seed block repeats at the top of several boxes on purpose: every box is independent and starts from a clean database.
In your repo, each box becomes a file. CREATE OR REPLACE VIEW main_staging.stg_bazaar__returns AS ... is what dbt does with models/staging/stg_bazaar__returns.sql plus +materialized: view. You write the SELECT; the wrapper is dbt's job.

Do it in the repo too The browser is for thinking. The capstone is only finished when dbt build is green on your own machine, because the parts a browser cannot show you - the DAG picking up your new edges, the tests interleaving with the models, the contract enforced before the object lands - are precisely the parts you came here to learn.

Self-studyWhat already exists that you must not duplicate2 min read▶

Before adding anything, inventory what is there. The project has 23 models, 100 tests, 1 snapshot and 8 sources, and returns touch several of them:

fct_transactions already holds the refunds. 37 rows with is_refund = 1 and a negative attempt_amount. Do not re-model the money; reconcile against it.
dim_dates already covers 2026-03-01 to 2026-09-30, which is wider than the data on purpose - so return dates resolve without you touching the calendar.
dim_merchants, dim_products and dim_users are conformed. Your new fact reuses them unchanged. That is what conformed means, and it is why this checkpoint list has only one new dimension in it.
stg_bazaar__order_items and stg_bazaar__orders are the staging models your intermediate model joins. An intermediate model reads staging, not marts.

A new subject area that needs four new dimensions is usually a new subject area that has not been thought about yet. Returns needs exactly one, because the only genuinely new descriptive thing in the brief is the reason.

Part 2 · the decision that determines everything downstream

Why returns are a new fact, not columns 6 min live

The tempting move is three columns on fct_order_items: units_returned, refund_value, return_reason. It looks smaller, it avoids a join, and it is wrong for three separate reasons - any one of which would be enough on its own.

🔍 Click to zoom - different grain, different date, different population

Question the brief raises	Answer	Where it lands
What is one row?	One requested return on one order line	`fct_returns` grain, key `return_line_sk`
Which date is the fact's date?	When the return was requested, not when the order was placed	`date_key` resolved from `requested_at`
Is a rejected return in the fact?	Yes - a request is a business event even when it is refused	`return_status` plus an `is_approved` flag
Where does the refund money live?	Already in `fct_transactions`; the returns fact must agree with it	The singular test in checkpoint 4
Is "damaged" a fact column or a dimension?	A dimension, because it needs a grouping nobody wants to repeat	`dim_return_reasons.reason_group`
Do we need new merchant or date dimensions?	No - they are conformed and reused unchanged	Existing `dim_merchants`, `dim_dates`, `dim_products`, `dim_users`

LiveWhy the reason becomes a dimension with a modelled group3 min▶

Four reason codes is small enough to feel like a column, and turning them into a dimension is still the right call - not for storage, for the grouping. The first question anybody asks about returns is "how many of these are our fault", and the answer requires a mapping: damaged, wrong_item and not_as_described are merchant-controllable, while changed_mind is shopper choice.

That mapping has to live somewhere. In a dimension it is one row per reason, written once, testable with accepted_values, visible in the docs and changeable in a reviewable diff. In a CASE statement it lives in whichever query somebody wrote most recently, and within a quarter you have three versions of "our fault" and a meeting about which one is right.

dim_return_reasons therefore carries the code, the human label, reason_group and a boolean like is_fulfilment_failure. Small dimension, high leverage - which is the same argument as dim_payment_methods, five rows, doing the same job for payment method families.

Self-studyCommon wrong turns3 min read▶

Six ways this capstone goes sideways. Every one has happened to a real team, and most of them build green before they bite:

Columns on the sales fact. The trap of checkpoint 3. Builds fine, passes every generic test, and makes return_rate unsummable forever. The tell is a consumer asking "which rows do I count in the denominator".
Filtering rejected returns out at staging. Then your fact silently means "approved returns", the rejection rate becomes unanswerable, and nobody can tell whether returns are rising or approvals are loosening. Keep the population whole and flag the status.
Using the order date as the return date. Convenient, produces a plausible-looking mart, and makes every trend in it wrong by however long the return window is. A return is its own event with its own date.
Joining fct_returns to fct_order_items in the mart. Two facts, two grains, the b5 and b8 lesson. Aggregate each to merchant-day first, then put them side by side.
Shipping a return_rate column. The trap of checkpoint 5. Store return_rate_numerator and return_rate_denominator and let the consumer divide, exactly as aov_numerator and aov_denominator already do.
Adding a CHECK constraint for returned quantity. The trap of checkpoint 2, and the most interesting one: a constraint can only see one row of one table, and this rule compares two tables. It has to be a test.

Self-studyWhere snapshots and incremental models would come in2 min read▶

Two things the brief does not ask for, and it is worth knowing why. A return moves through states - requested, then approved or rejected - so if the source table overwrites merchant_decision in place, the history of that decision is lost and a strategy: check snapshot on the returns source is the correct answer, exactly as merchants_snapshot does for commission changes.

Incremental materialization is the other one, and the honest trigger for it is a full refresh that no longer fits your build window. Forty return lines does not qualify, and an incremental model you cannot justify is a correctness risk with extra configuration. Note the shape of the decision though: returns are append-mostly with a late-updating status column, which is the case where incremental is genuinely hard and where people quietly lose rows.

Checkpoint 1 of 5 · 6 min · you build

Source and staging ★ four jobs, no joins

Declare the raw table once, then write exactly one staging model for it. The staging layer has a job description and returns do not get an exception.

Declare the source. In models/staging/_bazaar__sources.yml, add returns to the bazaar source. That is the only place the raw table name is ever written, and it takes the project from 8 sources to 9.

Write one staging model, stg_bazaar__returns.sql, doing four jobs and nothing else: rename to the project's vocabulary, recast the timestamp, lightly clean the enums with lower(trim(...)), and normalise the refund sign so downstream money is positive. No joins.

Add the two tests that belong at this layer: unique and not_null on return_id, and accepted_values on return_reason with the four codes. Test the enum where it enters the project, not after it has spread.

-- The loader lands raw.returns. In the repo this table is DECLARED in
-- _bazaar__sources.yml and never written again outside source('bazaar','returns').
CREATE OR REPLACE TABLE raw.returns AS
WITH refunded AS (
    SELECT order_id, abs(amount) AS refund_amount, txn_ts,
           row_number() OVER (ORDER BY txn_id) AS seq
    FROM raw.transactions WHERE status = 'refunded'
),
rejected AS (
    SELECT o.order_id, 0.0 AS refund_amount, o.order_ts + INTERVAL 3 DAY AS txn_ts,
           100 + row_number() OVER (ORDER BY o.order_id) AS seq
    FROM raw.orders o
    WHERE o.status = 'paid' AND o.order_id % 250 = 0
),
requests AS (SELECT * FROM refunded UNION ALL SELECT * FROM rejected),
top_line AS (
    SELECT order_id, line_no, quantity,
           row_number() OVER (PARTITION BY order_id
                              ORDER BY quantity * unit_price DESC, line_no) AS rn
    FROM raw.order_items
)
SELECT cast(9000 + q.seq AS BIGINT) AS return_id,
       q.order_id, l.line_no,
       CASE WHEN q.seq % 7 = 0 THEN l.quantity + 1 ELSE l.quantity END AS return_qty,
       CASE q.seq % 4 WHEN 0 THEN 'damaged' WHEN 1 THEN 'wrong_item'
            WHEN 2 THEN 'changed_mind' ELSE 'not_as_described' END      AS reason_code,
       CASE WHEN q.seq > 99 THEN 'rejected' ELSE 'approved' END         AS merchant_decision,
       CASE WHEN q.seq % 3 = 0 THEN 'written_off' ELSE 'resellable' END AS unit_disposition,
       cast(q.txn_ts AS TIMESTAMP) AS requested_ts,
       q.refund_amount
FROM requests q JOIN top_line l ON l.order_id = q.order_id AND l.rn = 1;

-- models/staging/stg_bazaar__returns.sql - four jobs, no joins.
CREATE OR REPLACE VIEW main_staging.stg_bazaar__returns AS
SELECT return_id,
       order_id,
       line_no,
       cast(return_qty AS INTEGER)     AS return_quantity,
       lower(trim(reason_code))        AS return_reason,
       lower(trim(merchant_decision))  AS return_status,
       lower(trim(unit_disposition))   AS unit_disposition,
       cast(requested_ts AS TIMESTAMP) AS requested_at,
       round(refund_amount, 2)         AS refund_value
FROM raw.returns;

SELECT * FROM main_staging.stg_bazaar__returns ORDER BY return_id LIMIT 8;

The trap. You will want to pull merchant_id in here, because the return needs a merchant and the order line has one sitting right there. Do not. A staging model that joins is a staging model somebody will later mistake for a fact, and the first thing that happens is a second staging model joining differently. The join is checkpoint 2's job, and it exists as a separate layer for exactly this reason.

Checkpoint 2 of 5 · 6 min · you build

The intermediate model ★ and the trap no constraint can catch

One ephemeral model that does the join staging was not allowed to do: every return, attached to the order line it belongs to, carrying the sold quantity and the dimension keys the fact will need.

Write int_returns_enriched.sql joining stg_bazaar__returns to stg_bazaar__order_items on (order_id, line_no), and to stg_bazaar__orders for the user. Ephemeral, so it is inlined into the fact and never materialized.

Bring units_sold through even though the fact does not strictly need it. It is what makes the checkpoint-2 test possible, and carrying one column to enable a test is always a good trade.

Then run the test. A returned quantity greater than the quantity sold is impossible in the business and perfectly legal in the database. Five rows come back.

CREATE OR REPLACE TABLE raw.returns AS
WITH refunded AS (
    SELECT order_id, abs(amount) AS refund_amount, txn_ts,
           row_number() OVER (ORDER BY txn_id) AS seq
    FROM raw.transactions WHERE status = 'refunded'
),
rejected AS (
    SELECT o.order_id, 0.0 AS refund_amount, o.order_ts + INTERVAL 3 DAY AS txn_ts,
           100 + row_number() OVER (ORDER BY o.order_id) AS seq
    FROM raw.orders o WHERE o.status = 'paid' AND o.order_id % 250 = 0
),
requests AS (SELECT * FROM refunded UNION ALL SELECT * FROM rejected),
top_line AS (
    SELECT order_id, line_no, quantity,
           row_number() OVER (PARTITION BY order_id
                              ORDER BY quantity * unit_price DESC, line_no) AS rn
    FROM raw.order_items
)
SELECT cast(9000 + q.seq AS BIGINT) AS return_id, q.order_id, l.line_no,
       CASE WHEN q.seq % 7 = 0 THEN l.quantity + 1 ELSE l.quantity END AS return_qty,
       CASE q.seq % 4 WHEN 0 THEN 'damaged' WHEN 1 THEN 'wrong_item'
            WHEN 2 THEN 'changed_mind' ELSE 'not_as_described' END      AS reason_code,
       CASE WHEN q.seq > 99 THEN 'rejected' ELSE 'approved' END         AS merchant_decision,
       CASE WHEN q.seq % 3 = 0 THEN 'written_off' ELSE 'resellable' END AS unit_disposition,
       cast(q.txn_ts AS TIMESTAMP) AS requested_ts, q.refund_amount
FROM requests q JOIN top_line l ON l.order_id = q.order_id AND l.rn = 1;

CREATE OR REPLACE VIEW main_staging.stg_bazaar__returns AS
SELECT return_id, order_id, line_no,
       cast(return_qty AS INTEGER)     AS return_quantity,
       lower(trim(reason_code))        AS return_reason,
       lower(trim(merchant_decision))  AS return_status,
       lower(trim(unit_disposition))   AS unit_disposition,
       cast(requested_ts AS TIMESTAMP) AS requested_at,
       round(refund_amount, 2)         AS refund_value
FROM raw.returns;

-- models/intermediate/int_returns_enriched.sql - the join staging is not allowed to do.
CREATE OR REPLACE VIEW main_staging.int_returns_enriched AS
SELECT r.return_id, r.order_id, r.line_no, r.return_quantity, r.return_reason,
       r.return_status, r.unit_disposition, r.requested_at, r.refund_value,
       oi.quantity   AS units_sold,
       oi.net_amount AS line_net_amount,
       oi.merchant_id, oi.product_id, o.user_id
FROM main_staging.stg_bazaar__returns r
JOIN main_staging.stg_bazaar__order_items oi
  ON oi.order_id = r.order_id AND oi.line_no = r.line_no
JOIN main_staging.stg_bazaar__orders o ON o.order_id = r.order_id;

-- tests/assert_return_quantity_never_exceeds_sold.sql - zero rows means pass.
SELECT return_id, order_id, line_no, return_quantity, units_sold
FROM main_staging.int_returns_enriched
WHERE return_quantity > units_sold
ORDER BY return_id;

The trap. The instinct is a CHECK constraint, and it cannot work. A check constraint evaluates one row of one table; this rule compares a returns row against an order-items row, and no constraint in any warehouse can see across tables. It has to be a test - a query that must return zero rows - which is the moment the b7 mechanism stops feeling like a testing framework and starts feeling like the only place some business rules can live. On the seeded data it returns five rows, so this test would fail the build, which is exactly right: five impossible returns are in the source and somebody needs to know.

Checkpoint 3 of 5 · 14 min · you build

The dimension and the new fact ★ the decision that matters most

One small dimension with a modelled group, and one new fact at return-line grain reusing four conformed dimensions unchanged. This is where the capstone is either right or quietly wrong for the next two years.

dim_return_reasons: one row per reason code, with the human label, reason_group and is_fulfilment_failure. Surrogate key from the same surrogate_key macro every other dimension uses, so the pattern stays uniform.

fct_returns: grain is one requested return line. Key return_line_sk. date_key from requested_at - the return date, not the sale date. Dimension keys resolved with LEFT JOIN and coalesced to the unknown member '-1', because an inner join here would silently drop a fact row.

Keep the rejected returns in. Ship return_status and an is_approved flag. A rejected request is a business event, and dropping it makes the rejection rate unanswerable.

Check the result. Forty return lines, forty distinct keys, 37 approved, and every dimension key resolved - no '-1' anywhere.

CREATE OR REPLACE TABLE raw.returns AS
WITH refunded AS (
    SELECT order_id, abs(amount) AS refund_amount, txn_ts,
           row_number() OVER (ORDER BY txn_id) AS seq
    FROM raw.transactions WHERE status = 'refunded'
),
rejected AS (
    SELECT o.order_id, 0.0 AS refund_amount, o.order_ts + INTERVAL 3 DAY AS txn_ts,
           100 + row_number() OVER (ORDER BY o.order_id) AS seq
    FROM raw.orders o WHERE o.status = 'paid' AND o.order_id % 250 = 0
),
requests AS (SELECT * FROM refunded UNION ALL SELECT * FROM rejected),
top_line AS (
    SELECT order_id, line_no, quantity,
           row_number() OVER (PARTITION BY order_id
                              ORDER BY quantity * unit_price DESC, line_no) AS rn
    FROM raw.order_items
)
SELECT cast(9000 + q.seq AS BIGINT) AS return_id, q.order_id, l.line_no,
       CASE WHEN q.seq % 7 = 0 THEN l.quantity + 1 ELSE l.quantity END AS return_qty,
       CASE q.seq % 4 WHEN 0 THEN 'damaged' WHEN 1 THEN 'wrong_item'
            WHEN 2 THEN 'changed_mind' ELSE 'not_as_described' END      AS reason_code,
       CASE WHEN q.seq > 99 THEN 'rejected' ELSE 'approved' END         AS merchant_decision,
       CASE WHEN q.seq % 3 = 0 THEN 'written_off' ELSE 'resellable' END AS unit_disposition,
       cast(q.txn_ts AS TIMESTAMP) AS requested_ts, q.refund_amount
FROM requests q JOIN top_line l ON l.order_id = q.order_id AND l.rn = 1;

CREATE OR REPLACE VIEW main_staging.stg_bazaar__returns AS
SELECT return_id, order_id, line_no,
       cast(return_qty AS INTEGER)     AS return_quantity,
       lower(trim(reason_code))        AS return_reason,
       lower(trim(merchant_decision))  AS return_status,
       lower(trim(unit_disposition))   AS unit_disposition,
       cast(requested_ts AS TIMESTAMP) AS requested_at,
       round(refund_amount, 2)         AS refund_value
FROM raw.returns;

CREATE OR REPLACE VIEW main_staging.int_returns_enriched AS
SELECT r.return_id, r.order_id, r.line_no, r.return_quantity, r.return_reason,
       r.return_status, r.unit_disposition, r.requested_at, r.refund_value,
       oi.quantity AS units_sold, oi.net_amount AS line_net_amount,
       oi.merchant_id, oi.product_id, o.user_id
FROM main_staging.stg_bazaar__returns r
JOIN main_staging.stg_bazaar__order_items oi
  ON oi.order_id = r.order_id AND oi.line_no = r.line_no
JOIN main_staging.stg_bazaar__orders o ON o.order_id = r.order_id;

-- models/marts/dim_return_reasons.sql - four rows, one modelled grouping.
CREATE OR REPLACE TABLE main_marts.dim_return_reasons AS
WITH reasons(return_reason) AS (
    VALUES ('damaged'), ('wrong_item'), ('changed_mind'), ('not_as_described')
)
SELECT md5(concat_ws('|', coalesce(cast(return_reason AS varchar), '_null_')))
                                                  AS return_reason_sk,
       return_reason,
       CASE WHEN return_reason IN ('damaged', 'wrong_item', 'not_as_described')
            THEN 'merchant controllable' ELSE 'shopper choice' END AS reason_group,
       return_reason IN ('damaged', 'wrong_item') AS is_fulfilment_failure
FROM reasons;

-- models/marts/fct_returns.sql - GRAIN: one requested return line.
CREATE OR REPLACE TABLE main_marts.fct_returns AS
WITH lines AS (SELECT * FROM main_staging.int_returns_enriched)
SELECT md5(concat_ws('|', coalesce(cast(lines.return_id AS varchar), '_null_'),
                          coalesce(cast(lines.line_no AS varchar), '_null_')))
                                                AS return_line_sk,
       coalesce(d.date_key, '-1')               AS date_key,
       coalesce(rr.return_reason_sk, '-1')      AS return_reason_sk,
       coalesce(dm.merchant_sk, '-1')           AS merchant_sk,
       coalesce(dp.product_sk, '-1')            AS product_sk,
       coalesce(du.user_sk, '-1')               AS user_sk,
       lines.return_id, lines.order_id, lines.line_no,
       lines.return_quantity                    AS units_returned,
       lines.refund_value,
       lines.return_status,
       lines.return_status = 'approved'         AS is_approved,
       lines.unit_disposition = 'resellable'    AS is_resellable
FROM lines
LEFT JOIN main_marts.dim_dates          d  ON d.date_day = cast(lines.requested_at AS date)
LEFT JOIN main_marts.dim_return_reasons rr ON rr.return_reason = lines.return_reason
LEFT JOIN main_marts.dim_merchants      dm ON dm.merchant_id = lines.merchant_id
                                          AND dm.is_current
LEFT JOIN main_marts.dim_products       dp ON dp.product_id = lines.product_id
LEFT JOIN main_marts.dim_users          du ON du.user_id = lines.user_id;

SELECT count(*)                                                    AS return_lines,
       count(DISTINCT return_line_sk)                              AS distinct_keys,
       sum(CASE WHEN is_approved THEN 1 ELSE 0 END)                AS approved,
       round(sum(CASE WHEN is_approved THEN refund_value ELSE 0 END), 2)
                                                                   AS approved_refund_value,
       sum(CASE WHEN merchant_sk = '-1' OR return_reason_sk = '-1'
                THEN 1 ELSE 0 END)                                 AS unresolved_keys
FROM main_marts.fct_returns;

The trap. Everything about this checkpoint is easier if you skip it and add units_returned to fct_order_items instead. It builds, it passes, and it destroys the metric: on a two-line order with one returned line you get one row with a return and one without, so return_rate fans out and every consumer has to know which rows belong in the denominator. A return is a different grain, on a different date, from a population that includes rejections. Three reasons, each sufficient. New fact.

Checkpoint 4 of 5 · 8 min · you build

Three tests, two kinds ★ shape and meaning

Two generic tests in the model YAML, and one singular test in tests/ that asserts something no generic test can express: that the money in your new fact agrees with the money already in the payments fact.

Generic 1 - the key. unique and not_null on return_line_sk. This is the grain claim, enforced. If it fails, every SUM over the fact is wrong and nothing else you test matters.

Generic 2 - the enum. accepted_values on return_reason with the four codes, plus relationships from fct_returns.return_reason_sk to the dimension. A fifth reason code appearing upstream is normal; a fifth reason code appearing while every report filters on four is a silent exclusion.

Singular - the money. Approved refund value in fct_returns must equal the refund total in fct_transactions, to the cent. Same shape as assert_star_reconciles_to_source.sql: a query that must return zero rows.

Read the result. Three PASS lines. Then break one on purpose - change the is_approved filter in the singular test - and watch which of the three notices.

CREATE OR REPLACE TABLE raw.returns AS
WITH refunded AS (
    SELECT order_id, abs(amount) AS refund_amount, txn_ts,
           row_number() OVER (ORDER BY txn_id) AS seq
    FROM raw.transactions WHERE status = 'refunded'
),
rejected AS (
    SELECT o.order_id, 0.0 AS refund_amount, o.order_ts + INTERVAL 3 DAY AS txn_ts,
           100 + row_number() OVER (ORDER BY o.order_id) AS seq
    FROM raw.orders o WHERE o.status = 'paid' AND o.order_id % 250 = 0
),
requests AS (SELECT * FROM refunded UNION ALL SELECT * FROM rejected),
top_line AS (
    SELECT order_id, line_no, quantity,
           row_number() OVER (PARTITION BY order_id
                              ORDER BY quantity * unit_price DESC, line_no) AS rn
    FROM raw.order_items
)
SELECT cast(9000 + q.seq AS BIGINT) AS return_id, q.order_id, l.line_no,
       CASE WHEN q.seq % 7 = 0 THEN l.quantity + 1 ELSE l.quantity END AS return_qty,
       CASE q.seq % 4 WHEN 0 THEN 'damaged' WHEN 1 THEN 'wrong_item'
            WHEN 2 THEN 'changed_mind' ELSE 'not_as_described' END      AS reason_code,
       CASE WHEN q.seq > 99 THEN 'rejected' ELSE 'approved' END         AS merchant_decision,
       CASE WHEN q.seq % 3 = 0 THEN 'written_off' ELSE 'resellable' END AS unit_disposition,
       cast(q.txn_ts AS TIMESTAMP) AS requested_ts, q.refund_amount
FROM requests q JOIN top_line l ON l.order_id = q.order_id AND l.rn = 1;

CREATE OR REPLACE VIEW main_staging.stg_bazaar__returns AS
SELECT return_id, order_id, line_no,
       cast(return_qty AS INTEGER)     AS return_quantity,
       lower(trim(reason_code))        AS return_reason,
       lower(trim(merchant_decision))  AS return_status,
       lower(trim(unit_disposition))   AS unit_disposition,
       cast(requested_ts AS TIMESTAMP) AS requested_at,
       round(refund_amount, 2)         AS refund_value
FROM raw.returns;

CREATE OR REPLACE VIEW main_staging.int_returns_enriched AS
SELECT r.return_id, r.order_id, r.line_no, r.return_quantity, r.return_reason,
       r.return_status, r.unit_disposition, r.requested_at, r.refund_value,
       oi.quantity AS units_sold, oi.net_amount AS line_net_amount,
       oi.merchant_id, oi.product_id, o.user_id
FROM main_staging.stg_bazaar__returns r
JOIN main_staging.stg_bazaar__order_items oi
  ON oi.order_id = r.order_id AND oi.line_no = r.line_no
JOIN main_staging.stg_bazaar__orders o ON o.order_id = r.order_id;

CREATE OR REPLACE TABLE main_marts.dim_return_reasons AS
WITH reasons(return_reason) AS (
    VALUES ('damaged'), ('wrong_item'), ('changed_mind'), ('not_as_described')
)
SELECT md5(concat_ws('|', coalesce(cast(return_reason AS varchar), '_null_')))
                                                  AS return_reason_sk,
       return_reason,
       CASE WHEN return_reason IN ('damaged', 'wrong_item', 'not_as_described')
            THEN 'merchant controllable' ELSE 'shopper choice' END AS reason_group,
       return_reason IN ('damaged', 'wrong_item') AS is_fulfilment_failure
FROM reasons;

CREATE OR REPLACE TABLE main_marts.fct_returns AS
WITH lines AS (SELECT * FROM main_staging.int_returns_enriched)
SELECT md5(concat_ws('|', coalesce(cast(lines.return_id AS varchar), '_null_'),
                          coalesce(cast(lines.line_no AS varchar), '_null_')))
                                             AS return_line_sk,
       coalesce(d.date_key, '-1')            AS date_key,
       coalesce(rr.return_reason_sk, '-1')   AS return_reason_sk,
       coalesce(dm.merchant_sk, '-1')        AS merchant_sk,
       coalesce(dp.product_sk, '-1')         AS product_sk,
       coalesce(du.user_sk, '-1')            AS user_sk,
       lines.return_id, lines.order_id, lines.line_no,
       lines.return_quantity                 AS units_returned,
       lines.refund_value, lines.return_status,
       lines.return_status = 'approved'      AS is_approved,
       lines.unit_disposition = 'resellable' AS is_resellable
FROM lines
LEFT JOIN main_marts.dim_dates          d  ON d.date_day = cast(lines.requested_at AS date)
LEFT JOIN main_marts.dim_return_reasons rr ON rr.return_reason = lines.return_reason
LEFT JOIN main_marts.dim_merchants      dm ON dm.merchant_id = lines.merchant_id
                                          AND dm.is_current
LEFT JOIN main_marts.dim_products       dp ON dp.product_id = lines.product_id
LEFT JOIN main_marts.dim_users          du ON du.user_id = lines.user_id;

-- The three tests, run as one scorecard. A dbt test passes on zero rows.
WITH t1 AS (
    SELECT 'generic - unique(return_line_sk)' AS test_name, count(*) AS failing_rows
    FROM (SELECT return_line_sk FROM main_marts.fct_returns
          GROUP BY 1 HAVING count(*) > 1)
),
t2 AS (
    SELECT 'generic - accepted_values(return_reason)', count(*)
    FROM main_marts.fct_returns WHERE return_reason_sk = '-1'
),
t3 AS (
    SELECT 'singular - refunds reconcile to fct_transactions', count(*)
    FROM (
        SELECT round(sum(CASE WHEN r.is_approved THEN r.refund_value ELSE 0 END), 2)
                   AS returns_side,
               (SELECT round(sum(-t.attempt_amount), 2)
                  FROM main_marts.fct_transactions t WHERE t.is_refund = 1)
                   AS payments_side
        FROM main_marts.fct_returns r
    ) WHERE abs(returns_side - payments_side) > 0.01
)
SELECT test_name, failing_rows,
       CASE WHEN failing_rows = 0 THEN 'PASS' ELSE 'FAIL' END AS result
FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2 UNION ALL SELECT * FROM t3)
ORDER BY test_name;

The trap. Two generic tests feel like a tested model, and they are the cheap half. Only the singular test notices if somebody later filters rejected returns out at staging, or flips the refund sign, or starts counting rejected requests in the refund total - all of which leave the shape immaculate. This is the b7 lesson arriving in your own code: generic tests caught 4 of 6 breakages, generic plus singular caught 6 of 6, and the two they needed help with were both cases where the meaning moved and the shape did not.

Checkpoint 5 of 5 · 12 min · you build

The mart and its contract ★ the denominator, and the promise

A returns number is meaningless without what it is a share of. The mart puts returns beside sold units at merchant-day grain, ships the rate as two additive parts, and then makes its own shape a promise.

Aggregate each fact separately. Sold units and revenue from fct_order_items to merchant-day; returns, returned units and refund value from fct_returns to merchant-day. Never join the two facts - b5's rule, b8's mart, your model now.

Union the grains before joining. A return lands on the day it was requested, which is often not a day that merchant sold anything. Start from the union of both grains and left join both sides, or you will silently drop returns.

Ship the rate as parts. return_rate_numerator is returned units, return_rate_denominator is sold units. No return_rate column, for the same reason mart_merchant_daily has no aov column.

Then put a contract on it, because this mart is what a merchant scorecard will read. The second box is the contract check from b9, applied to your new model.

CREATE OR REPLACE TABLE raw.returns AS
WITH refunded AS (
    SELECT order_id, abs(amount) AS refund_amount, txn_ts,
           row_number() OVER (ORDER BY txn_id) AS seq
    FROM raw.transactions WHERE status = 'refunded'
),
rejected AS (
    SELECT o.order_id, 0.0 AS refund_amount, o.order_ts + INTERVAL 3 DAY AS txn_ts,
           100 + row_number() OVER (ORDER BY o.order_id) AS seq
    FROM raw.orders o WHERE o.status = 'paid' AND o.order_id % 250 = 0
),
requests AS (SELECT * FROM refunded UNION ALL SELECT * FROM rejected),
top_line AS (
    SELECT order_id, line_no, quantity,
           row_number() OVER (PARTITION BY order_id
                              ORDER BY quantity * unit_price DESC, line_no) AS rn
    FROM raw.order_items
)
SELECT cast(9000 + q.seq AS BIGINT) AS return_id, q.order_id, l.line_no,
       CASE WHEN q.seq % 7 = 0 THEN l.quantity + 1 ELSE l.quantity END AS return_qty,
       CASE q.seq % 4 WHEN 0 THEN 'damaged' WHEN 1 THEN 'wrong_item'
            WHEN 2 THEN 'changed_mind' ELSE 'not_as_described' END      AS reason_code,
       CASE WHEN q.seq > 99 THEN 'rejected' ELSE 'approved' END         AS merchant_decision,
       CASE WHEN q.seq % 3 = 0 THEN 'written_off' ELSE 'resellable' END AS unit_disposition,
       cast(q.txn_ts AS TIMESTAMP) AS requested_ts, q.refund_amount
FROM requests q JOIN top_line l ON l.order_id = q.order_id AND l.rn = 1;

CREATE OR REPLACE VIEW main_staging.stg_bazaar__returns AS
SELECT return_id, order_id, line_no,
       cast(return_qty AS INTEGER)     AS return_quantity,
       lower(trim(reason_code))        AS return_reason,
       lower(trim(merchant_decision))  AS return_status,
       lower(trim(unit_disposition))   AS unit_disposition,
       cast(requested_ts AS TIMESTAMP) AS requested_at,
       round(refund_amount, 2)         AS refund_value
FROM raw.returns;

CREATE OR REPLACE VIEW main_staging.int_returns_enriched AS
SELECT r.return_id, r.order_id, r.line_no, r.return_quantity, r.return_reason,
       r.return_status, r.unit_disposition, r.requested_at, r.refund_value,
       oi.quantity AS units_sold, oi.net_amount AS line_net_amount,
       oi.merchant_id, oi.product_id, o.user_id
FROM main_staging.stg_bazaar__returns r
JOIN main_staging.stg_bazaar__order_items oi
  ON oi.order_id = r.order_id AND oi.line_no = r.line_no
JOIN main_staging.stg_bazaar__orders o ON o.order_id = r.order_id;

CREATE OR REPLACE TABLE main_marts.dim_return_reasons AS
WITH reasons(return_reason) AS (
    VALUES ('damaged'), ('wrong_item'), ('changed_mind'), ('not_as_described')
)
SELECT md5(concat_ws('|', coalesce(cast(return_reason AS varchar), '_null_')))
                                                  AS return_reason_sk,
       return_reason,
       CASE WHEN return_reason IN ('damaged', 'wrong_item', 'not_as_described')
            THEN 'merchant controllable' ELSE 'shopper choice' END AS reason_group,
       return_reason IN ('damaged', 'wrong_item') AS is_fulfilment_failure
FROM reasons;

CREATE OR REPLACE TABLE main_marts.fct_returns AS
WITH lines AS (SELECT * FROM main_staging.int_returns_enriched)
SELECT md5(concat_ws('|', coalesce(cast(lines.return_id AS varchar), '_null_'),
                          coalesce(cast(lines.line_no AS varchar), '_null_')))
                                             AS return_line_sk,
       coalesce(d.date_key, '-1')            AS date_key,
       coalesce(rr.return_reason_sk, '-1')   AS return_reason_sk,
       coalesce(dm.merchant_sk, '-1')        AS merchant_sk,
       coalesce(dp.product_sk, '-1')         AS product_sk,
       coalesce(du.user_sk, '-1')            AS user_sk,
       lines.return_id, lines.order_id, lines.line_no,
       lines.return_quantity                 AS units_returned,
       lines.refund_value, lines.return_status,
       lines.return_status = 'approved'      AS is_approved,
       lines.unit_disposition = 'resellable' AS is_resellable
FROM lines
LEFT JOIN main_marts.dim_dates          d  ON d.date_day = cast(lines.requested_at AS date)
LEFT JOIN main_marts.dim_return_reasons rr ON rr.return_reason = lines.return_reason
LEFT JOIN main_marts.dim_merchants      dm ON dm.merchant_id = lines.merchant_id
                                          AND dm.is_current
LEFT JOIN main_marts.dim_products       dp ON dp.product_id = lines.product_id
LEFT JOIN main_marts.dim_users          du ON du.user_id = lines.user_id;

-- models/marts/mart_returns_daily.sql - GRAIN: one merchant per day.
-- Two facts, aggregated separately, then joined on the conformed grain. Never joined directly.
CREATE OR REPLACE TABLE main_marts.mart_returns_daily AS
WITH sold AS (
    SELECT d.date_day AS activity_date, f.merchant_sk,
           sum(f.units) AS units_sold, round(sum(f.net_amount), 2) AS net_revenue
    FROM main_marts.fct_order_items f
    JOIN main_marts.dim_dates d ON d.date_key = f.date_key
    WHERE f.is_settled
    GROUP BY 1, 2
),
returned AS (
    SELECT d.date_day AS activity_date, r.merchant_sk,
           count(*) AS returns_requested,
           sum(CASE WHEN r.is_approved THEN r.units_returned ELSE 0 END) AS units_returned,
           round(sum(CASE WHEN r.is_approved THEN r.refund_value ELSE 0 END), 2)
               AS refund_value
    FROM main_marts.fct_returns r
    JOIN main_marts.dim_dates d ON d.date_key = r.date_key
    GROUP BY 1, 2
),
grain AS (
    SELECT activity_date, merchant_sk FROM sold
    UNION
    SELECT activity_date, merchant_sk FROM returned
)
SELECT g.activity_date,
       m.merchant_id,
       m.merchant_name,
       coalesce(s.units_sold, 0)        AS units_sold,
       coalesce(s.net_revenue, 0)       AS net_revenue,
       coalesce(x.returns_requested, 0) AS returns_requested,
       coalesce(x.units_returned, 0)    AS units_returned,
       coalesce(x.refund_value, 0)      AS refund_value,
       coalesce(x.units_returned, 0)    AS return_rate_numerator,
       coalesce(s.units_sold, 0)        AS return_rate_denominator
FROM grain g
JOIN main_marts.dim_merchants m ON m.merchant_sk = g.merchant_sk
LEFT JOIN sold     s ON s.activity_date = g.activity_date AND s.merchant_sk = g.merchant_sk
LEFT JOIN returned x ON x.activity_date = g.activity_date AND x.merchant_sk = g.merchant_sk;

SELECT merchant_id, merchant_name,
       sum(units_sold)            AS units_sold,
       sum(return_rate_numerator) AS units_returned,
       round(100.0 * sum(return_rate_numerator) / sum(return_rate_denominator), 2)
                                  AS return_rate_pct,
       round(sum(refund_value), 2) AS refund_value
FROM main_marts.mart_returns_daily
GROUP BY 1, 2
ORDER BY return_rate_pct DESC
LIMIT 8;

-- A contract checks SHAPE, not data - so this box deliberately builds only the
-- shape. Every box starts from a clean database, and the returns columns are
-- cast placeholders here to prove the point: dbt's contract check would be
-- identical whether these columns hold 40 returns or none.
CREATE OR REPLACE TABLE main_marts.mart_returns_daily AS
WITH sold AS (
    SELECT d.date_day AS activity_date, f.merchant_sk,
           sum(f.units) AS units_sold, round(sum(f.net_amount), 2) AS net_revenue
    FROM main_marts.fct_order_items f
    JOIN main_marts.dim_dates d ON d.date_key = f.date_key
    WHERE f.is_settled
    GROUP BY 1, 2
)
SELECT s.activity_date,
       m.merchant_id,
       m.merchant_name,
       s.units_sold,
       s.net_revenue,
       cast(0 AS BIGINT)               AS returns_requested,
       cast(0 AS HUGEINT)              AS units_returned,
       cast(0.0 AS DOUBLE)             AS refund_value,
       cast(0 AS HUGEINT)              AS return_rate_numerator,
       s.units_sold                    AS return_rate_denominator
FROM sold s
JOIN main_marts.dim_merchants m ON m.merchant_sk = s.merchant_sk;

-- The contract, checked the way b9 checked it: promised shape versus built shape.
WITH contract(column_name, data_type) AS (
    VALUES ('activity_date',           'DATE'),
           ('merchant_id',             'VARCHAR'),
           ('units_sold',              'HUGEINT'),
           ('units_returned',          'HUGEINT'),
           ('refund_value',            'DOUBLE'),
           ('return_rate_numerator',   'HUGEINT'),
           ('return_rate_denominator', 'HUGEINT')
),
built AS (
    SELECT column_name, data_type
    FROM information_schema.columns
    WHERE table_schema = 'main_marts' AND table_name = 'mart_returns_daily'
)
SELECT c.column_name,
       c.data_type AS promised_type,
       coalesce(b.data_type, 'not built') AS actual_type,
       CASE WHEN b.column_name IS NULL      THEN 'CONTRACT ERROR - column missing'
            WHEN b.data_type <> c.data_type THEN 'CONTRACT ERROR - type changed'
            ELSE 'ok' END AS build_verdict
FROM contract c
LEFT JOIN built b ON b.column_name = c.column_name
ORDER BY build_verdict, c.column_name;

The trap. One return_rate column would pass every test you wrote and be wrong the first time somebody groups by month. Ship the parts. And note the second thing this checkpoint quietly proved: on the seeded data the mart has 614 merchant-day rows of which 14 have zero sold units, because those merchants had a return on a day they sold nothing. If you had built the mart by starting from sold and left-joining returns, those 14 rows and their refund value would have vanished without a single test failing.

Real world

Returns is the subject area that exposes whether a warehouse was built or accumulated. In a warehouse that was accumulated, returns arrive as three columns on the orders table because that was the fastest change, and eighteen months later nobody can answer "what is our return rate by reason group" without a bespoke query and a caveat. In a warehouse that was built, returns is a new fact, one small dimension, four reused conformed dimensions, three tests and one mart - about 120 lines of SQL, reviewed in a pull request, and the question is a two-line group-by forever after. The difference is not effort. It is one decision at checkpoint 3, made in ten minutes, by somebody who knew what grain meant.

The finish line · 6 min

Acceptance checklist, and what green means

Run dbt build in your repo and check every line below. Green is not "no red text" - it is every model built, every test passing, and zero SKIPs. A SKIP means something upstream failed and the thing you were looking at was never rebuilt, which is the most dangerous kind of pass.

#	Deliverable	How you know it is done
1	`returns` declared in the sources YAML	`dbt ls --resource-type source` lists 9, not 8. No model contains the string `raw.returns`.
2	`stg_bazaar__returns.sql`, four jobs, no joins	The file contains no `join`. It has `unique` and `not_null` on `return_id` and `accepted_values` on `return_reason`.
3	`int_returns_enriched.sql`, ephemeral	`dbt ls` shows it; `information_schema` does not. It carries `units_sold` so checkpoint 2's test is possible.
4	`dim_return_reasons` with `reason_group`	Four rows. `reason_group` appears in exactly one file. `accepted_values` on the code.
5	`fct_returns` at return-line grain	40 rows, 40 distinct `return_line_sk`. `date_key` came from `requested_at`. Rejected returns are present with `is_approved = false`.
6	`fct_order_items` unchanged	`git diff` on that file is empty. This is the checkpoint-3 trap, verified.
7	Three new tests, two kinds	Two generic in the YAML, one singular `.sql` in `tests/`. The singular one reconciles refund value against `fct_transactions` to the cent.
8	A mart at merchant-day grain	Returns and refund value sit beside `units_sold`. There is no `return_rate` column, only its numerator and denominator.
9	A contract on the new mart	`contract: enforced: true` plus a `data_type` on every column. Rename one column on a branch and confirm the build fails.
10	`dbt build` green	Models built, all tests PASS, SKIP=0, ERROR=0. The baseline before your work was PASS=121, WARN=0, ERROR=0 in 1.22s.

One honest note about the seeded data: checkpoint 2's test finds five returns whose quantity exceeds the quantity sold, so on that data the build is correctly red. That is the test doing its job. In your repo you get to choose the professional response - fix the source, or downgrade the test to severity: warn with a comment saying why and who is chasing it. Silently deleting the test is the one wrong answer, and it is the most common one.

Homework

Try it yourself - this week ◐ 45-60 min total

Finish the capstone in the repo rather than the browser, and get dbt build to SKIP=0. Then run dbt docs generate and look at your new subject area in the lineage - nobody drew those edges, and they appeared because you wrote ref().
Add the exposure that reads your new mart: a merchant returns scorecard, with an owner. Then run dbt build --select +exposure:<name> and check that it selects your whole returns chain and nothing else.
Add a strategy: check snapshot on the returns source watching merchant_decision, so the history of approve-versus-reject survives an in-place update. Compare it with merchants_snapshot - same pattern, different column.
Do the same five checkpoints for a subject area in your own warehouse that arrived as columns on an existing fact. Write the grain sentence first, starting with "one row per". If the honest answer is a different grain, you have found a real bug with a business impact you can name.
Write the four-sentence version of this capstone for whoever reviews your pull requests: the grain, the date, the population, the denominator. Those four sentences are the review checklist for every new fact anybody on your team ever adds.

Source material

Sources covered

This page exercises the whole course, so it covers:

✓dbt documentation - sources, models, materializations, ephemeral, generic and singular tests, contracts, node selectionAll five checkpoints · every mechanism the previous nine sessions taught, applied once, unaided

✓Community layering conventions - source, staging, intermediate, marts, one staging model per source tableCheckpoints 1-3 · including the reason a join in staging is a bug even when it works

✓dbt documentation - snapshots and incremental models as the next decisionsPart 2 self-study · why neither is required here, and what would trigger each one

◐Kimball & Ross - grain, a new fact versus a wider fact, conformed dimension reuse, additivityRecap only for the models being implemented · the modeling course is learn-data-modeling

◐Scheduling the returns build, and extracting the returns table in the first placeOut of scope by design - orchestration is learn-dataops and extraction is learn-data-engineering

Check yourself

Three questions before you go 🎯 ◐ 90 seconds

1 · Why do returns become fct_returns rather than three columns on fct_order_items?

Three independent reasons, each sufficient on its own. The fan-out is the one that bites: on a two-line order with one returned line you get one row with a return and one without, so every consumer has to know which rows belong in the denominator.

2 · A returned quantity can exceed the quantity sold on that line. Where does that rule belong?

A check constraint evaluates one row of one table. This rule needs a returns row and an order-items row at the same time, which only a query can do. It is also why the intermediate model carries units_sold it does not otherwise need.

3 · dbt build finishes with no ERROR but reports SKIP=4. Are you done?

Green means models built, all tests passing and SKIP=0. A skipped node is the most dangerous kind of pass, because the object still exists and still answers queries - with yesterday's numbers and today's timestamp on the dashboard.

The whole builder track, on one card · pin this

b1-b2 · What dbt isCompiles SELECTs into tables and views, in dependency order, with tests. It does not extract, load, schedule or design. Materialization per layer is one config block.

b3 · Sources and stagingDeclare each raw table once. One staging model each: rename, recast, light clean, no joins. Test the enums where they enter.

b4 · ref() and the DAGNever write a table name. 32 nodes, 47 edges, derived. model+ downstream, +model upstream, state:modified+ for CI.

b5 · Intermediate modelsThe joins staging is not allowed to do, ephemeral so nothing materializes. Aggregate before joining, or revenue fans out.

b6 · Dimensions, facts, snapshotsDeterministic surrogate keys, LEFT JOIN plus an unknown member so no fact row disappears, and strategy: check for history a source overwrites.

b7 · TestsA test is a query that must return zero rows. Generic catches shape (4 of 6 breakages), singular catches meaning (6 of 6). Noise costs trust.

b8 · MartsThe star pre-aggregated to the grain a question set is asked at. Three grains, three marts. Never join two facts: 111,906.48, not 121,002.81. Ratios ship as parts.

b9-b10 · Ship itContracts check shape, exposures name consumers, docs are data, CI tests state:modified+. Green means SKIP=0. dbt does not schedule itself - that is learn-dataops.