Dynamic Tables

‹ View all packages

Package ID: @coalesce/snowflake/dynamic-tables

Supported Platform:

Latest Version: 2.1.7 (February 26, 2026)

auto-refreshdeclarative pipelinesdynamic tablesincrementallag specificationmaterializedreal-time

Overview

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

Installation

1. Copy the Package ID: @coalesce/snowflake/dynamic-tables
2. In Coalesce, open the Workspace where you wish to install the package.
3. Go to the Build Setting of the Workspace, tab Packages, and click the Install button on the top right of the page.
4. Paste the Package ID, and proceed with the installation process.

Description

Snowflake - Dynamic Tables - Brief Summary

• Dynamic Table Work Serve as intermediate processing layers within a data pipeline. These nodes handle complex transformations, data cleaning, and filtering before data reaches final consumption. They optimize performance by breaking down logic into manageable, automated steps.

• Dynamic Table Dimension Provide essential business context, such as attributes for customers, products, or locations. These nodes use declarative logic to ensure descriptive data is automatically synchronized with source changes, allowing for seamless slicing and dicing of metrics in reports.

• Dynamic Table Latest Record version Ensure data integrity by automatically surfacing the most recent state of a record. By handling deduplication and versioning natively, these nodes provide a "single source of truth" for the current status of business entities, eliminating the need for complex manual cleanup queries.

Summary: Together, these node types leverage Snowflake’s declarative orchestration to provide a low-maintenance, automated framework for real-time data modeling. They ensure that business insights are built on fresh, accurate, and high-performance data structures.

---

Nodetypes Config Matrix

Category	Feature	DT Work	DT Dimension	DT Latest Record
Create	Create As Dynamic Table	✅	✅	✅
Create	Create As Dynamic Transient Table	✅	✅	✅
Create	Warehouse	✅	✅	✅
Create	Advanced Warehouse Selection	✅	✅	✅
Create	Initialization Warehouse Size	✅	✅	✅
Create	Initialize	✅	✅	✅
Create	Cluster Key	✅	✅	✅
Refresh	Refresh Warehouse size	✅	✅	✅
Refresh	Lag Specification (Time/Period)	✅	✅	✅
Refresh	Downstream Lag	✅	✅	✅
Refresh	Refresh Mode	✅	✅	✅
Refresh	Backfill Options	✅	✅	✅
Logic	Distinct / Group By All	✅	⬜	⬜
Logic	Table Key(s) / Business Key	⬜	✅	✅
Logic	Record Versioning Logic	⬜	✅	✅
Logic	Sequence / Ordering Column	⬜	✅	✅
Logic	Timestamp-track Data Load	⬜	✅	✅
Options	Copy Grants	✅	✅	✅
Options	Immutability Constraint	✅	✅	✅
Others	Enable Tests	✅	✅	✅
Others	Pre-SQL / Post-SQL	✅	✅	✅

---

Dynamic Tables Package

Package includes:

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

---

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:

• Node Properties
• Dynamic Table Options
• General Options
• Advanced Options

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 when Advance Warehouse is disabled) Name of warehouse used to refresh the Dynamic Table.
Advance Warehouse Selection	A toggle that enables size-based warehouse configuration for Dynamic Tables - Refresh Warehouse: Selects the warehouse (by size) used for regular refresh operations. - Initialization Warehouse: Selects the warehouse (by size) used during the initial creation or backfill of the Dynamic Table. - When using Advanced Warehouse, the targetDynamicTableWarehouse parameter is not required.
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	Specifies refresh type: - BLANK(''): If the blank option is selected, the default behavior will trigger an INCREMENTAL refresh.- 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	Initial refresh behavior: - BLANK(''): If the blank option is selected, the default behavior will be ON_CREATE. - ON_CREATE: Refresh synchronously at creation - ON_SCHEDULE: Refresh at next scheduled time

Dynamic Table Work Advanced Options

Option	Description
Copy grants	Specifies to retain the access privileges from the original table when a new dynamic table is created.Useful during replication.More info on replication here
Immutability Constraint	True/False toggle: - True: Applies an IMMUTABLE condition to the Dynamic Table, preventing changes to data that matches the defined rule - False: No immutability is enforced; data can be updated normally
Immutable Where Expression	Visible when Immutability Constraint is enabled. - SQL condition used to identify rows that are considered immutable (no longer change). - This expression must reference valid dynamic table columns and should be deterministic.
Enable Backfill	- Visible only when Immutability Constraint is enabled. - True/False toggle: - True: Displays Backfill Options group. Refer Snowflake Dynamic Tables to understand how Immutability and Backfill features work.

Dynamic Table Work Backfill Options

Option	Description
Backfill Source Schema	(Optional) Schema name of the backfill table. - If provided, the backfill table is read from this schema. - If left blank, the current node’s schema is used by default.
Backfill Source Table	Specifies a source table used to load historical data into the Dynamic Table.
Time Travel	Visible when Backfill option in enabled. True/False toggle to create the table with Time Travel options.
Time Travel Type	If Time Travel parameters AT/BEFORE are specified, data from the backfill table is copied at the specified time.
Time Travel Reference	Dropdown to specify how Snowflake should time travel the backfill source data: - OFFSET: Uses a relative time offset from the current time. - STATEMENT: Uses a specific Snowflake query ID to time travel the data to the moment that query was executed.
Time Travel Value	Value depends on the selected Time Travel Reference: - OFFSET: Provide a negative integer value (for example: -60, -120, -1440) representing time in minutes before the current time. - STATEMENT: Provide a valid query ID of a completed DML from the query history that falls within the table’s time travel retention period.

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"
}

Advance Warehouse selection(Optional)

• Advanced Warehouse Selection allows using separate warehouses for initialization and refresh based on workload size.
• Users can select different sizes for refresh vs initialization to optimize cost/performance

Prerequisite

• Advanced Warehouse must be enabled.
• The required warehouses must already exist in Snowflake.
• Corresponding warehouse parameters must be defined in the deployment environment.

Example of parameter initialization

{
  "warehouseSizesDict": {
      "xsDynamicTableWarehouse": "dev_wh_xs",
      "sDynamicTableWarehouse": "dev_wh_s",
      "lDynamicTableWarehouse": "dev_wh_l"
  },
"targetDynamicTableWarehouse": "DEV ENVIRONMENT",
}

Note: dev_wh_xs, dev_wh_s, and dev_wh_l are example warehouse names. Users must replace these values with the names of the Snowflake warehouses they have already created and want to use in their environment.

Warehouse sizes supported in node config UI and corresponding parameter details

Size	Environment Parameter
X-Small	xsDynamicTableWarehouse
Small	sDynamicTableWarehouse
Medium	mDynamicTableWarehouse
Large	lDynamicTableWarehouse
X-Large	xlDynamicTableWarehouse
2X-Large	2xlDynamicTableWarehouse
3X-Large	3xlDynamicTableWarehouse
4X-Large	4xlDynamicTableWarehouse
5X-Large	5xlDynamicTableWarehouse
6X Large	6xlDynamicTableWarehouse

Notes

• These parameters are required only when Advanced Warehouse is enabled.
• When using Advanced Warehouse, the targetDynamicTableWarehouse parameter is not required.

📘 Deployment of nodes without adding parameters

This results in a WARNING stage getting executed insisting to execute the node after adding parameters

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:

1. Warehouse name
2. Downstream setting
3. Lag specification
4. Immutability Constraint
5. Advance Warehouse

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

If the materialization type changes in dynamic table config options, the following steps gets executed:

1. Drop 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:

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.

Redeployment with no changes

If the nodes are redeployed with no changes compared to previous deployment,then no stages are executed

Node Type Switching

Node Type switching is supported starting from Coalesce version 7.28+.

From this version onward, a node’s materialization type can be switched from one supported type to another, subject to certain limitations.

For more info click here - Node Type Switching Logic and Limitations

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:

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

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 when Advance Warehouse is disabled) Name of warehouse used to refresh the Dynamic Table
Advance Warehouse Selection	A toggle that enables size-based warehouse configuration for Dynamic Tables - Refresh Warehouse: Selects the warehouse (by size) used for regular refresh operations. - Initialization Warehouse: Selects the warehouse (by size) used during the initial creation or backfill of the Dynamic Table. - When using Advanced Warehouse, the targetDynamicTableWarehouse parameter is not required.
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	Specifies refresh type: - BLANK(''): If the blank option is selected, the default behavior will trigger an INCREMENTAL refresh. - 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	Initial refresh behavior: - BLANK(''): If the blank option is selected, the default behavior will be ON_CREATE. - 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 - Integer column
Timestamp	Required if Datetime column chosen for Record versioning.Note:If multiple columns are chosen.The first timestamp column chosen is considered for versioning order
Sequence	Required if Integer column chosen for Record versioning
Timetamp-track data load	Required if Integer column chosen for Record versioningNote:If multiple columns are chosen.The first timestamp column chosen is considered for versioning order
Date/Timestamp Columns	Required if Date and Time columns chosen for Record versioningNote:If multiple columns are chosen.The first timestamp column chosen is considered for versioning order

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 Advanced Options

Option	Description
Copy grants	Specifies to retain the access privileges from the original table when a new dynamic table is created.Useful during replication.More info on replication here
Immutability Constraint	True/False toggle: - True: Applies an IMMUTABLE condition to the Dynamic Table, preventing changes to data that matches the defined rule - False: No immutability is enforced; data can be updated normally
Immutable Where Expression	Visible when Immutability Constraint is enabled. - SQL condition used to identify rows that are considered immutable (no longer change). - This expression must reference valid dynamic table columns and should be deterministic.
Enable Backfill	- Visible only when Immutability Constraint is enabled. - True/False toggle: - True: Displays Backfill Options group. Review Snowflake Dynamic Tables to understand how Immutability and Backfill features work.

Dynamic Table Dimension Backfill Options

Option	Description
Backfill Source Schema	(Optional) Schema name of the backfill table. - If provided, the backfill table is read from this schema. - If left blank, the current node’s schema is used by default.
Backfill Source Table	Specifies a source table used to load historical data into the Dynamic Table.
Time Travel	Visible when Backfill option in enabled. True/False toggle to create the table with Time Travel options.
Time Travel Type	If Time Travel parameters AT/BEFORE are specified, data from the backfill table is copied at the specified time.
Time Travel Reference	Dropdown to specify how Snowflake should time travel the backfill source data: - OFFSET: Uses a relative time offset from the current time. - STATEMENT: Uses a Snowflake query ID to time travel data, but is not supported for Dynamic Table Dimension backfill.
Time Travel Value	Value depends on the selected Time Travel Reference: - OFFSET: Provide a negative integer value (for example: -60, -120, -1440) representing time in minutes before the current time. - STATEMENT: Provide a valid query ID of a completed DML from the query history that falls within the table’s time travel retention period.

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"
}

Advance Warehouse selection(Optional)

• Advanced Warehouse Selection allows using separate warehouses for initialization and refresh based on workload size.
• Corresponding warehouse parameters must be defined in the deployment environment.

Prerequisite

• Advanced Warehouse must be enabled.
• The required warehouses must already exist in Snowflake.
• Corresponding warehouse parameters must be defined in the deployment environment.

Example of parameter initialization

{
  "warehouseSizesDict": {
      "xsDynamicTableWarehouse": "dev_wh_xs",
      "sDynamicTableWarehouse": "dev_wh_s",
      "lDynamicTableWarehouse": "dev_wh_l"
  },
"targetDynamicTableWarehouse": "DEV ENVIRONMENT",
}

Note: dev_wh_xs, dev_wh_s, and dev_wh_l are example warehouse names. Users must replace these values with the names of the Snowflake warehouses they have already created and want to use in their environment.

Warehouse sizes supported in node config UI and corresponding parameter details

Size	Environment Parameter
X-Small	xsDynamicTableWarehouse
Small	sDynamicTableWarehouse
Medium	mDynamicTableWarehouse
Large	lDynamicTableWarehouse
X-Large	xlDynamicTableWarehouse
2X-Large	2xlDynamicTableWarehouse
3X-Large	3xlDynamicTableWarehouse
4X-Large	4xlDynamicTableWarehouse
5X-Large	5xlDynamicTableWarehouse
6X Large	6xlDynamicTableWarehouse

Notes

• These parameters are required only when Advanced Warehouse is enabled.
• When using Advanced Warehouse, the targetDynamicTableWarehouse parameter is not required.

📘 Deployment of nodes without adding parameters

This results in a WARNING stage getting executed insisting to execute the node after adding parameters

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:

1. Warehouse name
2. Downstream setting
3. Lag specification
4. Immutability Constraint
5. Advance Warehouse

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

If the materialization type changes in dynamic table config options, the following steps gets executed:

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

Redeployment with no changes

If the nodes are redeployed with no changes compared to previous deployment,then no stages are executed

Node Type Switching

Node Type switching is supported starting from Coalesce version 7.28+.

From this version onward, a node’s materialization type can be switched from one supported type to another, subject to certain limitations.

For more info click here - Node Type Switching Logic and Limitations

Dynamic Table Dimension 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:

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

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 Verion Table Options

Option	Description
Warehouse	(Required when Advance Warehouse is disabled) Name of warehouse used to refresh the Dynamic Table
Advance Warehouse Selection	A toggle that enables size-based warehouse configuration for Dynamic Tables - Refresh Warehouse: Selects the warehouse (by size) used for regular refresh operations. - Initialization Warehouse: Selects the warehouse (by size) used during the initial creation or backfill of the Dynamic Table. - When using Advanced Warehouse, the targetDynamicTableWarehouse parameter is not required.
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	Specifies refresh type: - BLANK(''): If the blank option is selected, the default behavior will trigger an INCREMENTAL refresh. - 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	Initial refresh behavior: - BLANK(''): If the blank option is selected, the default behavior will be ON_CREATE. - ON_CREATE: Refresh synchronously at creation - ON_SCHEDULE: Refresh at next scheduled time

Latest Record Version 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 - Integer column
Timestamp	Required if Datetime column chosen for Record versioning.Note:If multiple columns are chosen.The first timestamp column chosen is considered for versioning order
Sequence	Required if Integer column chosen for Record versioning
Timetamp-track data load	Required if Integer column chosen for Record versioningNote:If multiple columns are chosen.The first timestamp column chosen is considered for versioning order
Date/Timestamp Columns	Required if Date and Time columns chosen for Record versioningNote:If multiple columns are chosen.The first timestamp column chosen is considered for versioning order

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

Latest Record Version Advanced Options

Option	Description
Copy grants	Specifies to retain the access privileges from the original table when a new dynamic table is created.Useful during replication.More info on replication here
Immutability Constraint	True/False toggle: - True: Applies an IMMUTABLE condition to the Dynamic Table, preventing changes to data that matches the defined rule - False: No immutability is enforced; data can be updated normally
Immutable Where Expression	Visible when Immutability Constraint is enabled. - SQL condition used to identify rows that are considered immutable (no longer change). - This expression must reference valid dynamic table columns and should be deterministic.
Enable Backfill	- Visible only when Immutability Constraint is enabled. - True/False toggle: - True: Displays Backfill Options group. Review Snowflake Dynamic Tables to understand how Immutability and Backfill features work.

Latest Record Version Backfill Options

Option	Description
Backfill Source Schema	(Optional) Schema name of the backfill table. - If provided, the backfill table is read from this schema. - If left blank, the current node’s schema is used by default.
Backfill Source Table	Specifies a source table used to load historical data into the Dynamic Table.
Time Travel	Visible when Backfill option in enabled. True/False toggle to create the table with Time Travel options.
Time Travel Type	If Time Travel parameters AT/BEFORE are specified, data from the backfill table is copied at the specified time.
Time Travel Reference	Dropdown to specify how Snowflake should time travel the backfill source data: - OFFSET: Uses a relative time offset from the current time. - STATEMENT: Uses a Snowflake query ID to time travel data, but is not supported for Dynamic Table Dimension backfill.
Time Travel Value	Value depends on the selected Time Travel Reference: - OFFSET: Provide a negative integer value (for example: -60, -120, -1440) representing time in minutes before the current time. - STATEMENT: Provide a valid query ID of a completed DML from the query history that falls within the table’s time travel retention period.

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"
}

Advance Warehouse selection(Optional)

• Advanced Warehouse Selection allows using separate warehouses for initialization and refresh based on workload size.
• Corresponding warehouse parameters must be defined in the deployment environment.

Prerequisite

• Advanced Warehouse must be enabled.
• The required warehouses must already exist in Snowflake.
• Corresponding warehouse parameters must be defined in the deployment environment.

Example of parameter initialization

{
  "warehouseSizesDict": {
      "xsDynamicTableWarehouse": "dev_wh_xs",
      "sDynamicTableWarehouse": "dev_wh_s",
      "lDynamicTableWarehouse": "dev_wh_l"
  },
"targetDynamicTableWarehouse": "DEV ENVIRONMENT",
}

Note: dev_wh_xs, dev_wh_s, and dev_wh_l are example warehouse names. Users must replace these values with the names of the Snowflake warehouses they have already created and want to use in their environment.

Warehouse sizes supported in node config UI and corresponding parameter details

Size	Environment Parameter
X-Small	xsDynamicTableWarehouse
Small	sDynamicTableWarehouse
Medium	mDynamicTableWarehouse
Large	lDynamicTableWarehouse
X-Large	xlDynamicTableWarehouse
2X-Large	2xlDynamicTableWarehouse
3X-Large	3xlDynamicTableWarehouse
4X-Large	4xlDynamicTableWarehouse
5X-Large	5xlDynamicTableWarehouse
6X Large	6xlDynamicTableWarehouse

Notes

• These parameters are required only when Advanced Warehouse is enabled.
• When using Advanced Warehouse, the targetDynamicTableWarehouse parameter is not required.

📘 Deployment of nodes without adding parameters

This results in a WARNING stage getting executed insisting to execute the node after adding parameters

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:

1. Warehouse name
2. Downstream setting
3. Lag specification
4. Immutability Constraint
5. Advance Warehouse

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

If the materialization type changes in dynamic table config options, the following steps gets executed:

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

Redeployment with no changes

If the nodes are redeployed with no changes compared to previous deployment,then no stages are executed

Node Type Switching

Node Type switching is supported starting from Coalesce version 7.28+.

From this version onward, a node’s materialization type can be switched from one supported type to another, subject to certain limitations.

For more info click here - Node Type Switching Logic and Limitations

Latest Record Version Undeployment

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

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

Stage	Description
Drop Dynamic Table	Removes table from target environment

---

Node Type Switching Logic

Current MaterializationType	Desired MaterializationType	Stage
Dynamic Table	Dynamic Table	Follows existing redeployment stages
Dynamic Transient Table	Dynamic Transient Table	Follows existing redeployment stages
Any Other	Dynamic Table	1. Warning (if applicable) 2. Drop 3. Create
Any Other	Dynamic Transient Table	1. Warning (if applicable) 2. Drop 3. Create

Please review the documented limitations before performing a node type switch to ensure compatibility and avoid unintended deployment issues.

⚠ Limitations of Node Type Switching (Current)

#	Current Materialization	Desired Materialization	Limitation
1	Older Version Iceberg Table	Table	Results in ALTER failure. Iceberg tables require ALTER ICEBERG TABLE. Works only if latest package (with switching support) is already used.
2	Older Version Create or Alter-View Data Quality-DMF	Any(except View)	Switch fails unless current node uses latest package supporting node type switching.
3	First Node in Pipeline	Any	Not supported. First node is foundational and switching may disrupt the pipeline.
4	External Packages	Any	Not supported as they typically act as first nodes in the pipeline.
5	Functional Packages	Any	Not supported due to column re-sync behavior which may cause schema inconsistencies.
6	Dynamic Dimension / LRV	Any	System columns must be manually dropped before redeployment.
7	Any	Any Other	After performing node switching, the Create/Run in Workspace browser may not work as expected due to changes in the node’s materialization type.
8	Table(Data Profiling)	Table	This may result in ALTER failure unless latest package is used(with system column removal support)(Pending Release)

---

Code

Dynamic Table Work Code

• Node definition
• Create Template

Dynamic Table Dimension Code

• Node definition
• Create Template

Dynamic Table Latest Record Version Code

• Node definition
• Create Template

Macros

• Macros

Versions

Available versions of the package.

Version #	Release Date	Notes
2.1.7	February 26, 2026	Node Type Switching supported in Advance Deploy templates
2.1.6	January 05, 2026	Added Advance Warehouse, Immutability Constraint and Backfill Features
2.1.5	December 26, 2025	Fix for Missing Handler for Incoming Node Type Transitions (View/Table → Dynamic Table)
2.1.4	November 07, 2025	Mocro update and blank option added to Refresh Mode and Initialize
2.1.3	October 06, 2025	Dynamic Iceberg Table moved to Iceberg Tables Package
2.1.2	September 26, 2025	Integer versioning column support, transform support for tstamp columns,multiple tstamp notsupported
2.1.0	August 13, 2025	Advanced option Copy grants added to all dynamic table node types
1.1.11	June 06, 2025	Deployment fix-single source to multi-source
1.1.10	April 22, 2025	Adding data type definition to dynamic table node types.
1.1.9	March 27, 2025	Fix done for dynamic dimension to use hash key as table key
1.1.8	March 07, 2025	Force Users to Enter Parameters When Required
1.1.7	February 24, 2025	Redeployment fixes for join clause