Teradata Historisation – Daily snapshot to change history

0
657

 

For a variety of reasons, we may have the 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 are triggering a historisation.

A principal motivation for such a move away from a daily snapshot table could be space restrictions.

Whatever the reason may be, changing the historisation type is easier than one may expect. Here is a SQL template which handles exactly this task. The columns named RSD and RED are the technical columns defining the validity start date and end date of each record.

[quote]

SELECT
<KEY_COLUMNS>,
<NON_KEY_COLUMNS>,
RSD,
CASE WHEN
MAX(CHANGED_IND) OVER (PARTITION BY <KEY_COLUMNS> ORDER BY RSD ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING ) = 2
THEN
MAX(RED_PREV) OVER ( PARTITION BY <KEY_COLUMNS>, ORDER BY RSD ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING )
ELSE
COALESCE(((MAX(RSD) OVER ( PARTITION BY KEY_COLUMNS> ORDER BY RSD ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING ))-1), ENDI)
END AS RED
FROM
(
SELECT
<KEY_COLUMNS>,<NON_KEY_COLUMNS>,SNAPSHOT_DATE AS RSD,SNAPSHOT_DATE AS RED,
MAX(RED) OVER (PARTITION BY <KEY_COLUMNS> ORDER BY RSD ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS RED_PREV,

CASE
WHEN
(RED_PREV+1) <> RSD THEN 2
WHEN
(MIN() OVER (PARTITION BY <KEY_COLUMNS> ORDER BY RSD ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)=
MAX() OVER (PARTITION BY <KEY_COLUMNS> ORDER BY RSD ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) OR
(MIN() OVER (PARTITION BY <KEY_COLUMNS> ORDER BY RSD ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) IS NULL AND
MIN() OVER (PARTITION BY <KEY_COLUMNS> ORDER BY RSD ROWS BETWEEN CURRENT ROW AND CURRENT ROW) IS NULL))

AND

AND

(MIN() OVER (PARTITION BY <KEY_COLUMNS> ORDER BY RSD ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)=
MAX( <ATTRIBUTEn) OVER (PARTITION BY ORDER BY RSD ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) OR
(MIN() OVER (PARTITION BY <KEY_COLUMNS> ORDER BY RSD ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) IS NULL AND
MIN( <ATTRIBUTEn) OVER (PARTITION BY ORDER BY RSD ROWS BETWEEN CURRENT ROW AND CURRENT ROW) IS NULL))

THEN 0 ELSE 1
END AS CHANGED_IND,

MAX(RED) OVER ( PARTITION BY <KEY_COLUMNS> ORDER BY RSD ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS RED_NEXT,
MAX(RED) OVER ( PARTITION BY <KEY_COLUMNS> AS ENDI
FROM
<SNAPHOT_TABLE> i
QUALIFY (CHANGED_IND=0 and RED_PREV IS NULL) OR (CHANGED_IND <> 0)
) i;

[/quote]

 

Our Reader Score
[Total: 1    Average: 5/5]
Teradata Historisation – Daily snapshot to change history written by Roland Wenzlofsky on March 19, 2014 average rating 5/5 - 1 user ratings

LEAVE A REPLY

Please enter your comment!
Please enter your name here