Introduction to Temporal Data Management in Teradata

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.

Want more practical data engineering analysis like this?

Join DWHPro Letters and get field-tested notes on Teradata, Snowflake, AI, migrations, performance, and enterprise data work. Early subscribers keep launch access before the paid plan launches.

Get the next issue

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

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

Get the next issue by email.

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)

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.

VALIDTIME and TRANSACTIONTIME defined

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 VALIDTIMESELECT * 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 TRANSACTIONTIMESELECT * 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.

Planning or surviving an enterprise data platform migration?

I write regularly about the performance, cost, architecture, and project mistakes that show up in real Teradata, Snowflake, Databricks, and enterprise data work.

Subscribe before the paid plan launches and keep launch access.

Written by Roland Wenzlofsky, founder of DWHPro and author of Teradata Query Performance Tuning. DWHPro has helped data warehouse practitioners for 15+ years.