Three months of sales vanished, and every dbt run was green

Vijay Kumar — Mon, 05 Oct 2026 22:09:21 +0000

In this article I'll show you how a dbt incremental model that looked perfectly reasonable wiped out three months of finance data, why none of our checks caught it, and how we fixed it without a full refresh. If you use delete+insert anywhere in your project, it's worth ten minutes to check you aren't doing the same thing.

Some background

I'm an analytics engineer at a leading North American housewares company. We are moving our SAP reporting off SAP BW and onto Snowflake and dbt, one report at a time. One of those reports is a gross-to-net finance report built from SAP's general-ledger line items. That's around 1.5 million rows per fiscal period and more than 150 million rows of history, so rebuilding it from scratch on every run was never an option.

So the model was incremental, and the config looked like this:

{{
    config(
        materialized='incremental',
        incremental_strategy='delete+insert',
        unique_key=['fiscal_year', 'fiscal_period']
    )
}}

select ...
from {{ ref('stg_gl_line_items') }}
{% if is_incremental() %}
where entered_on >= dateadd(day, -30, current_date)
{% endif %}

Reprocess anything entered in the last month, keyed on the fiscal period. It ran three times a day for weeks without a single failure, and honestly, when I first read it, it looked fine to me too.

What we saw

Then the report showed zero sales for April, May and June.

We only found it by accident. I was building a new commentary report on top of this table as part of the migration, and when I checked the new report's numbers, April to June were almost empty. The new report was fine. The table underneath it wasn't.

When I counted rows per period for the current fiscal year, this is what came back:

Fiscal period	Rows in table	Same period, prior year
P004 (April)	88	~1.6M
P005 (May)	37	~1.6M
P006 (June)	26	~1.4M

The periods weren't empty. They had been cut down to a few dozen rows each, and that's part of the reason nothing caught it.

What delete+insert actually does

delete+insert is the strategy you pick when you want to replace a slice of a table cleanly. On every incremental run dbt does three things:

Builds the batch, which is your model's SQL with the is_incremental() filter applied.
Deletes every row in the target table whose unique_key matches any key in the batch.
Inserts the batch.

Step 2 is the one to pay attention to. With unique_key = (fiscal_year, fiscal_period) the key doesn't identify a row, it identifies a whole fiscal period. If April shows up anywhere in the batch, all of April gets deleted.

How it went wrong

Accounting doesn't stop at month end. Adjustments, reclasses and corrections get posted to earlier periods all the time.

So say in August someone posts a correction to April. The row was entered in August, so it passes the entered_on >= current_date - 30 filter and comes into the batch. The batch now contains the key (2026, 004). dbt deletes every April row in the table, all ~1.5 million of them, and then inserts the batch. For April, the batch has one row.

Every late posting to an old period did the same thing. After a few weeks, the periods that got the most corrections had nothing left in them except those corrections.

Why nothing caught it

The run succeeded because dbt did exactly what the config told it to. The generic tests passed because not_null and unique are just as happy with 88 rows as with 1.5 million. And the table was never empty, since new data kept arriving every day, so the freshness and "zero rows" checks had nothing to complain about.

In the end we only found it because someone happened to be building something else on top of the table and looked closely at the numbers.

The fix

With delete+insert, whatever your incremental filter brings in has to contain the entire partition for every key it touches. So instead of picking up rows that were entered recently, we pick up the periods that had recent activity, and then take every row in those periods:

{% if is_incremental() %}
where (fiscal_year, fiscal_period) in (
    select fiscal_year, fiscal_period
    from {{ ref('stg_gl_line_items') }}
    where entered_on >= dateadd(day, -30, current_date)
)
{% endif %}

Now the late April correction brings in all of April. dbt deletes April and puts the complete April back.

Yes, this costs more per run than the old filter, because it reprocesses whole periods. But that is the real cost of using this strategy correctly. The old filter only looked cheaper because it was throwing data away.

Repairing the data without a full refresh

--full-refresh would have fixed it, but that meant rebuilding 150M+ rows and every year of history. Instead we added a variable that widens the window to every period from a given date onward:

{% if var('reload_from', none) %}
where fiscal_year || fiscal_period >=
      to_varchar(year('{{ var("reload_from") }}'::date))
   || lpad(to_varchar(month('{{ var("reload_from") }}'::date)), 3, '0')
{% else %}
  -- the normal whole-period filter from above
{% endif %}

dbt build -s my_gl_model+ --vars '{reload_from: "2026-04-01"}'

One detail I'd point out: the date gets snapped to the fiscal period, it is never used as a plain date filter. A mid-period date would hand delete+insert half a period, which is the same bug all over again.

Reloading five periods was about 7.6M source rows instead of 154M. After it ran, April to June were back to between 1.37M and 1.60M rows each.

A test I would add on day one

A simple singular test that compares each period with the same period last year would have caught this the first night:

-- tests/assert_no_period_collapse.sql
with counts as (
    select fiscal_year, fiscal_period, count(*) as row_count
    from {{ ref('my_gl_model') }}
    group by 1, 2
)
select cur.fiscal_year, cur.fiscal_period, cur.row_count, prev.row_count as prior_year_count
from counts cur
join counts prev
  on prev.fiscal_year = cur.fiscal_year - 1
 and prev.fiscal_period = cur.fiscal_period
where cur.row_count < prev.row_count * 0.5   -- tune this for your seasonality

If it returns any rows, a period is suspiciously smaller than last year's and the test fails.

To sum up

If you use delete+insert, think of the unique_key as "what am I about to delete", not as a row ID. Make your incremental filter bring in whole partitions, and design for late corrections from day one, because in finance data they're normal. A green run only tells you the pipeline executed, so add at least one volume test that compares against history. And when you need to repair data, reload a bounded window lined up with your partitions instead of reaching for --full-refresh.

If you want to check your own project, this lists every model using the strategy:

grep -rl "delete+insert" models/

For each one, look at the is_incremental() filter and ask whether it can ever bring in only part of a partition. If it filters on a row-level timestamp, it probably can.

This is the first in a series I'm writing about failures in the data stack where everything is green and the numbers are still wrong. Next I'll write about a dbt default that dropped 18 columns from one of our tables for six weeks without a single warning, so follow along if that sounds useful. And if your team is in the middle of moving off SAP BW, I'd be happy to compare notes.

DEV Community: Vijay Kumar