DWHPro Insights

Teradata Historisation – Daily snapshot to change history

By Roland Wenzlofsky ·

Teradata Historisation – Daily snapshot to change history

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.

Explore independent platform assessments

Practical data engineering insights, regularly

Join more than 3,500 data professionals: a regular newsletter with practical insights on data engineering, new data platforms (Snowflake, Databricks, Teradata) and AI.

Follow DWHPro in Google to see our articles more often in Search.

Leave a Comment

Get more practical Teradata advice

Join the DWHPro newsletter for Teradata query-tuning tips, troubleshooting examples and data warehousing insights.

Protected by Security by CleanTalk and CleanTalk Anti-Spam