Edit

Share via

Tutorial: Time travel using T-SQL at statement level

  • Article

In this article, learn how to time travel in your warehouse at the statement level using T-SQL. This feature allows you to query data as it appeared in the past, within a retention period.

Note

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

Time travel

In this example, we'll update a row, and show how to easily query the previous value using the FOR TIMESTAMP AS OF query hint.

  1. From the Home tab of the ribbon, select New SQL query.

    Screenshot from the Fabric portal of the New SQL query button.

  2. In the query editor, paste the following code to create the view Top10CustomerView. Select Run to execute the query.

    CREATE VIEW dbo.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;

  3. In the Explorer, verify that you can see the newly created view Top10CustomersView by expanding the View node under dbo schema.

    Screenshot of the user's image.

  4. Create another new query, similar to Step 1. From the Home tab of the ribbon, select New SQL query.

  5. In the query editor, paste the following code. This updates the TotalIncludingTax column value to 200000000 for the record which has the SaleKey value of 22632918. Select Run to execute the query.

    /*Update the TotalIncludingTax value of the record with SaleKey value of 22632918*/
UPDATE [dbo].[fact_sale]
SET TotalIncludingTax = 200000000
WHERE SaleKey = 22632918;

  6. In the query editor, paste the following code. The CURRENT_TIMESTAMP T-SQL function returns the current UTC timestamp as a datetime. Select Run to execute the query.

    SELECT CURRENT_TIMESTAMP;

  7. Copy the timestamp value returned to your clipboard.

  8. Paste the following code in the query editor and replace the timestamp value with the current timestamp value obtained from the prior step. The timestamp syntax format is YYYY-MM-DDTHH:MM:SS[.FFF].

  9. Remove the trailing zeroes, for example: 2024-04-24T20:59:06.097.

  10. The following example returns the list of top ten customers by TotalIncludingTax, including the new value for SaleKey 22632918. Select Run to execute the query.

    /*View of Top10 Customers as of today after record updates*/
SELECT *
FROM [WideWorldImporters].[dbo].[Top10CustomersView]
OPTION (FOR TIMESTAMP AS OF '2024-04-24T20:59:06.097');

  11. Paste the following code in the query editor and replace the timestamp value to a time prior to executing the update script to update the TotalIncludingTax value. This would return the list of top ten customers before the TotalIncludingTax was updated for SaleKey 22632918. Select Run to execute the query.

    /*View of Top10 Customers as of today before record updates*/
SELECT *
FROM [WideWorldImporters].[dbo].[Top10CustomersView]
OPTION (FOR TIMESTAMP AS OF '2024-04-24T20:49:06.097');

For more examples, visit How to: Query using time travel at the statement level.

Next step

Tutorial: Create a query with the visual query builder

Related content