Why are Data Vault satellites insert-only rather than updated in place?

## The rule Every Data Vault satellite is keyed by its parent's hash key plus a `load_date`, and the loading pattern is **insert-only**: no `UPDATE`, no `DELETE`. When a descriptive attribute changes, the load inserts a new row with a later load date and leaves every earlier row physically untouched. ``` Load 2026-01-04 -> insert (a3f1..., 2026-01-04, tier=SILVER, name=Acme Ltd) Load 2026-05-12 -> tier changed to GOLD -> insert (a3f1..., 2026-05-12, tier=GOLD, ...) Load 2026-05-13 -> attributes identical to latest row -> insert nothing ``` ## Why insert-only **Audit is the product.** A Data Vault integration layer exists to be the defensible record of what arrived from each source and when. Regulators, restatements and "the number changed, why" investigations all need the *previous* value, not just the current one. An in-place update destroys exactly the evidence the layer was built to retain. Because each satellite is scoped to one source system, the vault can also say which system asserted which value — two sources disagreeing about a customer's tier produce two satellites, not a lost update. **Loads stay simple, parallel and restartable.** An insert-only load is a single append. There is no merge to reason about, no row-level contention between concurrent loads, and no half-applied state to unwind if a run dies mid-way. Satellites of the same hub, fed by different sources, can load simultaneously without coordinating. This matters enormously in a warehouse fed by dozens of upstream systems on different schedules. **Reprocessing is safe.** The load's only decision is whether the incoming attribute set differs from the latest stored row for that key. That is what the `hashdiff` column is for: hash the concatenation of the descriptive columns, compare it to the hashdiff of the current latest row, and insert only when it differs. Comparing one hash instead of forty columns is both faster and immune to the classic bug of forgetting a column in a long comparison chain — but you must normalize nulls and formatting identically on both sides, or unchanged rows will appear changed and the satellite will bloat with duplicates. ## Reading it back The price of insert-only is that "current" is derived rather than stored. The portable way to get it is to rank rows per parent key by load date: ```sql SELECT customer_hk, name, tier, load_date FROM ( SELECT s.*, ROW_NUMBER() OVER ( PARTITION BY customer_hk ORDER BY load_date DESC) AS rn FROM sat_customer_crm s ) ranked WHERE rn = 1; ``` An as-of read swaps the filter for `load_date <= :as_of` inside the subquery. Doing this across five satellites in one query is the reason point-in-time tables exist in the business vault. ## The end-dating debate Many implementations add a `load_end_date` column so each row explicitly states when it stopped being current, which turns an as-of read into a simple range predicate. This is a real and common optimization, and it is also a real compromise: maintaining it requires an `UPDATE` to the previously-current row after each insert, which reintroduces the very thing insert-only was avoiding — a mutating load, contention, and a partially-applied state if the job fails between the insert and the update. The later Data Vault guidance therefore prefers leaving end dates out of the satellite and deriving them at query time or materializing them in a disposable, rebuildable structure. Either choice is defensible; what is not defensible is claiming the satellite is insert-only while a post-load update is quietly rewriting rows. ## Deletes and the missing-row problem Sources delete records, and an insert-only satellite has nowhere to put that fact. The standard answer is a **status satellite**: when a full extract no longer contains a key that it contained before, insert a row recording a deleted status with the current load date. The record was deleted upstream, and the vault says so explicitly, on a new row, without erasing anything. The same discipline applies to hard-deleted rows discovered by comparison — the deletion is recorded as an event, never enacted as one. ## How this relates to dimension history A satellite's behaviour looks like tracked dimension history — a new row per change, old rows retained — but the intent differs. Dimension history is a *modelling* decision about which attributes deserve history for reporting. A satellite retains everything unconditionally, because the vault is a source-of-record layer, and the decision about what reporting should see is deferred to the marts built on top of it.