How to: Query using time travel at the statement level (preview)

In Microsoft Fabric, the capability to time travel unlocks the ability to query the prior versions of data without the need to generate multiple data copies, saving on storage costs. This article describes how to query warehouse tables using time travel at the statement level, using the T-SQL OPTION clause and the FOR TIMESTAMP AS OF syntax. This feature is currently in preview.

Warehouse tables can be queried up to a retention period of seven calendar days using the OPTION clause, providing the date format yyyy-MM-ddTHH:mm:ss[.fff].

The following examples can be executed in the SQL Query Editor, SQL Server Management Studio (SSMS), Azure Data Studio, or any T-SQL query editor.

Time travel on a warehouse table

This example shows how to time travel on an individual table in warehouse.

The OPTION T-SQL clause specifies the point-in-time to return the data.

/* Time travel using a SELECT statement */
SELECT *
FROM [dbo].[dimension_customer]
OPTION (FOR TIMESTAMP AS OF '2024-05-02T20:44:13.700');

Time travel on multiple warehouse tables

The OPTION Clause is declared once per query, and the results of the query will reflect the state of the data at the timestamp specified in the query for all tables.

SELECT Sales.StockItemKey, 
Sales.Description, 
CAST (Sales.Quantity AS int)) AS SoldQuantity, 
c.Customer
FROM [dbo].[fact_sale] AS Sales INNER JOIN [dbo].[dimension_customer] AS c
ON Sales.CustomerKey = c.CustomerKey
GROUP BY Sales.StockItemKey, Sales.Description, Sales.Quantity, c.Customer
ORDER BY Sales.StockItemKey
OPTION (FOR TIMESTAMP AS OF '2024-05-02T20:44:13.700');

Time travel in a stored procedure

Stored procedures are a set of SQL statements that are precompiled and stored so that it can be used repeatedly. The OPTION clause can be declared once in the stored procedure, and the result set reflects the state of all tables at the timestamp specified.

The FOR TIMESTAMP AS OF clause cannot directly accept a variable, as values in this OPTION clause must be deterministic. You can use sp_executesql to pass a strongly typed datetime value to the stored procedure. This simple example passes a variable and converts the datetime parameter to the necessary format with date style 126.

CREATE PROCEDURE [dbo].[sales_by_city] (@pointInTime DATETIME)
AS
BEGIN
DECLARE @selectForTimestampStatement NVARCHAR(4000);
DECLARE @pointInTimeLiteral VARCHAR(33);

SET @pointInTimeLiteral = CONVERT(VARCHAR(33), @pointInTime, 126);
SET @selectForTimestampStatement = '
SELECT *
    FROM [dbo].[fact_sale] 
    OPTION (FOR TIMESTAMP AS OF ''' + @pointInTimeLiteral + ''')';
 
    EXEC sp_executesql @selectForTimestampStatement
END

Then, you can call the stored procedure and pass in a variable as a strongly typed parameter. For example:

--Execute the stored procedure
DECLARE @pointInTime DATETIME;
SET @pointInTime = '2024-05-10T22:56:15.457';
EXEC dbo.sales_by_city @pointInTime;

Or, for example:

--Execute the stored procedure
DECLARE @pointInTime DATETIME;
SET @pointInTime = DATEADD(dd, -7, GETDATE())
EXEC dbo.sales_by_city @pointInTime;

Time travel in a view

Views represent a saved query that dynamically retrieves data from one or more tables whenever the view is queried. The OPTION clause can be used to query the views so that the results reflect the state of data at the timestamp specified in the query.

--Create View
CREATE VIEW Top10CustomersView
AS
SELECT TOP (10) 
    FS.[CustomerKey], 
    DC.[Customer], 
    SUM(FS.TotalIncludingTax) AS TotalSalesAmount
FROM 
    [dbo].[dimension_customer] AS DC
INNER JOIN 
    [dbo].[fact_sale] AS FS ON DC.[CustomerKey] = FS.[CustomerKey]
GROUP BY 
    FS.[CustomerKey], 
    DC.[Customer]
ORDER BY 
    TotalSalesAmount DESC;

/*View of Top10 Customers as of a point in time*/
SELECT *
FROM [Timetravel].[dbo].[Top10CustomersView]
OPTION (FOR TIMESTAMP AS OF '2024-05-01T21:55:27.513'); 

However, you cannot query past data from tables in a view from before the view was created.

Limitations

For more information on time travel at the statement level limitations with FOR TIMESTAMP AS OF, see Time travel Limitations.

Related content

• Query data as it existed in the past