fbpx

 

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]

 

__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"var(--tcb-skin-color-0)"}},"gradients":[]},"original":{"colors":{"62516":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Previous Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Next Article
Buy the Book Teradata Query Performance Tuning

Roland Wenzlofsky

Roland Wenzlofsky is an experienced freelance Teradata Consultant & Performance Trainer. Born in Austria's capital Vienna, he is building and tuning some of the largest Teradata Data Warehouses in the European financial and telecommunication sectors for more than 20 years. He has played all the roles of developer, designer, business analyst, and project manager. Therefore, he knows like no other the problems and obstacles that make many data warehouse projects fail and all the tricks and tips that will help you succeed.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

You might also like

>