To fully benefit from this article, familiarity with the basic concepts of historization is assumed.
Managing temporal data is a critical aspect of data warehousing. Previously, we had to develop temporal data management capabilities to store historical data and maintain multiple timelines.
Teradata introduced temporal data management capabilities in release 13.10, in accordance with the TSQL2 specification, which extends the SQL-92 language standard. This feature is common among data warehouse vendors.
Teradata’s temporal feature includes definitions for both valid and transaction time, along with temporal qualifiers, constraints, and comparison operators.
Database vendors use two distinct approaches for managing temporal data.
Teradata utilizes a query rewrite methodology that involves adding historical constraints to the temporal query, effectively transforming it into a non-temporal query.
Teradata utilizes a query rewrite methodology
Query-rewrite and native temporal implementations cannot be ranked without comparable measurements. This article does not establish a performance advantage or negligible rewrite overhead.
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 bookThis is an example of a temporal query demonstrating the applied rewrite step.
CURRENT VALIDTIME
SELECT *
FROM <TABLE>
WHERE <COLUMN1> = 10;
The rewritten query looks like this:
SELECT *
FROM <TABLE>
WHERE BEGIN(MyPeriod) <= DATE '2014-04-01'
AND END(MyPeriod) > DATE '2014-04-01'
AND <COLUMN1> = 10;
Assuming the current time is 2014-04-01.
We can implement single or double historization, whereby double historization involves maintaining a secondary timeline for historization at a higher level.
Single and double historization
Valid Time Temporal Tables
Transaction Time Temporal Tables
Bi-Temporal Tables (containing Valid Time and Transaction Time, i.e., a double historization)
The following bi-temporal DDL is a corrected syntax illustration. Temporal operations were unavailable in the audit environment, so it was not runtime-verified.
CREATE MULTISET TABLE TemporalExample
(PK INTEGER NOT NULL,
MY_VALID_TIME PERIOD(DATE) NOT NULL AS VALIDTIME,
MY_TRANSACTION_TIME PERIOD(TIMESTAMP(6) WITH TIME ZONE) NOT NULL AS TRANSACTIONTIME)
PRIMARY INDEX (PK);
The PERIOD data type consists of two dates or timestamps: {‘2014-01-31′,’9999-12-31’}.
VALIDTIME may be represented by a set of dates or timestamps, while TRANSACTIONTIME must always be a set of timestamps.
Valid time describes when a fact is valid in the modeled world. Transaction time records when database versions are current; it is a separate temporal dimension, not merely another valid-time interval.
VALIDTIME and TRANSACTIONTIME defined
Temporal tables are managed automatically by Teradata.
History behavior depends on valid-time/transaction-time definitions and the temporal DML qualifier. An INSERT does not universally create an additional historical version; understand the operation and session semantics.
An unqualified SELECT follows the applicable temporal session defaults. Specify the intended valid-time and transaction-time qualifiers explicitly instead of assuming it always retrieves the currently valid record.
SELECT * FROM <TABLE>;
To retrieve valid-time history, use NONSEQUENCED VALIDTIME. Transaction-time selection still follows the applicable qualifier or session default; this alone does not retrieve every bitemporal version:
NONSEQUENCED VALIDTIME
SELECT * FROM <TABLE>;
To obtain the accurate record for a specific date and time, an additional initial line is necessary:
VALIDTIME AS OF DATE '2014-04-01'
SELECT * FROM <TABLE>;
To retrieve all historical timelines and their respective rows, use the following method:
NONSEQUENCED VALIDTIME AND NONSEQUENCED TRANSACTIONTIME
SELECT * FROM <TABLE>;
This brief overview of Teradata temporal data management highlights its potential to supplant manual historization techniques.
It would be intriguing to receive insights from experts who work with Teradata’s temporal logic regarding query performance, particularly in comparison to manually crafted historization approaches.
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
Hi
Having worked on data management in the context of time for some years, I have developed an approach that uses standard RDBMS facilities and DB modeling. http://www.youtube.com/watch?v=V1EcsuJxUno is one example of implementation against a requirement that involved multiple entities and the demanding business reporting requirements of an investment bank. ie the temporal challenge can be addressed without special capabilities.
Hi Roland,
we had a session with Roy E. who is in charge of Teradata’s Global Control Framework (GCFR). And he mentioned that temporal tables are not yet supported in their framework. But that is not because it does not reliably work, but because they want to be able to sell GCFR to customers which do not have the Teradata Enterprise license. So not every Teradata system has this feature. It costs extra…
-Timm