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 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?
Time Travel allows this for up to 90 days, but we can keep that history indefinitely by making Zero Copy Clones. Here’s an example:
-- Snowflake's Zero Copy Cloning preserves table history for up to 90 days.
-- To preserve change history indefinitely, create a Zero Copy Clone
-- more frequently than the time travel retention period.
use role sysadmin;
use database TEST;
use schema SALES;
use warehouse TEST;
-- First, check how long Snowflake is set to preserve Time Travel data for the table
select RETENTION_TIME from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'SALES_FACT';
-- If necessary, increase the duration so you can clone it before Time Travel data rolls off.
alter table SALES_FACT set data_retention_time_in_days = 30;
-- Optionally, create a new schema to keep the historical clones. This will keep the base table
-- schema cleaner.
create schema if not exists SALES_FACT_HISTORY;
-- Run the remaining section as frequently as required to get the needed granularity of history
-- preservation. For critical tables in regulated industries, it may be necessary to create
-- a clone after every UPDATE or MERGE performed.
-- You'll need to create a dynamic table name, usually based on date.
set CLONE_TABLE_NAME = 'SALES_FACT_HISTORY.SALES_FACT_CLONE_' || to_varchar(CURRENT_DATE, 'YYYY_MM_DD');
-- Optionally check the name we'll use for the table
--select $CLONE_TABLE_NAME;
-- Use the identifier() SQL function to use a SQL variable as an object name.
-- Run this statement once a month, once a week, or any duration more frequent than the
-- table's Time Travel retention period.
create table identifier($CLONE_TABLE_NAME) clone SALES_FACT;
When preserving change history, there’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’s cloned. What does this mean in practice?
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’ll need to perform a clone after every update or merge that updates rows.
So I was able to set up the scripts to create daily snapshots of my table(s).
Now I want to create another table which logs the daily incremental changes on an individual record by the field.
My thought logic is to insert a row in to the audit table with the unique identifier (Id), the field which is different then the previous day (field), the date of the change (date), the previous day’s or old record (oldvalue), and the current day’s or new record (newvalue).
Thank you for your help.
That approach would work for a change capture on a daily basis. If you’d like to investigate capturing every change, not only snapshots on a daily basis, you may want to investigate using Snowflake Streams:
https://docs.snowflake.net/manuals/user-guide/streams.html
Have you all created any posts using streams? I am just looking for a better explanation than the product documentation.
I have not posted anything here yet, but I did give a presentation on Streams to the Charlotte user conference. I understand your point about the documentation; it’s intended to be more comprehensive than comprehensible. I’ll work on a use-case post for streams as my next topic. Thanks.
Hi Sean,
I have completed a post on how to use Snowflake streams. It discusses one of the most common use cases for streams, as an intermediary between a staging and production table. Since this is a straightforward use case, it’s a good one to use to explain how Snowflake implemented streams.
https://snowflake.pavlik.us/index.php/2020/01/12/snowflake-streams-made-simple/
Let me know if you have any questions.
Thanks,
-Greg