Contents
Introduction
Time travel can help pull accurate data from complex data models. In models which utilize multiple date fields across tables, for example, start and end dates for projects & contracts, or customer churn.
Time travel works across the following sources:
Snowflake
Google BigQuery
Databricks
⚠ Time travel is supported on tables, but not supported by Snowflake on views.
💡 Use Custom SQL in the Data View Editor to run custom queries (even the same SQL as your views) to work with time-travel
How can I use time travel for analysis?
The simple HR diagram below shows a problem easily solved with Time-Travel. Rather than an end-user potentially filtering between 3 different date fields (start date, end date, hire date) they can see the exact view of the data last month, in case of a need for historical reporting or comparison purposes. This can be done easily using the Time-Travel option available in the Filter Bar or in the properties panel filter section of an object.
How to use global time travel in the filter bar
Global time travel
Global time travel can be enabled in the filter bar menu. It can be limited to suit the setting of the environment. There is a maximum limit of 90 days, which is set by Snowflake. The limit can be reduced if you have less time travel range available.
Date
Time travel can specify an exact timestamp - this is particularly useful for deeper analysis where you don't want the data to change.
The timestamp queried is fixed and will not change in this mode. For dynamic querying, see offset below.
Offset
Offset mode provides a "playback" type experience. The value of the offset is queried each time the data is queried - in the case of -1h offset value, users are guaranteed to query one hour ago, each time.
With this, each query may produce differing results, since the time that is queried is dynamic and relative to the current time minus the offset value.
Time travel at chart level
Time travel can be performed at chart level. In the example below, we display identical metrics, 24 hours apart. The properties can be found in the filters tab of each object, in the properties panel.
This is particularly for comparisons.
Known Limitations
⚠ To use time travel with Custom SQL as a data source in Astrato, insert {TT} after the table name
⚠ The standard retention period is 1 day (24 hours) and is automatically enabled for all Snowflake accounts. For Snowflake Enterprise Edition (and higher), permanent databases, schemas, and tables, the retention period can be set to any value from 0 up to 90 days.
⚠ Time travel is supported on tables, databases and schemas. It is not supported on views [Ref]
Helpful resources