Comparison
SCD type 2vsTemporal join
SCD type 2
the change closes the old dimension row with an end date and opens a new one, so the customer now has three rows and only one is current.
Insert a new row for the new version and mark the old one as no longer current, usually with valid-from and valid-to dates and a current flag. It preserves history exactly, which is what makes point-in-time reporting possible, and it costs you every query that forgot to filter to the current row and therefore counted the customer three times. It is the default choice for anything a regulator or a finance team will look at.
Full entry →Temporal join
you priced the order using the exchange rate as it was at the moment of the order, not the rate as it is now.
Joining a stream of events to the version of a reference table that was current at each event's own event time. It is what stops a re-run producing different numbers, because the enrichment stops depending on when you ran it. Building it requires the reference data to be versioned rather than overwritten, which is the practical reason slowly changing dimensions and streaming enrichment turn out to be the same problem.
Full entry →