The Modern Data Engineer Part 17: Load Patterns and Their Costs

Four ways to find out what changed

A pipeline that loads the same source every night has to work out on each run what changed since the night before. Short of reloading everything, every way of doing that misses some kind of change, a row deleted outside the application, say, or a correction to a record dated last month. The run still finishes without an error, whichever method you picked.

There are four common methods. A full load reloads everything, at the price of reading, and usually storing, the whole source each time. A watermark load, also called a delta load, queries the source for rows whose modified timestamp has moved since the last run. A partition reload reads a recent window, the last seven days for example, in full. Change data capture, or CDC, reads the log in which the source database records every insert, update and delete. In the engine from Part 16 the choice is the load_pattern line in each source’s spec file, set per source, and Part 16 named the four values without saying what each one costs.

Full load: storage or replay

Full load is the simplest of the four to describe, because every run reads everything the source currently has. Its cost depends on how strict your Bronze layer is. In the medallion setup this series uses, Bronze is raw capture that is only ever appended to, the property Part 9’s erasure design and Part 10’s schema versioning were built on. Part 1 called this approach snapshots and used the name full load for dropping the target and replacing it, which is the reset load further down. Each full load adds a complete snapshot of the source next to the ones before it, and the differences between two consecutive snapshots get written to Silver-cleaned. Each snapshot holds every key in the source, so a key that was in last night’s snapshot and is missing from tonight’s has been deleted. It’s the full snapshot diff from Part 6, with the same limit on timing: you know which run detected the delete, which can be up to a full run interval after it happened.

An append-only full load can do without the deletion registry from Part 6, the technical table in Bronze that records the key and detection time of every delete so that a Silver rebuild doesn’t bring deleted rows back. Every earlier snapshot is still in Bronze, so a rebuild can find the deletes again by comparing them, at the price of a more elaborate merge into Silver. I’d write the registry anyway, so every pattern records its deletes in the same table.

The price is storage. Say a source holds ten million rows and loads every night. Within a year Bronze holds 3.65 billion rows for that one source, nearly all of them identical to the night before, and on a cloud platform you’ll see it on the bill well before you notice it in query performance.

At a previous client we treated this as two patterns. A full load worked as described above. A reset load replaced whatever Bronze held for the source with the latest copy, and the Silver model loaded that copy as it was, without comparing it to anything, because only the current state of that source was needed. A reset load removes the storage penalty and most of the merge logic. It also breaks the principle the Bronze layer in this series rests on, that Bronze is immutable and only ever appended to, and it loses the ability to replay, the reason that principle exists. A deleted record disappears at the next reset, and the platform keeps no history of that source at all. That’s the cost Part 1 named for full loads, destructive to historical state when downstream models depend on it, and with a reset load you accept that cost, which is safe as long as no downstream model needs the history of that source.

Even so, there are sources where I allow a reset load, and some where I’d recommend it. When the source keeps its own history, such as a data product from another platform or an application that stores every version of a record and doesn’t just return the current one, you can load that history again whenever you need it, and keeping every snapshot in Bronze would only mean paying to store a second copy of it. Part 10 relaxed Bronze strictness on a similar condition: dropping data is safe once you accept you won’t need to rebuild that period from Bronze.

Two things keep the exception safe. The source owner’s commitment to keep the history belongs in the source’s contract in the spec file, where it’s written down and someone is accountable for it, because if the source ever stops keeping it you have no other copy. The copy you replace doesn’t have to disappear straight away either. An expiry of a few days on the old files, or the versioning or soft delete your cloud provider offers at the storage level, gives you time to recover when something goes wrong at the source.

Part 1 named a second cost that applies to both variants. A full load stores whatever the extraction returned without checking that it’s complete. With snapshot diffing, a partial extraction looks like a normal load, and every row it missed shows up in Silver as a delete. The main defence is in the extraction itself. An extractor with proper error handling and retries fails the run as soon as the source returns an error, and with the landing step from Part 16, where a slice is written to a temporary folder and moved into Bronze in one step, an incomplete slice never reaches Bronze. With that in place I’ve never had an incomplete slice mistaken for a complete one. A row-count check at the boundary, next to the value-level rules from Part 11, is a backstop that stops the load when tonight’s extraction returns far fewer rows than last night’s, though a normal-looking count doesn’t prove the extraction was complete. Under a reset load the same safeguards stop a partial copy from replacing the full one.

Watermark load and the changes it misses

A watermark load, also called a delta load, takes the highest modified-at value it has already loaded and queries the source for every row with a later one. It’s cheap because it only reads what changed. It also misses any change that leaves the timestamp as it was, and a hard delete made outside the application is the clearest case, since a deleted row has no timestamp left to move.

At a previous client, our pipeline for an HR platform assumed that every deletion went through the application, which marks a record as deleted by setting a flag and leaves the row in place. What nobody on the team knew was that admins corrected faulty entries made by HR staff directly in the database, and some of those corrections were hard deletes. A delta load keyed on the soft-delete flag and a modified-at timestamp had nothing to select for those records, because the rows no longer existed at the source. The warehouse kept its last copy of each one, with the delete flag still false and the timestamp unchanged. Part 6 warned about this: a soft-delete convention only holds for the entities where the source actually follows it, and that has to be checked per entity.

No test or alert caught it, and Part 11’s contract checks wouldn’t have either, since they inspect what arrives and a deleted row never arrives. A data officer noticed the numbers didn’t add up and traced the cause on his own initiative. The records had been correct when they were loaded and became wrong the moment an admin deleted them at the source, and they would have stayed wrong until someone else happened to look.

The fix is the shadow table Part 16 built for the deletion registry from Part 6. It holds only the primary-key columns. Each refresh reloads those keys from the source and compares them against the previous copy, and any key that disappeared has been deleted. That makes it the full load from the previous section restricted to the key columns, which keeps the storage small, though it still reads every key in the source each time it runs. In Part 16 that happened once a day, so a delete could take up to a day to show up even when the watermark load ran more often. A shadow table is something you pair with a pattern that doesn’t detect deletes, so load_pattern doesn’t get a fifth value for it.

A watermark also needs a column from the source, because it only works if the source stamps each row at the moment it changes, with an application-level updated_at or a database trigger that writes the change time. A pipeline that fills in its own load time records when the pipeline read the row, and you can’t ask the source for rows changed since last night on the basis of a time the source never stored. Without a column like that you only know what the source holds right now, which puts you back at a full load.

Exactly where the next run starts is up to whoever builds the engine, though I’d take the highest modified-at in Bronze from runs the run record marks as complete and subtract a margin of a few minutes, so rows that arrive late or carry a timestamp from a slightly slow source clock don’t fall into the gap. On a high-volume source I’ve seen a clock difference of milliseconds be enough to lose rows.

dbt’s built-in way to keep Type 2 history, the snapshot, reads its whole source query every time it runs, however few rows the watermark load added. It compares the current state of that query with the previous state in the snapshot table. Under a watermark load Bronze holds only delta slices, so that current state has to be built first. You can build it with an ephemeral model that merges every slice so far, or by running a full load next to the watermark load. Either way the run reads the whole source again. With delete detection switched on, filtering the source query to recent rows doesn’t help, because the snapshot then marks every row it didn’t select as deleted.

A watermark load with a hand-written merge only needs the rows that changed since the last run, which is the slice that just landed in Bronze. On a large table that’s a small fraction of the reads. With a fresh full load as its input, the snapshot does catch hard deletes, because a deleted key is missing from it. A state rebuilt from delta slices still contains every hard-deleted key, so you have to apply the shadow table’s results to it first, and the hand-written merge has to be paired with the same shadow table. I’ve still written those merges by hand, because the merge plus a shadow table that reads only the key columns still reads far less than a snapshot. A snapshot table also loses its history, deletes included, if it ever has to be rebuilt, as Part 7 showed.

Partition reload and the edge of the window

A partition reload reads a recent window of the source in full on every run, the last seven days or the last thirty, whatever the source and the downstream models need. The window is defined on a date column in the source, usually a business date such as an order date or a transaction date. Nothing in Bronze gets replaced, because each run appends its snapshot of the window next to the earlier ones, and the diff against the previous run goes to Silver-cleaned, the same way a full load works over the whole source. Because the window is counted back from the run date, tonight’s run reads a range that starts a day later than last night’s, so the diff can only cover the dates both runs read. A record from the day that just dropped out of the window is missing from tonight’s snapshot because nobody asked for it, and treating it as deleted would close records in Silver that still exist at the source. Inside the overlap, corrections are picked up on the next run without needing a modified-at column. Deletes are harder, because comparing only the dates two runs share takes a custom script that nothing else in the engine uses, and even then a key missing from tonight’s window may belong to a record whose business date was corrected to a day outside it. A partition reload doesn’t detect deletes reliably, so it needs the same shadow table as a watermark load.

The weak spot is the edge of the window. Once a record’s business date has dropped out of the window it’s never read again, so a correction to last quarter’s invoice slips through. Widening the window catches more of those, but every run reads and stores the whole window whether anything in it changed or not, so storage and run time grow with the width of the window, however little of the source moved that day.

CDC, when you can get it

CDC is the only one of the four that records every change as it happens, deletes included. Part 6 and Part 7 already worked through the mechanism. CDC reads the source database’s transaction log, the write-ahead log in PostgreSQL or the binlog in MySQL, so a deletion arrives as an event carrying the transaction’s commit timestamp, which is the closest you’ll get to a real deleted_at. A shadow table or a full load can only give you detected_at, the time of the run that found the delete. The delete events land in Bronze like any other change, and the registry stores their commit timestamp as the deletion time. Even CDC usually starts with an initial snapshot of the tables, which is a full load, before it switches to reading the log.

In the field, CDC isn’t available for most of the sources you have to ingest. A SaaS product typically exposes a REST API that returns current state, and even where the vendor offers a webhook or event feed next to it, that feed can usually only be replayed within a retention window of days or weeks and can’t backfill anything older, so extraction still ends up polling the REST endpoint. A fair amount of what runs in more traditional environments arrives as a batch file transfer on a schedule, with no log to read underneath it. A genuine streaming source, with Kafka or something equivalent in front of the database, is rare.

CDC needs direct database connectivity and replication permissions, which the owner of the source has to grant, and a SaaS vendor doesn’t give those to its customers however strong the case for CDC is. Where you do have that access, Part 7 compares Debezium and the managed tools in detail.

Why load pattern lives in the spec file

Pattern	Reads per run	Detects deletes	Deletion registry	Main cost
Full load	The whole source	Yes, by diffing snapshots	Optional, none with a reset load	Bronze storage, or replay if you use a reset load
Watermark	Changed rows only	Not hard deletes, needs a shadow table	Written from the shadow table	A modified-at column the source maintains
Partition reload	A recent window	No, needs a shadow table	Written from the shadow table	Storage per window, and changes older than the window are missed
CDC	The database’s change log	Yes, with the change time	Written from delete events, with deleted_at	Log access most sources don’t grant

Which of the four fits depends on what a source supports and which of these costs you can live with for that source. That’s decided once, when someone writes the source’s spec file, which is why load_pattern is a field there.

None of these failures produces an error. A hard delete the watermark load misses, a correction just outside the partition window and a partial extraction without a row-count check in front of it all leave the run reporting success while the table holds wrong numbers. The next post covers monitoring, starting from the run record Part 16 described as the pipeline-run half of it.