Snowflake Overview: Time travel, Tasks, Fail-safe, Streams, and Command Copy

Snowflake is a powerful data warehouse platform known for its scalability, elasticity, and ability to process large datasets efficiently. It has become one of the most popular solutions for cloud data storage and analysis. Snowflake offers a range of advanced features to help organizations with data management, including Time Travel, Tasks, Fail-safe, and Copy. In this article, we’ll provide an overview of these features and give examples of how to use them.

1. Time Travel

Time Travel is a feature in Snowflake that allows you to query historical data by viewing previous versions of tables, schemas, or databases. This is particularly useful for recovering lost data, auditing, or analyzing changes over time.

Snowflake uses a combination of metadata and data storage to enable Time Travel. The feature allows you to perform queries on past data with a defined retention period. The default retention period for Time Travel is 1 day, but it can be extended up to 90 days, depending on the account’s configuration.

Querying Historical Data

Suppose you have a table named sales_data, and you want to retrieve the data as it existed three days ago. Here's how you can do it:

SELECT * 
FROM sales_data 
AT (TIMESTAMP => '2024-12-24 10:00:00');

Alternatively, you can use TIME_SLICE to refer to a specific point in time relative to the current system time:

SELECT * 
FROM sales_data 
AT (OFFSET => -3);

This query will return the data as it appeared three days ago.

Time Travel Use Cases:

• Data Recovery: Restore lost or deleted data by accessing older versions of your tables.
• Audit Tracking: Keep track of changes in your data over time.
• Analyzing Changes: Compare the state of the data at different points in time.

2. Tasks

Snowflake Tasks automate the execution of SQL statements based on a defined schedule. Tasks are useful for performing periodic data processing operations such as data refreshes, maintenance, or other repetitive jobs. Tasks run asynchronously in the background, and you can chain them to create complex workflows.

Creating and Running a Task

Let’s create a task to refresh a sales_data table every day at midnight. The task will run an INSERT operation that extracts and loads fresh data from a staging table:

SHOW TASKS; -- you can use to check yours tasks

CREATE OR REPLACE TASK refresh_sales_data
  WAREHOUSE = my_warehouse
  SCHEDULE = 'USING CRON 0 0 * * * UTC'
  AS
    INSERT INTO sales_data
    SELECT * 
    FROM staging_sales_data;

-- enable task:
ALTER TASK refresh_sales_data RESUME;

Check this website to understand CRON TAB.

Once the task is created, it will run every day at midnight and update the sales_data table with new entries from the staging_sales_data table.

Task Use Cases:

• Data ETL (Extract, Transform, Load): Automate ETL jobs to regularly move data from one table to another.
• Scheduled Reporting: Automatically generate and store reports at specific intervals.
• Batch Processing: Perform bulk data operations like aggregation or data transformations on a scheduled basis.

Creating and Running a DAG Task

SHOW TASKS; -- you can use to check yours tasks

CREATE OR REPLACE TASK refresh_sales_data2
  WAREHOUSE = my_warehouse
  AFTER refresh_sales_data
  AS
    INSERT INTO sales_data2
    SELECT * 
    FROM staging_sales_data;

-- enable task:
ALTER TASK refresh_sales_data2 RESUME;
-- suspend task:
ALTER TASK refresh_sales_data2 SUSPEND;
-- check executions:
SELECT * FROM TABLE(INFORMATION_SCHEMA.task_history())

3. Fail-safe

Fail-safe is a Snowflake feature designed to provide additional data protection. When you delete or modify data in Snowflake, the data is typically still recoverable for a period through Time Travel. However, if the data is permanently deleted, it might be unrecoverable through Time Travel. This is where Fail-safe comes into play.

Fail-safe acts as a safety net for data that has been permanently deleted from the system and ensures that it can be recovered in case of critical issues. Snowflake provides a seven-day retention period for Fail-safe, during which the data can be restored by Snowflake support.
It is not directly accessible by users; it’s a mechanism intended for extreme cases. If data needs to be restored after the Time Travel period, the Snowflake support team can assist in recovering it from the Fail-safe storage.

Fail-safe comes into play after the Time Travel retention period expires. If a table sales_data is dropped and you cannot restore it via Time Travel (because it’s beyond the retention period), you would need to contact Snowflake support for fail-safe recovery.

4. Streams

In Snowflake, Streams are a powerful feature that helps you track changes made to a table over time. Streams enable Change Data Capture (CDC), which allows you to detect and capture inserts, updates, and deletes that happen in a source table. This is extremely useful for use cases like data replication, ETL (Extract, Transform, Load) processes, and auditing, as it lets you easily track and synchronize data changes without querying the entire table.

A Stream is essentially a metadata object that records the changes made to a table, storing information about the rows that were inserted, updated, or deleted since the last time the stream was read. Streams are particularly valuable when you need to perform incremental data processing without reloading the entire table.

Once a stream is created on a table, it keeps track of data changes and allows you to access these changes using SQL queries.

Key Features of Streams

1. Change Data Capture (CDC): Streams capture changes in source tables by tracking insert, update, and delete operations.
2. Incremental Processing: Instead of querying the entire table for changes, you can query the stream to get just the changes, making the process more efficient.
3. Automatic Cleanup: Streams do not store data indefinitely. After you query the stream and process the changes, Snowflake automatically clears the tracked changes.

Types of Streams in Snowflake

There are two types of streams:

• Standard Streams: Track changes to a table at a row level, capturing inserts, updates, and deletes.
• Append-Only Streams: Only track new rows inserted into a table. These streams do not capture updates or deletes.

Creating a Stream in Snowflake

To create a stream on a table, you use the CREATE STREAM command. Here’s an example of creating a stream on a table called sales_data:

CREATE OR REPLACE STREAM sales_data_stream
  ON TABLE sales_data
  SHOW_INITIAL_ROWS = TRUE;

In this example:

• sales_data_stream is the name of the stream.
• ON TABLE sales_data specifies that the stream is tracking changes on the sales_data table.
• SHOW_INITIAL_ROWS = TRUE ensures that the initial state of the table (before any changes) is included in the stream.

The SHOW_INITIAL_ROWS option is important when you're first creating the stream. It allows the stream to capture the current state of the table, which is useful if you're using the stream for incremental processing.

Once the stream is created, you can query it to see the changes (insertions, updates, and deletions) that have occurred since the last time the stream was queried.

SELECT * 
FROM sales_data_stream;

The result set will contain the changes, with metadata columns indicating the type of change:

• METADATA$ACTION: Indicates the action type (INSERT, DELETE).
• METADATA$ISUPDATE : Indicate if data was updated (FALSE, TRUE ).
• METADATA$ROW_ID: Represents the unique row identifier (useful for updates).
• METADATA$CHANGE_VERSION: The version number of the change.

A common pattern is to combine Streams with Tasks, while streams capture the changes, tasks can be scheduled to periodically process the changes and update other tables or trigger other processes.

5. Copy

Command Copy in Snowflake allows you to copy data from external sources (such as cloud storage) into Snowflake tables. It’s a highly efficient way of loading bulk data, and it supports multiple formats such as CSV, JSON, and Parquet.

The COPY INTO command is used to load data from external files into a Snowflake table, and it can handle large data volumes efficiently.

Using COPY INTO to Load Data

Suppose you have a file in an Amazon S3 bucket that you want to load into a Snowflake table named sales_data. Here’s an example of how to use the COPY INTO command:

COPY INTO sales_data
FROM {@my_s3_stage}
FILE_FORMAT = (TYPE = 'CSV' FIELD_OPTIONALLY_ENCLOSED_BY = '"')
ON_ERROR = 'CONTINUE';

In this example:

• @my_s3_stage refers to an external stage that points to the S3 bucket containing the data.
• The FILE_FORMAT option specifies the format of the files being loaded (in this case, CSV).
• The ON_ERROR = 'CONTINUE' clause ensures that errors are handled gracefully, allowing the load to continue even if some rows are problematic.

Command Copy Use Cases:

• Data Integration: Easily load large datasets from external storage (e.g., AWS S3, Azure Blob Storage) into Snowflake.
• Data Warehouse Population: Populate your data warehouse with structured and unstructured data.
• ETL Pipelines: Integrate external data into your ETL pipelines for processing.

Conclusion

Snowflake offers a range of powerful features that streamline data management, querying, and automation, making it a top choice for cloud data processing. Key features include Time Travel, which allows querying historical data; Streams, which enable real-time tracking of inserts, updates, and deletes without scanning entire tables, ideal for Change Data Capture (CDC), real-time analytics, and incremental ETL; Tasks, which automate workflows and can be combined with Streams for scalable data processing; Fail-safe, ensuring data recovery in critical scenarios; and Command Copy, which simplifies loading external data. These features, combined with Snowflake’s scalability and flexibility, help organizations optimize data processing, automate tasks, and ensure data consistency and security.