Teradata Historisation Part 5/5: Mixed Continuation Cases

While the cases in the final part of our Teradata historisation series do not mean any change in the watermark historisation level of one side of the merge and are as such a mere net continuation, they also include an upleveling on the other side and are therefore neither a conceptually straightforward merge-and-continue on a particular level nor a complete level shift. Therefore let us treat these cases as a class of its own. They are three such constellations: (0-1-1, 0-2-2, 1-2-2).

 

Case 0-1->1 This case is less straightforward than it might seem at a first glance. While we have a historised table B1, we have to make an assumption about how to interpret the data in Table A1. First, they could be seen as stable history and therefore ready to be merged with all historic rows per primary key in table B1. Second, they could be seen as just the most recent state per primary key. This means that the history of the Table A1 part of the entity is unknown and to be left NULL for any time prior to now. Therefore, we join A1 to the most recent entry in Table B1 and store this time series in the target table. Furthermore, we have to decide on how to initialize the history of those entities of A that have no record in B. We treat them as if the appeared at the date of the transformation. Any other date must be derived from information outside of what the tables alone provide and requires a business understanding or knowledge of the history and development of the data warehouse at hand.

You can see the difference in both versions discussed when you look at the SQL of both:

— CASE 0-1-> 1, FIRST Interpretation: A Table shows alltime valid history

SELECT

COALESCE(B.ENT_ID, A.ENT_ID) AS ENT_ID, A. C_STATUS ,A.D_LEVEL_CD , A.E_VALUE, B.F_STATUS, B.G_LEVEL_CD, B.H_VALUE, COALESCE(B.RSD,CURRENT_DATE), COALESCE(B.RED, DATE’3000-12-31′)

FROM

TABLE_B1 B

FULL OUTER JOIN

TABLE_A0 A

ON B.ENT_ID = A.ENT_ID

;

— CASE 0-1-> 1, SECOND Interpretation: A Table shows most recent history only

SELECT

COALESCE(B.ENT_ID, A.ENT_ID) AS ENT_ID, A. C_STATUS ,A.D_LEVEL_CD , A.E_VALUE, B.F_STATUS, B.G_LEVEL_CD, B.H_VALUE, COALESCE(B.RSD,CURRENT_DATE), COALESCE(B.RED, DATE’3000-12-31′)

FROM

TABLE_B1 B

FULL OUTER JOIN

TABLE_A0 A

ON B.ENT_ID = A.ENT_ID

AND B.RED>DATE

;

Case 0-2->2 We treat this case as an extension to the solution of Case 0-1->2. Making the appropriate assumption about what the unhistorised table A holds – a steady all-time list or most recent data only – we adhere to the validity of table B in the result. In case there is no B history for an entity, we state the presence and openness validity as default.

— CASE 0-2> 2, based on Interpretation that A Table shows alltime valid history AND it fits in to the validity of table B

SELECT

COALESCE(B.ENT_ID, A.ENT_ID) AS ENT_ID, A. C_STATUS ,A.D_LEVEL_CD , A.E_VALUE, B.F_STATUS, B.G_LEVEL_CD, B.H_VALUE, COALESCE(B.RSD,CURRENT_DATE), COALESCE(B.RED, DATE’3000-12-31′),

COALESCE(B.VSD, CAST(CURRENT_TIMESTAMP AS TIMESTAMP(6) ) ) AS VSD, COALESCE(B.VED, TIMESTAMP ‘3000-12-31 23:59:59.000000’ ) AS VED

FROM

TABLE_B2 B

FULL OUTER JOIN

TABLE_A0 A

ON B.ENT_ID = A.ENT_ID

;

Case 1-2->2 There is a conceptual problem in this case we have to solve first. How can we preserve any input validity if the task involves aligning two not necessarily congruent RSD and RED histories? Our answer is that we cannot do so in a meaningful way. Therefore, we reduce this case to the solution of Case 1-1->1 and add a present and openness validity layer on top of it. This is equal to Case 1-1->2.

Paul Timar
 

Paul Timar is a graduated Sociologist and Economist who turned Data Warehouse Specialist. He has more than 7 years of experience in working with Oracle and Teradata Data Warehouses in the fields of Social Security Data and Banking. His latest focus is on performance optimization.

>