Base Node Types Advanced Deploy
@coalesce/snowflake/base-node-types-advanced-deploy 
Overview
The Coalesce Advanced Deploy Nodes are Node Types that allows you to develop and deploy objects in Snowflake.
Installation
- Copy the Package ID:
@coalesce/snowflake/base-node-types-advanced-deploy - 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
Base Node Types Advanced Deploy
The Coalesce Base Node Types Package includes:
- Work Advanced Deploy
- Persistent Stage Advanced Deploy
- Dimension Advanced Deploy
- Fact Advanced Deploy
- Factless Fact Advanced Deploy
- Code
Work Advanced Deploy
The Coalesce Work Node is a versatile node that allows you to develop and deploy a Work table/view in Snowflake.
A Work node serves as an intermediary object and is commonly employed to store raw data before undergoing the crucial phases of transformation and loading into the main tables of the data warehouse.
This pivotal step ensures that the raw data is processed and structured effectively.
Work Advanced Deploy Node Configuration
The Work node type has two configuration groups:
Work Advanced Deploy Node Properties
| Setting | Description |
|---|---|
| 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 |
Work Advanced Deploy Options
You can create the node as:
Work Advanced Deploy Create as Table
| Setting | Description |
|---|---|
| Create As | Table |
| Cluster key | Toggle: True/False If the dimension is clustered or not. True: Allows you to specify the column based on which clustering is to be done. - Allow Expressions Cluster Key: Allows to add an expression to the specified cluster key False:No clustering done |
| Multi Source | Toggle: True/False Implementation of SQL UNIONs True: Combine multiple sources in a single node True Options: - UNION: Combines with duplicate elimination - UNION ALL: Combines without duplicate elimination - INSERT: Individual insert for each source False: Single source node or multiple sources combined using a join. |
| Truncate Before | Toggle: True/False This determines whether a table will be overwritten each time a task executes. True: Uses INSERT OVERWRITE False: Uses INSERT to append data |
| Enable tests | Toggle: True/False Determines if tests are enabled |
| Distinct | Toggle: True/False True: Group by All is invisible. DISTINCT data is chosen for processing. False: Group by All is visible. |
| Group by All | Toggle: True/False True: DISTINCT is invisible, data grouped by all columns False: DISTINCT is visible |
| Order By | Toggle: True/False True: Sort column and sort order drop down are visible and are required to form order by clause. False: Sort options invisible |
| ASOF Join | Toggle: True/False True: ASOF Join Options will be visible. False: ASOF Join Options will be invisible |
| Pre-SQL | SQL to execute before data insert operation |
| Post-SQL | SQL to execute after data insert operation |
Work Advanced Deploy Create as View
| Setting | Description |
|---|---|
| Create As | View |
| Cluster key | Toggle: True/False If the dimension is clustered or not. True: Allows you to specify the column based on which clustering is to be done. - Allow Expressions Cluster Key: Allows to add an expression to the specified cluster key False:No clustering done |
| Multi Source | Toggle: True/False Implementation of SQL UNIONs True: Combine multiple sources in a single node True Options: - UNION: Combines with duplicate elimination - UNION ALL: Combines without duplicate elimination - INSERT: Individual insert for each source False: Single source node or multiple sources combined using a join. |
| Truncate Before | Toggle: True/False This determines whether a table will be overwritten each time a task executes. True: Uses INSERT OVERWRITE False: Uses INSERT to append data |
| Enable tests | Toggle: True/False Determines if tests are enabled |
| Distinct | Toggle: True/False True: Group by All is invisible. DISTINCT data is chosen for processing. False: Group by All is visible. |
| Group by All | Toggle: True/False True: DISTINCT is invisible, data grouped by all columns False: DISTINCT is visible |
| ASOF Join | Toggle: True/False True: ASOF Join Options will be visible. False: ASOF Join Options will be invisible |
Work Advanced Deploy Create as Transient Table
| Setting | Description |
|---|---|
| Create As | Transient Table |
| Cluster key | Toggle: True/False If the dimension is clustered or not. True: Allows you to specify the column based on which clustering is to be done. - Allow Expressions Cluster Key: Allows to add an expression to the specified cluster key False:No clustering done |
| Multi Source | Toggle: True/False Implementation of SQL UNIONs True: Combine multiple sources in a single node True Options: - UNION: Combines with duplicate elimination - UNION ALL: Combines without duplicate elimination - INSERT: Individual insert for each source False: Single source node or multiple sources combined using a join. |
| Truncate Before | Toggle: True/False This determines whether a table will be overwritten each time a task executes. True: Uses INSERT OVERWRITE False: Uses INSERT to append data |
| Enable tests | Toggle: True/False Determines if tests are enabled |
| Distinct | Toggle: True/False True: Group by All is invisible. DISTINCT data is chosen for processing. False: Group by All is visible. |
| Group by All | Toggle: True/False True: DISTINCT is invisible, data grouped by all columns False: DISTINCT is visible |
| Order By | Toggle: True/False True: Sort column and sort order drop down are visible and are required to form order by clause. False: Sort options invisible |
| ASOF Join | Toggle: True/False True: ASOF Join Options will be visible. False: ASOF Join Options will be invisible |
| Pre-SQL | SQL to execute before data insert operation |
| Post-SQL | SQL to execute after data insert operation |
ASOF Join Options
| Setting | Description |
|---|---|
| Match Condition | Toggle: True/False Match Condition Clause from Snowflake ASOF join True: Allows you to specify the Match Condtion. - Right Table Storage Location: Add right table storage location - Right Table Name: Add name of the right table - Match Condition: Add a match condition in the format "Left Table Name"."Column Name" Condition Operator "Right Table Name"."Column Name" False : No Match Condition Added |
| On | Toggle: True/False ON Clause with Match Condition from Snowflake ASOF join.Using will be invisible True: Allows you to add the ON Clause. ON Condition: Add a match condition in the format "Left Table Name"."Column Name" = "Right Table Name"."Column Name" False: No ON Clause Added.Using will be visible |
| Using | Toggle: True/False Using Clause with Match Condition from Snowflake ASOF join.On will be invisible True: Allows you to add the Using Clause. Using Column Name : Add a Column Name for Using clause False: No Using Clause Added.On will be visible |
Work Advanced Deploy Joins
Join conditions and other clauses can be specified in the join space next to mapping of columns in the UI.
📘 Specify Group by and Order by Clauses
Best Practice is to specify group by and order by clauses in this space if you are not opting for the group by all and order by provided in OPTIONS config.
Work Advanced Deploy ASOF Join
After selecting options for ASOF Join,Click on Generate join, use the 'Copy To Editor' to add the new ASOF join.
Work Advanced Deploy Deployment
Work Advanced Deploy Initial Deployment
When deployed for the first time into an environment the Work node of materialization type table or view will execute the below stage:
| Stage | Description |
|---|---|
| Create Work Table | This will execute a CREATE OR REPLACE statement and create a table in the target environment |
| Create Work View | This will execute a CREATE OR REPLACE statement and create a view in the target environment |
Work Advanced Deploy Redeployment
After the WORK node with materialization type table/transient table/view has been deployed for the first time into a target environment, subsequent deployments may result in either altering the WORK Table or recreating the WORK table.
Altering the Work Tables and Transient Tables
A few types of column or table changes will result in an ALTER statement to modify the Persistent Table in the target environment, whether these changes are made individually or all together:
- Changing table names
- Dropping existing columns
- Altering column data types
- Adding new columns
The following stages are executed:
| Stage | Description |
|---|---|
| Rename Table| Alter Column | Delete Column | Add Column | Edit table description | Alter table statement is executed to perform the alter operation |
Sometimes, changes to config can result in metadata changes from node edits, DML changes, or storage updates. A few cases are listed below:
- Changes in business keys
- Changes to change tracking keys
- Changes in join clauses
- Transformations made at column level
- Changing DML options like DISTINCT, ORDER BY, GROUP BY ALL
And many more. Most of the time, specific ‘is’ and ‘was’ values will be displayed to specifically show what changed.
The following stages are executed:
| Stage | Description |
|---|---|
| Metadata Update | Business Keys | Change Tracking | Distinct | Transformation | Join | A dummy statement would execute with specific changes listed in comments |
Work Advanced Deploy Recreating the Work Views
The subsequent deployment of Work node of materialization type view with changes in view definition, adding table description or renaming view results in deleting the existing view and recreating the view.
The following stages are executed:
| Stage | Description |
|---|---|
| Create View | Creates a new view with updated definition |
Work Advanced Deploy Drop and Recreate Work View/Table/Transient Table
| Change | Stages Executed |
|---|---|
| View to table/transient table | Drop view Create or Replace Work table/transient table |
| Table/transient table to View | Drop table/transient table Create Work view |
| Table to transient table or vice versa | Drop table/transient table Create or Replace Work table/transient table |
📘 Materialization Work Node
When the materialization type of Work node is changed from table/transient table to View and use Override Create SQL for view creation. This ensures that the following change is made in the stage function in Create SQL tab so that the order of deployment is maintained.
Work Advanced Deploy Undeployment
If a Work Node of materialization type table/view/transient table are deleted from a Workspace, that Workspace is committed to Git and that commit deployed to a higher level environment then the WorkTable in the target environment will be dropped.
This is executed in below stage:
| Stage | Description |
|---|---|
| Drop table/view | Removes the table or view from the environment |
Persistent Stage Advanced Deploy
The Coalesce Persistent Stage Nodes element, serving as an intermediary object, is frequently utilized to maintain data persistence across multiple execution cycles.
It plays a crucial role in tracking the historical changes of columns linked to business keys.
This functionality is particularly beneficial when the objective is to retain raw data for prolonged durations.
Persistent Stage Advanced Deploy Node Configuration
The Persistent node type has two configuration groups:
Persistent Stage Advanced Deploy Node Properties
| Setting | Description |
|---|---|
| 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 |
Persistent Stage Advanced Deploy Options
You can create the node as:
Persistent Stage Table
| Setting | Description |
|---|---|
| Create As | Table |
| Cluster key | Toggle: True/False If the dimension is clustered or not. True: Allows you to specify the column based on which clustering is to be done. - Allow Expressions Cluster Key: Allows to add an expression to the specified cluster key False:No clustering done |
| Multi Source | Toggle: True/False Implementation of SQL UNIONs True: Combine multiple sources in a single node True Options: - UNION: Combines with duplicate elimination - UNION ALL: Combines without duplicate elimination - INSERT: Individual insert for each source False: Single source node or multiple sources combined using a join. |
| Business key | Required column for both Type 1 and Type 2 . Note: Geometry and Geography data type columns are not supported as business key columns. |
| Change tracking | Required column for Type 2 |
| Truncate Before | Toggle: True/False This determines whether a table will be overwritten each time a task executes. True: Uses INSERT OVERWRITE False: Uses INSERT to append data |
| Enable tests | Toggle: True/False Determines if tests are enabled |
| Distinct | Toggle: True/False True: Group by All is invisible. DISTINCT data is chosen for processing. False: Group by All is visible. |
| Group by All | Toggle: True/False True: DISTINCT is invisible, data grouped by all columns False: DISTINCT is visible |
| Order By | Toggle: True/False True: Sort column and sort order drop down are visible and are required to form order by clause. False: Sort options invisible |
| Pre-SQL | SQL to execute before data insert operation |
| Post-SQL | SQL to execute after data insert operation |
Persistent Stage Transient Table
| Setting | Description |
|---|---|
| Create As | Table |
| Cluster key | Toggle: True/False If the dimension is clustered or not. True: Allows you to specify the column based on which clustering is to be done. - Allow Expressions Cluster Key: Allows to add an expression to the specified cluster key False:No clustering done |
| Multi Source | Toggle: True/False Implementation of SQL UNIONs True: Combine multiple sources in a single node True Options: - UNION: Combines with duplicate elimination - UNION ALL: Combines without duplicate elimination - INSERT: Individual insert for each source False: Single source node or multiple sources combined using a join. |
| Business key | Required column for both Type 1 and Type 2 |
| Change tracking | Required column for Type 2 |
| Truncate Before | Toggle: True/False This determines whether a table will be overwritten each time a task executes. True: Uses INSERT OVERWRITE False: Uses INSERT to append data |
| Enable tests | Toggle: True/False Determines if tests are enabled |
| Distinct | Toggle: True/False True: Group by All is invisible. DISTINCT data is chosen for processing. False: Group by All is visible. |
| Group by All | Toggle: True/False True: DISTINCT is invisible, data grouped by all columns False: DISTINCT is visible |
| Order By | Toggle: True/False True: Sort column and sort order drop down are visible and are required to form order by clause. False: Sort options invisible |
| Pre-SQL | SQL to execute before data insert operation |
| Post-SQL | SQL to execute after data insert operation |
Persistent Stage Advanced Deploy Joins
Join conditions and other clauses can be specified in the join space next to mapping of columns in the UI.
📘 Specify Group by and Order by Clauses
Best Practice is to specify group by and order by clauses in this space if you are not opting for the group by all and order by provided in OPTIONS config.
Persistent Stage Advanced Deploy Deployment
Persistent Stage Advanced Deploy Initial Deployment
When deployed for the first time into an environment the Persistent node will execute the below stage:
| Stage | Description |
|---|---|
| Create Persistent Stage Table | This will execute a CREATE OR REPLACE statement and create a table in the target environment |
Persistent Stage Advanced Deploy Redeployment
After the Persistent node has been deployed for the first time into a target environment, subsequent deployments may result in either altering the Persistent Table or recreating the Persistent table.
Altering the Persistent Tables/Transient Tables
A few types of column or table changes will result in an ALTER statement to modify the Persistent Table in the target environment, whether these changes are made individually or all together:
- Changing table names
- Dropping existing columns
- Altering column data types
- Adding new columns
The following stages are executed:
| Stage | Description |
|---|---|
| Rename Table| Alter Column | Delete Column | Add Column | Edit table description | ALTER table statement is executed to perform the alter operation accordingly |
Sometimes, changes to config can result in metadata changes from node edits, DML changes, or storage updates. A few cases are listed below:
- Changes in business keys
- Changes to change tracking keys
- Changes in join clauses
- Transformations made at column level
- Changing DML options like DISTINCT, ORDER BY, GROUP BY ALL
And many more. Most of the time, specific ‘is’ and ‘was’ values will be displayed to specifically show what changed.
The following stages are executed:
| Stage | Description |
|---|---|
| Metadata Update | Business Keys | Change Tracking | Distinct | Transformation | Join | A dummy statement would execute with specific changes listed in comments |
Drop and Recreate Persistent Stage Table/Transient Table
When the materialization type of Persistent stage node is changed from table to transient table or transient table to table, the below stages are executed:
| Stage | Description |
|---|---|
| Drop table/transient table | Removes existing table |
| Create or Replace Persistent stage table/transient table | Creates new table with updated configuration |
Persistent Stage Advanced Deploy Undeployment
If a Persistent Node is deleted from a Workspace, that Workspace is committed to Git and that commit deployed to a higher level environment then the Persistent Table in the target environment will be dropped.
This is executed in the stages:
| Stage | Description |
|---|---|
| Drop Table or View | Removes the table from the environment |
Dimension Advanced Deploy
The Coalesce Dimension UDN is a versatile node that allows you to develop and deploy a Dimension table in Snowflake.
A dimension table or dimension entity is a table or entity in a star, snowflake, or starflake schema that stores details about the facts. Dimension tables describe the different aspects of a business process.
Dimension Advanced Deploy Node Configuration
Dimension Advanced Deploy Node Properties
| Property | Description |
|---|---|
| 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 |
Dimension Advanced Deploy Options
You can create the node as:
Dimension Advanced Deploy Table
| Setting | Description |
|---|---|
| Create As | Table |
| Insert Zero Key Record | Toggle: True/False Insert Zero Key Record to Dimention True: Zero Key Record Options enabled. False: Zero Key Record not added |
| Multi Source | Toggle: True/False Implementation of SQL UNIONs True: Combine multiple sources in a single node True Options: - UNION: Combines with duplicate elimination - UNION ALL: Combines without duplicate elimination - INSERT: Individual insert for each source False: Single source node or multiple sources combined using a join. |
| Primary key | Allows you to specify one or more columns based on which primary constraint is set on the table. Primary Key Name: Primary key constraint name. If not specified defaults to pk_ {{tablename}} |
| Update Strategy | Options : MERGE,INSERT/UPDATE - MERGE: Uses a single MERGE statement to handle both insert and update operations based on matching keys. - INSERT/UPDATE: Separately executes UPDATE for existing records and INSERT for new ones using custom logic.For preferred choice,refer Preferences |
| Business key | Required column for Type 1 and Type 2 Dimensions . Note: Geometry and Geography data type columns are not supported as business key columns. |
| Change tracking | Required column for Type 2 Dimension |
| Unmatched Record Strategy | Available for single source nodes with Merge as update strategy - NO DELETE: An option introduced to ensure existing data flows remain intact and unchanged, preventing any delete operation on the target table. - SOFT DELETE: Marks records as logically deleted (isSystemCurrentFlag = 0) while retaining the history. - HARD DELETE: Permanent removal of records from the target table. |
| Exclude Columns from Merge | Available only for SCD type 1 Merges. Allows you to specify one or more columns that are excluded during both the comparison (matching) and updating phases of the MERGE statement. |
| Truncate Before | Toggle: True/False This determines whether a table will be overwritten each time a task executes. True: Uses INSERT OVERWRITE False: Uses INSERT to append data |
| Enable tests | Toggle: True/False Determines if tests are enabled |
| Cluster key | True: Allows you to specify the column based on which clustering is to be done Allow Expressions Cluster Key: Allows to add an expression to the specified cluster key False: No clustering done |
| Distinct | Toggle: True/False True: Group by All is invisible. DISTINCT data is chosen for processing. False: Group by All is visible. |
| Group by All | Toggle: True/False True: DISTINCT is invisible, data grouped by all columns False: DISTINCT is visible |
| Order By | Toggle: True/False True: Sort column and sort order drop down are visible and are required to form order by clause. False*: Sort options invisible |
| Zero Key Record Options | Add custom zero key record values for : -Default Surrogate Key Value -Default String Value -Default Date Value (Date Format DD-MM-YYYY) -Default Timestamp Value (Timestamp Format YYYY-MM-DD HH24:MI:SS.FF) -Default Boolean Value |
| Advanced Zero Key Record Options | Toggle: True/False True: Select Columns and the default value of the column for zero key record False: Advanced Zero Key Record Options not enabled |
| Pre-SQL | SQL to execute before data insert operation |
| Post-SQL | SQL to execute after data insert operation |
Dimension Advanced Deploy Transient Table
| Setting | Description |
|---|---|
| Create As | Transient Table |
| Multi Source | Toggle: True/False Implementation of SQL UNIONs True: Combine multiple sources in a single node True Options: - UNION: Combines with duplicate elimination - UNION ALL: Combines without duplicate elimination - INSERT: Individual insert for each source False: Single source node or multiple sources combined using a join. |
| Primary key | Allows you to specify one or more columns based on which primary constraint is set on the table. Primary Key Name: Primary key constraint name. If not specified defaults to pk_ {{tablename}} |
| Update Strategy | Options : MERGE,INSERT/UPDATE - MERGE: Uses a single MERGE statement to handle both insert and update operations based on matching keys. - INSERT/UPDATE: Separately executes UPDATE for existing records and INSERT for new ones using custom logic.For preferred choice,refer Preferences |
| Unmatched Record Strategy | Available for single source nodes with Merge as update strategy - NO DELETE: An option introduced to ensure existing data flows remain intact and unchanged, preventing any delete operation on the target table. - SOFT DELETE: Marks records as logically deleted (isSystemCurrentFlag = 0) while retaining the history. - HARD DELETE: Permanent removal of records from the target table. |
| Exclude Columns from Merge | Available only for SCD type 1 Merges. Allows you to specify one or more columns that are excluded during both the comparison (matching) and updating phases of the MERGE statement. |
| Business key | Required column for Type 1 and Type 2 Dimensions |
| Change tracking | Required column for Type 2 Dimension |
| Truncate Before | Toggle: True/False This determines whether a table will be overwritten each time a task executes. True: Uses INSERT OVERWRITE False: Uses INSERT to append data |
| Enable tests | Toggle: True/False Determines if tests are enabled |
| Cluster key | True: Allows you to specify the column based on which clustering is to be done Allow Expressions Cluster Key: Allows to add an expression to the specified cluster key False: No clustering done |
| Distinct | Toggle: True/False True: Group by All is invisible. DISTINCT data is chosen for processing. False: Group by All is visible. |
| Group by All | Toggle: True/False True: DISTINCT is invisible, data grouped by all columns False: DISTINCT is visible |
| Order By | Toggle: True/False True: Sort column and sort order drop down are visible and are required to form order by clause. False: Sort options invisible |
| Pre-SQL | SQL to execute before data insert operation |
| Post-SQL | SQL to execute after data insert operation |
Dimension Advanced Deploy View
| Setting | Description |
|---|---|
| Create As | View |
| Override Create SQL | Toggle: True/False True: Custom Create SQL False: Generated view SQL |
| Multi Source | Toggle: True/False Implementation of SQL UNIONs True: Combine multiple sources in a single node True Options: - UNION: Combines with duplicate elimination - UNION ALL: Combines without duplicate elimination - INSERT: Individual insert for each source False: Single source node or multiple sources combined using a join. |
| Business key | Required column for Type 1 and Type 2 Dimensions |
| Distinct | Toggle: True/False True: Group by All is invisible. DISTINCT data is chosen for processing. False: Group by All is visible. |
| Group by All | Toggle: True/False True: DISTINCT is invisible, data grouped by all columns False: DISTINCT is visible |
| Order By | Toggle: True/False True: Sort column and sort order drop down are visible and are required to form order by clause. False: Sort options invisible |
Preferences
- With Update Strategy config option in Dimension,we can choose either to perform a MERGE or INSERT/UPDATE operation to create various SCDs(Slowly Changing Dimensions).
- Insert/Update operations scale well up to 10 million rows but MERGE is recommended for its superior execution performance.
- For large datasets,MERGE operation is recommended.
Dimension Advanced Deploy Joins
Join conditions and other clauses can be specified in the join space next to mapping of columns in the UI.
📘 Specify Group by and Order by Clauses
Best Practice is to specify group by and order by clauses in this space if you are not opting for the group by all and order by provided in OPTIONS config.
Dimension Advanced Deploy Deployment
Dimension Advanced Deploy Initial Deployment
When deployed for the first time into an environment the Dimension node of materialization type table or view will execute the following stage:
| Stage | Description |
|---|---|
| Create Dimension Table | This will execute a CREATE OR REPLACE statement and create a table in the target environment |
| Create Dimension View | This will execute a CREATE OR REPLACE statement and create a view in the target environment |
Dimension Advanced Deploy Redeployment
After the Dimension node of materialization type table has been deployed for the first time into a target environment, subsequent deployments may result in either altering the Dimension Table or recreating the Dimension table.
Altering the Dimension Tables and Transient Tables
A few types of column or table changes will result in an ALTER statement to modify the Persistent Table in the target environment, whether these changes are made individually or all together:
- Changing table names
- Dropping existing columns
- Altering column data types
- Adding new columns
The following stages are executed:
| Stage | Description |
|---|---|
| Rename Table| Alter Column | Delete Column | Add Column | Edit table description | Alter table statement is executed to perform the alter operation |
Sometimes, changes to config can result in metadata changes from node edits, DML changes, or storage updates. A few cases are listed below:
- Changes in business keys
- Changes to change tracking keys
- Changes in join clauses
- Transformations made at column level
- Changing DML options like DISTINCT, ORDER BY, GROUP BY ALL
And many more. Most of the time, specific ‘is’ and ‘was’ values will be displayed to specifically show what changed.
The following stages are executed:
| Stage | Description |
|---|---|
| Metadata Update | Business Keys | Change Tracking | Distinct | Transformation | Join | A dummy statement would execute with specific changes listed in comments |
Recreating the Dimension Views
The subsequent deployment of Dimension node of materialization type view with changes in view definition, adding table description or renaming view results in recreating the dimension view.
Drop and Recreate Dimension View/Table/Transient
| Change | Stages Executed |
|---|---|
| View to table/transient table | Drop view Create Dimension table/transient table |
| Table/transient table to View | Drop table/transient table Create Dimension view |
| Table to transient table or vice versa | Drop table/transient table Create Dimension table/transient table |
📘 Materialization type of Dimension node
When the materialization type of Dimension node is changed from table/transient table to View and use Override Create SQL for view creation to ensure that the below change is made in the stage function in Create SQL tab so that the order of deployment is maintained.
Dimension Advanced Deploy Undeployment
If a Dimension Node of materialization type table is deleted from a Workspace, that Workspace is committed to Git and that commit deployed to a higher level environment then the Dimension Table in the target environment will be dropped.
The stage executed:
| Stage | Description |
|---|---|
| Drop table/view | Removes the table or view from the environment |
Fact Advanced Deploy
The Coalesce Fact UDN is a versatile node that allows you to develop and deploy a Fact table in Snowflake.
A fact table or a fact entity is a table or entity in a star or snowflake schema that stores measures that measure the business, such as sales, cost of goods, or profit. Fact tables and entities aggregate measures, or the numerical data of a business.
Fact Advanced Deploy Node Configuration
Fact Advanced Deploy Node Properties
| Setting | Description |
|---|---|
| Storage Location | Storage Location where the view 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: node will be deployed/redeployed when changes are detected If FALSE: node will not be deployed or will be dropped during redeployment |
Fact Advanced Deploy Options
You can create the node as:
Fact Advanced Deploy Table
| Setting | Description |
|---|---|
| Create As | Table |
| Multi Source | Toggle: True/False Implementation of SQL UNIONs True: Combine multiple sources in a single node True Options: - UNION: Combines with duplicate elimination - UNION ALL: Combines without duplicate elimination - INSERT: Individual insert for each source False: Single source node or multiple sources combined using a join. |
| Primary key | Allows you to specify one or more columns based on which primary constraint is set on the table. Primary Key Name: Primary key constraint name. If not specified defaults to pk_ {{tablename}} |
| Business key | Required column for Type 1 and Type 2 Dimensions . Note: Geometry and Geography data type columns are not supported as business key columns. |
| Unmatched Record Strategy | Available for single source nodes and only when business key is chosen - NO DELETE: An option introduced to ensure existing data flows remain intact and unchanged, preventing any delete operation on the target table. - HARD DELETE: Permanent removal of records from the target table. |
| Exclude Columns from Merge | Available only when business key is chosen. Allows you to specify one or more columns that are excluded during both the comparison (matching) and updating phases of the MERGE statement. |
| Truncate Before | Toggle: True/False This determines whether a table will be overwritten each time a task executes. True: Uses INSERT OVERWRITE False: Uses INSERT to append data |
| Enable tests | Toggle: True/False Determines if tests are enabled |
| Cluster key | True: Allows you to specify the column based on which clustering is to be done Allow Expressions Cluster Key: Allows to add an expression to the specified cluster key False: No clustering done |
| Distinct | Toggle: True/False True: Group by All is invisible. DISTINCT data is chosen for processing. False: Group by All is visible. |
| Group by All | Toggle: True/False True: DISTINCT is invisible, data grouped by all columns False: DISTINCT is visible |
| Order By | Toggle: True/False True: Sort column and sort order drop down are visible and are required to form order by clause. False: Sort options invisible |
| Pre-SQL | SQL to execute before data insert operation |
| Post-SQL | SQL to execute after data insert operation |
Fact Advanced Deploy Transient Table
| Setting | Description |
|---|---|
| Create As | Transient Table |
| Multi Source | Toggle: True/False Implementation of SQL UNIONs True: Combine multiple sources in a single node True Options: - UNION: Combines with duplicate elimination - UNION ALL: Combines without duplicate elimination - INSERT: Individual insert for each source False: Single source node or multiple sources combined using a join. |
| Primary key | Allows you to specify one or more columns based on which primary constraint is set on the table. Primary Key Name: Primary key constraint name. If not specified defaults to pk_ {{tablename}} |
| Business key | Required column for Type 1 and Type 2 Dimensions |
| Unmatched Record Strategy | Available for single source nodes and only when business key is chosen - NO DELETE: An option introduced to ensure existing data flows remain intact and unchanged, preventing any delete operation on the target table. - HARD DELETE: Permanent removal of records from the target table. |
| Exclude Columns from Merge | Available only when business key is chosen. Allows you to specify one or more columns that are excluded during both the comparison (matching) and updating phases of the MERGE statement. |
| Truncate Before | Toggle: True/False This determines whether a table will be overwritten each time a task executes. True: Uses INSERT OVERWRITE False: Uses INSERT to append data |
| Enable tests | Toggle: True/False Determines if tests are enabled |
| Cluster key | True: Allows you to specify the column based on which clustering is to be done Allow Expressions Cluster Key: Allows to add an expression to the specified cluster key False: No clustering done |
| Distinct | Toggle: True/False True: Group by All is invisible. DISTINCT data is chosen for processing. False: Group by All is visible. |
| Group by All | Toggle: True/False True: DISTINCT is invisible, data grouped by all columns False: DISTINCT is visible |
| Order By | Toggle: True/False True: Sort column and sort order drop down are visible and are required to form order by clause. False: Sort options invisible |
| Pre-SQL | SQL to execute before data insert operation |
| Post-SQL | SQL to execute after data insert operation |
Fact Advanced Deploy View
| Setting | Description |
|---|---|
| Create As | View |
| Override Create SQL | Toggle: True/False True: Executes custom Create SQL False: Creates view based on chosen options |
| Multi Source | Toggle: True/False Implementation of SQL UNIONs True: Combine multiple sources in a single node True Options: - UNION: Combines with duplicate elimination - UNION ALL: Combines without duplicate elimination - INSERT: Individual insert for each source False: Single source node or multiple sources combined using a join. |
| Distinct | Toggle: True/False True: Group by All is invisible. DISTINCT data is chosen for processing. False: Group by All is visible. |
| Group by All | Toggle: True/False True: DISTINCT is invisible, data grouped by all columns False: DISTINCT is visible |
Fact Advanced Deploy Joins
Join conditions and other clauses like where, qualify can be specified in the join space next to mapping of columns in the Coalesce app.
📘 Specify Group by and Order by Clauses
Best Practice is to specify group by and order by clauses in this space if you are not opting for the group by all and order by provided in OPTIONS config.
Fact Advanced Deploy Deployment
Fact Advanced Deploy Initial Deployment
When deployed for the first time into an environment the Fact node of materialization type table will execute the below stage:
| Stage | Description |
|---|---|
| Create Fact Table | This will execute a CREATE OR REPLACE statement and create a table in the target environment |
| Create Fact View | This will execute a CREATE OR REPLACE statement and create a view in the target environment |
Fact Advanced Deploy Redeployment
After the Fact node of materialization type table has been deployed for the first time into a target environment, subsequent deployments may result in either altering the Fact Table or recreating the Fact table.
Altering the Fact Tables/Transient Tables
A few types of column or table changes will result in an ALTER statement to modify the Persistent Table in the target environment, whether these changes are made individually or all together:
- Changing table names
- Dropping existing columns
- Altering column data types
- Adding new columns
The following stages are executed:
| Stage | Description |
|---|---|
| Rename Table| Alter Column | Delete Column | Add Column | Edit table description | Alter table statement is executed to perform the alter operation |
Sometimes, changes to config can result in metadata changes from node edits, DML changes, or storage updates. A few cases are listed below:
- Changes in business keys
- Changes to change tracking keys
- Changes in join clauses
- Transformations made at column level
- Changing DML options like DISTINCT, ORDER BY, GROUP BY ALL
And many more. Most of the time, specific ‘is’ and ‘was’ values will be displayed to specifically show what changed.
The following stages are executed:
| Stage | Description |
|---|---|
| Metadata Update | Business Keys | Change Tracking | Distinct | Transformation | Join | A dummy statement would execute with specific changes listed in comments |
Recreating the Fact Advanced Deploy Views
The subsequent deployment of Fact node of materialization type view with changes in view definition, adding table description or renaming view results recreating the view.
The following stages are executed:
| Stage | Description |
|---|---|
| Create View | Creates a new view with updated definition |
Fact Advanced Deploy Undeployment
If a Fact Node of materialization type table is deleted from a Workspace, that Workspace is committed to Git and that commit deployed to a higher level environment then the Fact Table in the target environment will be dropped.
This is executed in stages:
| Stage | Description |
|---|---|
| Drop table/view | Removes the table or view from the environment |
Drop and recreate Fact View/Table/Transient
| Change | Stages Executed |
|---|---|
| View to table/transient table | Drop view Create Fact table/transient table |
| Table/transient table to View | Drop table/transient table Create Fact view |
| Table to transient table or vice versa | Drop table/transient table Create Fact table/transient table |
📘 Materialization Type of Dimension node
When the materialization type of Dimension node is changed from table/transient table to View and use Override Create SQL for view creation, ensure that the below change is made in the stage function in Create SQL tab so that the order of deployment is maintained.
Factless Fact Advanced Deploy
The Coalesce Fact UDN is a versatile node that allows you to develop and deploy a Fact table in Snowflake.
A factless fact table is used to record events or situations that have no measures, and it has the same level of detail as the dimensions.
Factless Fact Advanced Deploy Node Configuration
Factless Fact Advanced Deploy Node Properties
| Setting | Description |
|---|---|
| Storage Location | Storage Location where the view 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: node will be deployed/redeployed when changes are detected If FALSE: node will not be deployed or will be dropped during redeployment |
Factless Fact Advanced Deploy Options
| Setting | Description |
|---|---|
| Create As | Table or Transient Table |
| Multi Source | Toggle: True/False Implementation of SQL UNIONs True: Combine multiple sources in a single node True Options: - UNION: Combines with duplicate elimination - UNION ALL: Combines without duplicate elimination - INSERT: Individual insert for each source False: Single source node or multiple sources combined using a join. |
| Truncate Before | Toggle: True/False This determines whether a table will be overwritten each time a task executes. True: Uses INSERT OVERWRITE False: Uses INSERT to append data |
| Enable tests | Toggle: True/False Determines if tests are enabled |
| Distinct | Toggle: True/False True: Group by All is invisible. DISTINCT data is chosen for processing. False: Group by All is visible. |
| Group by All | Toggle: True/False True: DISTINCT is invisible, data grouped by all columns False: DISTINCT is visible |
| Order By | Toggle: True/False True: Sort column and sort order drop down are visible and are required to form order by clause. False: Sort options invisible |
| Pre-SQL | SQL to execute before data insert operation |
| Post-SQL | SQL to execute after data insert operation |
Factless Fact Advanced Deploy Joins
Join conditions and other clauses like where, qualify can be specified in the join space next to mapping of columns in the Coalesce app.
📘 Specify Group by and Order by Clauses
Best Practice is to specify group by and order by clauses in this space if you are not opting for the group by all and order by provided in OPTIONS config.
Factless Fact Advanced Deploy Deployment
Factless Fact Advanced Deploy Initial Deployment
When deployed for the first time into an environment the Factless Fact node of materialization type table will execute the below stage:
| Stage | Description |
|---|---|
| Create Fact Table | This will execute a CREATE OR REPLACE statement and create a table in the target environment |
Factless Fact Advanced Deploy Redeployment
After the Fact node of materialization type table has been deployed for the first time into a target environment, subsequent deployments may result in either altering the Fact Table or recreating the Fact table.
Altering the Factless Fact Tables/Transient Tables
A few types of column or table changes will result in an ALTER statement to modify the Persistent Table in the target environment, whether these changes are made individually or all together:
- Changing table names
- Dropping existing columns
- Altering column data types
- Adding new columns
The following stages are executed:
| Stage | Description |
|---|---|
| Rename Table| Alter Column | Delete Column | Add Column | Edit table description | Alter table statement is executed to perform the alter operation accordingly |
Sometimes, changes to config can result in metadata changes from node edits, DML changes, or storage updates. A few cases are listed below:
- Changes in business keys
- Changes to change tracking keys
- Changes in join clauses
- Transformations made at column level
- Changing DML options like DISTINCT, ORDER BY, GROUP BY ALL
And many more. Most of the time, specific ‘is’ and ‘was’ values will be displayed to specifically show what changed.
The following stages are executed:
| Stage | Description |
|---|---|
| Metadata Update | Business Keys | Change Tracking | Distinct | Transformation | Join | A dummy statement would execute with specific changes listed in comments |
Drop and Recreate Factless Fact Table/Transient Table
When the materialization type of Factless Fact node is changed from table to transient table or transient table to table, the below stages are executed:
| Stage | Description |
|---|---|
| Drop table/transient table | Removes existing table |
| Create Factless Fact table/transient table | Creates new table with updated configuration |
Factless Fact Advanced Deploy Undeployment
If a Fact Node of materialization type table is deleted from a Workspace, that Workspace is committed to Git and that commit deployed to a higher level environment then the Fact Table in the target environment will be dropped.
This is executed in two stages:
| Stage | Description |
|---|---|
| Drop table/view | Removes the table or view from the environment |
Redeployment with no changes
If the nodes are redeployed with no changes compared to previous deployment, then no stages are executed
Code
Work Advanced Deploy Code
| Component | Link |
|---|---|
| Node definition | definition.yml |
| Create Template | create.sql.j2 |
| Run Template | run.sql.j2 |
Persistent Stage Advanced Deploy Code
| Component | Link |
|---|---|
| Node definition | definition.yml |
| Create Template | create.sql.j2 |
| Run Template | run.sql.j2 |
Dimension Advanced Deploy Code
| Component | Link |
|---|---|
| Node definition | definition.yml |
| Create Template | create.sql.j2 |
| Run Template | run.sql.j2 |
Fact Advanced Deploy Code
| Component | Link |
|---|---|
| Node definition | definition.yml |
| Create Template | create.sql.j2 |
| Run Template | run.sql.j2 |
Factless Fact Advanced Deploy Code
| Component | Link |
|---|---|
| Node definition | definition.yml |
| Create Template | create.sql.j2 |
| Run Template | run.sql.j2 |
Versions
Available versions of the package.
| Version # | Release Date | Notes |
|---|---|---|
| 2.2.0 | November 13, 2025 | Added Primary Key, Delete Strategy, Merge Exclude Features |
| 2.1.1 | August 26, 2025 | Metadata Updates - Block Comment Changes |
| 2.1.0 | August 20, 2025 | Metadata Refresh during deployment - Added |
| 2.0.1 | July 29, 2025 | Join clause update for job refresh |
| 2.0.0 | July 02, 2025 | Removal of deploy phases from stage function across all node types |
| 1.5.2 | May 14, 2025 | Testing added for View materialization type ,dimension fixes |
| 1.5.1 | May 08, 2025 | Support for Geometry and Geography data types, View-Qualify moved to functional nodes package. |
| 1.5.0 | April 28, 2025 | New node type View-Qualify added. Dimension fix included |
| 1.4.0 | April 15, 2025 | Added Update Strategy with INSERT/UPDATE which Separately executes UPDATE and INSERT Statements |
| 1.3.1 | March 13, 2025 | Added Zero Key Record Advanced Options To Dimension Advanced Deploy |
| 1.3.0 | March 06, 2025 | Advanced options for Zero Key Record added for Dimension |
| 1.2.1 | February 21, 2025 | Fix for changing column descriptions and removed "Nothing to do." stage. |
| 1.2.0 | February 04, 2025 | ASOF join functionality has been added for the Work Advanced Deploy Node |
| 1.1.7 | December 12, 2024 | Storage Location Redeployment issue fix - NM-95 |