Time Travel Queries - Teradata Vantage

Apache Iceberg and Delta Lake Open Table Format on VantageCloud Lake Getting Started

Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
December 2024
ft:locale
en-US
ft:lastEdition
2025-01-03
dita:mapPath
bsr1702324250454.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
bsr1702324250454

Time travel queries allow a table to be queried at a particular point in time based on a timestamp or snapshot id. It can show how the data has changed over time and what was inserted, deleted or updated in the table. It can also show any changes to the table schema over time.

Time travel queries are supported based on snapshot id or timestamp/date literal. If timestamp or date is specified, then the snapshot id AT or BEFORE that timestamp will be picked.

The snapshot id for a table can be retrieved using the TD_SNAPSHOTS() Function.

Syntax

<from_clause>::=  FROM <source_spec> [ , <source_spec> ... ]
<source_spec>::=  <table_name> [ [AS] <correlation_name> ] [ <time_travel_clause> ]
<correlation_name>::= !! Teradata identifier
<table_name>::= !! Teradata identifier
<time_travel_clause> := FOR SNAPSHOT AS OF <time_travel_value>
<time_travel_value>::= <timestamp_literal> | <date_literal> | <numeric_literal>
<timestamp_literal>::= TIMESTAMP <quote> <timestamp_string> <quote>
<date_literal>::= DATE <quote> <date_string> <quote>
<numeric_literal>::= <quote> <snapshot_id> <quote>
<timestamp_string>::= !! Timestamp in YYYY-MM-DD HH:MI:SS format
<date_string>::= !! Date in YYYY-MM-DD format
<snapshot_id>::= !! Delta Lake or Iceberg snapshot/version ID