Unlocking Microsoft Fabric Warehouse: Time Travel -III

How to perform time travel in Microsoft Fabric Warehouse?

Time travel is a cost effective and space efficient way to store and query the prior version of data in warehouse. This feature can only read the data. Operations like insert, update and delete cannot be used.

This feature can be used with T-SQL statements, store procedures and views. User can query data across different warehouse under same workspace.

Time travel is used for various use cases such as,

1. Historical trend analysis
2. Low-cost comparison with pervious version of data
3. Analysis of performance over time
4. Auditing and compliance validation
5. Use to perform Machine learning over previous version of data
6. Query historical trend across different warehouses in Fabric Workspace

Currently this feature is in preview mode.

Currently Microsoft Fabric allows retrieval of past version of data with following approaches.

1. At SQL statement level ‘FOR TIMESTAMP AS OF’
2. Table cloning (I covered this in past)

Scripting select example to use option clause with Timestamp AS OF:

SELECT top 100 *
FROM [TimeTravelWareHouse].[nyc].[dimension_trip]
OPTION (FOR TIMESTAMP AS OF '2024-07-06T20:44:13.700');

Another example with cross warehouse query to join 2 different tables and get data using timestamp from the past.

SELECT TOP (100) tableA.PassengerCount
            ,tableA.TripDurationSeconds
            ,tableA.TripDistanceMiles
            ,tableA.PaymentType
            ,tableA.FareAmount
            ,tableA.SurchargeAmount
FROM [WideWorldImporters].[dbo].[Trip] as tableA inner join [TimeTravelWareHouse].[nyc].[dimension_trip] as tableb
on tableA.PickupLatitude = tableb.pickupLatitude and tableA.PickupLongitude = tableb.pickupLongitude
OPTION (FOR TIMESTAMP AS OF '2024-07-06T20:44:13.700');

Note: You may already knew this but T-SQL is case sensitive. It took me few minutes to figure our the reason even though error was clear. learn from my mistakes :D