The Power of Dynamic Tables in Snowflake : A Hands-On Guide
INTRODUCTION:
The capability of dynamic tables is absolutely crucial for businesses that require real-time decision-making. These tables empower organizations to instantly organize, analyze, and visualize their data, enabling stakeholders to make informed decisions with up-to-the-minute insights.
Dynamic tables are the building blocks of declarative data transformation pipelines. It materializes the outcomes of a specified query. It is possible to designate the target table as a dynamic table and specify a SQL statement that conducts the transformation instead of creating a separate target table and writing code to transform and update the data in that table. Through frequent refreshes, an automated mechanism automatically changes the materialized results.
Imagine you’re handling a quickly changing resources; Dynamic Tables can be a game changer!
The steps for creating Dynamic tables using an automated approach is explained in this blog.
Overview:
1. Creating the source tables
2. Defining the Target dynamic table
3. Data insertion, modification, and deletion in source tables to observe changes in dynamic table.
Step 1:
Once logged in to Snowflake, open a new worksheet to execute the queries. Create two source tables(DY_SOURCE, DY_SOURCE1) from where the Dynamic table is going to consume the data.
CREATE OR REPLACE TABLE DY_SOURCE(
ID INT,
NAME VARCHAR,
AGE INT
);
CREATE OR REPLACE TABLE DY_SOURCE1(
ID INT,
PROD VARCHAR
);
Step 2:
Create a dynamic table called DY_SINK with a TARGET_LAG =1 minute so that it is supposed to update the query results with the given time.
CREATE OR REPLACE DYNAMIC TABLE DY_SINK
TARGET_LAG = ‘1 MINUTE’
WAREHOUSE = COMPUTE_WH
AS
SELECT D.ID, D.NAME, D1.PROD FROM DY_SOURCE D INNER JOIN DY_SOURCE1 D1 ON D.ID = D1.ID;
Target Lag:
It is the Target freshness of the data by which its query defined. For example, if we set target lag as 1 minute so that dynamic table tries to refresh the data within 1 minute. Ideally Dynamic table data should not be more than 1 minute older than source table data.
Query explanation:
This query will only return entries that matches by comparing the ID column of both the source tables using INNER JOIN.
Since columns for the dynamic table will be automatically generated based on query results, we won’t define them here.
Step 3:
Insert sample records into only one source table(DY_SOURCE)
INSERT INTO DY_SOURCE(ID, NAME) VALUES(1, ‘KUMAR’);
INSERT INTO DY_SOURCE(ID, NAME, AGE) VALUES(2, ‘VASEE’, 36);SELECT * FROM DY_SOURCE;
Inserting data into the DY_SOURCE table alone has no effect on the dynamic table DY_SINK since, as per the defined SQL statement, it will search for matching records between both of the source tables DY_SOURCE & DY_SOURCE1. Since no records are matching, no update in the dynamic table(DY_SINK).
Step 4:
By inserting data in another source table DY_SOURCE1 with the common ‘ID’ column value of DY_SOURCE which makes the dynamic table defined SQL statement with an updated record.
INSERT INTO DY_SOURCE1 VALUES(1, ‘APPLE’);
A new record is inserted in the table in accordance with the SQL query specified in the dynamic table.
Step 5:
Let’s try updating and deleting the record of same ID: 1 in source table which should get reflected in dynamic table as well.
UPDATING IN SOURCE TABLE:
UPDATE DY_SOURCE SET NAME = ‘AYYAPPAN’ WHERE ID = 1;
MODIFIED DYNAMIC TABLE:
DELETING FROM SOURCE TABLE:
DELETE FROM DY_SOURCE WHERE ID = 1;
MODIFIED DYNAMIC TABLE:
CONCLUSION:
As per the query defined for Dynamic table if any differences seen between the dynamic table records and query result records during the refresh lag period then that records will get updated in the table.
About Us
Bi3 has been recognized for being one of the fastest-growing companies in Australia. Our team has delivered substantial and complex projects for some of the largest organizations around the globe, and we’re quickly building a brand that is well-known for superior delivery.
Website: https://bi3technologies.com/
Follow us on,
LinkedIn: https://www.linkedin.com/company/bi3technologies
Instagram: https://www.instagram.com/bi3technologies/
Twitter: https://twitter.com/Bi3Technologies
