In an SCD Type 2 load, what does a source updated_at strategy miss that comparing tracked columns catches?

## Two ways to answer "what changed" Every Type 2 load needs a changed set, and there are two families of answer. **Timestamp strategy**: trust a source-maintained column — `updated_at`, `modified_ts`, a row version — and treat any row whose stamp advanced since the last run as changed. **Check strategy**: compare the current values of a declared list of tracked columns against what the dimension already stores, usually through a hashdiff, and treat a difference as a change. Snapshot-based transformation tools expose exactly this choice, and interviewers ask about it because the failure modes are asymmetric and both are real. ## Where the timestamp strategy fails **Missed changes — the dangerous direction.** The stamp is maintained by the source, so it is only as reliable as the source's discipline: - A data migration or a one-off `UPDATE ... WHERE` issued by an operator often bypasses the application code that sets the column, and some sources deliberately suppress triggers during bulk loads. - Rows written before the column was introduced carry NULL, and `NULL > last_run` is unknown, so those rows are invisible forever. - Deletes never bump a timestamp on a row that is gone. - Clock skew between writers, or a stamp taken at transaction start rather than commit, can place a change just before your watermark; the next run's `>` filter then steps over it. Overlapping the watermark by a safety margin and relying on the load's idempotence to absorb the re-read is the usual mitigation. A missed change is silent. The dimension simply keeps reporting an old attribute value, and nothing in the pipeline errors. **Spurious versions — the noisy direction.** The stamp records that *some* column changed, not that a *tracked* one did. A source that rewrites a `last_seen` or `sync_status` column on every sync bumps `updated_at` for every row, and a timestamp-driven load then versions the entire dimension nightly with identical attributes. You end up paying for history that records nothing. ## Where the check strategy fails It is not free. Comparing values means reading the full current state of the source table on every run — you cannot push a cheap watermark filter down to the extract — so the cost scales with dimension size rather than with change volume. On a large dimension that is the whole argument for the timestamp approach. It is also only as good as its tracked list: a change outside that list is invisible by design. That is a feature (audit columns are excluded on purpose) but it means "the check strategy catches everything" is wrong as stated — it catches everything *you declared*. And because the stored hash encodes the tracked list implicitly, editing the list re-versions the whole dimension on the next run. Finally, it depends on being able to see the row at all. If the source snapshot is filtered, a member's absence is indistinguishable from a member that never changed. ## What both strategies miss Both are **snapshot comparisons at run boundaries**. If a customer's tier goes Silver → Gold → Silver between two nightly runs, the timestamp strategy sees a bumped stamp and writes a version whose attributes equal the previous one; the check strategy sees no difference and writes nothing. Neither records the Gold interval, because neither ever observed it. Increasing run frequency narrows the window but never closes it. Recording every intermediate state requires a source that emits changes rather than states — a change-data-capture stream or an application-maintained change log — replayed in order. That is a source-side decision, and it is worth naming in an interview: if the business genuinely needs every state, no snapshot cadence will deliver it. ## Choosing A workable default: use the check strategy unless the dimension is large enough that a full scan hurts, because it depends on nothing outside your control. Use the timestamp strategy when the source's stamp is genuinely trustworthy and the volume argument is real — and then verify it periodically with a reconciliation run that does a full value comparison and reports any row the timestamp path would have missed. Hybrid forms exist: filter the extract by timestamp for volume, then still compare hashes before writing, so a spurious stamp bump costs a read but not a version.