Uredi

Deli putem

How to: Query using time travel at the statement level

  • Članak

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 thirty 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.

Note

Currently, only the Coordinated Universal Time (UTC) time zone is used for time travel.

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');
  • The historical data from tables in a view can only be queried for time travel beginning from the time the view was created.
  • After a view is altered, time travel queries are only valid after it was altered.
  • If an underlying table of a view is altered without changing the view, time travel queries on the view can return the data from before the table change, as expected.
  • When the underlying table of a view is dropped and recreated without modifying the view, data for time travel queries is only available from the time after the table was recreated.

Limitations

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

Related content