Dynamic Tables
Overview
Dynamic tables simplify data engineering in Snowflake by providing a reliable, cost-effective, and automated way to transform data.
Installation
- In Coalesce, open the Workspace where you wish to install the package.
- Go to the Build Setting of the Workspace, tab Packages, and click the Install button on the top right of the page.
- Paste the Package ID, and proceed with the installation process.
Description
Dynamic Tables Package
Package includes:
Dynamic Table Work
The Coalesce Dynamic Table Work UDN is a versatile node that allows you to develop and deploy a single Dynamic Table Work or a DAG of Dynamic Tables in Snowflake.
Dynamic tables are a new table type offered by Snowflake that allow data teams to use SQL statements to declaratively define the results of data pipelines. Dynamic tables simplify the process of creating and managing data pipelines by streamlining data transformations without having to manage Streams and Tasks.
Dynamic Table Work Node Configuration
The Dynamic Table Work has three configuration groups:
Dynamic Table Work Node Properties
| Property | Description |
|---|---|
| Storage Location | (Required) Storage Location where the Dynamic Table will be created |
| Node Type | (Required) Name of template used to create node objects |
| Description | A description of the node's purpose |
| Deploy Enabled | If TRUE the node will be deployed/redeployed when changes are detected If FALSE the node will not be deployed or will be dropped during redeployment |
Dynamic Table Work Options
| Option | Description |
|---|---|
| Warehouse | (Required) Name of warehouse used to refresh the Dynamic Table |
| Downstream | (Required) True/False toggle: - True: Refresh on demand when dependent tables need refresh - False: Set Lag Specification for refresh schedule |
| Lag Specification | Only if Downstream is False. Review Snowflakes Dynamic Tables Refresh to understand how to specify the target lag. Set refresh schedule with: - Time Value: Frequency of the refresh - Time Period: Seconds/Minutes/Hours/Days |
| Refresh_Mode | (Required) Specifies refresh type: - AUTO: Default incremental refresh. If the CREATE DYNAMIC TABLE statement does not support the incremental refresh mode, the dynamic table is automatically created with the full refresh mode. - INCREMENTAL: Force incremental refresh - FULL: Force full refresh |
| Initialize | (Required) Initial refresh behavior: - ON_CREATE: Refresh synchronously at creation - ON_SCHEDULE: Refresh at next scheduled time |
Dynamic Table Work Iceberg Options
| Option | Description |
|---|---|
| Snowflake EXTERNAL VOLUME name | Specifies the identifier (name) for the external volume where the Iceberg table stores its metadata files and data in Parquet format. External volume needs to be created in snowflake as a prerequisite. |
| Base location name | The path to a directory where Snowflake can write data and metadata files for the table. Specify a relative path from the table's EXTERNAL_VOLUME location. |
Dynamic Table Work General Options
| Option | Description |
|---|---|
| Distinct | True/False toggle to return DISTINCT rows |
| Group By All | True/False toggle to add non-aggregated columns to GROUP BY |
| Multi Source | True/False toggle for combining multiple sources via UNION or UNION ALL |
| Create As | Choose 'dynamic table' or 'transient dynamic table' |
| Cluster key | True/False toggle for clustering: - True: Specify clustering column and optional expressions - False: No clustering |
| Allow Expressions Cluster Key | When cluster key is set to true. Allows to add an expression to the specified cluster key |
Dynamic Table Work Deployment
Dynamic Table Work Initial Deployment Parameters
The Dynamic Table Work includes an environment parameter that allows you to specify a different warehouse to refresh a Dynamic Table in different environments.
The parameter name is targetDynamicTableWarehouse and the default value is DEV ENVIRONMENT.
When set to DEV ENVIRONMENT, the value entered in the Dynamic Table Options config "Warehouse on which to execute Dynamic Table" will be used when creating the Dynamic Table.
{
"targetDynamicTableWarehouse": "DEV ENVIRONMENT"
}
When set to any value other than DEV ENVIRONMENT the node will attempt to create the Dynamic Table using a Snowflake warehouse with the specified value.
For example, the Dynamic Table will refresh using a warehouse named compute_wh.
{
"targetDynamicTableWarehouse": "compute_wh"
}
Dynamic Table Work Initial Deployment
When deployed for the first time into an environment the Dynamic Table Work node will execute the following stage:
| Stage | Description |
|---|---|
| Create Work Dynamic Table/Dynamic Transient Table | This stage will execute a CREATE OR REPLACE statement and create a Dynamic Table in the target environment. |
Deploying a DAG of Dynamic Tables
When a DAG of related Dynamic Tables are deployed together Coalesce will deploy the Dynamic Tables in the order that the Dynamic Tables are ordered.
Dynamic Table Work Redeployment
After initial deployment, subsequent deployments may alter or recreate the Dynamic Table.
Altering the Dynamic Table Work
The following config changes trigger ALTER statements:
- Warehouse name
- Downstream setting
- Lag specification
These execute the two stages:
| Stage | Description |
|---|---|
| Alter Dynamic Table | Executes ALTER to modify parameters |
| Refresh Dynamic Table | Refreshes table to make data available |
Also if the location of the node, node name, column level description, and table level description results in an ALTER statement, whereas other column or table level changes like data type change, column name change, column addition/deletion result in a CREATE statement.
Changing Materialization Type From Dynamic Table to Transient Dynamic Table
If the materialization type changes from dynamic table to transient dynamic table and there are changes in dynamic table config options or table level changes that result in ALTER, the following steps are executed:
- Clone Work node
- Swap Work node
- Drop dynamic table
- Alter Dynamic Table
- Refresh Dynamic Table
- Apply Table Clustering(if cluster key option is provided)
- Resume Recluster Table(if cluster key option is provided)
If the materialization type changes from dynamic table to transient dynamic table and there are changes in dynamic table config options or table level changes that result in CREATE, the following steps are executed:
- Drop dynamic table
- Create Work dynamic transient table
- Apply Table Clustering(if cluster key option is provided)
- Resume Recluster Table(if cluster key option is provided)
Changing Materialization Type From Transient Dynamic Table to Dynamic Table
If the materialization type from transient dynamic table to dynamic table and there are changes in dynamic table config options, the following steps gets executed:
- Drop transient dynamic table
- Create Work dynamic table
- Apply Table Clustering(if cluster key option is provided)
- Resume Recluster Table(if cluster key option is provided)
Recreating the Dynamic Table
If anything changes other than the configuration options specified in Altering the Dynamic Table then the Dynamic Table will be recreated by running a CREATE OR REPLACE statement.
If the changes in node results in recreating the Dynamic table,then following stages are executed:
| Stage | Description |
|---|---|
| Drop table/transient table | Table is dropped before recreating in case the node name or location is changed |
| Create Work Dynamic table/Dynamic transient table | Dynamic table is created |
Redeploying a DAG of Dynamic Tables Work
If an entire DAG of Dynamic Tables has been deployed and changes are made to a deployed Dynamic Table Coalesce will only redeploy Dynamic Tables that have changed metadata.
Dynamic Tables Work Undeployment
A table will be dropped if all of these are true:
- The Dynamic Work Node is deleted from a Workspace.
- The Workspace is committed to Git.
- The Workspace committed to Git is deployed to a higher level environment.
| Stage | Description |
|---|---|
| Drop Dynamic Table | Removes table from target environment |
Dynamic Table Dimension
The Coalesce Dynamic Table Dimension UDN is a versatile node that allows you to develop and deploy a single Dynamic Table Dimension or a DAG of Dynamic Tables in Snowflake.
Dynamic tables are a new table type offered by Snowflake that allow data teams to use SQL statements to declaratively define the results of data pipelines. Dynamic tables simplify the process of creating and managing data pipelines by streamlining data transformations without having to manage Streams and Tasks.
Dimension Node Configuration
The Dynamic Table Dimension has four configuration groups:
Dimension Node Properties
| Property | Description |
|---|---|
| Storage Location | Storage Location where table will be created |
| Node Type | Name of template used to create node objects |
| Description | A description of the node's purpose |
| Deploy Enabled | If TRUE the node will be deployed/redeployed when changes are detected If FALSE the node will not be deployed or will be dropped during redeployment |
Dimension Table Options
| Option | Description |
|---|---|
| Warehouse | (Required) Name of warehouse used to refresh the Dynamic Table |
| Downstream | (Required) True/False toggle: - True: Refresh on demand when dependent tables need refresh - False: Set Lag Specification for refresh schedule |
| Lag Specification | Only if Downstream is False. Review Snowflakes Dynamic Tables Refresh to understand how to specify the target lag. Set refresh schedule with: - Time Value: Frequency of refresh for a given Time Period. - Time Period: Seconds/Minutes/Hours/Days |
| Refresh_Mode | (Required) Specifies refresh type: - AUTO: Default incremental refresh. If the CREATE DYNAMIC TABLE statement does not support the incremental refresh mode, the dynamic table is automatically created with the full refresh mode. - INCREMENTAL: Force incremental refresh - FULL: Force full refresh |
| Initialize | (Required) Initial refresh behavior: - ON_CREATE: Refresh synchronously at creation - ON_SCHEDULE: Refresh at next scheduled time |
Dimension Options
| Option | Description |
|---|---|
| Table keys | (Required) Business key columns for Dimension key formation |
| Record versioning | (Required) Type of column for history maintenance: - Datetime column - Date and Time column |
| Timestamp/sequence | Required if Datetime column chosen for Record versioning |
| Date/Timestamp Columns | Required if Date and Time columns chosen for Record versioning |
Dimension Iceberg Options
| Option | Description |
|---|---|
| Snowflake EXTERNAL VOLUME name | Specifies the identifier (name) for the external volume where the Iceberg table stores its metadata files and data in Parquet format. External volume needs to be created in snowflake as a prerequisite. |
| Base location name | The path to a directory where Snowflake can write data and metadata files for the table. Specify a relative path from the table's EXTERNAL_VOLUME location. |
Dimension General Options
| Option | Description |
|---|---|
| Create As | Choose 'dynamic table' or 'transient dynamic table' |
| Cluster key | True/False toggle for clustering: - True: Specify clustering column and optional expressions - False: No clustering |
| Allow Expressions Cluster Key | When cluster key is set to true. Allows to add an expression to the specified cluster key |
Dimension Deployment
Dimension Initial Deployment Parameters
The Dynamic Table Work includes an environment parameter that allows you to specify a different warehouse to refresh a Dynamic Table in different environments.
The parameter name is targetDynamicTableWarehouse and the default value is DEV ENVIRONMENT.
When set to DEV ENVIRONMENT, the value entered in the Dynamic Table Options config "Warehouse on which to execute Dynamic Table" will be used when creating the Dynamic Table.
{
"targetDynamicTableWarehouse": "DEV ENVIRONMENT"
}
When set to any value other than DEV ENVIRONMENT the node will attempt to create the Dynamic Table using a Snowflake warehouse with the specified value.
For example, the Dynamic Table will refresh using a warehouse named compute_wh.
{
"targetDynamicTableWarehouse": "compute_wh"
}
Dimension Initial Deployment
When deployed for the first time into an environment the Dynamic Table Work node will execute the following stage:
| Stage | Description |
|---|---|
| Create Dimension Dynamic Table/Dynamic Transient Table | This stage will execute a CREATE OR REPLACE statement and create a Dynamic Table in the target environment. |
Deploying a DAG of Dimension Tables
When a DAG of related Dynamic Tables are deployed together Coalesce will deploy the Dynamic Tables in the order that the Dynamic Tables are ordered.
Dimension Redeployment
After initial deployment, subsequent deployments may alter or recreate the Dynamic Table.
Altering the Dimension Table
The following config changes trigger ALTER statements:
- Warehouse name
- Downstream setting
- Lag specification
These execute the two stages:
| Stage | Description |
|---|---|
| Alter Dynamic Table | Executes ALTER to modify parameters |
| Refresh Dynamic Table | Refreshes table to make data available |
Also if the location of the node, node name, column level description, and table level description results in an ALTER statement, whereas other column or table level changes like data type change, column name change, column addition/deletion result in a CREATE statement.
Changing Materialization Type From Dimension Table to Transient Dimension Table
If the materialization type changes from dynamic table to transient dynamic table and there are changes in dynamic table config options or table level changes that result in ALTER, the following steps are executed:
- Clone Dimension node
- Swap Dimension node
- Drop Dynamic table
- Alter Dynamic Table
- Refresh Dynamic Table
- Apply Table Clustering(if cluster key option is provided)
- Resume Recluster Table(if cluster key option is provided)
If the materialization type changes from dynamic table to transient dynamic table and there are changes in dynamic table config options or table level changes that result in CREATE, the following steps are executed:
- Drop dynamic table
- Create dimension dynamic transient table
- Apply Table Clustering(if cluster key option is provided)
- Resume Recluster Table(if cluster key option is provided)
Changing Materialization Type From Transient Dynamic Dimension Table to Dynamic Table
If the materialization type from transient dynamic table to dynamic table and there are changes in dynamic table config options, the following steps gets executed:
- Drop transient dimension table
- Create Work dimension table
- Apply Table Clustering(if cluster key option is provided)
- Resume Recluster Table(if cluster key option is provided)
Recreating the Dimension Table
If anything changes other than the configuration options specified in Altering the Dimension Table then the Dynamic Table will be recreated by running a CREATE OR REPLACEstatement.
If the changes in node results in recreating the Dynamic table, then following stages are executed:
| Stage | Description |
|---|---|
| Drop table/transient table | Table is dropped before recreating in case the node name or location is changed |
| Create Work Dynamic table/Dynamic transient table | Dynamic table is created |
Redeploying a DAG of Dimension
If an entire DAG of Dynamic Tables has been deployed and changes are made to a deployed Dynamic Table Coalesce will only redeploy Dynamic Tables that have changed metadata.
Dimension Tables Work Undeployment
A table will be dropped if all of these are true:
- The Dynamic Dimension Node is deleted from a Workspace.
- The Workspace is committed to Git.
- The Workspace committed to Git is deployed to a higher level environment.
| Stage | Description |
|---|---|
| Drop Dynamic Table | Removes table from target environment |
Dynamic Table Latest Record Version
The Coalesce Dynamic Table Latest Record Version UDN is a versatile node that allows you to develop and deploy a single Dynamic Table Work or a DAG of Dynamic Tables with only the latest version of rows in Snowflake.
Dynamic tables are a new table type offered by Snowflake that allow data teams to use SQL statements to declaratively define the results of data pipelines. Dynamic tables simplify the process of creating and managing data pipelines by streamlining data transformations without having to manage Streams and Tasks.
Latest Record Version Node Configuration
The Dynamic Table Dimension has four configuration groups:
Latest Record Version Node Properties
| Property | Description |
|---|---|
| Storage Location | Storage Location where table will be created |
| Node Type | Name of template used to create node objects |
| Description | A description of the node's purpose |
| Deploy Enabled | If TRUE the node will be deployed/redeployed when changes are detected If FALSE the node will not be deployed or will be dropped during redeployment |
Latest Record Version Options
| Options | Description |
|---|---|
| Table keys | The business keys columns based on which the Dimension key is formed. (required) |
| Record versioning | Type of column based on which columns that maintain history are updated. (required) |
| Datetime column | Date and Time column |
| Timestamp or sequence | The timestamp column name needs to be specified if Datetime column is chosen for Record versioning. |
| Date/Timestamp Columns | Date column, time column, and sort order of the columns to be specified if Date column and Time column is chosen for Record versioning. |
Latest Record Version Iceberg Options
| Option | Description |
|---|---|
| Snowflake EXTERNAL VOLUME name | Specifies the identifier (name) for the external volume where the Iceberg table stores its metadata files and data in Parquet format. External volume needs to be created in snowflake as a prerequisite. |
| Base location name | The path to a directory where Snowflake can write data and metadata files for the table. Specify a relative path from the table's EXTERNAL_VOLUME location. |
Latest Record Version General Options
| Option | Description |
|---|---|
| Create As | Choose 'dynamic table' or 'transient dynamic table' |
| Cluster key | True/False toggle for clustering: - True: Specify clustering column and optional expressions - False: No clustering |
| Allow Expressions Cluster Key | When cluster key is set to true. Allows to add an expression to the specified cluster key |
DAG of Dynamic Table Latest Record Version
When designing DAG of Dynamic tables, you should specify the target lag. Review Understanding dynamic table refresh - Snowflake
Latest Record Version Deployment
Latest Record Version Initial Deployment Parameters
The Dynamic Table Work includes an environment parameter that allows you to specify a different warehouse to refresh a Dynamic Table in different environments.
The parameter name is targetDynamicTableWarehouse and the default value is DEV ENVIRONMENT.
When set to DEV ENVIRONMENT, the value entered in the Dynamic Table Options config "Warehouse on which to execute Dynamic Table" will be used when creating the Dynamic Table.
{
"targetDynamicTableWarehouse": "DEV ENVIRONMENT"
}
When set to any value other than DEV ENVIRONMENT the node will attempt to create the Dynamic Table using a Snowflake warehouse with the specified value.
For example, the Dynamic Table will refresh using a warehouse named compute_wh.
{
"targetDynamicTableWarehouse": "compute_wh"
}
Latest Record Version Initial Deployment
When deployed for the first time into an environment the Dynamic Table Work node will execute the following stage:
| Stage | Description |
|---|---|
| Create Dimension Dynamic Table/Dynamic Transient Table | This stage will execute a CREATE OR REPLACE statement and create a Dynamic Table in the target environment. |
Deploying a DAG of Latest Record Version Tables
When a DAG of related Dynamic Tables are deployed together Coalesce will deploy the Dynamic Tables in the order that the Dynamic Tables are ordered.
Latest Record Version Redeployment
After initial deployment, subsequent deployments may alter or recreate the Dynamic Table.
Altering the Latest Record Version Table
The following config changes trigger ALTER statements:
- Warehouse name
- Downstream setting
- Lag specification
These execute the two stages:
| Stage | Description |
|---|---|
| Alter Dynamic Table | Executes ALTER to modify parameters |
| Refresh Dynamic Table | Refreshes table to make data available |
Also if the location of the node, node name, column level description, and table level description results in an ALTER statement, whereas other column or table level changes like data type change, column name change, column addition/deletion result in a CREATE statement.
Changing Materialization Type From Latest Record Version Table to Transient Dimension Table
If the materialization type changes from dynamic table to transient dynamic table and there are changes in dynamic table config options or table level changes that result in ALTER, the following steps are executed:
- Clone Work node
- Swap Work node
- Drop Dynamic table
- Alter Dynamic Table
- Refresh Dynamic Table
- Apply Table Clustering(if cluster key option is provided)
- Resume Recluster Table(if cluster key option is provided)
If the materialization type changes from dynamic table to transient dynamic table and there are changes in dynamic table config options or table level changes that result in CREATE, the following steps are executed:
- Drop dynamic table
- Create Work dynamic transient table
- Apply Table Clustering(if cluster key option is provided)
- Resume Recluster Table(if cluster key option is provided)
Changing Materialization Type From Transient Dynamic Dimension Table to Latest Record Version
If the materialization type from transient dynamic table to dynamic table and there are changes in dynamic table config options, the following steps gets executed:
- Drop transient dimension table
- Create Work dimension table
- Apply Table Clustering(if cluster key option is provided)
- Resume Recluster Table(if cluster key option is provided)
Recreating the Latest Record Version Table
If anything changes other than the configuration options specified in Altering the Latest Record Version Table, then the Dynamic Table will be recreated by running a CREATE OR REPLACEstatement.
If the changes in node results in recreating the Dynamic table, then following stages are executed:
| Stage | Description |
|---|---|
| Drop table/transient table | Table is dropped before recreating in case the node name or location is changed |
| Create Work Dynamic table/Dynamic transient table | Dynamic table is created |
Redeploying a DAG of Latest Record Version
If an entire DAG of Dynamic Tables has been deployed and changes are made to a deployed Dynamic Table Coalesce will only redeploy Dynamic Tables that have changed metadata.
Latest Record Version Work Undeployment
A table will be dropped if all of these are true:
- The Dynamic Dimension Node is deleted from a Workspace.
- The Workspace is committed to Git.
- The Workspace committed to Git is deployed to a higher level environment.
| Stage | Description |
|---|---|
| Drop Dynamic Table | Removes table from target environment |
Code
Dynamic Table Work Code
Dynamic Table Dimension Code
Dynamic Table Latest Record Version Code
Macros
Versions
Available versions of the package.
| Version # | Release Date | Notes |
|---|---|---|
| 1.1.6 | November 20, 2024 | |
| 1.1.5 | November 13, 2024 | |
| 1.1.4 | October 25, 2024 | |
Support
If you need help, please see our support section or contact us.