Data engineering in the AI era · intermediate · About 10 minutes
The new column that only exists in development
The shop's orders now carry a discount. You added it to the orders model, development and CI passed, and the first production run failed with "column discount_amount does not exist".
You look after the dbt project that builds the shop's revenue tables.
What happened
On 1 September the shop's checkout started recording a discount on each order, in a new column
discount_amount in the source table raw_shop.orders. On 14 October you added the column to the
orders model and wrote net revenue as amount minus discount.
Your development run built everything. CI built everything. The production run that night failed, and the morning revenue report did not refresh.
The incident is made up. How things work in it is real, with sources at the end.
The evidence
Everything you need is here. Read it before you decide.
-- models/marts/fct_orders.sql
{{ config(materialized='incremental', unique_key='order_id') }}
select order_id, customer_id, ordered_at, amount, discount_amount
from {{ ref('stg_orders') }}
{% if is_incremental() %}
where ordered_at > (select max(ordered_at) from {{ this }})
{% endif %}-- models/marts/fct_net_revenue.sql
select date(ordered_at) as day,
sum(amount - coalesce(discount_amount, 0)) as net_revenue
from {{ ref('fct_orders') }}
group by 1- 02:00:04info1 of 3 OK created sql view model analytics.stg_orders
- 02:00:41info2 of 3 OK created sql incremental model analytics.fct_orders (9,812 rows)
- 02:00:43error3 of 3 ERROR creating sql table model analytics.fct_net_revenue: column "discount_amount" does not exist
| Development | CI | Production | |
|---|---|---|---|
| How it was built | From scratch | From scratch | Incremental, since March 2025 |
| Has `discount_amount` | yes | yes | no |
| Rows | 38.2M | 38.2M | 38.2M |
Your decisions
Decision 1 of 3
Decision 1
Why did production fail when development and CI passed?
Decision 2
How do you get tonight's report right?
Decision 3
How do you make the next change safer?
What went wrong
An incremental model is 2 things: the SQL, and a table that already holds the results of earlier
runs. A fresh build makes the table from the SQL, so development and CI saw the new column. An
incremental run only adds rows to the table that exists, and what it does with a changed column list
is set by on_schema_change. The default, ignore, adds nothing: dbt's docs say a column added to an
incremental model will not appear in the target table. So production had a model that selected
discount_amount and a table without it, and the first model to ask for the column failed.
None of the on_schema_change settings fills a new column for rows already loaded. append_new_columns
adds the column, empty for the past. To have the value everywhere, run a full refresh, or a manual
update for the old rows. That is why dbt's advice is a full refresh of an incremental model and all
its downstream models whenever its logic changes.
The wider lesson is schema driftSchema DriftWhen the shape of incoming data changes without warning, such as a renamed or new column, and breaks or silently corrupts the pipelines that read it.: a source that changes shape under a pipeline. A data contractData ContractAn agreement between the team that produces data and the teams that use it, written down and checked, that fixes its columns, meaning and freshness. with the checkout team would have announced the column on 1 September instead of 6 weeks later.
Keep these
- An incremental model's table remembers its old shape. A fresh build in development does not show you that.
- dbt ignores a new column in an incremental model by default, and no setting fills it for old rows.
- Change an incremental model's columns, then plan the full refresh that goes with it.
The ideas behind it
Sources