Intermediate models: the layer nobody explains well

The hand-written version of this warehouse had a sentence in its documentation: never join fct_order_items to fct_transactions. It was correct, it was prominent, and it was completely unenforceable. An order line is one grain, a payment attempt is another, and an order with a decline followed by a successful retry appears twice on the payments side. Join them on order_id and every line on that order is duplicated - so the revenue you sum is money that was never taken.

In this project that sentence is a model. int_payments_per_order aggregates the attempts down to one row per order first, which is the only shape that can safely be joined to the sales fact. Three things change once the correct shape has a name:

• It is reusable. Anybody who needs payment context per order refs the model instead of writing their own GROUP BY and getting the aggregation subtly wrong.
• It is testable. The singular test assert_payments_never_fan_out_revenue joins the fact to this model and fails the build if the total moves by more than a cent. A warning in a wiki cannot fail a build.
• It is discoverable. The model's own header comment explains why it exists, and it sits in the DAG next to the thing it protects. Documentation you have to go looking for is documentation nobody reads.

int_order_lines_enriched computes three derived measures in three lines of SQL:

• commission_amount is net_amount times the merchant's commission_pct - which means it needs the merchant, which means it needs a join.
• cost_amount is quantity times the product's unit_cost - which needs the product.
• margin_amount is net_amount minus that cost.

Put those three lines in the intermediate layer and margin has exactly one definition in the whole estate. Leave them out and each of the twelve marts that wants margin writes its own version, and the versions drift: one rounds before summing, another after; one uses gross_amount because it read the column list too fast. Then somebody asks why the merchant scorecard and the finance dashboard disagree by 40 dollars, and the answer takes two days to find.

The measurable version: settled margin in this project is 46.89% of net revenue, and there is precisely one place in 23 models where that percentage could be made wrong. You will query it shortly.

The convention is int_<entity>_<verb>: int_order_lines_enriched, int_cart_contents, int_payments_per_order. The verb matters more than it looks, because it tells a reader what happened to the grain. Enriched means the grain did not change and columns were added. Per order means the grain changed. A reviewer who sees int_payments_per_order in a mart's ref() list knows immediately what shape arrives.

The second convention is harder and matters more: when a model makes a choice, the choice gets documented where the consumer will see it. int_cart_contents credits an entire cart to its dominant merchant, which means a cart containing two merchants' products credits all of its abandoned value to one of them. That is a real limitation, so it is written into _marts__models.yml on fct_carts.merchant_sk as a known limitation - not left in a Slack thread.

In Bazaar's current data every cart happens to contain products from a single merchant, so today the choice costs nothing at all. That is exactly why it must be written down: the day mixed carts appear, the mart quietly changes meaning and nothing in the build will complain. You will measure this in Demo 2, Q2.

This is the real cost of ephemeral, and the reason people abandon it after one bad afternoon. Your usual debugging move is to run select * from the_model limit 10 and look. With an ephemeral model there is nothing to select from - the name does not exist in the warehouse at all. Three moves replace it, in order of how often you will need them:

• dbt compile --select int_order_lines_enriched, then read target/compiled/. The compiled file contains the model's own SQL, fully resolved, ready to paste into any SQL client. This is the answer 90% of the time, and it is the single most useful debugging habit in dbt.
• Read the consumer's compiled SQL instead. Open target/compiled/bazaar/models/marts/fct_order_items.sql and the intermediate model is right there at the top as with __dbt__cte__int_order_lines_enriched as (...). That is not a paraphrase of what dbt did, it is what dbt handed DuckDB. Session b4's explorer shows the same thing side by side with the Jinja.
• Flip it to a view for one afternoon. Change the config to materialized: view, rebuild, poke at it with a normal query, then change it back. It is one line, and there is no shame in it.

An ephemeral model with two consumers is compiled into two queries, each containing a full copy of its SQL. That is fine at two. At eight consumers you have eight copies of the same joins, and three things go wrong at once:

• The warehouse recomputes it eight times. On DuckDB and 1,434 rows this is unmeasurable. On a billion-row table in a warehouse you pay per query for, it is the whole bill.
• Compiled queries get enormous. Nested ephemeral models compound, and a 2,000-line compiled query is not something you can read when you need to.
• Query plans get worse, not better. Optimisers cope well with a table and less well with the same subquery eight times.

The fix is one line. materialized: table in the model's config, and the same SQL now builds an object once and everybody selects from it. Nothing else in the project changes, because every consumer already goes through ref() - which is the point of session b4. The model's name stays, so no downstream SQL is edited.

The trade you are accepting when you promote it: the shape becomes queryable, so somebody will find it and build on it, and now you own a half-finished thing as an interface. That is usually the moment to ask whether it should have been a mart with documentation and tests all along.

Materialization is dbt's answer to "what object should this SELECT become". There are four in core use, and this project uses three of them:

• view - a view, rebuilt in milliseconds, always fresh, recomputed on every read. All 8 staging models. Cheap to build, and the cost moves to whoever queries it.
• table - dropped and recreated in full on every run. All 12 marts. Expensive to build, fast to query, and simple enough that nothing about it can surprise you.
• ephemeral - not built at all, inlined as a CTE. The 3 intermediate models. Zero storage, zero refresh, and zero ability to inspect it directly.
• incremental - a table that only processes new or changed rows. Not used here, on purpose. Done properly it needs a dataset far larger than 985 orders, and the interesting parts (merge strategies, late-arriving rows, backfills, warehouse-specific tuning) are out of scope for this course. Named in b2 with the trade-off stated.

The useful mental model: materialization is a performance and safety decision, not a modeling decision. The same SELECT is correct under all four. You choose between them based on how often it is read, how expensive it is to compute, and whether anybody should be able to query it directly - and you can change your mind later with one line, because ref() hides the difference from everything downstream.

Here is the commission line of int_order_lines_enriched, computed from staging by hand and compared line by line with what fct_order_items carries. If the intermediate model is doing what it claims, every one of the 1,434 lines agrees exactly.

WITH int_order_lines_enriched AS (
    SELECT lines.order_id,
           lines.line_no,
           round(lines.net_amount * merchants.commission_pct, 2) AS commission_amount
    FROM main_staging.stg_bazaar__order_items lines
    JOIN main_staging.stg_bazaar__merchants merchants
      ON merchants.merchant_id = lines.merchant_id
)
SELECT count(*) AS lines_checked,
       sum(CASE WHEN i.commission_amount = f.commission_amount THEN 1 ELSE 0 END) AS agreeing
FROM int_order_lines_enriched i
JOIN main_marts.fct_order_items f
  ON f.order_id = i.order_id AND f.line_no = i.line_no;

Try changing round(..., 2) to no rounding at all and run it again. The agreement count collapses, which is a small demonstration of why rounding belongs in one model rather than in every consumer.

int_cart_contents credits each cart to a single merchant. Before defending or attacking that decision, measure it: how many carts actually contain more than one merchant's products?

WITH cart_merchants AS (
    SELECT ci.cart_id, count(DISTINCT p.merchant_id) AS merchants_in_cart
    FROM main_staging.stg_bazaar__cart_items ci
    JOIN main_staging.stg_bazaar__products p ON p.product_id = ci.product_id
    GROUP BY ci.cart_id
)
SELECT merchants_in_cart,
       count(*) AS carts,
       round(100.0 * count(*) / sum(count(*)) OVER (), 1) AS pct_of_carts
FROM cart_merchants
GROUP BY 1
ORDER BY 1;

So the choice is free right now and will not stay free. When a marketplace adds a shared basket, this model starts attributing one merchant's abandonment to another and the build stays green throughout. The honest response is not to fix it today - it is to have the limitation written on the column, which it is, and to add the test that would notice. Session b7 is where you write that test.

No SQL. Think about each model against the two criteria - number of consumers, and cost to compute:

• int_order_lines_enriched has one consumer today and joins four staging models. At Bazaar's size, ephemeral is obviously right. Scale the order lines to 500 million and the same model is recomputed inside fct_order_items on every single run, and you are paying to re-join four tables for a result you already had. That is the classic promote-to-table case, and at that size it is usually the incremental case too.
• int_cart_contents has one consumer and does a window function over cart items. Same answer, same reasoning, arriving sooner because window functions are the expensive part.
• int_payments_per_order is the interesting one. Its only consumer is a test. Should something exist as a model when no mart reads it? Yes, and it is worth being able to argue why: the model is how the correct shape gets a name, a home in the DAG, and a place for the comment explaining the trap. The day an analyst needs payment context per order, the right thing already exists and is already tested. A model that prevents a mistake is earning its keep even with zero downstream marts.

The general rule to take to your own project: promote when more than a handful of models consume it, or when it is expensive enough that recomputing it per consumer shows up on the bill. Until one of those is true, ephemeral keeps your database free of shapes that were never meant to be interfaces.