The Other Hash in Your Data Vault
How A Generated Hashdiff Keeps Unchanged Rows Out Of Your Satellites
July 24, 2026
Every Data Vault satellite load answers the same question, over and over: is this row new information, or the same information showing up again? Get it wrong in one direction and the satellite fills with copies of rows that never changed, bloating storage and every downstream point-in-time query. Get it wrong in the other direction and you silently drop history, which is worse, because nobody notices until an auditor asks what the record looked like in March.
We have written before about keeping Data Vault hash keys deterministic across platforms. That post was about identity: the hash that says which hub or link a row belongs to. This one is about the other hash. The hashdiff decides whether a row is worth keeping, and in a BimlFlex-generated load it is not just a column, it is the entire change detection mechanism. One setting extends it from "compare against the satellite" to "collapse the whole batch down to genuine changes first."
Two hashes, two jobs
The hash key answers "which customer is this?" The hashdiff answers "did anything about this customer change?" In a generated satellite the hashdiff lands as its own column, FlexRowHashSat by default. It is one of the standard metadata configurations, so you can rename it or change its data type in your model, but the job stays fixed: one value that fingerprints the satellite's descriptive attributes for that row. The generated load computes it from the payload columns before any comparison happens, and from that point on, change detection never reads those columns again.
That is the part most hand-written loads get wrong. If your change check reads s.name <> t.name OR s.segment <> t.segment OR ..., the check is a second copy of your column list, with all the NULL-handling traps an OR chain invites. Add an attribute to the satellite and forget to add it to the chain, and changes to that attribute stop being recorded. Silently. A hashdiff moves the column list into one place, the hash expression generated from the model, and turns the comparison into a single equality test. Regenerate the model and the expression, the column, and the comparison all move together.
One comparison per key
The generated satellite load does not scan history. It first builds a working set of the satellite's current rows: each key's latest version, carried with its record source and its hashdiff. Every staged row then gets matched against that current row on exactly three things:
INNER JOIN #SAT_Customer_Current CUR
ON STG.[Customer_SK] = CUR.[Customer_SK]
AND STG.[FlexRowRecordSource] = CUR.[FlexRowRecordSource]
AND STG.[FlexRowHashSat] = CUR.[FlexRowHashSat]
(Key and table names here come from your model; the pattern is what BimlFlex generates.)
A staged row that matches is already recorded: same key, same fingerprint, nothing to do. A staged row that does not match becomes a new version. The load is insert-only, so the satellite stays append-only and the effective-from date carries the timeline. No updates, no column-by-column diff, one hash equality per key.
If every batch carried exactly one row per key, you could stop reading here. Batches do not cooperate.
The batch problem
Real loads deliver several rows per key at once. A catch-up run replays three days of extracts. A CDC feed emits multiple change events for the same key between loads. A full-extract source re-sends every record whether it changed or not. Compare each of those staged rows only against the satellite's current row and the answer comes back wrong: the second staged row for a key is checked against a "current" row that the first staged row has already superseded, so exact duplicates inside the batch sail through as new versions.
This is what delta collapsing is for. DvDeltaCollapseRows is a boolean setting scoped to the individual object, described in the product as "Collapse duplicate rows during Data Vault delta processing." Turn it on and the generated load stops trusting the batch. It orders every staged row onto the key's timeline and compares each row against its neighbors before anything touches the satellite. There is also a small pre-pass for the degenerate case: rows that arrive with the same key and the same effective-from timestamp are reduced to one with a row-number filter, so exact same-instant duplicates never even reach the timeline.
The rows that survive
With delta collapsing on, the staging step of the generated load wraps the batch in window functions over the same hashdiff column. Each row learns about its neighbors on the key's timeline:
,LAG(SRC.[FlexRowEffectiveFromDate]) OVER (
PARTITION BY SRC.[Customer_SK], SRC.[FlexRowHashSat]
ORDER BY SRC.[FlexRowEffectiveFromDate]) AS [LAG_DATE_DIFF]
,COALESCE(LAG(SRC.[FlexRowHashSat]) OVER (
PARTITION BY SRC.[Customer_SK]
ORDER BY SRC.[FlexRowEffectiveFromDate]),
SRC.[FlexRowHashSat]) AS [LAG_HASH_DIFF]
,COALESCE(LEAD(SRC.[FlexRowHashSat]) OVER (
PARTITION BY SRC.[Customer_SK]
ORDER BY SRC.[FlexRowEffectiveFromDate]),
SRC.[FlexRowHashSat]) AS [LEAD_HASH_DIFF]
Notice the two different partitions. The date comparison partitions by key and hashdiff, so it can tell whether this exact value has appeared before for this key. The hash comparisons partition by key alone, so they see the immediately preceding and following values whatever those values are. For multi-active satellites the partitions carry the multi-active keys too, so each active set gets its own timeline.
Then the filter decides who lives:
WHERE [LAG_DATE_DIFF] IS NULL
OR ([FlexRowHashSat] <> [LAG_HASH_DIFF] AND [LAG_DATE_DIFF] IS NOT NULL)
OR ([FlexRowHashSat] <> [LAG_HASH_DIFF] AND [LAG_HASH_DIFF] = [LEAD_HASH_DIFF])
Walk a batch through it. One customer key arrives with four rows, hash sequence A, A, B, A:
- The first A survives: first time this value appears for the key.
- The second A dies: its hash matches the row before it, so it adds nothing.
- B survives: it differs from the row before it. A real change.
- The final A survives too, and this is the case that separates a correct collapse from a naive one. Its value was seen earlier, but it differs from the B immediately before it, so it is a genuine change back. It inserts as a new version with its own effective-from date.
That last row matters. A dedup written as "have I seen this hash before?" would drop the returning A and erase a real state transition from history. The generated filter compares against neighbors, not against everything, which is precisely what lets a value flip back to a previous state and still count as change. The final branch of the WHERE covers the tightest edge, a value that changes and changes straight back inside one batch, so the middle row of an A-B-A sandwich is preserved as its own version rather than smoothed away.
Four rows in, three versions out, and the version count now reflects reality instead of extract frequency.
Same rules on every engine
None of this logic is exotic. LAG, LEAD, and ROW_NUMBER are standard SQL, which is why the generated pattern holds whether your satellite loads run as stored procedures on SQL Server or Synapse, as parallel Snowflake loads, or on Databricks. The change semantics come from the model, not from the engine, so a satellite that collapses deltas one way on SQL Server collapses them the same way after a platform move.
The initial load gets the same treatment. The generated procedure checks whether the satellite is empty and, if it is, takes a shorter path that skips the current-row comparison, there being nothing to compare against, but still runs the collapse over the incoming batch. Your very first bulk load, often the one carrying years of accumulated full extracts, arrives already reduced to genuine changes instead of fossilizing every duplicate the source ever emitted.
Change detection you can read
The uncomfortable truth about satellite change detection is that it fails quietly. A missed attribute in a compare list does not throw an error; it just stops recording history for that attribute. Duplicate versions do not throw errors either; they just make every point-in-time query a little slower and every storage bill a little higher, load after load. Neither failure announces itself, which is why this is a bad place for hand-maintained code.
The generated approach closes both gaps with the same move: derive the hash expression, the comparison, and the collapse filter from one model, and regenerate all three together when the model changes. And because BimlFlex emits the load as code you own, you can open the satellite's load procedure and read the exact WHERE clause shown above, with your column names in it. If you are weighing how far automation should reach into your vault, that is the standard worth holding any tool to, ours included: not "trust the black box," but "read what it wrote." Our guide to Data Vault automation covers the rest of the pattern; the hashdiff is where it earns its keep, one collapsed row at a time.
