Dynamic Tables

‹ View all packages

Latest Version: 

1.1.5

Release Date: 

November 13, 2024

Package ID: 

@coalesce/dynamic-tables 

Coalesce Certified Package

Overview

Dynamic tables simplify data engineering in Snowflake by providing a reliable, cost-effective, and automated way to transform data.

Installation

• Copy the Package ID  @coalesce/dynamic-tables
• 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

The Dynamic Tables Package includes:

• Dynamic Table Work
• Dynamic Table Dimension
• Dynamic Table Latest Record Version
• Code

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 Dimension has three configuration groups:

• Node Properties
• Dynamic Table Options
• Dynamic Iceberg Table Options
• General Options

Go to the node and select the Config tab to see the Node Properties, Dynamic Table Options and General Options.

Dynamic Table Work Node Properties

There are four configs within the Node Properties group.

• 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(required):
  • If TRUE the node will be deployed or redeployed when changes are detected.
  • If FALSE the node will not be deployed or the node will be dropped during redeployment.

Dynamic Table Options

There are three configs within the Dynamic Table Options group.

• Warehouse on which to execute Dynamic Table (required): The name of the warehouse used to refresh the Dynamic Table.
• Downstream(required): True or False Toggle to determine Dynamic Table refresh schedule.
  • True - Specifies that the dynamic table should be refreshed on demand when other dynamic tables that depend on it need to refresh.
  • False - Allows you to set a Lag Specification for the Dynamic Table refresh.
• Lag Specification: If the Downstream option is set to false you can set the refresh schedule using Lag Specification. Review Snowflakes Dynamic Tables Refresh to understand how to specify the target lag.
  • Time Value: Number representing the frequency of refresh for a given Time Period.
  • Time Period: Seconds / Minutes / Hours Days related to the entered Time Value.
• Refresh_Mode(required): Specifies the refresh type for the dynamic table.
  • AUTO: Enforces an incremental refresh of the dynamic table by default. 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: Enforces an incremental refresh of the dynamic table.
  • FULL: Enforces a full refresh of the dynamic table
• Initialize(required): Specifies the behavior of the initial refresh of the dynamic table.
  • ON_CREATE: Refreshes the dynamic table synchronously at creation
  • ON_SCHEDULE: Refreshes the dynamic table at the next scheduled refresh.

Dynamic Iceberg Options

• 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.

General Options

There are three configs related to the General Options group.

• Distinct: True or False toggle that specifies whether or not to return DISTINCT rows.
• Group By All: True or False toggle that specifies whether or not to add non-aggregated columns to GROUP BY.
• Multi Source: True or False toggle that specifies whether or not multiple sources will be combined using either UNION or UNION ALL.
• Create As:Provides option to choose materialization type as ‘dynamic table’ or ‘transient dynamic table’
• Cluster key: True/False to determine whether Dimension is to be clustered or not
  • True -Allows you to specify the column based on which clustering is to be done. -Allow Expressions Cluster Key-True ->allows to add an expression to the specified cluster key
  • False – No clustering done

Dynamic Table Deployment

Dynamic Table Deployment Parameters

The Dynamic Table Work includes an environment parameter that allows you to specify a different warehouse used 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 Initial Deployment

When deployed for the first time into an environment the Dynamic Table Work node will execute the following stage:

• 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 Redeployment

After the Dynamic Table Work has deployed for the first time into a target environment, subsequent deployments may result in either altering the Dynamic Table or recreating the Dynamic table.

If a Dynamic Table is altered this will run two stages:

• Alter Dynamic Table:This stage will execute an ALTER statement and alter the Dynamic Table in the target environment setting the new parameters.
• Refresh Dynamic Table:This stage will refresh the Dynamic Table so that data is available after refresh is complete.

Altering the Dynamic Table

There are three config changes that if made in isolation or all-together will result in an ALTER statement to modify the Dynamic Table in the target environment.

• Warehouse Name
• Downstream
• Lag Specification

Also if the location of the node,node name,column level description and table level description results in an ALTERstatement whereas the other column or table level changes like data type change,column name change,column addition/deletion results in a CREATE statement

Changing Materialization Type and Dynamic Table Config Options

Changing Materialization Type From Dynamic Table to Transient Dynamic Table

If the materialization type from dynamic table to transient dynamic table and also if there are changes in dynamic table config options or table level changes that result in ALTER ,the following steps gets executed:

1. Clone Work node
2. Swap Work node
3. Drop dynamic table
4. Alter Dynamic Table
5. Refresh Dynamic Table
6. Apply Table Clustering(if cluster key option is provided)
7. Resume Recluster Table(if cluster key option is provided)

If the materialization type from dynamic table to transient dynamic table and also if there are changes in dynamic table config options or table level changes that result in CREATE,the following steps gets executed:

1. Drop dynamic table
2. Create Work dynamic transient table
3. Apply Table Clustering(if cluster key option is provided)
4. 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 also if there are changes in dynamic table config options ,the following steps gets executed:

1. Drop transient dynamic table
2. Create Work dynamic table
3. Apply Table Clustering(if cluster key option is provided)
4. 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

• 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

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 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.

This is executed as a single stage:

Drop Dynamic Table

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.

Dynamic Table Dimension Node Configuration

The Dynamic Table Dimension has three configuration groups:

• Node Properties
• Dynamic Table Options
• Dimension Options
• Dynamic Iceberg Table Options
• General Options

Go to the node and select the Config tab to see the Node Properties, Dynamic Table Options and General Options.

Node Properties

• Storage Location: Storage Location where the WORK 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.

Dynamic Table Options

There are three configs within the Dynamic Table Options group.

• Warehouse on which to execute Dynamic Table (required): The name of the warehouse used to refresh the Dynamic Table.
• Downstream(required): True or False Toggle to determine Dynamic Table refresh schedule.
  • True - Specifies that the dynamic table should be refreshed on demand when other dynamic tables that depend on it need to refresh.
  • False - Allows you to set a Lag Specification for the Dynamic Table refresh.
• Lag Specification: If the Downstream option is set to false you can set the refresh schedule using Lag Specification. Review Snowflakes Dynamic Tables Refresh to understand how to specify the target lag.
  • Time Value: Number representing the frequency of refresh for a given Time Period.
  • Time Period: Seconds / Minutes / Hours Days related to the entered Time Value.
• Refresh_Mode(required): Specifies the refresh type for the dynamic table.
  • AUTO: Enforces an incremental refresh of the dynamic table by default. 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: Enforces an incremental refresh of the dynamic table.
  • FULL: Enforces a full refresh of the dynamic table
• Initialize(required): Specifies the behavior of the initial refresh of the dynamic table.
  • ON_CREATE: Refreshes the dynamic table synchronously at creation
  • ON_SCHEDULE: Refreshes the dynamic table at the next scheduled refresh.

Dimension Options

There are three configs within the Dimension group.

• Table keys (required): The business keys columns based on which the Dimension key is formed.
• Record versioning (required): Type of column based on which columns that maintain history are updated.
  • 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.

Dynamic Iceberg Options

• 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.

General Options

There are two configs related to the General Options group.

• Create As:Provides option to choose materialization type as ‘dynamic table’ or ‘transient dynamic table’ or ‘iceberg dynamic table’.
• Cluster key: True/False to determine whether Dimension is to be clustered or not.
  • True - Allows you to specify the column based on which clustering is to be done.
• Allow Expressions Cluster Key- True allows to add an expression to the specified cluster key.
  • False – No clustering done.Not supported for iceberg dynamic table.

Dynamic Table Deployment

Dynamic Table Deployment Parameters

The Dynamic Table includes an environment parameter that allows you to specify a different warehouse used 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 Initial Deployment

When deployed for the first time into an environment the Dynamic Table Work node will execute the following stage:

• 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 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 Redeployment

After the Dynamic Table Dimension has deployed for the first time into a target environment, subsequent deployments may result in either altering the Dynamic Table or recreating the Dynamic table.

If a Dynamic Table is altered this will run two stages:

• Alter Dynamic Table:This stage will execute an ALTER statement and alter the Dynamic Table in the target environment setting the new parameters.
• Refresh Dynamic Table:This stage will refresh the Dynamic Table so that data is available after refresh is complete.

Altering the Dynamic Table

There are three config changes that if made in isolation or all-together will result in an ALTER statement to modify the Dynamic Table in the target environment.

• Warehouse Name
• Downstream
• Lag Specification

Also if the location of the node,node name,column level description and table level description results in an ALTERstatement whereas the other column or table level changes like data type change,column name change,column addition/deletion results in a CREATE statement

Changing Materialization Type and Dynamic Table Config Options

Changing Materialization Type From Dynamic Table to Transient Dynamic Table

If the materialization type from dynamic table to transient dynamic table and also if there are changes in dynamic table config options or table level changes that result in ALTER ,the following steps gets executed:

1. Clone Dimension node
2. Swap Dimension node
3. Drop dynamic table
4. Alter Dynamic Table
5. Refresh Dynamic Table
6. Apply Table Clustering(if cluster key option is provided)
7. Resume Recluster Table(if cluster key option is provided)

If the materialization type from dynamic table to transient dynamic table and also if there are changes in dynamic table config options or table level changes that result in CREATE,the following steps gets executed:

1. Drop dynamic table
2. Create Dimension dynamic transient table
3. Apply Table Clustering(if cluster key option is provided)
4. 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 also if there are changes in dynamic table config options ,the following steps gets executed:

1. Drop transient dynamic table
2. Create Dimension dynamic table
3. Apply Table Clustering(if cluster key option is provided)
4. 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

• Drop table/transient table:Table is dropped before recreating in case the node name or location is changed
• Create Dimension Dynamic table/Dynamic transient table:Dynamic table is created

Redeploying a DAG of Dynamic Tables

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 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.

This is executed as a single stage:

Drop Dynamic Table

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.

Dynamic Table Latest Record Version Node Configuration

The Dynamic Table Latest Record Version has three configuration groups:

• Node Properties
• Record Versioning Options
• Dynamic Iceberg Table Options
• General Options

Go to the node and select the Config tab to see the Node Properties, Dynamic Table Options and General Options.

Node Properties

• Storage Location: Storage Location where the WORK 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.

Dynamic Table Latest Record Version Record Versioning Options

There are three configs within the Record Versioning Options group.

• Table keys (required): The business keys columns based on which the Dimension key is formed.
• Record versioning (required): Type of column based on which columns that maintain history are updated.
  • 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.

Dynamic Iceberg Options

• 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 Latest Record Version General Options

There are two configs related to the General Options group.

• Create As:Provides option to choose materialization type as ‘dynamic table’ or ‘transient dynamic table.

• Cluster key: True/False to determine whether Dimension is to be clustered or not

  • True -Allows you to specify the column based on which clustering is to be done.

• Allow Expressions Cluster Key- True, allows to add an expression to the specified cluster key.

  • False – No clustering done.

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

Dynamic Table Latest Record Version Deployment

Dynamic Table Latest Record Version Deployment Parameters

The Dynamic Table includes an environment parameter that allows you to specify a different warehouse used 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 Latest Record Version Initial Deployment

When deployed for the first time into an environment the Dynamic Table Latest Record Version node will execute the below stage:

• Create Dynamic Work Table/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 Table Latest Record Version

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 Latest Record Version Redeployment

After the Dynamic Table Latest Record Version has deployed for the first time into a target environment, subsequent deployments may result in either altering the Dynamic Table or recreating the Dynamic table.

If a Dynamic Table is to be altered this will run two stages:

• Stage 1: Alter Dynamic Table
  • This stage will execute an ALTER statement and alter the Dynamic Table in the target environment setting the new parameters.
• Stage 2: Refresh Dynamic Table
  • This stage will refresh the Dynamic Table so that data is available for analysis as soon as the refresh is complete.

Altering the Dynamic Table Latest Record Version

There are three config changes that if made in isolation or all-together will result in an ALTER statement to modify the Dynamic Table in the target environment.

• Warehouse Name
• Downstream
• Lag Specification

Also if the location of the node,node name,column level description and table level description results in an ALTERstatement whereas the other column or table level changes like data type change,column name change,column addition/deletion results in a CREATE statement

Changing Materialization Type and Dynamic Table Version Record Config Options

Changing Materialization Type From Dynamic Table to Transient Dynamic Table Version Record

If the materialization type from dynamic table to transient dynamic table and also if there are changes in dynamic table config options or table level changes that result in ALTER ,the following steps gets executed:

1. Clone Work node
2. Swap Work node
3. Drop dynamic table
4. Alter Dynamic Table
5. Refresh Dynamic Table
6. Apply Table Clustering(if cluster key option is provided)
7. Resume Recluster Table(if cluster key option is provided)

If the materialization type from dynamic table to transient dynamic table and also if there are changes in dynamic table config options or table level changes that result in CREATE,the following steps gets executed:

1. Drop dynamic table
2. Create Work Dynamic transient table
3. Apply Table Clustering(if cluster key option is provided)
4. Resume Recluster Table(if cluster key option is provided)

Changing Materialization Type From Transient Dynamic Table to Dynamic Table Version Record

If the materialization type from transient dynamic table to dynamic table and also if there are changes in dynamic table config options ,the following steps gets executed

1. Drop transient dynamic table
2. Create Work dynamic table
3. Alter Dynamic Table
4. Refresh Dynamic Table
5. Apply Table Clustering(if cluster key option is provided)
6. Resume Recluster Table(if cluster key option is provided)

Recreating the Dynamic Table Version Record

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

• 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 Table Version Record

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 Table Latest Record Version Undeployment

A table will be dropped if all of these are true:

• The Dynamic Table Latest Record Version Node is deleted from a Workspace.
• The Workspace is committed to Git.
• The Workspace committed to Git is deployed to a higher level environment.

This is executed as a single stage:

• Drop Dynamic Table

Code

Dynamic Table Work

• Node definition
• Create Template

Dynamic Table Dimension

• Node definition
• Create Template

Dynamic Table Latest Record Version

• Node definition
• Create Template

Macros

• Macros

Versions

Available versions of the package.

Version #	Release Date	Notes
1.1.5	November 13, 2024	Fix for escape characters in column and table description
1.1.4	October 25, 2024	Support for Dynamic Iceberg Table Options
1.1.3	August 19, 2024	Alter statements for node name/location change, column description supported
1.1.2	July 26, 2024	Bug fix for alter scenarios in redeployment
1.1.1	July 24, 2024	Bug fix for redeployment
1.1.0	May 29, 2024	Added support for new Dynamic Table features:
1.0.0	May 14, 2024	Initial Version - Dynamic Table Work - Dynamic Table Dimension - Dynamic Table Latest Record Version

Support

If you need help, please see our support section or contact us.