Snowflake Dynamic Tables

Snowflake Dynamic tables are released in Snowflake Summit ’23 and are available in private preview right now. Idea here is to simplify data engineering in Snowflake by providing a reliable, cost-effective, and automated way of building data pipelines.

Let us understand dynamic tables and associated use cases.

What is a Snowflake Dynamic Table?

A dynamic table is a table that materializes the results of a query. What this means is that instead of creating a permanent table and writing code to transform and update the data in that table, users define the target table as a dynamic table, along with the SQL statement for data transformation.

In Dynamic tables, for incremental data refresh, you don’t have to create a stream to track changes and tasks to apply the changes to the target table. Instead, the refresh process associated with dynamic tables does this automatically based on the query. This results in a very simple SQL to build Dynamic Tables instead of complex SQL that requires Streams and Tasks.

Let us create a Snowflake Dynamic table (EMPLOYEE_DETAILS) to extract EMPLOYEE_ID, EMPLOYEE_NAME, and ADDRESS as the data is streamed into a landing table (SAMPLE_DATA) containing sample JSON Data.

CREATE OR REPLACE TABLE SAMPLE_DATA (VAR VARIANT);

CREATE OR REPLACE DYNAMIC TABLE EMPLOYEE_DETAILS
LAG= ‘1 minute’
WAREHOUSE = demo_wh
AS
SELECT var: EID::int EMPLOYEE_ID,
var: NAME::string EMPLOYEE_NAME,
var:ADDRESS::string ADDRESS FROM SAMPLE_DATA;

When To Use Snowflake Dynamic Tables

• You don’t want any DML on the table- Dynamic table data is based on the results of a SQL query so users cannot change it using any DML statement.
• Build data transformation pipelines that require complex SQL.
• Build a pipeline of transformations instead of aggregations on one table.
• You need control over when tables are refreshed.
• You don’t want to write code to track data dependencies and manage data refresh.
• Avoid the complexity of streams and tasks.

When Not to Use Snowflake Dynamic Tables

• You need to incorporate UDFs/UDTFs, Stored Procedures, External Functions, Snowpark code, etc.
• Real-time data refresh.
• You need query rewrite/DML on the table.
• For basic aggregation on one table.
• To improve the performance of external tables.

Information contained in this channel are my personal views and Snowflake is in no way affiliated with the same.

Stay tuned for more updates!!!