{"id":102447,"date":"2024-05-24T08:30:00","date_gmt":"2024-05-24T08:30:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=102447"},"modified":"2024-09-03T20:04:45","modified_gmt":"2024-09-03T20:04:45","slug":"the-evolution-of-time-travel-in-fabric-data-warehouse","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/the-evolution-of-time-travel-in-fabric-data-warehouse\/","title":{"rendered":"The Evolution of Time Travel in Fabric Data Warehouse"},"content":{"rendered":"

The May Microsoft Fabric updates bring new about Time Travel in a Data Warehouse. This is good but surprising because this feature is available for a while. Let\u2019s discover what\u2019s new in Data Warehouse time travel.<\/p>\n

Time travel is a feature of Delta Tables which allow us to retrieve the data as it was in a specific moment in time.<\/p>\n

There is a lot of considerations in the market if we should rely on a technical feature to hold data history for us or if we should ignore the feature and use a Slowly Changing Dimension type 2 (SCD type 2). If we use the SCD type 2, the history responsibility would be on us, independent of any feature.<\/p>\n

Most professionals I talked to prefer to take the responsibility for the history into modelling, a SCD type 2. However, the time travel can still be useful in exceptional scenarios.<\/p>\n

How does it work in Microsoft Fabric<\/h2>\n

I wrote before about lakehouse delta tables maintenance<\/a> and this is directly related to the Time Travel feature.<\/p>\n

In a lakehouse, the maintenance depends on us. We decide for how long we would like to keep the data for time travel. If we are using a good modelling for history, a SCD type 2, we don\u2019t need this history at all.<\/p>\n

In a Data Warehouse, the maintenance is made for us. A Data Warehouse keeps the data for 7 days before cleaning the history. In this way, at any moment a time travel can be made for historical data in the last 7 days.<\/p>\n

The Clone Feature<\/h2>\n

The first time-travel feature in Data Warehouse was the Clone statement feature. I published a video about it: Fabric Monday 27: Clone and Time Travel in Fabric Data Warehouse<\/a><\/p>\n

The method is simple: We can clone a table to a specific point in time back up to 7 days. This can be done using the Clone statement or using the UI.<\/p>\n

Using the UI, we right-click the table, choose the Clone option:<\/p>\n

\"A<\/p>\n

On the following window we specify the time travel we would like to make:<\/p>\n

\"A<\/p>\n

Clone Statement Example<\/h2>\n

An interesting example is to make an update on a table and clone it to before the update time and compare the values.<\/p>\n

    \n
  1. Check the 10 first records<\/li>\n<\/ol>\n
    SELECT<\/span>\u00a0TOP<\/span>\u00a010<\/span>\u00a0salekey<\/span>,<\/span>
    \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0customerkey<\/span>,<\/span>
    \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0totalincludingtax<\/span>
    \nFROM<\/span>\u00a0\u00a0\u00a0fact_sale<\/span>
    \nORDER<\/span>\u00a0\u00a0BY<\/span>\u00a0salekey<\/span>\u00a0 <\/span><\/div>\n

    \"A<\/p>\n

      \n
    1. Update the records<\/li>\n<\/ol>\n
      UPDATE<\/span>\u00a0fact_sale<\/span>
      \nSET<\/span>\u00a0\u00a0\u00a0\u00a0totalincludingtax<\/span>\u00a0=<\/span>\u00a0totalincludingtax<\/span>\u00a0*<\/span>\u00a02<\/span>\u00a0 <\/span><\/div>\n
        \n
      1. Clone the table with time travel<\/li>\n<\/ol>\n
        CREATE<\/span>\u00a0TABLE<\/span>\u00a0dbo<\/span>.<\/span>fact_sale_past<\/span>\u00a0AS<\/span>\u00a0clone<\/span>\u00a0OF<\/span>\u00a0dbo<\/span>.<\/span>fact_sale<\/span>\u00a0at<\/span>\u00a0‘2024-05-22T00:00:00.000’<\/span>;<\/span> <\/span><\/div>\n
          \n
        1. Compare the cloned table with the original one<\/li>\n<\/ol>\n
          SELECT<\/span>\u00a0TOP<\/span>\u00a010<\/span>\u00a0fs<\/span>.<\/span>salekey<\/span>,<\/span>
          \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0fs<\/span>.<\/span>customerkey<\/span>,<\/span>
          \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0fs<\/span>.<\/span>totalincludingtax<\/span>,<\/span>
          \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0fsp<\/span>.<\/span>totalincludingtax<\/span>,<\/span>
          \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0fs<\/span>.<\/span>totalincludingtax<\/span>\u00a0–<\/span>\u00a0fsp<\/span>.<\/span>totalincludingtax<\/span>\u00a0AS<\/span>\u00a0Difference<\/span>
          \nFROM<\/span>\u00a0\u00a0\u00a0fact_sale<\/span>\u00a0fs<\/span>
          \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0INNER<\/span>\u00a0JOIN<\/span>\u00a0fact_sale_past<\/span>\u00a0fsp<\/span>
          \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ON<\/span>\u00a0fs<\/span>.<\/span>salekey<\/span>\u00a0=<\/span>\u00a0fsp<\/span>.<\/span>salekey<\/span>
          \nORDER<\/span>\u00a0\u00a0BY<\/span>\u00a0salekey<\/span>\u00a0 <\/span><\/div>\n

          \"A<\/p>\n

          The new Time Travel feature<\/h2>\n

          The new Time Travel feature released in May is the possibility to make time travel in a select statement, like the example below:<\/p>\n

          SELECT<\/span>\u00a0TOP<\/span>\u00a010<\/span>
          \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0salekey<\/span>,<\/span>
          \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0customerkey<\/span>,<\/span>
          \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0totalincludingtax<\/span>
          \nFROM<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0fact_sale<\/span>
          \nORDER<\/span>\u00a0BY<\/span>\u00a0salekey<\/span>\u00a0OPTION<\/span>\u00a0(<\/span>for<\/span>\u00a0timestamp<\/span>\u00a0AS<\/span>\u00a0OF<\/span>\u00a0‘2024-05-22T00:00:00.000’<\/span>)<\/span> <\/span><\/div>\n

          \"A<\/p>\n

          The Clone statement creates a different table and allows us to compare the values, while the SELECT only allows us to recover information from a past point in time.<\/p>\n

          Summary<\/h2>\n

          In my opinion, modelling is the best way to keep history. But in some exceptional situations, the time-travel can be very useful. I already needed it myself to solve some specific problems.<\/p>\n

           <\/p>\n","protected":false},"excerpt":{"rendered":"

          The May Microsoft Fabric updates bring new about Time Travel in a Data Warehouse. This is good but surprising because this feature is available for a while. Let\u2019s discover what\u2019s new in Data Warehouse time travel. Time travel is a feature of Delta Tables which allow us to retrieve the data as it was in……<\/p>\n","protected":false},"author":50808,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2,159164],"tags":[123645,159106,158997,159105],"coauthors":[6810],"class_list":["post-102447","post","type-post","status-publish","format-standard","hentry","category-blogs","category-microsoft-fabric","tag-data-warehouse","tag-delta-table","tag-microsoft-fabric","tag-time-travel"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/102447","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=102447"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/102447\/revisions"}],"predecessor-version":[{"id":102453,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/102447\/revisions\/102453"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=102447"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=102447"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=102447"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=102447"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}