PD.
← All workGet in touch ↗

Case study · Australian wellness brand · Sole engineer

The snapshot that failed every build

One dbt snapshot stopped every build on an error that named no row, no column and no key. The obvious fix would have turned the build green and erased the table’s entire change history.

duplicate rows removed, out of 11,287
4
real history versions lost
0
months the duplicates had sat unnoticed
7

Context

The client runs its analytics on dbt and BigQuery, fed from its ERP. I was the only engineer on the engagement, so the warehouse, the models and the dashboards on top were mine to keep right.

One table tracks how each product changes over time. It is a snapshot: every time a product’s record changes, the old version is closed and a new one opened. That history cannot be rebuilt, because the source only ever holds the current state.

The problem

Every build that included this table died at the snapshot step. Five of six nodes passed. The sixth failed with:

UPDATE/MERGE must match at most one source row for each target row

Nothing in the error named a row or a key. And because the step failed, later steps in the scheduled job had been skipping silently since the failure began.

The fix I rejected

A full refresh would have passed on the first try. It would also have dropped the table and rebuilt it from current state, destroying every past version of every product with no way back. I ruled it out after reading the snapshot’s configuration, before running anything.

Finding it

My first suspect was the model feeding the snapshot. Its final deduplication is on product code, not id, so duplicate ids looked possible. One diagnostic query disproved that: zero duplicate ids, and the model already had a passing uniqueness test.

So the fault was inside the snapshot table itself. dbt builds a version key for each row from the product id and its last-modified timestamp, then merges on that key. Seven months earlier, one run had inserted four products twice. Each of those four had two identical open rows sharing one version key.

When a source row changed, it matched both open rows, and BigQuery refuses a merge where one target matches twice.

Only one of the four ever triggered the error. The other three had a NULL last-modified timestamp, so the change check evaluated to NULL and skipped them. They were waiting for the day the ERP touched them.

The repair I almost shipped

My first repair query kept one row per version key by filtering on the key’s value. Both duplicates share that value, so the row to keep was also in the set to delete, and the query would have removed nothing. I caught it by working through the predicate on paper before running it. The repair that shipped picks rows by position within each key instead.

What I shipped

Result

The table went from 11,287 rows to 11,283. Every real version was preserved and the build went green.

Before touching anything I checked what depended on the table. Nothing did: no model and no report read from it, so no published number had ever been wrong. The real damage was the job steps that had been skipping.

The same pass surfaced two problems nobody had asked about. Every snapshot in the project wrote to the production schema even from a development run. And 16% of product rows had no last-modified date at all, which made them impossible to version under the existing strategy. Both went to the client with a recommended fix.