17.00 - Valid-Time Modifications - Teradata Database

Teradata Vantageā„¢ - ANSI Temporal Table Support

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
created_date
June 2020
category
Programming Reference
featnum
B035-1186-170K

Assume a table represents contracts, with each row presenting the terms of an existing contract. If the terms of the contract are changed during the contract period, a regular update to a nontemporal table would result in the table reflecting the new contract terms. But potentially useful information would be lost: the fact that, prior to the modification, the contract terms were different. Valid-time tables let you model this reality using special temporal syntax for modifications, and by special handling of the rows changed by these modifications.

Modifications to valid-time tables are more flexible than those to system-time tables. Although the database again does the temporal bookkeeping for you automatically, the nature of the automatic modifications depends on the relationship between the valid time of the row and the effective time period you specify for the modification.

When you make UPDATE and DELETE modifications to valid-time tables, you can optionally specify a time period for which the modification applies. This is called the period of applicability (PA) of the modification. In this context, the valid-time period of a row is called the period of validity (PV) of the row. The PA of the modification syntax acts as an additional qualifier on the SQL. It is the relationship between the PA and PV that determines which rows can be modified, and whether the modification automatically creates new rows in the table that allow the database to reflect the time-delimited nature of the change.

The simplest case is a change that is effective as of one point in time during the PV of a row, and that lasts for the remaining duration of the PV. A simple change to terms of a contract is an example of this. This is how Teradata Database handles the change to preserve the temporal nature of change:

  • A copy of the row is automatically created and modified to show the new terms. The PV of the new row begins at the time of the change, to show when the new terms started. The PV of the row retains the original ending bound for the valid-time column, to retain the original contract end date, because the new terms are valid for the remaining life of the contract.
  • The original row, storing the original terms of the contract is marked as a history row. The ending bound of the valid-time of the old row is set to the time of the modification, because that is when the old terms in that row ceased to be valid.

Modifications to tables that have a valid-time dimension can apply to any period of time, even times that have passed or that are in the future. The changes affect only those rows with PVs that overlap the PA of the modification, and only for as long as the PA and PV coincide.

For example, if the PA of the modification lies within the PV of a row, such as a change to a contract that starts after the contract has begun, but ends before the contract expires, three rows will result from the modification:

  • One row has the original information, and a valid-time period that covers the time from the beginning of the original PV of the row until the modification takes effect.
  • The second row has the modified information, and a valid-time period that matches the PA of the modification statement.
  • The third row retains the original row information, but has a valid-time period that begins when the modification is no longer valid, and extends through the end time of the original PV of the row.

In this way, valid-time tables keep an automatic history of all changes. Unlike closed rows in system-time tables, history rows in valid-time tables remain accessible to all SQL operations. Because they model the real world, valid-time tables can have rows with a PV in the future, because things like contracts and policies may not begin or end until a future date.

Modifications that do not specify a PA behave just as modifications do on nontemporal tables, without affecting the valid time of the modified rows. They affect all rows in the table that meet the query criteria, regardless of the valid time of rows.

For more information about modifying rows in ANSI valid-time tables, see Modifying Rows in ANSI Valid-Time Tables.