Building a CDF History Table That Outlives Your VACUUM Window
- Jul 1
- 6 min read
A CDF history table pattern for permanent change capture — and why it's a different problem to SCD Type 2, not a smaller version of it.
Introduction
The first failure pattern I wrote about in my article on Change Data Feed in production was VACUUM removing change history before a lagging pipeline had processed it. The recovery is a full snapshot fallback. The prevention is keeping your processing lag inside the retention window.
Both of those assume you're treating CDF as a transient feed — something you read once, apply, and move past. For a lot of pipelines that's the right model. But there's a category of table where that assumption is wrong from the start: anything where you need to answer "what exactly happened to this record, and when, forever" rather than "what does this record look like right now." Financial transactions. Patient records. Anything under a regulatory retention obligation. Anything where a future auditor, lawyer, or incident responder is going to ask you a question about a row's history that your current production table cannot answer because the relevant version was already overwritten, or the relevant CDF files were already vacuumed.
The fix for that category of table is a CDF history table — a permanent, append-only Delta table that captures every change event CDF produces, independent of the source table's VACUUM schedule. Once a change lands in the history table, it is yours forever, regardless of what retention policy governs the source.
This is a different pattern from SCD Type 2, which I wrote about separately, and the difference is worth being precise about before getting into the implementation.
CDF History Table vs SCD Type 2 — Two Different Questions
SCD Type 2 answers: what was this entity's attribute state during this time window? It is built for point-in-time joins — "show me this customer's segment as it was on the date of this transaction." Each row represents a version of the entity's state, bounded by __START_AT and __END_AT. Critically, SCD2 only creates a new row when a tracked attribute actually changes. A no-op update — a write that touches an excluded column, or re-writes the same value — produces no new SCD2 row at all, because nothing relevant to the dimension actually changed.
A CDF history table answers a different question: show me every change event that occurred, exactly as it happened, in order. This includes no-op updates if you choose to capture them. It includes the full pre-image and post-image of every update, not just the consolidated "current" state. It includes every individual commit, even ones that produced functionally identical values, because the existence of the write itself may be the thing you need to prove later — that a record was touched at a specific time, by a specific process, regardless of whether the value changed.
You build a CDF history table when the audit requirement is about the event, not just the state. In practice, most regulated environments need both: an SCD2 dimension for reporting and joins, and a CDF history table sitting underneath it as the permanent, unfiltered record of what actually happened on the source.
The Pattern: Append-Only Capture from CDF
The implementation is deliberately simple. Complexity here is a liability, not a feature — a history table is infrastructure you want to trust completely, not infrastructure you want to be clever with.
Two things in this pipeline definition are doing real work, and both are easy to skip past.
"delta.appendOnly": "true" is a table property, not a suggestion. It tells Delta to reject any UPDATE or DELETE statement against this table at the engine level — not just "we don't intend to update this," but "Delta will throw an error if anyone tries." For a table whose entire value proposition is "this is the permanent, untouched record," that protection should not depend on every future engineer remembering not to write a MERGE against it.
startingVersion=0 (or your true source baseline) matters because a history table built from a later starting point has a gap in it from day one — and that gap is invisible until someone asks a question about a date before the table existed. If you are enabling CDF on an existing table to start this pattern, capture a full snapshot baseline at the same time, exactly as you would for any CDF consumer, and document that baseline date as the table's known starting point.
What the Tutorial Doesn't Cover
Pre-image and post-image both need to survive, unflattened
The single most common mistake I see when people adapt an SCD2-style pipeline into a history table is filtering the CDF stream down to insert and update_postimage only — which is the right filter for SCD2, and the wrong filter for an audit table. For SCD2 you want the current state after each change. For an audit table you want both the before and after state of every update, because "what was it before" is frequently the exact question an auditor or incident responder is asking.
If storage cost is a genuine constraint and you want to filter something, filter at the table or column level — not by dropping pre-images, which removes information you cannot reconstruct later.
Schema evolution behaves differently here than in SCD2
In an SCD2 target, a new source column is a deliberate decision about whether it's a tracked attribute. In a history table, the default posture should be the opposite: capture every column the source has, including new ones, because the entire point is fidelity to what the source actually looked like at each point in time.
mergeSchema=true here means new source columns are added to the history table automatically, with historical rows showing NULL for any column that didn't exist yet at the time they were captured. That NULL is correct and meaningful — it is not missing data, it is an accurate record that the column did not exist when that row was captured. Resist the urge to backfill it with a default value. The gap is the truth.
Unbounded growth is the trade-off, not an edge case
An SCD2 dimension is bounded by entity cardinality — you have roughly as many rows as you have entities times the number of times each one's tracked attributes changed. A CDF history table has no equivalent ceiling. It grows by one row for every single write the source table ever processes, forever. For a high-write operational table, that is a meaningfully different storage and query profile, and it needs to be a deliberate decision rather than something discovered eighteen months in when the table is the largest thing in the lakehouse.
Two things make this manageable in practice. Partition the history table by a derived date column from committimestamp — daily or monthly, depending on write volume — so that time-bounded audit queries (which are the overwhelming majority of how these tables actually get queried) only scan the relevant partitions.
And be honest about retention at the architecture review stage, not after the table is in production. "Capture everything forever" is the right call for regulated, audit-critical tables. It is the wrong default to apply blanket-style to every table in a lakehouse — the storage cost compounds, and most tables do not carry an audit obligation that justifies it. Decide table by table, and write the decision down.
The same checkpoint and idempotency discipline from the original CDF article still applies
Because this is built as a Structured Streaming consumer of CDF, it inherits every operational discipline from the patterns I wrote about previously: checkpoints belong in a Unity Catalog Volume, never /tmp/. If the checkpoint is ever lost, recovery means querying the history table itself for the highest commitversion already captured and resuming from there — which a well-built history table makes easier than almost any other consumer, because the entire point of the table is that it already contains a perfect, ordered record of everything processed so far.
What This Looks Like as a Production Pattern
A CDF history table earns its keep specifically on tables where the cost of not being able to answer "what happened, exactly, and when" exceeds the storage and engineering cost of capturing everything. That is usually a short, deliberate list — financial ledgers, anything under a data retention regulation, identity and access change logs, clinical records — not a default applied to every table a CDF pipeline touches.
Where it earns that keep, the shape is consistent: append-only enforced at the table level, no filtering on changetype, schema evolution that adds rather than reconciles, partitioning by commit date, and the same checkpoint discipline as any other CDF consumer. Built once, correctly, it is one of the few patterns in a data platform that genuinely does not need to be revisited — because revisiting it is exactly the kind of write the table exists to prevent.
If this was useful
The full pipeline, the schema evolution variant, and the partitioning DDL above are available on GitHub, alongside a runnable notebook that seeds sample CDC data and walks through the difference between this pattern and SCD2 directly: https://github.com/keithjenneke/databricks-engineering-patterns
This article was written by Keith Jenneke, Principal Consultant & Practice Lead at Cypher Agency. Keith leads our data platform and AI engineering practice, building on Azure Databricks across professional services, resources, and government.
Comments