3 Types of Temporal Tables in Teradata

temporal tables

What is a Temporal Database Definition - Temporal – the ability to store all states of a given set of data (a database row), as part of the query select a point in time to reference the data.

  • What was this account balance (share price, inventory level, asset value, etc) on this data?
  • What data went into the calculation on 11/3/07, and what adjustments were made in 2008?
  • On this historic data, what was the service level (contract status, customer value, insurance policy coverage) for said customer?

Three Types of Temporal Tables

1. Valid Time tables

When a fact is true in the modeled reality. User specified times.

2. Transaction time Tables

When a fact is stored in the database. System maintained time, no user control.

3. Bitemporal Tables

Both Transaction Time and Valid Time

User defined Time

User can add time period columns, and take advantage of the added temporal operators Database does not enforce any rules on user defined time columns.

Temporal Query

Provides list of members who were reported as covered on JAN 15,2000 in the Feb 1 2000 NCQA report, with names as accurate as our best data shows today.

Temporal Update - Bi Temporal Table

Current Valid time, Current transaction time Query jeans(125,102) are sold today (2005-08-30)


Popular posts from this blog

SQL for Quantile Function in Teradata

3 Uses of SAMPLE function in Teradata

All You Need NULL Value Functions