Snowflake Dynamic Tables — The Complete Guide

In continuation to previous post Snowflake Dynamic Tables — A way forward to simplify data pipelines let us deep dive into implementation aspects of creating and maintaining data pipelines using dynamic tables.

Step 1) Data Preparation

For this demonstration, I am using internal stage my_csv_stage to load the data into mycsvtable. I am placing the file manually into the stage periodically and allow the task load_task to copy the file into the table.

create database demo_db;

create or replace table mycsvtable (
id integer,
last_name string,
first_name string,
company string,
email string,
workphone string,
cellphone string,
streetaddress string,
city string,
postalcode string);

create or replace stage my_csv_stage;

create or replace file format mycsvformat
type = ‘CSV’
field_delimiter = ‘|’
skip_header = 1;

list @my_csv_stage;

CREATE OR REPLACE TASK load_task
Warehouse = ‘COMPUTE_WH’
Schedule = ‘10 Minute’
AS
copy into mycsvtable
from @my_csv_stage
file_format = (type = ‘CSV’ field_delimiter = ‘|’ skip_header = 1)
on_error = continue;

alter task load_task resume;

Step 2) Dynamic Table Creation

Created dynamic table contacts transforming the data from mycsvtable with lag of 1 minute.

CREATE OR REPLACE DYNAMIC TABLE contacts
TARGET_LAG = ‘1 minute’
WAREHOUSE = compute_wh
AS
select id, concat (first_name, ‘ ‘, last_name) as name, city
from mycsvtable
;

Step 3) Managing Dynamic Tables

Altering dynamic table to change lag from 1 minute to 15 mins

ALTER DYNAMIC TABLE contacts SET lag = ‘15 minute’;

The dynamic tables created can be seen under snowsight - Data → Databases.

To list dynamic tables under the database via SQL command in Worksheet

show dynamic tables;

To get information of the columns -

desc dynamic table <table_name>;

Step 4) Monitoring Dynamic Tables

Refresh History

Navigate to dynamic tables detailed page seen in Step 2) and select Refresh History tab.

It has information related to -
Data Freshness — The date and time as of when the dynamic table is currently up-to-date.
Target Lag — The target lag time for the dynamic table.
Maximum Refresh Lag (last 24h) — The longest actual lag time for the dynamic table during the given interval.

Note: The target lag changed from 1 minute to 15 minutes in Step 3) is also captured

Graph

Navigate to dynamic tables detailed page seen in Step 2) and select Graph tab.

The directed acyclic graph (DAG) gives information related to dynamic table dependencies and lag time.

The detailed pane gives additional details related to dynamic table, Lag and Refresh information.

We can get the above Refresh & Graph details from INFORMATION_SCHEMA table functions as well.

DYNAMIC_TABLE_REFRESH_HISTORY

DYNAMIC_TABLE_GRAPH_HISTORY

Step 5) Controlling Dynamic Tables

The refresh of dynamic tables can be additionally controlled using below operations:
Suspend — Suspend refreshes of a dynamic table.
Resume — Resume refreshes on a suspended dynamic table.
Refresh — Trigger a manual refresh of dynamic table.

ALTER DYNAMIC TABLE <name> SUSPEND;

When the dynamic table is suspended it’s information is updated in show dynamic tables

Can see an updated information in Refresh History tab

ALTER DYNAMIC TABLE <name> REFRESH;

Step 6) Data Validation & Conclustion

The related data from mycsvtable

The final data in dynamic table contacts

Can observe that the data in dynamic table is updated to that of source table with the transformations in place. As the new files come into the stage, the scheduled task loads the data into source table and the target dynamic table maintains the incremental transformation loads based on the lag time specified. This way data engineers can easily create and maintain the data pipelines.

Note: We cannot use dynamic tables to load the data from files. It will not support access from STAGE files. They are meant for pipelines within the Snowflake.

References:

https://docs.snowflake.com/en/user-guide/dynamic-tables-about

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.