Snowflake Dynamic Tables—Simple Way to Automate Data Pipeline

Snowflake has just unveiled its latest and most groundbreaking feature—the Snowflake Dynamic Tables. This new table type revolutionizes data pipeline creation, allowing Snowflake users/data engineers to use straightforward SQL statements to define their pipeline outcomes. Dynamic Tables stand out for their ability to refresh periodically, responding only to new data changes since the last refresh.TL;DR—Dynamic Tables simplify the creation and management of data pipelines, helping data teams to confidently build robust Snowflake data pipelines for production use cases.

In this article, we will provide a comprehensive overview of Snowflake Dynamic Tables. We will discuss what they are, their advantages, and their functionality. We will also compare them to Snowflake streams and tasks, and explain the overall benefits of using Snowflake Dynamic Tables.

How Do Snowflake Dynamic Tables Work?

Snowflake Dynamic Tables are a special type of table in Snowflake that are used to simplify data transformation pipelines. They are created by specifying a SQL query that defines the results of the table. The results of the query are then materialized into a dynamic table, which can then be used like any other table in Snowflake.

Snowflake Dynamic Tables have a number of advantages over traditional data transformation methods. They are declarative, meaning that the transformation logic is defined in the Snowflake SQL query, making them easier to understand and maintain. They are also automated, meaning that the dynamic table is refreshed automatically whenever the underlying data changes, which eliminates the need to write code to manage the refresh process.

Benefits and use cases of Snowflake Dynamic Tables:

• Snowflake Dynamic Tables can be used to create a wide variety of data transformation pipelines. They can be used to extract data from one or more sources, clean and transform data, enrich data with additional information, aggregate data, and publish data to other systems.
• Snowflake Dynamic Tables eliminate the need for manual code development in tracking data dependencies and managing manual data refreshes.
• Snowflake Dynamic Tables significantly reduces the complexity compared to using Snowflake streams and Snowflake tasks for data transformation.
• Snowflake Dynamic Tables support the materialization of query results derived from multiple base tables, enhancing data processing efficiency and simplicity.
• Dynamic Tables can be seamlessly integrated with Snowflake streams, providing additional flexibility.
• Snowflake Dynamic Tables are only charged for the storage and compute resources that they use.

Snowflake Dynamic Tables greatly streamline the process of creating and managing Snowflake data pipelines, thereby enabling users to create reliable, production-ready data pipelines. Initially introduced as "Materialized Tables" at the Snowflake Summit 2022, a name that caused some confusion, this feature has now been rebranded as Snowflake Dynamic Tables and is accessible across all accounts. In the past, users had to utilize Snowflake Streams and Snowflake Tasks, along with manual management of database objects (tables, Snowflake streams, Snowflake tasks, and Snowflake SQL DML code), to establish a Snowflake data pipeline. However, Snowflake Dynamic Tables have made the creation of data pipelines a whole lot easier!!

Check out the following diagram for more details:

Traditional methods of data transformation, such as Snowflake streams and Snowflake tasks, require defining a series of tasks and monitoring dependencies, and scheduling. In contrast, Snowflake Dynamic Tables allow you to define the end state of the transformation and leave the complex pipeline management to Snowflake and Snowflake alone.

Example of how you might use Snowflake streams and Snowflake tasks to transform data:

Snowflake Streams:

To create a Snowflake stream, you can use the CREATE OR REPLACE STREAM statement. Below is an example of how to create a Snowflake stream for a table called "my_table":

-- Creating a stream for table "my_table"
CREATE OR REPLACE STREAM my_stream
ON TABLE my_table;

As you can see, Snowflake stream is created on the existing table my_table. This stream will capture the changes (inserts, updates, and deletes) that occur on my_table and allow you to use it in combination with Snowflake tasks or other operations to track and process those changes.

Snowflake Tasks:

To create a Snowflake task, you can use the CREATE OR REPLACE TASK statement. Below is an example of how to create a Snowflake task called "my_task":

CREATE OR REPLACE TASK my_task
WAREHOUSE = my_warehouse
SCHEDULE = "5 minute"
WHEN SYSTEM$STREAM_HAS_DATA("my_stream")
AS
INSERT INTO my_destination_table
SELECT * FROM my_source_table;

As you can see, task is created with the following attributes:

• WAREHOUSE: Specifies the Snowflake warehouse
• SCHEDULE: Specifies the frequency at which the task should run
• WHEN: Defines the condition for the task to be triggered.
• AS: Specifies the SQL statement(s) to be executed when the task is triggered.

Snowflake tasks are used for scheduling and automating SQL operations. They can be combined with Snowflake streams to create powerful data integration and data processing pipelines.

Example of how you might use Snowflake Dynamic Tables to transform data:

Finally, to create a Snowflake Dynamic Tables, you can use the CREATE OR REPLACE DYNAMIC TABLE statement (as simple as that).

Below is an example of how to create a Snowflake Dynamic Tables called "my_dynamic_table":

CREATE OR REPLACE DYNAMIC TABLE my_dynamic_table
TARGET_LAG = ' { seconds | minutes | hours | days }'
WAREHOUSE = my_warehouse
AS
SELECT column1, column2, column3
FROM my_source_table;

As you can see, Snowflake Dynamic Tables is created with the following attributes:

• TARGET_LAG: Specifies the desired freshness of the data in the Snowflake Dynamic Tables. It represents the maximum allowable lag between updates to the base table and updates to the dynamic table.
• WAREHOUSE: Specifies the Snowflake warehouse to use for executing the query and managing the Snowflake Dynamic Tables.
• AS: Specifies the SQL query that defines the data transformation logic.

This is just the tip of the iceberg. We'll discuss this topic in more depth later on.

Creating and Using Snowflake Dynamic Tables

You can create dynamic tables just like regular tables, but there are some differences and certain limitations. If you change the tables, views, or other Snowflake Dynamic Tables used in a dynamic table query, it might alter how things work or even stop a Snowflake Dynamic Tables from working. Now, let"s discuss how to create dynamic tables and discuss some of the limitations and issues of Snowflake Dynamic Tables.

Creating a Snowflake Dynamic Table:

To create a Snowflake Dynamic Table, use the "CREATE DYNAMIC TABLE" command. You need to specify the query, the maximum delay of the data (TARGET_LAG), and the warehouse for the refreshes. For example, if you want to create a Snowflake Dynamic Tables called "employees" with “employee_id” and employee name columns from the “employee_staging_table”, there are a few steps to follow. First and foremost, you need to make sure that the data in the “employees” table is up-to-date. To do so, we need to specify the TARGET_LAG. In this example, we"ve added a target lag of at least 10 minutes behind the data in the  “employee_source_table”. This makes sure that any recent changes or additions to the data are reflected accurately in the "employees" table. Also, you need to specify/utilize your warehouse to handle the required computing resources for refreshing the data, whether it"s an incremental update or a full refresh of the table, which ensures that the necessary computational power is available to efficiently update the "employees" table.

To create this Snowflake Dynamic Table, run this SQL statement:

CREATE OR REPLACE DYNAMIC TABLE employees
TARGET_LAG = "10 minutes"
WAREHOUSE = warehouse_name
AS
SELECT employee_id, employee_first_name, employee_last_name FROM employee_source_table;

Like a materialized view, the columns in a dynamic table are determined by the columns specified in the SELECT statement used to create the table. For columns that are expressions, you need to specify aliases for the columns in the SELECT statement.

Make sure all objects used by the Snowflake Dynamic Tables query have change tracking enabled.

Note: If the query depends on another dynamic table, see the guidelines on choosing the target lag time.

Check out this video on building pipelines with Snowflake Dynamic Tables, which covers the step-by-step setup process, troubleshooting techniques, optimization strategies—and more.

What Privileges Are Required for Snowflake Dynamic Tables ?

To create and work with Snowflake Dynamic Tables, certain privileges are required:

• You need “USAGE” permission on the database and schema where you want to create the table.
• You need "CREATE DYNAMIC TABLE" permission on the schema where you plan to create the table.
• You need "SELECT" permission on the existing tables and views that you plan to use for the dynamic table.
• You need "USAGE" permission on the warehouse that you plan to use to update the table.

If you want to query a Snowflake Dynamic Tables or create a dynamic table that uses another dynamic table, you need "SELECT" permission on the dynamic table.

Source: Snowflake documentation

How do you drop a Snowflake Dynamic Tables?

Dropping Snowflake Dynamic Tables can be done in two ways: using Snowsight or through SQL commands. Here are the steps for each method:

Using Snowsight:

Step 1: Log into Snowflake snowsight.

Step 2: Select "Data" and then "Databases"

Step 3: In the left navigation, use the database object explorer to choose a database schema.

Step 4: On the schema details page, go to the "Dynamic Tables" tab.

Step 5: Click on the "More" menu located in the upper-right corner of the page.

Step 6: Select "Drop"

Using SQL:

To drop a Snowflake Dynamic Tables using SQL, simply execute the following DROP DYNAMIC TABLE SQL command:

DROP DYNAMIC TABLE employees;

When to Use Snowflake Dynamic Tables

Snowflake Dynamic Tables are best used in scenarios where data transformation needs to be automated and simplified. They are particularly useful when dealing with large volumes of data, where manual transformation would be time-consuming and error-prone.

They're particularly useful when:

• You don't want to write code to manage data updates and dependencies.
• You want to avoid the complexity of Snowflake streams and Snowflake tasks.
• You don't need to control the data refresh schedule in detail.
• You need to show the results of a query from multiple base tables.
• You don't need to use advanced query features like stored procedures, certain non-deterministic functions, or external functions.
  

Note: You can easily integrate Snowflake Dynamic Tables with Snowflake streams.

Source: Snowflake documentation

What query constructs are currently unsupported in Snowflake Dynamic Tables?

There are certain query constructs that you can't use with Snowflake Dynamic Tables. If you try to use these, you'll get an error. These include:

• External functions.
• Non-deterministic functions (except for some that are allowed).
• Shared tables
• External tables
• Materialized views
• Views on dynamic tables or other unsupported objects.

Source: Snowflake documentation

How to Monitor Snowflake Dynamic Tables?

There are a few ways to monitor Snowflake Dynamic Tables.

Using Snowsight:

You can use the Refresh History tab on the dynamic table details page in Snowsight to monitor the status of refreshes and the lag time for the dynamic table. You can also use the DAG view to see the dependencies between dynamic tables.

You can use the following steps to monitor Snowflake Dynamic Tables using Snowsight:

Step 1: Navigate to the Snowflake Dynamic Tables page in Snowsight, similar to the previous step where we dropped the Snowflake Dynamic Tables.

Step 2: Click on the name of the Snowflake Dynamic Tables that you want to monitor.

Step 3: On the Details tab, select the Refresh History tab. This tab will show you the status of the most recent refresh, as well as the lag time for the dynamic table.

Step 4: You can also use the Graph view to see the details of the Snowflake Dynamic Tables.

Using SQL

You can use the following SQL commands to monitor Snoflake dynamic tables:

Using SHOW DYNAMIC TABLES — This command lists all of the dynamic tables in your account, including its refresh history and lag time.

SHOW DYNAMIC TABLES;

Using DESCRIBE DYNAMIC TABLE —- This command provides detailed information about a specific dynamic table.

DESCRIBE DYNAMIC TABLE employees;

Using the SELECT * command. This command allows you to query a dynamic table to see its current data.

SELECT * FROM employees;

Differences Between Snowflake Dynamic Tables and Snowflake streams and tasks

Here is the differences between Snowflake Streams and Tasks and Snowflake Dynamic Tables:

Differences Between Snowflake Dynamic Tables and Materialized Views

Here is the differences between Snowflake Materialized Views and Snowflake Dynamic Tables:

Conclusion

Snowflake Dynamic Tables are a powerful tool that can simplify and automate the data engineering process. They offer several advantages over traditional methods, such as simplicity, declarative nature, and the ability to handle streaming data. If you are looking to improve the efficiency and effectiveness of your Snowflake data pipelines, Snowflake Dynamic Tables are a great option to consider. In this article, we covered what Snowflake Dynamic Tables are, their advantages, and their functionality. We also explored how Dynamic Tables differ from Snowflake's tasks and streams, with their imperative, procedural nature. Snowflake Dynamic Tables shine for their simplicity, relying on straightforward SQL to define pipeline outcomes rather than requiring manual scheduling and maintenance of tasks.

Snowflake Dynamic Tables are like the conductor of an orchestra, orchestrating the flow of data seamlessly. Just as a conductor guides each musician to create a harmonious symphony, Dynamic Tables simplify the data orchestration, ensuring smooth and efficient data pipeline performance.

FAQs

How do Snowflake Dynamic Tables work?

Snowflake Dynamic Tables work by allowing users to define pipeline outcomes using straightforward SQL statements. They refresh periodically and respond to new data changes since the last refresh.

What are the advantages of using Snowflake Dynamic Tables?

Snowflake Dynamic Tables offer several advantages including simplified data pipeline creation, periodic refreshes, and the ability to respond to new data changes.

What is the difference between Snowflake Dynamic Tables and Snowflake Streams and Tasks?

While Snowflake streams and tasks also aid in data management, Dynamic Tables stand out for their ability to simplify the creation and management of data pipelines and their adaptability to new data changes.

What are the three layers of snowflake architecture?

The three layers of the Snowflake architecture are storage, compute, and cloud services. Separating storage, compute and services provides flexibility and scalability.

What is the difference between transient table and permanent table in Snowflake?

Transient tables in Snowflake persist only until explicitly dropped, have no fail-safe period, and limited time travel. Permanent tables persist until dropped, have a 7-day fail-safe period, and larger time travel. Transient tables suit temporary data while permanent tables are for persistent data.

How do Snowflake Dynamic Tables improve Snowflake data pipeline creation?

Snowflake Dynamic Tables improve Snowflake data pipeline creation by allowing users to define pipeline outcomes using simple SQL statements, and by refreshing and adapting to new data changes.

Are Snowflake Dynamic Tables suitable for production use cases?

Yes, Snowflake Dynamic Tables are designed to help data teams confidently build robust Snowflake data pipelines suitable for production use cases.

How do Snowflake Dynamic Tables handle new data changes?

Snowflake Dynamic Tables handle new data changes by refreshing periodically and responding only to new data changes since the last refresh.

What table types are available in Snowflake?

Snowflake offers three types of tables: Temporary, Transient and Permanent.