{"id":165,"date":"2019-10-03T17:24:02","date_gmt":"2019-10-03T17:24:02","guid":{"rendered":"https:\/\/snowflake.pavlik.us\/?p=165"},"modified":"2020-01-26T20:51:38","modified_gmt":"2020-01-26T20:51:38","slug":"time-travel-zero-copy-clones-perpetual-table-history","status":"publish","type":"post","link":"https:\/\/snowflake.pavlik.us\/index.php\/2019\/10\/03\/time-travel-zero-copy-clones-perpetual-table-history\/","title":{"rendered":"Preserving Table Change History Indefinitely"},"content":{"rendered":"\n<p>Snowflake supports <a rel=\"noreferrer noopener\" aria-label=\"Time Travel  (opens in a new tab)\" href=\"https:\/\/docs.snowflake.net\/manuals\/user-guide\/data-time-travel.html\" target=\"_blank\">Time Travel<\/a> and <a href=\"https:\/\/docs.snowflake.net\/manuals\/user-guide\/streams.html#data-retention-period-and-staleness\">Stream<\/a> retention up to 90 days. What if you need to preserve the history indefinitely? You can do that by making a <a rel=\"noreferrer noopener\" aria-label=\"Zero Copy Clone (opens in a new tab)\" href=\"https:\/\/docs.snowflake.net\/manuals\/user-guide\/object-clone.html\" target=\"_blank\">Zero Copy Clone<\/a> at intervals shorter than the <a href=\"https:\/\/docs.snowflake.net\/manuals\/user-guide\/data-time-travel.html#data-retention-period\">Time Travel retention period<\/a>. <\/p>\n\n\n\n<p>Suppose Finance sends you occasional corrections to a table containing sales records. For a handful of records, UPDATE statements correct the figures. For large-scale corrections you use MERGE operations. The table now contains the corrected figures, but what if for audit and reporting purposes you need to know the values as they existed before correction?<\/p>\n\n\n\n<p>Time Travel allows this for up to 90 days, but we can keep that history indefinitely by making Zero Copy Clones. Here&#8217;s an example:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\n-- Snowflake&#039;s Zero Copy Cloning preserves table history for up to 90 days.\n-- To preserve change history indefinitely, create a Zero Copy Clone\n-- more frequently than the time travel retention period.\n\nuse role sysadmin;\nuse database TEST;\nuse schema SALES;\nuse warehouse TEST;\n\n-- First, check how long Snowflake is set to preserve Time Travel data for the table\nselect RETENTION_TIME from INFORMATION_SCHEMA.TABLES where TABLE_NAME = &#039;SALES_FACT&#039;;\n\n-- If necessary, increase the duration so you can clone it before Time Travel data rolls off.\nalter table SALES_FACT set data_retention_time_in_days = 30;\n\n-- Optionally, create a new schema to keep the historical clones. This will keep the base table\n-- schema cleaner.\ncreate schema if not exists SALES_FACT_HISTORY;\n\n-- Run the remaining section as frequently as required to get the needed granularity of history \n-- preservation. For critical tables in regulated industries, it may be necessary to create \n-- a clone after every UPDATE or MERGE performed.\n\n-- You&#039;ll need to create a dynamic table name, usually based on date.\nset CLONE_TABLE_NAME = &#039;SALES_FACT_HISTORY.SALES_FACT_CLONE_&#039; || to_varchar(CURRENT_DATE, &#039;YYYY_MM_DD&#039;);\n\n-- Optionally check the name we&#039;ll use for the table\n--select $CLONE_TABLE_NAME;\n\n-- Use the identifier() SQL function to use a SQL variable as an object name.\n-- Run this statement once a month, once a week, or any duration more frequent than the\n-- table&#039;s Time Travel retention period.\ncreate table identifier($CLONE_TABLE_NAME) clone SALES_FACT;\n\n<\/pre><\/div>\n\n\n<p>When preserving change history, there&#8217;s an important consideration for highly-regulated data. Time Travel and Streams preserve a complete record of every change to a table. Creating a clone preserves the state only at the time it&#8217;s cloned. What does this mean in practice?<\/p>\n\n\n\n<p>Suppose you clone a table once a day at midnight. Months later, Finance sends revised sales figures. You update the rows quickly. A few hours later, Finance notices a miscalculation and sends corrected values. When you apply updates to the same rows twice that day, the clone at midnight will reflect only the later change. For most data, this is probably fine. For highly regulated data, it may not be acceptable. In this case, you&#8217;ll need to perform a clone after every update or merge that updates rows.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Snowflake supports Time Travel and Stream retention up to 90 days. What if you need to preserve the history indefinitely? You can do that by making a Zero Copy Clone at intervals shorter than the Time Travel retention period. Suppose Finance sends you occasional corrections to a table containing sales records. For a handful of [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[15],"tags":[18,19,16,17],"class_list":["post-165","post","type-post","status-publish","format-standard","hentry","category-change-data-capture","tag-change-data-capture","tag-history-table","tag-time-travel","tag-zero-copy-clone"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\r\n<title>Preserving Table Change History Indefinitely - Snowflake in the Carolinas<\/title>\r\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\r\n<link rel=\"canonical\" href=\"https:\/\/snowflake.pavlik.us\/index.php\/2019\/10\/03\/time-travel-zero-copy-clones-perpetual-table-history\/\" \/>\r\n<meta property=\"og:locale\" content=\"en_US\" \/>\r\n<meta property=\"og:type\" content=\"article\" \/>\r\n<meta property=\"og:title\" content=\"Preserving Table Change History Indefinitely - Snowflake in the Carolinas\" \/>\r\n<meta property=\"og:description\" content=\"Snowflake supports Time Travel and Stream retention up to 90 days. What if you need to preserve the history indefinitely? You can do that by making a Zero Copy Clone at intervals shorter than the Time Travel retention period. Suppose Finance sends you occasional corrections to a table containing sales records. For a handful of [&hellip;]\" \/>\r\n<meta property=\"og:url\" content=\"https:\/\/snowflake.pavlik.us\/index.php\/2019\/10\/03\/time-travel-zero-copy-clones-perpetual-table-history\/\" \/>\r\n<meta property=\"og:site_name\" content=\"Snowflake in the Carolinas\" \/>\r\n<meta property=\"article:published_time\" content=\"2019-10-03T17:24:02+00:00\" \/>\r\n<meta property=\"article:modified_time\" content=\"2020-01-26T20:51:38+00:00\" \/>\r\n<meta name=\"author\" content=\"Greg Pavlik\" \/>\r\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\r\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Greg Pavlik\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\r\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/snowflake.pavlik.us\\\/index.php\\\/2019\\\/10\\\/03\\\/time-travel-zero-copy-clones-perpetual-table-history\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/snowflake.pavlik.us\\\/index.php\\\/2019\\\/10\\\/03\\\/time-travel-zero-copy-clones-perpetual-table-history\\\/\"},\"author\":{\"name\":\"Greg Pavlik\",\"@id\":\"https:\\\/\\\/snowflake.pavlik.us\\\/#\\\/schema\\\/person\\\/019455f4675665b6cf5edea31ec44d7b\"},\"headline\":\"Preserving Table Change History Indefinitely\",\"datePublished\":\"2019-10-03T17:24:02+00:00\",\"dateModified\":\"2020-01-26T20:51:38+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/snowflake.pavlik.us\\\/index.php\\\/2019\\\/10\\\/03\\\/time-travel-zero-copy-clones-perpetual-table-history\\\/\"},\"wordCount\":256,\"commentCount\":5,\"keywords\":[\"Change Data Capture\",\"History Table\",\"Time Travel\",\"Zero Copy Clone\"],\"articleSection\":[\"Change Data Capture\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/snowflake.pavlik.us\\\/index.php\\\/2019\\\/10\\\/03\\\/time-travel-zero-copy-clones-perpetual-table-history\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/snowflake.pavlik.us\\\/index.php\\\/2019\\\/10\\\/03\\\/time-travel-zero-copy-clones-perpetual-table-history\\\/\",\"url\":\"https:\\\/\\\/snowflake.pavlik.us\\\/index.php\\\/2019\\\/10\\\/03\\\/time-travel-zero-copy-clones-perpetual-table-history\\\/\",\"name\":\"Preserving Table Change History Indefinitely - Snowflake in the Carolinas\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/snowflake.pavlik.us\\\/#website\"},\"datePublished\":\"2019-10-03T17:24:02+00:00\",\"dateModified\":\"2020-01-26T20:51:38+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/snowflake.pavlik.us\\\/#\\\/schema\\\/person\\\/019455f4675665b6cf5edea31ec44d7b\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/snowflake.pavlik.us\\\/index.php\\\/2019\\\/10\\\/03\\\/time-travel-zero-copy-clones-perpetual-table-history\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/snowflake.pavlik.us\\\/index.php\\\/2019\\\/10\\\/03\\\/time-travel-zero-copy-clones-perpetual-table-history\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/snowflake.pavlik.us\\\/index.php\\\/2019\\\/10\\\/03\\\/time-travel-zero-copy-clones-perpetual-table-history\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/snowflake.pavlik.us\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Preserving Table Change History Indefinitely\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/snowflake.pavlik.us\\\/#website\",\"url\":\"https:\\\/\\\/snowflake.pavlik.us\\\/\",\"name\":\"Snowflake in the Carolinas\",\"description\":\"Random thoughts on all things Snowflake in the Carolinas\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/snowflake.pavlik.us\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/snowflake.pavlik.us\\\/#\\\/schema\\\/person\\\/019455f4675665b6cf5edea31ec44d7b\",\"name\":\"Greg Pavlik\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/d81df729eebf37a042922b17d4a4c834b1e0ccfa9fea1c2c78cb8e95c7e91701?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/d81df729eebf37a042922b17d4a4c834b1e0ccfa9fea1c2c78cb8e95c7e91701?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/d81df729eebf37a042922b17d4a4c834b1e0ccfa9fea1c2c78cb8e95c7e91701?s=96&d=mm&r=g\",\"caption\":\"Greg Pavlik\"},\"description\":\"Greg is a Senior Sales Engineer at Snowflake Computing, in the Raleigh-Durham area. He's been in data management and security for the twenty years.\"}]}<\/script>\r\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Preserving Table Change History Indefinitely - Snowflake in the Carolinas","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/snowflake.pavlik.us\/index.php\/2019\/10\/03\/time-travel-zero-copy-clones-perpetual-table-history\/","og_locale":"en_US","og_type":"article","og_title":"Preserving Table Change History Indefinitely - Snowflake in the Carolinas","og_description":"Snowflake supports Time Travel and Stream retention up to 90 days. What if you need to preserve the history indefinitely? You can do that by making a Zero Copy Clone at intervals shorter than the Time Travel retention period. Suppose Finance sends you occasional corrections to a table containing sales records. For a handful of [&hellip;]","og_url":"https:\/\/snowflake.pavlik.us\/index.php\/2019\/10\/03\/time-travel-zero-copy-clones-perpetual-table-history\/","og_site_name":"Snowflake in the Carolinas","article_published_time":"2019-10-03T17:24:02+00:00","article_modified_time":"2020-01-26T20:51:38+00:00","author":"Greg Pavlik","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Greg Pavlik","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/snowflake.pavlik.us\/index.php\/2019\/10\/03\/time-travel-zero-copy-clones-perpetual-table-history\/#article","isPartOf":{"@id":"https:\/\/snowflake.pavlik.us\/index.php\/2019\/10\/03\/time-travel-zero-copy-clones-perpetual-table-history\/"},"author":{"name":"Greg Pavlik","@id":"https:\/\/snowflake.pavlik.us\/#\/schema\/person\/019455f4675665b6cf5edea31ec44d7b"},"headline":"Preserving Table Change History Indefinitely","datePublished":"2019-10-03T17:24:02+00:00","dateModified":"2020-01-26T20:51:38+00:00","mainEntityOfPage":{"@id":"https:\/\/snowflake.pavlik.us\/index.php\/2019\/10\/03\/time-travel-zero-copy-clones-perpetual-table-history\/"},"wordCount":256,"commentCount":5,"keywords":["Change Data Capture","History Table","Time Travel","Zero Copy Clone"],"articleSection":["Change Data Capture"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/snowflake.pavlik.us\/index.php\/2019\/10\/03\/time-travel-zero-copy-clones-perpetual-table-history\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/snowflake.pavlik.us\/index.php\/2019\/10\/03\/time-travel-zero-copy-clones-perpetual-table-history\/","url":"https:\/\/snowflake.pavlik.us\/index.php\/2019\/10\/03\/time-travel-zero-copy-clones-perpetual-table-history\/","name":"Preserving Table Change History Indefinitely - Snowflake in the Carolinas","isPartOf":{"@id":"https:\/\/snowflake.pavlik.us\/#website"},"datePublished":"2019-10-03T17:24:02+00:00","dateModified":"2020-01-26T20:51:38+00:00","author":{"@id":"https:\/\/snowflake.pavlik.us\/#\/schema\/person\/019455f4675665b6cf5edea31ec44d7b"},"breadcrumb":{"@id":"https:\/\/snowflake.pavlik.us\/index.php\/2019\/10\/03\/time-travel-zero-copy-clones-perpetual-table-history\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/snowflake.pavlik.us\/index.php\/2019\/10\/03\/time-travel-zero-copy-clones-perpetual-table-history\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/snowflake.pavlik.us\/index.php\/2019\/10\/03\/time-travel-zero-copy-clones-perpetual-table-history\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/snowflake.pavlik.us\/"},{"@type":"ListItem","position":2,"name":"Preserving Table Change History Indefinitely"}]},{"@type":"WebSite","@id":"https:\/\/snowflake.pavlik.us\/#website","url":"https:\/\/snowflake.pavlik.us\/","name":"Snowflake in the Carolinas","description":"Random thoughts on all things Snowflake in the Carolinas","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/snowflake.pavlik.us\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/snowflake.pavlik.us\/#\/schema\/person\/019455f4675665b6cf5edea31ec44d7b","name":"Greg Pavlik","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/d81df729eebf37a042922b17d4a4c834b1e0ccfa9fea1c2c78cb8e95c7e91701?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/d81df729eebf37a042922b17d4a4c834b1e0ccfa9fea1c2c78cb8e95c7e91701?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/d81df729eebf37a042922b17d4a4c834b1e0ccfa9fea1c2c78cb8e95c7e91701?s=96&d=mm&r=g","caption":"Greg Pavlik"},"description":"Greg is a Senior Sales Engineer at Snowflake Computing, in the Raleigh-Durham area. He's been in data management and security for the twenty years."}]}},"_links":{"self":[{"href":"https:\/\/snowflake.pavlik.us\/index.php\/wp-json\/wp\/v2\/posts\/165","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/snowflake.pavlik.us\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/snowflake.pavlik.us\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/snowflake.pavlik.us\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/snowflake.pavlik.us\/index.php\/wp-json\/wp\/v2\/comments?post=165"}],"version-history":[{"count":18,"href":"https:\/\/snowflake.pavlik.us\/index.php\/wp-json\/wp\/v2\/posts\/165\/revisions"}],"predecessor-version":[{"id":296,"href":"https:\/\/snowflake.pavlik.us\/index.php\/wp-json\/wp\/v2\/posts\/165\/revisions\/296"}],"wp:attachment":[{"href":"https:\/\/snowflake.pavlik.us\/index.php\/wp-json\/wp\/v2\/media?parent=165"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/snowflake.pavlik.us\/index.php\/wp-json\/wp\/v2\/categories?post=165"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/snowflake.pavlik.us\/index.php\/wp-json\/wp\/v2\/tags?post=165"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}