Delta Lake is ACID. Your dbt run is not.

Every layer beneath a dbt run keeps its promise. The bug lives in the seconds between two SQL statements.

Delta Lake gives you ACID transactions. Every read is served from a consistent snapshot, every write commits atomically or not at all, and the machinery underneath is multi-version concurrency control (MVCC), the same technique relational databases have relied on for decades. Delta’s concurrency control documentation spells out these guarantees. And yet a dbt pipeline running on top of all that consistency can produce a fact table that references a dimension row which does not exist, because the pipeline read a version of the world that never existed as a whole.

I hit this in production. The short version: a referential integrity test between a rentals fact and a vehicle dimension failed in the 3 PM scheduled run, then passed on rerun with no code change. A new vehicle had been registered to the fleet and rented within minutes, right between the moment the dimension was built and the moment the fact was built. The dimension never saw the vehicle. The fact did. That summary is all the context this post needs. If you want the full incident story, including the silent variant hiding inside the UNKNOWN bucket and the one-line fix, it is in Part 1.

Part 1 left one question unanswered. Every component in this pipeline is transactional. Nothing errored. Nothing violated its contract. So where, physically, does the wrongness live? Which layer read what, and when?

By layers I mean the path a dbt run travels between the command you type and the files on disk: the orchestrator at the top, storage at the bottom, each layer handing work to the one below it. This diagram is a map of that path, showing who hands work to whom, not a sequence of timed steps:

graph TB
    dbt["dbt build<br/>walks the DAG, opens warehouse connections"]
    stmt["SQL statements<br/>one per model"]
    delta["Delta Lake transaction log<br/>versions and snapshots"]
    parquet["Parquet data files<br/>immutable storage"]
    dbt -->|"issues, in DAG order"| stmt
    stmt -->|"each reads a pinned snapshot from"| delta
    delta -->|"points at"| parquet

Before going further, one point needs to be clear: every one of these layers keeps its promise. The trouble is not vertical, between the layers. It is horizontal, in time. dbt issues many SQL statements one after another, and the source data can change in the seconds between one statement starting and the next. Those few seconds are the gap this post is about.

The next three sections walk down the map: what a Delta table actually is, how a query decides what it sees, and what dbt actually sends over the wire. By the end you will be able to point at the exact place the inconsistency enters, and you will see why the fix from Part 1 was not one option among many. It was the only kind of fix that could have worked.

A Delta table is a log

The walk starts at the bottom of the map, with storage. A Delta table is not a file. It is a log.

Under every Delta table sits a directory called _delta_log, and the log is the table. The Parquet files hold bytes; the log holds truth. The Delta protocol puts it plainly:

“Delta Log Entries, also known as Delta files, are JSON files stored in the _delta_log directory at the root of the table.”

Each of those JSON files is one commit, and each commit creates a new numbered version of the table. The naming is literal: the commit for version 42 is a file called 00000000000000000042.json. Inside it is an atomic set of actions applied to version 41 to produce version 42. Every action names a whole Parquet data file, never an individual row. Either a new data file joins the table’s list, or an existing one leaves it. For data, those two moves are the whole vocabulary. The protocol again:

“A Delta file, corresponding to version v, contains an atomic set of actions that should be applied to the previous table state corresponding to version v-1, in order to construct the vth snapshot of the table.”

Here is what that looks like in the incident timeline. At noon, the vehicles source table sits at version 41. At 12:00:05, the new vehicle’s registration lands. Physically, that write is one new file in the log:

vehicles/_delta_log/00000000000000000042.json      committed 12:00:05

{"commitInfo": {"operation": "WRITE", "timestamp": ...}}
{"add": {"path": "part-00417-...-c000.snappy.parquet", "dataChange": true, ...}}

One add action, one new Parquet file, one new version. Nothing was updated in place. Version 41 still exists, fully intact, right next to version 42. The table’s history is not a metaphor; it is a directory listing:

graph LR
    v40["...0040.json<br/>version 40"] --> v41["...0041.json<br/>version 41<br/>fleet as of 11:58"]
    v41 --> v42["...0042.json<br/>version 42<br/>add: new vehicle<br/>12:00:05"]
    style v42 stroke-width:3px

Under the hood: how a commit changes the file list

One rule governs everything in this section: a Parquet data file, once written, is never edited in place and never appended to. All any commit can do is play two moves: an add action, which puts a whole data file on the table’s list of live files, and a remove action, which takes a whole data file off it.

That list itself is worth pausing on, because it is not a file you can open anywhere. It is the result of replaying the log: replay the commits from the first version up to a chosen version, where every add puts a file on the list and every remove takes one off, and what remains is the table’s contents at that version. The list for version 41 and the list for version 42 are two different replays of the same log, which is how both can exist at once.

Every operation is a different combination of the same two moves:

• Insert. The engine writes a brand-new Parquet file containing only the new rows, and the commit records one add action for it. No existing file is copied, rewritten, or touched; the files already on the list simply stay there. That is exactly what commit 42 above shows, a single add with no remove, and it is why an insert is the cheapest write a Delta table can receive.
• Delete and update. A row cannot be crossed out of an immutable file, so both operations play the same pair of moves: the file holding the row comes off the list with a remove, and a copy of it, with the row deleted or corrected, joins the list with an add. Every other row in that file is carried across unchanged. The Databricks documentation is blunt about the cost: “without deletion vectors, modifying a single row requires rewriting the entire Parquet file containing that record.” Deletion vectors soften it by marking the row invalid in a small companion file instead, but even then the original file is never edited.

What “remove” actually removes. A remove action takes a file off the table’s current list; it does not delete the file from storage. The file stays on disk until a VACUUM run cleans up files that are no longer referenced and are older than a retention threshold, seven days by default. This is why version 41 remains fully readable after version 42 exists: every file it points at is still there.

Whatever the operation, the rule holds. A commit edits the list of files. It never edits a file. That is what makes every past version reconstructable.

The version chain above shows something easy to miss. Versions 40, 41, and 42 all exist at the same time, in the same directory, and each one is a complete, valid table. That makes “read the table” an ambiguous instruction: read which one? Every query has to answer that question before it can return a single row. A read is not a fetch; it is a choice of version. Who makes that choice, when exactly it is made, and how long it holds: that is the next stop on the map.

Every query picks a version and never lets go

Here is the choosing rule, and it is the single most important sentence in this post. When a query starts, it resolves the table to the latest committed version at that instant, and it keeps reading that version until it finishes, no matter what happens to the table in the meantime. Delta’s own documentation:

“Readers continue to see a consistent snapshot view of the table that the Apache Spark job started with, even when a table is modified during a job.”

Databricks states the same guarantee in the same words for its own runtime. This is snapshot isolation, and it is a genuinely strong promise. A query that starts at 12:00:00 against the vehicles table resolves it to version 41 and reads version 41 for its entire lifetime. The commit at 12:00:05 creates version 42 in the log, but our running query does not care. It chose its world at the moment it started, and it never lets go.

So at 12:00:00, the dimension build reads the fleet. It pins version 41. The new vehicle enters the log five seconds later as version 42, and as far as that read is concerned, the vehicle does not exist. Not as an error. As the guarantee working exactly as designed. The read is internally consistent, repeatable, immune to the churn of concurrent writes. This is what ACID reads are supposed to feel like.

Writes keep their promise too. Delta writers use optimistic concurrency control: no locks, write your files, then attempt to commit, and if another writer committed first, check for conflict and retry. Writers never block readers, readers never block writers, and every commit gets a serial position in the log. The write at 12:00:05 was flawless. The reads before and after it were flawless.

Hold on to the scope of that guarantee, because the entire bug hides inside it. The snapshot belongs to one executing query. Not to the pipeline, not to the run, not to any larger unit of work you might have in your head. One query, one snapshot. Which means everything now turns on a question that sounds administrative but carries the whole answer: when dbt builds your project, how many queries is that?

What dbt actually issues

A ten-model dbt run is not one query. It is at least ten.

Walk through what dbt build really does, because the mental model of “dbt runs my pipeline” hides the part that matters. dbt compiles your project into a DAG and executes it, per its docs, “in DAG order, for selected resources or an entire project.” It parallelises where dependencies allow: a thread in dbt is an open connection to your warehouse, and threads: 16 means up to sixteen models in flight at once, each on its own connection.

And what is “a model” once it reaches the warehouse? One SQL statement. The materialization docs are explicit: a table model “is rebuilt as a table on each run, via a create table as statement,” a view via create view as, an incremental model via inserts and updates. Your elegant DAG, at the wire level, is a stream of independent DDL and DML statements arriving one after another on a handful of connections.

Now the load-bearing fact, and I want to be honest that no single line of documentation states it, because nobody documents the absence of a thing. dbt does not wrap your models in one big transaction. You can assemble the proof from three pieces. First, the only transaction dbt’s docs mention is per model, the little drop-and-rename swap at the end of a table build. Second, each model is its own statement on its own connection, issued whenever the DAG unblocks it. Third, Databricks does not even offer a client-controlled transaction that spans multiple statements; on Delta, each statement commits through its own entry in the transaction log, atomically and independently. There is no BEGIN at the top of your run and no COMMIT at the bottom. There never was.

A precision note. It is tempting to say “each model is one Spark job.” Not quite. The unit that pins a snapshot is the SQL statement; a single statement can fan out into several Spark jobs, which you can see in the Spark UI, where one query’s page lists every job it triggered. Jobs, stages, and tasks are the machinery below the floor this bug lives on. The statement is the unit of consistency, so the statement is the unit we count.

Here is the full timeline on a single clock, with everything we now know:

graph LR
    t0["12:00:00<br/>statement 1 starts<br/>dim_vehicle pins vehicles v41"]
    t5["12:00:05<br/>vehicles commits v42<br/>new vehicle registered"]
    t8["12:00:08<br/>rental booked<br/>against that vehicle"]
    t12["12:00:12<br/>statement 10 starts<br/>fct_rentals pins fresh versions"]
    t0 --> t5 --> t8 --> t12
    style t5 stroke-width:3px
    style t8 stroke-width:3px

At 12:00:00, statement one, create table dim_vehicle as ..., starts and pins vehicles at version 41. At 12:00:05, the registration commits version 42. At 12:00:08, the rental lands in the rentals table. At 12:00:12, statement ten, create table fct_rentals as ..., starts. In between, statements two through nine ran on other connections, each pinning its own sources at its own start; they play no part in this incident, so the timeline skips them. Statement ten pins its snapshots at its own start time: the rentals source at its freshest version, rental included, and dim_vehicle, the physical table statement one wrote, frozen forever with the version 41 fleet inside it.

Both source commits land in the twelve seconds between statement one starting and statement ten starting. That window is not an accident of my pipeline. Between any two statements in any multi-model build there is such a window, and nothing at any layer spans it. Each statement pinned its snapshot at its own start. Ten models, ten statements, ten separately chosen instants.

The verdict: nothing broke, everything drifted

Now the question from the top of this post can be answered precisely. Where does the wrongness physically live?

Not in Delta. Every read served a consistent snapshot, exactly as promised. Not in the write path; the registration committed atomically. Not in dbt’s scheduling; the dimension was built before the fact, dependencies respected, precisely as the DAG demanded. Every component, checked in isolation, is innocent.

The wrongness lives in the arithmetic. N statements means N snapshots means N different answers to the question “what time is it?” The dimension’s world was frozen at 12:00:00. The fact’s world was frozen at 12:00:12. Both worlds are internally consistent, and the join is computed across the two of them:

graph LR
    w1["world A, pinned 12:00:00<br/>vehicles @ v41<br/>new vehicle absent"]
    w2["world B, pinned 12:00:12<br/>rentals fresh<br/>rental present"]
    w1 --> dim["dim_vehicle"]
    w2 --> fact["fct_rentals"]
    dim -->|"join across<br/>two worlds"| fact

No moment ever existed in which the fleet matched one and the rentals matched the other, and the join gets computed against exactly that nonexistent moment.

This is why the bug survives every check. Each read, examined on its own, is correct, and that is exactly what hides the problem: a fault that lives between statements cannot be found by checking statements one at a time. The failure only becomes visible at a level of granularity that no component owns: the run. Delta’s transactions end at the statement boundary. dbt’s responsibilities end at ordering. The primitive that would fix this, a transaction spanning multiple models, does not exist at any of these layers. Read skew is not a defect in any one component. It lives in the gaps between them.

Objection: Delta has a Serializable mode. Surely that fixes it? It does not, and the doc wording shows why. Databricks defaults tables to WriteSerializable, which “ensures that only write operations (not reads) are serializable”; the stricter Serializable level extends that guarantee to the ordering of reads against writes on that table’s history. Both levels govern how commits are ordered on one table. Neither creates a shared snapshot across two separate statements, and the same page notes that “read operations always use snapshot isolation” regardless of level. Our two statements would each still pin their own instant. Cranking the isolation dial cannot join what was never one transaction.

The fix, now with mechanism

Part 1 ended with a fix that looked almost too small: pick one timestamp and filter every source read against it. Now you can see what that line is really doing. It is a hand-built substitute for the run-level transaction that no layer provides.

Within a single dbt invocation, the right timestamp already exists. run_started_at is a Jinja variable holding the timestamp the run started, a Python datetime, UTC by default. Since a run starts exactly once, it is by construction the same value in every model of that invocation. It is an object rather than a string, so format it on the way into SQL, seconds included:

FROM {{ source('rental', 'rentals') }}
WHERE created_at <= '{{ run_started_at.strftime("%Y-%m-%d %H:%M:%S") }}'

Apply it to every unbounded source read and the behaviour described in the last four sections changes. Each statement still pins its own Delta snapshot at its own start; nothing can stop that. But now every snapshot is filtered down to the same logical instant. The dimension’s read at 12:00:00 and the fact’s read at 12:00:12 both cut the world at the run’s start time. The vehicle registered at 12:00:05 is invisible to both, lands cleanly in the next run, and the two models finally agree on what time it is. On the timeline from earlier, the fix draws one line at 12:00:00: everything before it belongs to this run, everything after it waits for the next one, and the twelve-second window stops mattering. You have not frozen the database. You have given every statement the same definition of “now.”

Finding the places to apply it is mechanical: search the project for source reads that carry no time filter. Every one of them is a candidate.

Two seams remain. First, run_started_at is constant within one invocation, but a production job that runs dbt snapshot followed by dbt build is two invocations, each with its own value. Close that by computing one cutoff and injecting it into every step through an env var or --vars. Second, if you want the physical version of this rather than the logical one, Delta time travel is the real thing: SELECT * FROM vehicles TIMESTAMP AS OF '...' pins an actual table version, the truest run-level snapshot available. dbt does not parametrise it for you, and retention limits apply (seven days by default before vacuumed versions become unreachable), which is why the timestamp cutoff is the pragmatic fix and time travel is the principled one.

Two kinds of time

One idea from this incident is worth naming before closing, because it applies well beyond dbt.

A warehouse deals with two kinds of time. Event time is when something happened in the business. The rental’s pickup timestamp is event time: it records the moment a customer collected a vehicle. Transaction time is when the warehouse found out. The instant a row landed in a table, or the instant a statement read that table, is transaction time. It has nothing to do with the business; it depends on job schedules, queue lengths, and which second a statement happened to start.

The incident happened because the two got mixed. My join asked an event-time question: what was this vehicle’s class when the rental began? But the data it ran against was cut by transaction time. The dimension was frozen at the instant statement one started, the fact at the instant statement ten started, and the twelve seconds between those two cuts is where the phantom row came from. An event-time question was answered with transaction-time data.

In this vocabulary, the fix from the previous section is one sentence: it forces every statement to cut the data at the same transaction-time instant. The full discipline, keeping both kinds of time as separate, queryable columns in your models, is called bitemporal modeling. It deserves its own post rather than a closing paragraph. If this series gets a Part 3, that is one of the candidates.

What each layer promises

The satisfying thing about walking down every layer is that you can now write down each layer’s contract in one line, and see that the incident fits neatly in between the lines.

Delta promises consistency per read. It delivered. dbt promises order, not simultaneity. It delivered. Nobody promised that ten consistent reads add up to one consistent run, because per-operation isolation does not compose into run-level isolation, not for free, not on any warehouse. Run-level consistency was always your job. It just never appeared in anyone’s error message until a vehicle happened to be registered inside a twelve-second gap.

The transferable lesson is a smell you can now recognise on sight: two reads of an unbounded source inside one logical unit of work. Wherever that pattern exists without a shared cutoff, this bug exists, patiently waiting for its window. You will not find it by auditing components, because every component is correct. You find it by asking the question no component can answer: do all my reads agree on what time it is?

If you remember three things:

• Delta’s snapshot belongs to the statement, not the run. Ten models means ten separately pinned instants, and nothing at any layer spans them.
• The smell: two reads of an unbounded source inside one logical unit of work, with no shared cutoff. Wherever that pattern exists, this bug exists.
• The fix: one timestamp per run, run_started_at within a single invocation or an injected cutoff across several, filtered into every source read. No isolation level can do this for you, because the statements were never one transaction.

There is one more layer to walk down some day: what actually happens between typing dbt build and a Spark task reading a Parquet file, the full path through connections, drivers, and executors. That is a different post, because none of that machinery changes the answer here. The answer here is simpler: nothing was broken. Delta Lake is ACID. Your dbt run is not, and now you know exactly why.