Tutorial: Time travel with T-SQL in a Warehouse

Članak
12/31/2024

In this tutorial, learn how to use T-SQL statements to time travel in a warehouse table. Time travel means to query data as it existed at a specific point in time, which is made automatically possible by Fabric Warehouse data retention.

Note

This tutorial forms part of an end-to-end scenario. In order to complete this tutorial, you must first complete these tutorials:

Work with time travel queries

In this task, learn how to create a view of the top 10 customers by sales. You will use the view in the next task to run time-travel queries.

Ensure that the workspace you created in the first tutorial is open.
On the Home ribbon, select New SQL query.

In the query editor, paste the following code. The code creates a view named Top10Customers. The view uses a query to retrieve the top 10 customers based on sales.

--Create the Top10Customers view.
CREATE VIEW [dbo].[Top10Customers]
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;

Run the query.
When execution completes, rename the query as Create Top 10 Customer View.
In the Explorer pane, from inside the Views folder for the dbo schema, verify that the Top10Customers view exists.
Create a new query to work with time travel queries.

In the query editor, paste the following code. The code updates the TotalIncludingTax value for a single fact row to deliberately inflate its total sales. It also retrieves the current timestamp.

 --Update the TotalIncludingTax for a single fact row to deliberately inflate its total sales.
 UPDATE [dbo].[fact_sale]
 SET [TotalIncludingTax] = 200000000
 WHERE [SaleKey] = 22632918; --For customer 'Tailspin Toys (Muir, MI)'
 GO

 --Retrieve the current (UTC) timestamp.
 SELECT CURRENT_TIMESTAMP;

Note

Currently, you can only use the Coordinated Universal Time (UTC) time zone for time travel.

Run the query.
When execution completes, rename the query as Time Travel.
In the Results pane, notice the timestamp value (your value will be the current UTC date and time).
To retrieve the top 10 customers as of now, in a new query editor, paste the following statement. The code retrieves the top 10 customers by using the FOR TIMESTAMP AS OF query hint.
```
 --Retrieve the top 10 customers as of now.
 SELECT *
 FROM [dbo].[Top10Customers]
 OPTION (FOR TIMESTAMP AS OF 'YOUR_TIMESTAMP');
```
Rename the query as Time Travel Now.
Return to the Time Travel query, and then use the Copy command to copy the query results.
Return to the Time Travel Now query, and then replace YOUR_TIMESTAMP with the timestamp you copied to the clipboard.
Run the query, and notice that the second top CustomerKey value is 49 for Tailspin Toys (Muir, MI).
Modify the timestamp value to an earlier time by subtracting one minute from the timestamp.
Run the query again, and notice that the second top CustomerKey value is 381 for Wingtip Toys (Sarversville, PA).