Models: Advanced

A model's table has whatever columns its SELECT returns. If you rename country to country_code in dim_customers.sql and run the model, the table changes shape and dbt raises no complaint. The complaints arrive later, from the 6 models and 2 dashboards that read country, each failing at its own build time or rendering a blank panel. A model contract moves that failure to dim_customers itself, before it writes a single row.

This is a properties file, the same kind of YAML that declares sources, with a models: list in place of sources:. On its own, the columns list documents the model. contract: enforced: true under config turns it into a promise dbt checks on every run. Each data_type is written in the warehouse's own type names, the words it prints when asked to describe a column. A properties file can hold several models; 1 file beside each model, named like it, keeps the promise next to the SQL it governs.

Before the model runs, dbt compiles the SELECT and asks the warehouse which columns it would return, by running the query inside a wrapper that matches no rows. The answer is a column list with a type for each. dbt checks that list against the YAML: the SELECT must return exactly the declared columns, each with its declared type. A mismatch fails the model with a table of the differences, and nothing is written. A match lets dbt create the table with the declared types first and load the SELECT's rows into it afterwards, so the warehouse holds integer for customer_id even on a day the query would have inferred something wider.

That early failure is the reason to add a contract. Suppose the application team migrates customer ids to strings on a Tuesday night. Without a contract, dim_customers rebuilds with a varchar key, the 6 models joining it on customer_id fail 1 by 1 over the next hour, and the dashboard that casts nothing shows 0 customers. With the contract, dim_customers fails first with 1 line naming the column and the 2 types, the 6 models never run, and yesterday's table stays in place for every reader until the fix lands.

Type names are compared as the warehouse prints them, so a contract that says varchar where the warehouse reports text fails on a system that treats the 2 as different words, and passes on one that treats them as aliases.

definition_type is what the SELECT returns, contract_type is what the YAML promised. A row with an empty contract_type is a column the SELECT produces that the YAML never mentioned, and a row with an empty definition_type is a promised column the SELECT lost. The fix goes either into the SQL or into the YAML, and the choice has a consequence: an edit to the YAML changes the promise, so every reader of the model is told before it lands.

A contract stops country from turning into country_code by accident. Sometimes the change is the plan: the regions team wants country_code and a new region column, while 4 downstream models and a dashboard still read country. A versioned model lets both shapes build on the same run, each under its own contract, so the readers move when their owners choose.

The model is still dim_customers. Its SQL now lives in dim_customers_v1.sql and dim_customers_v2.sql, 1 file per entry under versions. dbt builds 1 relation per file. The default relation names are dim_customers_v1 and dim_customers_v2; the alias under version 1 overrides the first to dim_customers, for a reason the dashboard explains below. The top-level columns list is the shape every version starts from. Version 1 adds nothing, so it inherits all 3 columns. Version 2 says include: all then excludes country, and declares 2 new columns, so its contract has 4 columns: customer_id, signup_date, country_code, region.

latest_version decides where an unpinned ref('dim_customers') points. Leaving it at 1 while version 2 first appears means nothing downstream changes shape: version 2 builds, sits beside version 1, and a reader opts in with ref('dim_customers', v=2) when its owner is ready. When most readers have moved, flip latest_version to 2 and pin the stragglers with v=1, which compiles to the same relation they always read. A reader's shape changes only when its owner edits it to add a pin or remove one.

A dashboard reads a relation by name and knows nothing about ref() or latest_version. Before versioning, that name was dim_customers. After it, with no alias, dbt builds dim_customers_v1 and dim_customers_v2. The dim_customers relation the dashboard reads stops updating, and neither side reports an error. The alias: dim_customers on version 1 keeps that name attached to the old shape. Moving the alias to version 2 later is the dashboard's cutover, a YAML edit made on the day its owner has changed the panels that read country.

deprecation_date on the v: 1 entry puts an end date on the old shape. Every parse then prints a warning for each model that still refs version 1, as an upcoming deprecation before the date and a passed one after it, so every run lists the stragglers. When the list is empty, delete dim_customers_v1.sql along with the v: 1 entry. The same change moves the alias to version 2. The next run builds version 2 into dim_customers, replacing the old shape under the name the dashboard reads, so that relation stays. The one left behind is dim_customers_v2, which version 2 built under its default name until now: drop it after that first run. Retiring version 1 without moving the alias leaves the reverse, a dim_customers frozen in the old shape, to drop once nothing reads it. Versions are for changes of shape. A wrong region mapping gets fixed in version 2 in place, because the shape its readers depend on does not change.

A snapshot reads a table that overwrites itself and keeps 1 row per version it has seen. On each row, dbt_valid_from and dbt_valid_to bound the time that version held. The intermediate lesson showed that result. Behind it, every run asks 2 questions of every row: has this row changed since the last run, and is this row still there. The strategy answers the first question, the hard_deletes setting answers the second.

With strategy: timestamp, a source row counts as changed when its updated_at is later than the updated_at the snapshot recorded for that unique_key. The comparison reads 1 column, so it costs little, and dbt_valid_from takes the source's own updated_at, so the history records when the application changed the row rather than when dbt noticed. Both benefits depend on the application keeping updated_at honest. A support tool that edits plan without touching updated_at makes a change the snapshot never records. A nightly job that rewrites updated_at on every row records a new version of every row, each identical to the last apart from its dates.

With strategy: check, dbt compares each column in check_cols between the source row and the snapshot's latest row for the same key, counting 2 nulls as equal. A difference in any listed column records a new version. A difference in a column outside the list records nothing. check_cols: all compares every column, at the price of comparing every column on every row. The timeline is dbt's own: dbt_valid_from is the run's timestamp, so a row that changed twice between 2 nightly runs yields 1 version holding the final values, and the history is as fine as the schedule.

Choose by what the source gives you. A table with an updated_at the application maintains on every write should use timestamp, which gives the cheaper comparison and the truer dates. A table with no such column, or one where edits arrive from tools that skip it, needs check, with check_cols listing the columns whose history anyone will ask about and nothing more, since every listed column is compared on every row on every run.

A key present last run and absent this run is a hard delete, and the default, hard_deletes: ignore, does nothing with it: the last version keeps a null dbt_valid_to, and every query for current rows reports a customer who no longer exists. The 2 other settings record the disappearance in different shapes.

In the animation, run 2 edits 2 rows: customer 1 moves from free to pro, customer 3 moves from US to CA. The snapshot gains 1 row, for customer 1, because country is outside check_cols and the edit to customer 3 is invisible to this snapshot. Run 3 finds customer 2 gone. new_record sets dbt_valid_to on customer 2's open row and inserts a 5th row carrying the same values with dbt_is_deleted set to True, so the table says when customer 2 left and what the row held at that moment.

The last row marks the boundary. dbt owns the name it is about to build, so a view called fct_orders gives way to a table called fct_orders. The 4 rows above it change the name or the schema, and the old name belongs to nobody. The frozen fct_orders is the dangerous case. The finance dashboard reading it renders every panel. Its monthly total on June 30 equals the total shown on March 3, the morning of the rename, because the table has held the same rows since, and no error appears anywhere.

The warehouse answers with what exists. dbt ls --resource-type model --resource-type seed --resource-type snapshot answers with the nodes the project builds, seeds included because each seed loads a table of its own. dbt ls prints node names, and a relation's name can differ: an alias replaces it, so fct_orders_daily builds fct_orders, and a version without an alias adds _v and its number, so version 2 of dim_customers builds dim_customers_v2. Translate each node into its relation name before comparing. A relation no translated name matches is either one dbt left behind or a table a person created by hand inside dbt's schema. Both deserve a look, and the warehouse's last-modified metadata, where it keeps any, dates the day each orphan stopped changing.

Dropping is a statement run against the warehouse, outside dbt. Run it after the readers have moved, because a dropped table takes the dashboard from stale to broken, and a stale dashboard is at least a dashboard someone can compare against. dbt clean deletes local folders such as target and touches nothing in the warehouse.

The alias config separates the model's name in the project from the relation's name in the warehouse. fct_orders_daily.sql with alias='fct_orders' builds the relation fct_orders the dashboard already reads, so the rename is visible to every ref() and invisible to the warehouse. 2 models that would build the same relation name in the same schema stop the run at parse time with an error naming both, so an alias can never silently overwrite a neighbour. Without the alias, a rename is 3 changes in a fixed order: rename the file and build, move the dashboard to the new name, drop the old relation. Dropping first leaves the dashboard with nothing, and never dropping leaves it showing frozen numbers.

Copy the query into models/legacy/legacy_customer_revenue.sql unchanged, apart from swapping each raw table name for a source() call, and materialize it as a table. dbt now builds the result the dashboard trusts on the same schedule as everything else, and that table becomes the reference every later step is measured against. The dashboard can read it at once, which retires the pasted copy on day 1.

Start from the tables the query reads, then follow its joins and its grain. 3 raw tables become 3 staging models, each renaming and casting 1 table, and the test-account filter moves into stg_customers, the only place it belongs. The subquery that sums captured payments per order is 1 grain, 1 row per order, so it becomes int_order_payments, joining orders to their captured total. The outer query sums per customer per month, so it becomes fct_customer_monthly_revenue, 1 row per customer per month, the grain the dashboard reads. Each model does the work the legacy query did at that level and nothing from the levels around it.

A subquery with a GROUP BY is a grain of its own and becomes a model. An expression repeated in 2 places, such as date_trunc('month', o.created) in the select and the group by, becomes 1 column, revenue_month, computed once in int_order_payments. A hard-coded list of ids becomes a seed, so the finance team edits a CSV when a new test account appears, and the staging model joins against it.

The query returns every row in 1 result and missing from the other, tagged by side. 0 rows means the 2 results match for every customer in every month, down to the amount. The first run rarely returns 0. The differences repeat across refactors: a left join written as an inner join drops customers whose orders have no captured payment, a join to payments at the wrong grain doubles an order with 2 payments, round applied per order instead of per month moves cents, and 1 test id in the hard-coded list is missing from the seed. Each difference names a model to fix. After each fix, rebuild the chain and rerun the audit until it is empty. Saving the audit as a view called audit_revenue keeps the query in the project, but building a view only stores the query: nothing runs it, and a non-empty result fails nothing. The proof is a select count(*) from audit_revenue, run after each scheduled build and read by the engineer doing the refactor.

Point the dashboard at fct_customer_monthly_revenue, under a contract that fixes the 3 column names and types the panels read. After the next scheduled run, audit_revenue should still return 0 rows; query it again to confirm. Then delete legacy_customer_revenue.sql and audit_revenue.sql, and drop both relations by hand, as the previous section showed. The 220 lines are now 5 models plus 1 seed. Each model sits at 1 grain and is short enough to read in a minute, and you can build and check any of them on its own.