Snowflake Dynamic Tables — A way forward to simplify data pipelines
Dynamic Tables are a new table kind in Snowflake (now in public preview announced via Snowflake Summit 2023) that use simple SQL statements for building blocks of declarative data transformation pipelines. Dynamic Tables provide a reliable, cost-effective, and automated way to transform data for consumption while the scheduling and orchestration needed to achieve this are transparently managed by Snowflake.
As a user instead of defining data transformation steps as a series of tasks and having to monitor dependencies and scheduling, you can simply define the end state of the transformation using dynamic tables and leave the complex pipeline management to Snowflake.
How to create Dynamic Table
CREATE [ OR REPLACE ] DYNAMIC TABLE <table_name>
TARGET_LAG = { ‘<num> { seconds | minutes | hours | days }’ | DOWNSTREAM }
WAREHOUSE = <warehouse_name>
AS <sql query>
Dynamic Tables 2 best use cases are :
1) Easy of use — Bypassing the complexity of streams and tasks
2) Materialize multi-tables data — Snowflake Materialized view currently has limitation of querying only single table. Dynamic tables materializes results of multiple base tables query.
Having said that Dynamic Tables doesn’t make Streams & Tasks or Materialized Views redundant in Snowflake. Here is the guide highlighting the situations based on which what snowflake service to use, along with the key differences between them.
Streams and Tasks
~ Need full control over incremental data processing
~ Need to incorporate UDFs/UDTFs, Stored Procedures, External Functions, and Snowpark transformations
~ Need flexibility around scheduling and dependency management
Materialized Views
~ View defined for performance needs of reporting or visualization tools as Query Rewrite is possible
~ Performance of External Tables
~ Access to latest data
Dynamic Tables
~ Building SQL based pipeline
~ Transformation requiring complex SQL involving Joins, Aggregates, Window Functions etc.
~ To have control over when tables are refreshed
Key Differences between Dynamic Tables Vs Streams and Tasks
Key Differences between Dynamic Tables Vs Materialized Views
Data engineers challenges to effectively process the incremental change data without complicated data orchestration and transformation process is comprehensively solved by Dynamic Tables. All of the database objects and DML management is automated by Snowflake, enabling data engineers to easily build scalable, performant, and cost-effective data pipelines on Snowflake.
For implementation of solutions using dynamic tables do refer to my follow up content at Snowflake Dynamic Tables — The Complete Guide
References:
https://docs.snowflake.com/en/user-guide/dynamic-tables-about
https://www.snowflake.com/blog/dynamic-tables-delivering-declarative-streaming-data-pipelines/
Follow and Clap if you like the content and feel free to ask if you have any questions in the comments. I will be more than happy to assist and guide you.
