Introduction to Temporal Data Management in Teradata

Familiarity with the basic concepts of historization is assumed to benefit fully from this article.

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 non-temporal query, effectively transforming it into a non-temporal query.

Some database vendors have opted to integrate temporal functions directly into the RDBMS, resulting in improved performance. However, the approach of rewriting has little impact on query execution and negligible influence on the design of the Teradata Optimizer. Therefore, it was a more direct method of incorporating temporal data management into an existing RDBMS.

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

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

Below is an example of a temporal table (bi-temporal table example follows):

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

VALIDTIME defines the validity timeframe for each record in an object, while TRANSACTIONTIME maintains versions of historical timelines specified by VALIDTIME.

Temporal tables are managed automatically by Teradata.

Teradata automatically creates a historical row whenever a row is inserted, updated, or deleted. This process occurs without any manual intervention.

When executing the given statement on a temporal table, the system will automatically retrieve the latest timeline that contains the currently valid record.

SELECT * FROM <TABLE>;

To retrieve the complete history, include the following initial line:

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.