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
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 bookCREATE 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.
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