Teradata - Merging two change history tables
Have you encountered a poorly designed physical data model where object columns are distributed randomly across tables, and you wish to unify them in their rightful place?
Merging non-historical tables is simple, but the process becomes more complex when historization is applied to at least one of the tables.
Change history tables are typically complex. However, you need not worry; we have a solution to simplify your task. We will guide you through all the steps using a small example.
Want more practical data engineering analysis like this?
Join DWHPro Letters and get field-tested notes on Teradata, Snowflake, AI, migrations, performance, and enterprise data work. DWHPro Letters is free. Subscribe to get new issues by email.
CREATE MULTISET VOLATILE TABLE <JOIN_TYPE_LOOKUP> (JOIN_TYPE CHAR(4)) ON COMMIT PRESERVE ROWS;INSERT INTO <JOIN_TYPE_LOOKUP> VALUES ('before');INSERT INTO <JOIN_TYPE_LOOKUP> VALUES ('between');INSERT INTO <JOIN_TYPE_LOOKUP> VALUES ('after');-- Two historized test tables
Get the next issue by email.
CREATE MULTISET VOLATILE TABLE <TABLE_A> (ID_A INTEGER,RSD DATE,RED DATE) ON COMMIT PRESERVE ROWS;CREATE MULTISET VOLATILE TABLE <TABLE_B> (ID_B INTEGER,RSD DATE,RED DATE) ON COMMIT PRESERVE ROWS;INSERT INTO <TABLE_A> VALUES (1, '2014-01-01', '2014-01-31');
INSERT INTO <TABLE_B> VALUES (1, '2014-01-31', '9999-12-31');
SELECT--------- Block for columns from table A ---------CASEWHEN JOIN_TYPE = 'before' AND a.RSD < b.RSD THEN ID_AWHEN JOIN_TYPE = 'before' AND b.RSD < a.RSD THEN NULLWHEN JOIN_TYPE = 'between' THEN ID_AWHEN JOIN_TYPE = 'after' AND a.RED > b.RED THEN ID_AWHEN JOIN_TYPE = 'after' AND b.RED > a.RED THEN NULLEND AS ID_A,--------- Block for columns from table B ---------CASEWHEN JOIN_TYPE = 'before' AND a.RSD < b.RSD THEN NULLWHEN JOIN_TYPE = 'before' AND b.RSD < a.RSD THEN ID_BWHEN JOIN_TYPE = 'between' THEN ID_BWHEN JOIN_TYPE = 'after' AND a.RED > b.RED THEN NULLWHEN JOIN_TYPE = 'after' AND b.RED > a.RED THEN ID_BEND AS ID_B,-------- Identify new RSD/RED values --------CASEWHEN JOIN_TYPE = 'before' AND a.RSD < b.RSD THEN a.RSDWHEN JOIN_TYPE = 'before' AND b.RSD < a.RSD THEN b.RSDWHEN JOIN_TYPE = 'between' AND a.RSD < b.RSD THEN b.RSDWHEN JOIN_TYPE = 'between' AND b.RSD <= a.RSD THEN a.RSDWHEN JOIN_TYPE = 'after' AND a.RED < b.RED THEN a.RED + 1WHEN JOIN_TYPE = 'after' AND b.RED < a.RED THEN b.RED + 1WHEN JOIN_TYPE = 'between' THEN (coalesce(a.RSD, b.RSD))END AS new_RSD,CASEWHEN JOIN_TYPE = 'before' AND a.RSD < b.RSD THEN b.RSD - 1WHEN JOIN_TYPE = 'before' AND b.RSD < a.RSD THEN a.RSD - 1WHEN JOIN_TYPE = 'between' AND a.RED < b.RED THEN a.REDWHEN JOIN_TYPE = 'between' AND b.RED <= a.RED THEN b.REDWHEN JOIN_TYPE = 'after' AND a.RED < b.RED THEN b.REDWHEN JOIN_TYPE = 'after' AND b.RED < a.RED THEN a.REDWHEN JOIN_TYPE = 'between' THEN (coalesce(a.RED, b.RED))END AS new_RED---------------------------------------------------from <TABLE_A> aFULL OUTER JOIN<TABLE_B> bON (a.RSD, a.RED+1) OVERLAPS (b.RSD, b.RED+1)-- further join conditionsCROSS JOIN <JOIN_TYPE_LOOKUP> hWHEREnew_RSD IS NOT NULLAND new_RED IS NOT NULL;Please note the language used in this text:
I assume that you are familiar with the concept of volatile tables and the basic concepts in historisation. RSD and RED stand for the record start and end dates.
Planning or surviving an enterprise data platform migration?
I write regularly about the performance, cost, architecture, and project mistakes that show up in real Teradata, Snowflake, Databricks, and enterprise data work.
Subscribe for free and keep launch access.
Written by Roland Wenzlofsky, founder of DWHPro and author of Teradata Query Performance Tuning. DWHPro has helped data warehouse practitioners for 15+ years.