Fan-out from a single 1.2B-row staging model
A subscription commerce company runs dbt on Snowflake. The project has 410 models: 90 staging, 20 marts, and 300 models that select directly from stg_subscriptions and join two to four other staging models. Every one of those 300 is materialized as a view; only six marts are tables. The nightly build takes 5h40m at eight threads against a 45-minute SLA window, so the team routinely ships stale marts. stg_subscriptions is a view over a 1.2B-row table and contains a qualify row_number() over (partition by subscription_id order by updated_at desc) dedupe. Two analysts want to add 30 more models next quarter. FinOps reports that 38% of warehouse credits come from repeated scans of the subscriptions source, concentrated in a small number of models. A staff engineer proposes converting all 90 staging models to tables and inserting an int_subscriptions_daily layer between staging and the 300 consumers.
1. The proposal bundles two changes: materializing all 90 staging models as tables, and adding `int_subscriptions_daily`. Which parts do you adopt, defer, or reject? Justify with the mechanics of how each change affects scanned bytes and wall-clock time at eight threads.
Read the worked answer
Separate the two proposals; they attack different mechanisms.
Blanket table-izing of 90 staging models - reject. A view lets Snowflake push predicates down: a consumer filtered to where event_date >= '2025-01-01' never scans the other partitions of the 1.2B-row source. As a table, the staging model is rebuilt in full every run regardless of which consumers execute, so you pay the dedupe once per run plus storage, and narrow consumers lose pushdown. Second, it serializes the DAG: 90 tables must finish before 300 consumers start, which typically makes wall clock worse at eight threads, not better. Table-izing only wins when a model has many consumers and an expensive body and no selective filters - that is a per-model decision, not a layer decision.
Selective materialization - adopt for one model. stg_subscriptions runs a row_number() dedupe. Materialize it as an incremental table whose window function processes only new or changed partitions, then merges the deduped slice. That converts N repeated dedupes into one, and the table stays bounded because you never rebuild history.
Intermediate layer - adopt, but scope it explicitly. int_subscriptions_daily reduces 1.2B event rows to a day-grain aggregate, orders of magnitude smaller, and should absorb the majority of the 300 consumers that only need daily figures. Models that genuinely need event-level detail must stay on the event-grain path. If the daily layer becomes the only path, you silently change grain and break those consumers.
Before shipping, measure: pull 14 days of ACCOUNT_USAGE.QUERY_HISTORY with dbt query tags, rank models by credits and bytes. If 20 models account for 80% of the 38%, refactoring those 20 beats a project-wide rematerialization.
2. Two analysts want to add 30 models that select from `stg_subscriptions` directly. What dbt-level rule and enforcement mechanism would you put in place so this fan-out stops growing, without blocking the analysts?
Read the worked answer
Start with a published rule: only models in the staging layer may ref a stg_ model; everything else goes through an int_ or fct_/dim_ model. Then enforce it mechanically rather than in review comments.
Mechanism: model access and groups. Set access: private on staging models and assign them to a subscriptions group. Private models can only be referenced by models in the same group. Publish int_subscriptions_daily and the fct_ models as protected or public so any group can consume them. Analysts who need event-level detail get a new published model in the group, reviewed once, rather than 30 private joins.
Mechanism: CI guard. Add a CI step that runs dbt ls over changed models and fails if a non-staging model refs a stg_ model. This catches the case where someone removes access: private to unblock themselves.
Mechanism: contract for the new surface. Every new model in the 30 must declare its grain in the description, carry an owner meta field, and have a unique + not_null test on the grain plus one relationships test at the layer boundary. A model without a declared grain is a model nobody can safely join.
Keep it usable. If the published layer is slow or missing coverage, analysts will route around it. Budget a week to add the fields the 30 models need before you close the door, and give a 30-day deprecation window on existing direct references so nothing breaks on a Friday.
3. What would you measure in the four weeks after the refactor, and what evidence would make you reverse a decision?
Read the worked answer
Measure against three axes: cost, latency, and correctness.
Cost. Per-model credits and bytes from ACCOUNT_USAGE.QUERY_HISTORY with dbt query tags, aggregated weekly. Specifically watch (a) total credits for the subscriptions path, (b) credits spent rebuilding stg_subscriptions and any other materialized staging model, and (c) whether aggregate cost fell by more than the new storage and rebuild cost. If the incremental stg_subscriptions rebuild consumes more credits than the dedupe it replaced, revert that model to a view.
Latency. Nightly wall clock and per-model p95 duration. The refactor should reduce the number of models in the critical path, not just scanned bytes. Check that the intermediate layer did not become a new serialization point.
Correctness and coverage. Row-count reconciliation between int_subscriptions_daily and the event-grain path for three sample days, plus a check that no consumer silently moved from event grain to day grain. Compare fct_ row counts before and after with the same date range.
Reversal triggers. Reverse the intermediate layer if more than roughly 20% of consumers turn out to need event-level detail - at that point the aggregate adds a hop without reducing work. Reverse the selective materialization if freshness requirements tighten below the rebuild interval. Reverse access: private if the group boundary produces more than a handful of exceptions per month, which signals the boundary is in the wrong place.
Write these thresholds down before the refactor; post-hoc thresholds always get rationalized.