Snowflake Dynamic Tables — Hands-on Lab
This article will experiment with Snowflake’s latest Dynamic tables to understand where it works and fails.
Executive Summary:
- Creating Dynamic Tables On Various Objects
- DDL Operations on Dynamic Tables
- Data Governance on Dynamic Tables
- Streams and Dynamic Tables
Creating Dynamic Tables On Various Objects:
We will begin with the basics and will experiment with what objects are not supported while creating the dynamic table:
- Temporary Table: Temporary tables reside within the session, hence it’s genuine that dynamic tables won’t be supporting them.
- Transient/ Permanent Table: As we expected, dynamic tables can be created on Transient tables or Permanent Tables.
- Shared Table: Unfortunately we may not be able to create dynamic tables on top of shared objects.
- Materialized View: Dynamic Tables do not support materialized views.
- Similarly, Dynamic Tables do not support External Tables.
- Views: You can create the dynamic tables on top of VIEWs but you can not create them if the view DDL makes use of another dynamic table.
DDL Operations on Dynamic Tables:
Let’s try out if dynamic tables have common DDL syntax just like permanent tables.
- Renaming Dynamic Table: Yes, we can rename the table.
BUT WAIT! See what happens when you alter the dynamic table
Even though it allows you to alter the dynamic table to rename it OR to rename the columns, it fails on refreshing the table. So dynamic table Does not support renaming, or changing the columns. Though the query works but the refresh fails.
Similarly, the alter statement allows us to apply the clustering key on the dynamic table but then the refresh fails.
Data Governance on Dynamic Tables:
Data Governance plays a major role in every organization in managing the PII Data or restricting data access from unwanted users. Let’s find out the support for Column Level Security or Row Level Security on Dynamic Tables.
- Masking Policy on Dynamic Table: If the underlying table gets assigned with the masking policy, we would require to recreate the dynamic table else the refresh fails.
So once we recreate it, the masking policy is visible on the dynamic tables.
Suppose we apply masking policy directly on the dynamic table. In that case, it works and the refresh runs successfully.
- Row Level Access Policy: Yes Dynamic Tables support direct application of row access policy on the column and refresh works too.
Streams and Dynamic Tables, a possible combination?
Yes, we can create streams with dynamic table, but it only accepts the incremental refresh.
- REFRESH: Streams can be created on any dynamic table, regardless of whether they refresh incrementally or fully. However, as a reminder, streams produce a set of events based on changes to the underlying table. When a dynamic table is refreshed, each refreshed row will produce a stream event. If the table experiences a full refresh a stream event or row will be generated for every row in the dynamic table.
- Stream type: Dynamic tables support only standard streams. Refer to Types of Streams for more information.
If you are new to dynamic tables and want to understand the concepts, how does it work? Then follow my latest article Snowflake Dynamic Tables — Continuous Data Pipeline
About Me:
Hi there! I am Divyansh Saxena
I am an experienced Data Engineer with a proven track record of success in Snowflake Data Cloud technology. Highly skilled in designing, implementing, and maintaining data pipelines, ETL workflows, and data warehousing solutions. Possessing advanced knowledge of Snowflake’s features and functionality, I work at IBM and am a Snowflake Data superhero & Snowflake Snowpro Core SME. With a major career in Snowflake Data Cloud, I have a deep understanding of cloud-native data architecture and can leverage it to deliver high-performing, scalable, and secure data solutions.
Follow me on Medium for regular updates on Snowflake Best Practices and other trending topics:
Also, I am open to connecting all data enthusiasts across the globe on LinkedIn:
