Authors - Ayushi Rawat & Sahil Adlakha
Introduction
This blog aims to walk you through the implementation of Snowflake's latest addition, Dynamic Table. Our objective is to create a declarative pipeline that incorporates Snowpipe streaming and culminates with the use of Dynamic tables.
If you haven't already read the previous blog in the Declarative Pipeline series on Snowpipe streaming, I highly recommend checking it out first.
Dynamic tables serve as fundamental building blocks in the declarative data pipeline. They offer a cost-effective and automated approach to streamline the data engineering process in Snowflake, simplifying transformation tasks. By using dynamic tables, the need to define and manage a series of transformation tasks with dependencies is eliminated. Instead, you can focus on writing the end state of transformation logic, leaving Snowflake to handle the complexities of pipeline management.
Absolutely fascinating! Now, let's delve into the blog to gain an in-depth understanding of Dynamic Tables, explore its implementation, and discover the additional benefits it has to offer.
Let’s first understand what you can expect from this blog.
What will be covered in this Blog?
What is Dynamic Tables
An explained example - How to create a Dynamic Table
How to DESCRIBE, SHOW, ALTER, DROP Dynamic Tables
Working of Dynamic Tables
When to Use Dynamic Tables
Dynamic Tables Privileges
Streams on Dynamic Tables
Dynamic Table vs. Streams & Tasks
Dynamic Table vs. Materialized Views
Let’s get started!
What are Dynamic Tables?
In a dynamic table, you can materialize the output of a query that you specify. Unlike the conventional approach of creating a separate target table and writing code to transform and store data, dynamic tables allow you to define the transformation logic or SQL statement directly within the table definition. The query's result will be stored in the table, and an automatic process will periodically refresh or materialize the results based on the schedule defined in the table definition. This automated process simplifies data transformation and keeps the table updated with the latest information.
Let's explore a simple example that will guide you through the creation steps of a dynamic table, making it easier to understand the process.
Example of Dynamic Tables
Let us create a dynamic table with name myDynamicTable:
SYNTAX:
CREATE [ OR REPLACE ] DYNAMIC TABLE <name>
TARGET_LAG = { '<num> { seconds | minutes | hours | days }' | DOWNSTREAM }
WAREHOUSE = <warehouse_name>
AS <query>
EXAMPLE:
CREATE OR REPLACE DYNAMIC TABLE myDynamicTable
TARGET_LAG = '5 minutes'
WAREHOUSE = mywarehouse
AS
SELECT customer_id, customer_name FROM myTable;
In this example:
The dynamic table materializes the results of the defined SQL and stores the 'customer_id' and 'customer_name' columns from the 'staging_table'
The target lag of 5 minutes ensures that the data in the dynamic table should always be within the last 5 minutes compared to the data in the 'staging_table'.
For the automated refresh process, the dynamic table utilizes the 'mywarehouse' warehouse to perform the refresh.
You can refer to the below screenshot for a better understanding.
Show Dynamic Tables
Warehouse is not required to execute the command. To view a dynamic table, the user must have a role with MONITOR privilege on the table.
SYNTAX:
SHOW DYNAMIC TABLES LIKE 'product_%' IN SCHEMA mydb.myschema;
In our case, let us see how we can use the same command to see our dynamic table.
EXAMPLE:
SHOW DYNAMIC TABLES LIKE 'myDynamicTable%' IN SCHEMA mydb.myschema;
You can refer to the below screenshot for a better understanding.
Describe Dynamic Tables
SYNTAX:
DESC[RIBE] DYNAMIC TABLE <name>;
In our case, let us see how we can use the same command to describe our dynamic table.
EXAMPLE:
DESC DYNAMIC TABLE myDynamicTable;
You can refer to the below screenshot for a better understanding.
In a similar manner, you can drop and alter a dynamic table in Snowflake.
Alter Dynamic Tables
To make changes to a dynamic table, the user must be using a role with OPERATE privilege granted on the dynamic table.
SYNTAX:
ALTER DYNAMIC TABLE <name> SET
[ TARGET_LAG = { '<num> { seconds | minutes | hours | days }' | DOWNSTREAM } ]
[ WAREHOUSE = <warehouse_name> ]
In our case, let us see how we can use the same command to alter our dynamic table.
EXAMPLE:
ALTER DYNAMIC TABLE myDynamicTable
SET
TARGET_LAG = '1 hour';
You can refer to the below screenshot for a better understanding.
Now finally let’s see how to drop the dynamic table.
Drop Dynamic Tables
Dropping a dynamic table requires the user to be using a role with OWNERSHIP privilege on the table.
SYNTAX:
DROP DYNAMIC TABLE <name>;
In our case, let us see how we can use the same command to drop our dynamic table.
EXAMPLE:
DROP DYNAMIC TABLE myDynamicTable;
You can refer to the below screenshot for a better understanding.
Working of Dynamic Tables
Let us try and understand the workings of dynamic tables.
When you create a dynamic table, you define a query that transforms data from one or more base or dynamic tables. An automated refresh process then regularly executes this query, updating the dynamic table with any changes made to the base tables.
The automated process calculates the modifications made to the base tables and incorporates these changes into the dynamic table. To accomplish this task, the process utilizes compute resources from the warehouse linked to the dynamic table.
When setting up a dynamic table, you define the desired data "freshness" or target lag. This indicates how up-to-date the data in the dynamic table should be compared to updates in the base table. For instance, you can specify that the dynamic table should not be more than 5 minutes behind the updates in the base table. The automated process then schedules refreshes accordingly to ensure that the data in the dynamic table remains within the specified target freshness (e.g., within 5 minutes of the base table updates).
When data freshness is not a critical factor, it is possible to opt for a longer target freshness time, which can help in reducing costs. For instance, if the data in the target table only needs to be within 1 hour of updates to the base tables, one can specify a target freshness of 1 hour instead of the previous 5 minutes, leading to potential cost savings.
When should you use Dynamic Tables?
In your data pipeline, there are several options for transforming data, such as using streams and tasks, CTAS (Create Table As Select), custom solutions, and dynamic tables. Dynamic tables are just one of the available approaches for data transformation.
Dynamic tables are best used for cases in which:
Avoid writing code to handle data dependencies and manage data refresh.
Prefer a simple solution and avoid the complexity of streams and tasks.
Don't require fine-grained control over the refresh schedule.
Need to materialize the results of a query involving multiple base tables.
Do not use unsupported dynamic query constructs like stored procedures, non-deterministic functions (not listed in "Non-Deterministic Functions Supported in Dynamic Tables"), or external functions.
Dynamic Tables Privileges
To create a dynamic table, the following privileges are necessary:
USAGE privilege on the database and schema where you intend to create the table.
CREATE DYNAMIC TABLE privilege on the schema where you plan to create the table.
SELECT privilege on the existing tables and views that you intend to query for the dynamic table.
USAGE privilege on the warehouse you plan to use for refreshing the table.
To perform queries on a dynamic table or create a dynamic table that queries another dynamic table, you must possess the following privileges:
SELECT on the dynamic table.
Streams and Dynamic Tables
Streams can be created on dynamic tables in a manner similar to how streams are applied to conventional tables. However, it's important to take note of the subsequent constraints:
REFRESH: Streams can be established on dynamic tables, irrespective of whether these tables undergo incremental or complete refresh. It's requisite to remember that streams generate event sets when modifications occur in the underlying table. In the event of a dynamic table refresh, every refreshed row will yield a stream event. In the case of a full table refresh, a stream event or row will be created for each individual row within the dynamic table.
Stream type: Only standard streams are supported by Dynamic tables. Refer to Types of Streams for more information.
https://docs.snowflake.com/en/user-guide/streams-intro#types-of-streams
Streams on Dynamic Tables Example is shown below.
SYNTAX:
-- Create the stream.
CREATE OR REPLACE STREAM deltaStream on DYNAMIC TABLE dtBase;
In our case, let us see how we can use the same command to see our dynamic table.
EXAMPLE:
-- Create the stream on dynamic tables.
CREATE OR REPLACE STREAM myStream
on DYNAMIC TABLE myDynamicTable;
You can refer to the below screenshot for a better understanding.
Dynamic Table vs. Streams & Tasks
Let us understand the difference between Dynamic Table and Streams & Tasks.
Dynamic tables employ a declarative approach where you define a query to specify the desired result. The data is then fetched and transformed from the base tables involved in the query.
On the other hand, tasks adopt an imperative approach, where you write procedural code to perform data transformations directly from the base tables.
The automated refresh process establishes the refresh schedule, ensuring the dynamic table meets the desired data freshness level.
You can schedule the code execution to transform the data.
While the SELECT statement for a dynamic table can involve joins, aggregations, window functions, and other SQL constructs, it cannot include calls to stored procedures and tasks. Additionally, currently, it cannot include calls to User-Defined Functions (UDFs) and external functions.
The procedural code is allowed to include calls to non-deterministic code, stored procedures, and other tasks. It can also contain calls to User-Defined Functions (UDFs) and external functions.
An automated refresh process carries out incremental refreshes of dynamic tables at regular intervals. The schedule for these refreshes is determined by the specified target "freshness" of the data.
Tasks have the ability to leverage streams to perform incremental data refreshes on target tables. You can conveniently schedule these tasks to execute at regular intervals.
Dynamic Table vs. Materialized Views
Let us understand the difference between Dynamic Table and Materialized Views.
Dynamic tables are specifically intended for transforming streaming data within a data pipeline. Even though dynamic tables can improve query performance, Snowflake's query optimizer doesn't automatically rewrite queries to take advantage of dynamic tables. To include a dynamic table in a query, you must explicitly specify it.
On the other hand, materialized views are designed to seamlessly enhance query performance. When querying the base table, Snowflake's query optimizer can automatically rewrite the query to access the materialized view instead, transparently improving performance.
A dynamic table allows you to build it upon a complex query, supporting joins and unions for data transformation.
In contrast, materialized views are limited to using a single base table and cannot be built on complex queries involving joins or nested views.
Regarding data freshness, a dynamic table reflects data up to the target lag time specified. On the other hand, data accessed through materialized views is always current. If any Data Manipulation Language (DML) operation modifies the base table, Snowflake updates the materialized view accordingly, ensuring the data remains up-to-date.
References:
Understanding Dynamic Table Refresh | Snowflake Documentation
Dynamic Tables Compared to Streams & Tasks, and Materialized Views | Snowflake Documentation
Conclusion:
With this, you are all set to implement Dynamic tables right away! Dynamic tables in Snowflake represent a remarkable advancement in data management and analytics. Their ability to adapt and evolve in real-time based on changing data needs empowers businesses to make more informed decisions. With features like automatic scaling, improved performance, and simplified maintenance, dynamic tables offer a streamlined approach to handling data growth and variability.