I wanted to show you an example of the TEMPORAL feature in Teradata. Its a pretty neat feature.
Basically, it will allow us to keep a history of changes where required. For example, the FULL REFRESH dimensions, will change (based on the customers feedback) to a CDC (change data capture). This is normally a pretty complex process.
With the TEMPORAL feature, coupled with UPSERTS (MERGE), it becomes quite simple.
First - TEMPORAL. Basically Teradata under the covers of a table will manage a "PERIOD" data type - instead of a process having to manage the classic START_DT and END_DT logic. In fact, you dont even need to know about the column.
Also - when a user does a select * from the table, they will only get the CURRENT rows returned.
Please see this example to help.
Example FULL REFRESH Table that will move to this process:
CREATE MULTISET TABLE EDW_STAGE.tempo_test_auto_purchase_pack_size ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT, DEFAULT MERGEBLOCKRATIO ( auto_purchase_pack_size_id INTEGER, duration_mins INTEGER, pack_size INTEGER, display_order INTEGER, is_active CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC, is_default CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC, created_dt TIMESTAMP(6), created_by_user_id VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC, last_update_dt TIMESTAMP(6), last_updated_by_user_id VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC, is_deleted CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC, source_sys_id SMALLINT, company_brand_cd VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC, duration PERIOD(TIMESTAMP(6) WITH TIME ZONE) NOT NULL AS TRANSACTIONTIME ) PRIMARY INDEX ( auto_purchase_pack_size_id );
NOTE the column called duration - it will NOT have values inserted...Teradata manages this.
I will do an initial insert of 3 columns and those look like this when we select them:
select * from tempo_test_auto_purchase_pack_size;
Note The 2nd row (ID of 3) where the column duration_mins is 40.
We issue an update (or merge) to this table:
update tempo_test_auto_purchase_pack_size set duration_mins = 95 where auto_purchase_pack_size_id = 3;
Now look at the select * from tempo_test_auto_purchase_pack_size;
Note that the new value is the LATEST row....BUT if we want to see history:
TRANSACTIONTIME AS OF TIMESTAMP '2015-01-10 11:24:10' select * from tempo_test_auto_purchase_pack_size;
And finally - so see them all:
NONSEQUENCED TRANSACTIONTIME select * from tempo_test_auto_purchase_pack_size;
Note that with this query you see the duration column for this temporal feature.
Hope this helps.