A relationships test failed in the 3 PM scheduled run. The test says every vehicle referenced by a rental must exist in the vehicle dimension, and a handful of rentals pointed at a vehicle the dimension had never heard of.
I did what everyone does. I reran the job. It passed. Green. No code change, no data fix, no migration. The same test, the same models, the same warehouse, ten minutes apart, and the second time it was clean.
If you have run dbt in production for any length of time, you recognise this at once, and you have already learned to distrust it. The intermittent test. The one that fails at the busy hour and passes when you poke it. The temptation is enormous: mark it flaky, add a retry, move on with your day.
I want to argue the opposite. The rerun did not fix anything. The rerun is the clue.
The two models
Picture a small warehouse for a car rental company. It has two models that matter here.
fct_rentals is the reservation fact: one row per rental, identified by rental_id, carrying a vehicle_id for the car that was booked and a pickup_at timestamp for when it left the lot. dim_vehicle_history is the fleet dimension, tracked as a Type 2 slowly changing dimension: one row per version of a vehicle, holding attributes like vehicle_class and condition_grade, each row carrying a row_valid_from and row_valid_to window. When a vehicle is reclassified, say from Standard SUV to Premium SUV, the old row is closed off and a new one opens, so the dimension remembers what every vehicle was at every point in time.
The two are stitched together by a temporal join, so every rental picks up the vehicle’s classification as it stood the moment the customer drove off:
SELECT
r.rental_id,
r.pickup_at,
v.vehicle_class,
v.condition_grade
FROM fct_rentals AS r
LEFT JOIN dim_vehicle_history AS v
ON r.vehicle_id = v.vehicle_id
AND r.pickup_at >= v.row_valid_from
AND r.pickup_at < v.row_valid_toThat join is correct. I made the case for it in an earlier post and I still stand behind it. What broke is not the join. It is that the build fed it vehicles and rentals read at two different moments, and nobody told it they were supposed to agree.
What the rerun actually did
Here is the thing about a green rerun: it confirms the failure was real, then quietly erases the evidence.
When the test failed, a vehicle existed in fct_rentals that did not exist in the fleet dimension. When the test passed ten minutes later, that same vehicle existed in both. Nothing in my project changed between the two runs. So something outside my project changed, and the only thing outside my project is the source data and the clock.
The vehicle was real. It had been added to the fleet that afternoon. A customer rented it minutes later. By the second run, the dimension had caught up and the vehicle was present, so the join resolved and the test went quiet. The bug did not get fixed. The window simply closed on its own.
A test that heals itself is not telling you the problem is gone. It is telling you the problem is a function of timing. And timing bugs do not stay quiet.
Two models, two clocks
Here is the simplest version of the setup. Two source tables feed this corner of the warehouse, and both keep moving through the day. A vehicles source: a row lands when a car is registered to the fleet. A rentals source: a row lands when a customer books. Two models read them:
dim_vehicle, a gold dimension built fromvehicles, one row per vehicle.fct_rentals, the reservation fact built fromrentals, which joins todim_vehicleto attach vehicle attributes.
For the rest of this post I use a plain dim_vehicle table rather than the Type 2 dim_vehicle_history from the opening. The plain table keeps the mechanism uncluttered, and it is the harder case to dismiss, because it has no snapshot to blame. The snapshot-backed version behaves the same way, only worse.
Because fct_rentals references dim_vehicle, dbt builds the dimension first and the fact second. Same build, same warehouse, one after the other. Picture it:
graph LR
vsrc[("vehicles source<br/>(still arriving)")]
rsrc[("rentals source<br/>(still arriving)")]
vsrc --> dim["dim_vehicle<br/>built 12:00:00"]
rsrc --> fact["fct_rentals<br/>built 12:00:12"]
dim --> fact
Look at the two boxes. At 12:00:00 dim_vehicle reads vehicles and freezes the fleet as it stood at that instant. Twelve seconds later, at 12:00:12, fct_rentals reads rentals and joins the dimension that was frozen back at 12:00:00.
Now register a new vehicle at 12:00:05, and let a customer rent it at 12:00:08. The dimension was built at 12:00:00, so it never saw the registration. The fact was built at 12:00:12, after both rows landed, so it has the rental. The fact references a vehicle the dimension does not contain. The foreign key resolves to nothing.
How wide is that gap, the twelve seconds between the two builds? In this two-model example that is all it is, the time one create table as takes to finish before the next begins, so the bug needs a registration and a booking to land in that narrow slot. That sounds unlikely until you remember a busy fleet books continuously.
The slot is not always seconds. A real job runs dozens of models, often with a separate dbt snapshot step between the dimension and the fact, and the gap stretches to minutes or to a whole run cycle. Slower or busier compute widens it further. The window breathes, which is exactly why the same job fails at 3 PM and passes on a quiet rerun.
Two boxes, two times, two sources that kept moving after the dimension was frozen. That single picture is the whole bug. Everything else in this post is consequence.
Why this is read skew, not flakiness
The reflex is to call this a snapshot problem, or a scheduling problem, or bad luck at a busy hour. It is none of those. It has a name in database theory: read skew. Berenson and his co-authors catalogued it as anomaly A5A in their 1995 critique of the ANSI SQL isolation levels, and it appears whenever one logical unit of work reads related data at two different points in time. The structure underneath it is this: a dbt run has no global read cutoff.
When dbt builds your project, it walks the DAG and issues one statement per model. The dimension is one create table as. The fact is another create table as. They are separate statements, committed separately. dbt does not wrap them in a shared transaction, and there is no instruction anywhere that says “all of you read the source as of the same instant.” Each statement reads the source whenever it happens to run.
So the two clocks in the diagram are not an accident of our pipeline. They are the default behaviour of every multi-model dbt build on every warehouse. Each model is its own statement, pinned to its own moment. The gap between those moments is where entities are born, and any entity born in the gap exists for one model and not the other.
The engine-level detail of why each statement reads its own moment, and why no amount of Delta’s ACID guarantees prevents it, is the subject of the next post in this series, Part 2. Nothing in a dbt run forces two reads of a moving source to agree on the time.
That is why the rerun “worked.” It did not close the gap. It just took a new pair of readings, and this time no vehicle happened to be born between them.
The failure you do not see
Everything so far described the lucky case: the test failed loudly and I noticed. Now the uncomfortable part.
Look at how facts usually handle a foreign key that does not resolve. The join matches on vehicle_id. The fact stores the key it resolved to, vehicle_key_sk. That is the column the test checks. The Kimball convention, the one I have followed and recommended, is to never leave a key dangling. You coalesce it to a sentinel:
COALESCE(v.vehicle_key_sk, 'UNKNOWN') AS vehicle_key_skAnd because every real fact has a few legitimately unknowable keys, the relationships test that guards it often carries a filter so those sentinels do not trip it:
- relationships:
arguments:
to: ref('dim_vehicle')
field: vehicle_key_sk
config:
where: "vehicle_key_sk != 'UNKNOWN'"So why did the test in the incident fail at all? Because that particular fact left the foreign key raw, with no COALESCE to a sentinel. When the vehicle was missing, the key pointed at nothing, the relationships test found a value with no parent, and it failed out loud. That is the version you can see.
Now read the two snippets above together and feel the floor tilt. A fact that follows the convention, coalescing the missing key to UNKNOWN, with an UNKNOWN row in the dimension so the key always resolves, and a relationships test that excludes UNKNOWN, produces no failure from the same skew. The rental is quietly attributed to a vehicle that does not exist, the dashboard files it under “unknown vehicle,” and no notification fires.
The failing test was the lucky case. It was loud only because that one fact had not yet adopted the sentinel pattern, so the miss had nowhere to hide. Across a real project the COALESCE and the where filter are applied inconsistently, fact by fact, and everywhere they line up the same skew becomes a silent mis-attribution. You do not get a red run. You get slightly wrong numbers that nobody can explain.
And if the fact is incremental, it gets worse. The bad row, attributed to UNKNOWN during the gap, gets written once and never reconsidered. A full-refresh table self-heals on the next run when the dimension has caught up. An incremental fact freezes the mistake into history. The rerun that made the test go green never touched the rows that were already persisted wrong.
Reproduce it in about fifteen lines
You do not need our warehouse to see this. You need two sources you can insert into, and two models that read them at different points in a build.
-- models/dim_vehicle.sql
{{ config(materialized='table') }}
SELECT vehicle_id, vehicle_class, condition_grade
FROM {{ source('rental', 'vehicles') }}-- models/fct_rentals.sql
{{ config(materialized='table') }}
SELECT
r.rental_id,
r.pickup_at,
COALESCE(v.vehicle_class, 'UNKNOWN') AS vehicle_class
FROM {{ source('rental', 'rentals') }} AS r
LEFT JOIN {{ ref('dim_vehicle') }} AS v
ON r.vehicle_id = v.vehicle_idThe gap between those two builds is milliseconds if you run them together, which is not something you can step into by hand. So widen it. Run each model as its own invocation and stand in the gap yourself.
dbt run --select dim_vehicle
# insert one new vehicle, then a rental against it, into the sources
dbt run --select fct_rentalsThen count the damage:
SELECT COUNT(*) FROM fct_rentals WHERE vehicle_class = 'UNKNOWN'dim_vehicle built first and pinned the set of vehicles registered so far. fct_rentals built second and read a rental referencing a vehicle the dimension never saw, so the COALESCE filed it under UNKNOWN. Nothing failed. Rerun both once the sources have settled and the count drops back, with no record that it was ever wrong.
Splitting the commands makes it deterministic. Left alone, the same window is only as wide as the dimension takes to build, and I am not going to quote you a failure rate for that. I did not run this a thousand times to measure how often the window catches a row, and a number would be dishonest because the answer depends entirely on how fast your warehouse is and how fast your source arrives. The point is not the frequency. The point is that the window exists at all, and that it is invisible whenever the COALESCE absorbs it.
The fix: one cutoff
The bug is two reads of a moving source disagreeing about the time. The fix is to make them agree. Pick one instant and force every read to freeze there, no matter when the statement physically runs.
dbt hands you exactly the right value for this inside a single build. run_started_at is a Python datetime, UTC, constant for the whole invocation, so every model in the build sees the same value. It is an object rather than a string, so format it on the way into SQL. Filter every unbounded source read against it:
FROM {{ source('rental', 'rentals') }}
WHERE created_at <= '{{ run_started_at.strftime("%Y-%m-%d %H:%M:%S") }}'Keep the seconds. dbt’s own documentation formats run_started_at as "%Y-%m-%d" in its examples, and a date-only cutoff would admit every row that arrived later the same day, which reopens the window this is meant to close.
Filter on the column that records when the row landed in the source, created_at, not on pickup_at. The skew is about arrival time, when a rental was written, not event time, when the rental starts. Filtering pickup_at would wrongly exclude a rental booked today for a pickup next week, and it would confuse two different kinds of time.
Apply the same cutoff to every unbounded source read, the vehicles read in the dimension and the rentals read in the fact alike. It does not freeze the database. It gives every statement the same line in the sand to filter against, so the dimension and the fact draw from the same logical instant even though they ran minutes apart. A vehicle registered after run_started_at, and any rental booked against it, is invisible to both models, and falls naturally into the next run, which is exactly the batch semantics you want. Late data is not lost, it is deferred by one cycle.
There is one seam to watch. run_started_at is constant within a single dbt invocation, but the production job runs dbt snapshot and dbt build as separate invocations, each with its own run_started_at. To close the window across steps, compute the cutoff once and inject the same value into every step, through an environment variable or a --vars timestamp, so the whole job shares one cutoff rather than one per step.
The principled north star, if you want to go further, is Delta time travel: pin every read to one physical table version with TIMESTAMP AS OF. That is the truest job-level snapshot there is, and dbt does not parametrise it for you, so it is heavier to wire up. The run watermark is the pragmatic fix; time travel is the ideal you are approximating.
If you use snapshots, this is worse. Our production fleet dimension is not a plain table, it is snapshot-backed. A plain table rebuilds every run and self-heals once the source settles, the way the rerun did here. A snapshot is forward-only and only advances when
dbt snapshotactually runs, so the dimension can stay behind for a full cycle, and rentals can sit silently on UNKNOWN until the next snapshot catches up. Part 2 opens the engine to show exactly why.
What dbt promises, and what it does not
dbt promises order. It will build your dimension before the fact that depends on it, every time, because that is what the DAG is for. What dbt does not promise is simultaneity. It will not make two models read a moving source at the same instant, because nothing in a multi-statement build spans the statements with a shared snapshot.
Order is not the same as a shared clock, and on a warehouse where sources keep arriving during the build, the difference between the two is a class of bug that hides inside your UNKNOWN bucket and surfaces as a test that fails at 3 PM and passes at 3:10.
The temporal join I trusted at the top of this post is still correct. It was never the problem. The problem was upstream, in the gap between two statements that read the world at two different moments. A snapshot, it turns out, is not a transaction.
I wanted to know why. Delta Lake is ACID, transactional and MVCC, and our pipeline still read a world that never existed as a whole. Every layer underneath kept its promise. So if every layer is correct, where does the wrongness actually live? That is Part 2.