Teradata Temporal Query Processing Review

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. A few years ago, we had to write our temporal data management functionality to historize our data and keep different historical timelines.

Starting with release 13.10, Teradata, like many other data warehouse vendors, added temporal data management functionality based on the TSQL2 specification, 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.

Database vendors implement two different strategies for temporal data management.

Teradata’s plan 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 directly build temporal features into the RDBMS without the indirect way mentioned above, resulting in better performance. Still, the rewrite approach has no impact on how a query is executed and almost no effect on the design of the Teradata Optimizer. Hence, it was a more straightforward 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 valid time 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 for 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.

Teradata will write a historical row if you insert, update, or delete a row. 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 particular 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 handwritten historization solutions.