The May Microsoft Fabric updates bring new about Time Travel in a Data Warehouse. This is good but surprising because this feature is available for a while. Let’s discover what’s new in Data Warehouse time travel.
Time travel is a feature of Delta Tables which allow us to retrieve the data as it was in a specific moment in time.
There is a lot of considerations in the market if we should rely on a technical feature to hold data history for us or if we should ignore the feature and use a Slowly Changing Dimension type 2 (SCD type 2). If we use the SCD type 2, the history responsibility would be on us, independent of any feature.
Most professionals I talked to prefer to take the responsibility for the history into modelling, a SCD type 2. However, the time travel can still be useful in exceptional scenarios.
How does it work in Microsoft Fabric
I wrote before about lakehouse delta tables maintenance and this is directly related to the Time Travel feature.
In a lakehouse, the maintenance depends on us. We decide for how long we would like to keep the data for time travel. If we are using a good modelling for history, a SCD type 2, we don’t need this history at all.
In a Data Warehouse, the maintenance is made for us. A Data Warehouse keeps the data for 7 days before cleaning the history. In this way, at any moment a time travel can be made for historical data in the last 7 days.
The Clone Feature
The first time-travel feature in Data Warehouse was the Clone statement feature. I published a video about it: Fabric Monday 27: Clone and Time Travel in Fabric Data Warehouse
The method is simple: We can clone a table to a specific point in time back up to 7 days. This can be done using the Clone statement or using the UI.
Using the UI, we right-click the table, choose the Clone option:
On the following window we specify the time travel we would like to make:
Clone Statement Example
An interesting example is to make an update on a table and clone it to before the update time and compare the values.
- Check the 10 first records
customerkey,
totalincludingtax
FROM fact_sale
ORDER BY salekey
- Update the records
SET totalincludingtax = totalincludingtax * 2
- Clone the table with time travel
- Compare the cloned table with the original one
fs.customerkey,
fs.totalincludingtax,
fsp.totalincludingtax,
fs.totalincludingtax – fsp.totalincludingtax AS Difference
FROM fact_sale fs
INNER JOIN fact_sale_past fsp
ON fs.salekey = fsp.salekey
ORDER BY salekey
The new Time Travel feature
The new Time Travel feature released in May is the possibility to make time travel in a select statement, like the example below:
salekey,
customerkey,
totalincludingtax
FROM fact_sale
ORDER BY salekey OPTION (for timestamp AS OF ‘2024-05-22T00:00:00.000’)
The Clone statement creates a different table and allows us to compare the values, while the SELECT only allows us to recover information from a past point in time.
Summary
In my opinion, modelling is the best way to keep history. But in some exceptional situations, the time-travel can be very useful. I already needed it myself to solve some specific problems.
Load comments