The Evolution of Time Travel in Fabric Data Warehouse

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:

A screenshot of a computer

Description automatically generated

On the following window we specify the time travel we would like to make:

A screenshot of a computer

Description automatically generated

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.

  1. Check the 10 first records
SELECT TOP 10 salekey,
FROM   fact_sale
ORDER  BY salekey 

A screenshot of a data

Description automatically generated

  1. Update the records
UPDATE fact_sale
SET    totalincludingtax = totalincludingtax * 2 
  1. Clone the table with time travel
CREATE TABLE dbo.fact_sale_past AS clone OF dbo.fact_sale at ‘2024-05-22T00:00:00.000’;
  1. Compare the cloned table with the original one
SELECT TOP 10 fs.salekey,
              fs.totalincludingtax  fsp.totalincludingtax AS Difference
FROM   fact_sale fs
       INNER JOIN fact_sale_past fsp
               ON fs.salekey = fsp.salekey
ORDER  BY salekey 

A screenshot of a calculator

Description automatically generated

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:

FROM     fact_sale
ORDER BY salekey OPTION (for timestamp AS OF ‘2024-05-22T00:00:00.000’)

A screenshot of a data

Description automatically generated

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.


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.