teradata temporal
It is assumed that you are familiar with the basic concepts of historization to get the most out of this article.

Temporal data management is a crucial task in data warehousing. Just a few years ago we had to write our temporal data management functionality to historize our data and to keep different timelines of history.

Starting with release 13.10, Teradata, like many other data warehouse vendors, added the functionality of temporal data management, based on the TSQL2 specification, which is a temporal extension to the SQL-92 language standard.

The Teradata temporal feature incorporates temporal table definitions (valid time and transaction time), temporal qualifiers, temporal constraints and comparison operators.

There are two different strategies for temporal data management implemented by database vendors.

The plan chosen by Teradata is a query rewrite approach: The temporal query is rewritten into a non-temporal query, by adding the historical constraints to the non-temporal query.

Other database vendors have chosen to build temporal features directly into the RDBMS without the indirect way mentioned above, which can result in better performance. Still, the rewrite approach has no impact on the way a query is executed, and almost no impact on the design of the Teradata Optimizer. Hence, it was the easier approach to introducing temporal data management into an existing RDBMS.

Here is an example of a temporal query, showing 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;

In the above example it’s assumed, that the current validtime equals 2014-04-01.

We can implement single and double historizations; double historization means keeping a separate, second-level historization timeline:

Valid Time Temporal Tables
Transaction Time Temporal Tables
Bi-Temporal Tables (containing Valid Time and Transaction time, i.e. a double historization)

Here is one example for creating a temporal table (see below a bi-temporal table):

CREATEMULTISET TABLE
(
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 data type PERIOD is a pair of dates or timestamps: {‘2014-01-31′,’9999-12-31’}

While VALIDTIME can be a pair of dates or timestamps, TRANSACTIONTIME always has to be a pair of timestamps.

Usually, VALIDTIME would be used to define the validity timeframe of each record belonging to an object and TRANSACTIONTIME would keep versions of the different historical timelines specified by VALIDTIME.

Teradata automatically manages temporal tables.

In case you insert, update or delete a row, a historical row will be written. The historization works in an entirely automatic mode.

In case you run the following statement on a temporal table, the most current timeline with the currently valid record in this timeline will be automatically retrieved:

SELECT * FROM <TABLE>;

If you want to retrieve the whole history, add the following first line:

NONSEQUENCED VALIDTIME
SELECT * FROM <TABLE>;

If you want to retrieve the valid record for a certain date/time, another first line is needed:

VALIDTIME AS OF DATE ‘2014-04-01’
SELECT * FROM <TABLE>;

Finally, if you want to retrieve all historical timelines and all history rows per timeline:

NONSEQUENCED VALIDTIME AND NONSEQUENCED TRANSACTIONTIME
SELECT * FROM <TABLE>;

Although this was just a short introduction to Teradata temporal data management, you probably recognized that it could easily replace your handwritten historization logic.

It would be interesting to hear from professionals working with the Teradata temporal logic about query performance, especially when comparing Teradata temporals with hand wrote historization solutions.

Our Reader Score
[Total: 10    Average: 4/5]
Teradata Temporal Query Processing Review written by Roland Wenzlofsky on October 18, 2014 average rating 4/5 - 10 user ratings

2 COMMENTS

  1. Hi

    Having worked on data management in the context of time for some years, I have developed an approach which uses standard rdbms facilities and db modelling. http://www.youtube.com/watch?v=V1EcsuJxUno is one example of an implementation against a requirement which involved multiple entities and the demanding business reporting requirements of an investment bank. ie the temporal challenge can be addressed without special capabilities.

  2. 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

LEAVE A REPLY

Please enter your comment!
Please enter your name here