For various reasons, we may need to switch our Teradata historisation from snapshot tables to a start date/end date logic table, which I usually call a change history, as only changes trigger a historisation.
A principal motivation for such a move away from a daily snapshot table could be space restrictions.
The former full conversion template has been withdrawn because it was incomplete and its MIN/MAX comparison missed transitions to or from NULL. RSD and RED denote validity start and end dates. A complete conversion must define duplicate-date resolution, business keys, gaps and final end dates before it can be used.
For each business key, compare the current value with the previous value explicitly: equal values or two NULLs mean unchanged; one NULL and one non-NULL mean changed. Handle the first row and date gaps separately. The audited predicate returned change flags 1,1,1,0,2 across a NULL/A/NULL sequence with a gap, but it does not implement the full interval-construction algorithm.
Deciding what to change in your data platform?
Compare optimisation, modernisation and migration against your workload and business requirements.
