Restoring a Prior Table State | Temporal Table Support | Teradata Vantage - 17.05 - Restoring a Prior Table State - Teradata Database

Teradata Vantage™ - Temporal Table Support

Advanced SQL Engine
Teradata Database
June 2020
Programming Reference

Tables with transaction time automatically store in the table a snapshot copy of any row that is modified or deleted. This characteristic of these tables can be used to recover a prior state of the table using only SQL. This can be useful to quickly recover from a localized problem, such as if a table or set of tables have become corrupted by a user error, and need to be restored to a consistent state.

Assume a table that has transaction time needs to be restored to the state it was in at a point (time X) prior to the current time. Rows in the table can be classified based on whether they are open or closed, and on their transaction-time column period relation to time X:

Row Row State BEGIN(TT)

Beginning Transaction Time


End Transaction Time

1 Open Prior to time X UNTIL_CLOSED
2 Open Equal to time X UNTIL_CLOSED
3 Open After time X UNTIL_CLOSED
4 Closed Prior to time X Prior to time X
5 Closed Prior to time X Equal to time X
6 Closed Prior to time X After time X
7 Closed Equal to time X After time X
8 Closed After time X After time X

These rows are represented graphically below.

Graphical representation of open or closed rows