DWHPro Insights

Teradata – Merging two change history tables

By Roland Wenzlofsky ·

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.

The fragments below illustrate interval overlap; they are not a complete general history-merging solution. The key equality assumes ID_A and ID_B identify the same business entity. Define gaps, NULL dates, unmatched rows and overlapping records within each source before using this design. Inclusive-date overlap avoids adding one day to the maximum date 9999-12-31.

CREATE MULTISET VOLATILE TABLE <JOIN_TYPE_LOOKUP> (
JOIN_TYPE VARCHAR(7)
) 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

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book
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 ---------
CASE
WHEN JOIN_TYPE = 'before' AND a.RSD < b.RSD THEN ID_A
WHEN JOIN_TYPE = 'before' AND b.RSD < a.RSD THEN NULL
WHEN JOIN_TYPE = 'between' THEN ID_A
WHEN JOIN_TYPE = 'after' AND a.RED > b.RED THEN ID_A
WHEN JOIN_TYPE = 'after' AND b.RED > a.RED THEN NULL
END AS ID_A,
--------- Block for columns from table B ---------
CASE
WHEN JOIN_TYPE = 'before' AND a.RSD < b.RSD THEN NULL
WHEN JOIN_TYPE = 'before' AND b.RSD < a.RSD THEN ID_B
WHEN JOIN_TYPE = 'between' THEN ID_B
WHEN JOIN_TYPE = 'after' AND a.RED > b.RED THEN NULL
WHEN JOIN_TYPE = 'after' AND b.RED > a.RED THEN ID_B
END AS ID_B,
-------- Identify new RSD/RED values --------
CASE
WHEN JOIN_TYPE = 'before' AND a.RSD < b.RSD THEN a.RSD
WHEN JOIN_TYPE = 'before' AND b.RSD < a.RSD THEN b.RSD
WHEN JOIN_TYPE = 'between' AND a.RSD < b.RSD THEN b.RSD
WHEN JOIN_TYPE = 'between' AND b.RSD <= a.RSD THEN a.RSD
WHEN JOIN_TYPE = 'after' AND a.RED < b.RED THEN a.RED + 1
WHEN JOIN_TYPE = 'after' AND b.RED < a.RED THEN b.RED + 1
WHEN JOIN_TYPE = 'between' THEN (coalesce(a.RSD, b.RSD))
END AS new_RSD,
CASE
WHEN JOIN_TYPE = 'before' AND a.RSD < b.RSD THEN b.RSD - 1
WHEN JOIN_TYPE = 'before' AND b.RSD < a.RSD THEN a.RSD - 1
WHEN JOIN_TYPE = 'between' AND a.RED < b.RED THEN a.RED
WHEN JOIN_TYPE = 'between' AND b.RED <= a.RED THEN b.RED
WHEN JOIN_TYPE = 'after' AND a.RED < b.RED THEN b.RED
WHEN JOIN_TYPE = 'after' AND b.RED < a.RED THEN a.RED
WHEN JOIN_TYPE = 'between' THEN (coalesce(a.RED, b.RED))
END AS new_RED
---------------------------------------------------
from <TABLE_A> a
FULL OUTER JOIN
<TABLE_B> b
ON a.ID_A = b.ID_B AND a.RSD <= b.RED AND b.RSD <= a.RED
-- further join conditions
CROSS JOIN <JOIN_TYPE_LOOKUP> h
WHERE
new_RSD IS NOT NULL
AND 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.

Deciding what to change in your data platform?

Compare optimisation, modernisation and migration against your workload and business requirements.

Explore independent platform assessments

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

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

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam