What is time travel?
Time travel is one of the most exciting but underreported features available in Microsoft Fabric. Traditionally, when you make changes to a table or a file in a Microsoft SQL warehouse or Azure Data Lake, these changes are permanent and there’s no easy way to undo those changes or go back in time to view what the data looked like before the changes.
This is where the time travel feature offered by Fabric comes in. As a result of Microsoft adopting the open-source Delta Lake format, we can query data as it was stored at any point of time prior to the current day. For a Fabric data warehouse, this retention period is set to 30 days by default, whereas when using workloads with the Spark engine, we can configure it to suit our needs.
In the past this functionality was applicable to files stored in the Fabric Lakehouse. Now, the feature can also be accessed from a Fabric data warehouse.
Real-world applications
There are many scenarios where having built-in functionality to query data from a previous point in time is useful, such as:
Tracking transaction lifecycle: Use time travel if you want to perform a periodic check on how long it takes for a transaction to move between states. For example, in retail sales, you could use it to track a sales transaction as it moves from a notational to a reconciled sale.
Trend analysis: If you don’t want to implement a Slowly Changing Dimension, yet still want to be able to perform comparative analysis for recent time periods, you could lean on the time travel functionality.
Debugging issues: In case of any inaccuracies with reports, you can use time travel to query past data to determine if it’s a data- or a logic-related issue.
Data recovery: In the event of accidental operations on data, you can go back and recover the data to a previous state.
Compliance: Short-term, built-in data retention functionality can help fulfill some compliance and auditing requirements.
Data Science workloads: Time travel allows you to go back and look at data in its original state for predictive analysis or training models.
How it works
Similar to how traditional databases log data operations, any modifications to the data in the table or files are recorded and stored separately in the Delta Log folder within the Table/File directory.
Querying point in time data with ‘as of’
With Fabric Data Warehouse:
Let’s assume we have the following records in a table:
Now, I’ll update the passenger count to 5 for trip ID 1224 and delete trip ID 147. But first, I’ll note the time of execution which will help us when retrieving data from that time period.
Now we no longer see Trip ID 147, and do see the passenger count as 5 for Trip ID 1224:
Next, let’s try travelling back in time to retrieve these records by providing the timestamp of the time you want to refer to. Notice that the deleted record can be seen, and the number of passengers shows the earlier values.
With Fabric Lakehouse:
Let’s perform a similar operation using a Spark notebook on a file inside the lakehouse. Here’s the data contained in the Holiday list file:
Now, update the date for ‘Thanksgiving’ holiday to ‘2024-11-27’ and overwrite the file.
We’ll next use time travel functionality to retrieve the record state from an earlier version of the file.
To see all versions of the file that are available, you can use this query:
Clone Table:
Another interesting potential applicating is using time travel to create a clone of the table as it existed at a specific point in time using the Clone Table command. Once we have a separate table, we don’t need to provide query hints to retrieve point-in-time data.
Example:
The above example would create a clone of the existing table, as a new table, with data as of ‘2023-10-01’.
Considerations
There are a few things to keep in mind when using time travel functionality:
- You can only perform read operations when using time travel.
- View definitions cannot contain the OPTION FOR TIMESTAMP AS OF syntax. The view can be queried with the SELECT .. FROM <view> … OPTION FOR TIMESTAMP AS OF syntax. However, you cannot query past data from tables in a view from before the view was created.
- Currently, only the Coordinated Universal Time (UTC) time zone is used for time travel.
- Currently, the data retention for time travel queries is thirty calendar days.
- Time travel is not supported for the SQL analytics endpoint of the Lakehouse.
- The OPTION FOR TIMESTAMP AS OF syntax can only be used in queries that begin with SELECT statement. Queries such as INSERT INTO SELECT and CREATE TABLE AS SELECT cannot be used along with the OPTION FOR TIMESTAMP AS OF. Consider instead the ability to Clone a warehouse table at a point in time.
- Be sure to set an optimum retention period and vacuum the files periodically (cleanup files that are no longer referenced by Delta Table log) to improve performance and ensure the Lakehouse is performing optimally.
Ready to get started with Fabric? Let’s talk.
Inviso was one of the few partners chosen by Microsoft to participate in the Fabric Private Preview. After being granted early access in November 2022, our team has been deeply involved with Fabric ever since. We helped with initial testing of the product and provided critical feedback to the Fabric Product Team leading up to the Public Preview announcement in June. Our early involvement earned us a spot in the Fabric Feature Partner program.
If you’d like to learn more about time travel or explore other Microsoft Fabric implementations, please feel free to reach out to us – we’d love to help.
