Snowflake - Base Node Types Advanced Deploy – Brief Summary

• Fact nodes
  Represent measurable business events such as sales, transactions, or usage. These are the core tables used for reporting, KPIs, and trend analysis.

• Dimension nodes
  Provide business context for facts, like customer, product, time, or location. They help slice and analyze facts in meaningful ways.

• Factless fact nodes
  Capture business events that do not have numeric measures, such as attendance, eligibility, or process milestones. Useful for compliance, tracking, and operational analysis.

• Persistent nodes
  Store curated, reusable data that remains stable over time. They act as trusted reference layers, reduce reprocessing, and ensure consistency across reports and teams.

• Work nodes
  Temporary or intermediate processing layers used during transformations. They support complex logic and performance optimization but are not intended for direct business consumption.

Summary:
Together, these node types ensure data is accurate, reusable, scalable, and aligned with business reporting and decision-making needs.

---

Nodetypes Config Matrix

Category	Feature	Dim	Fact	Factless	Work	PStage
Create	Create As Table	✅	✅	✅	✅	✅
Create	Create As Transient Table	✅	✅	✅	✅	✅
Create	Create As View	✅	✅	⬜	✅	⬜
Create	Create with Override SQL	✅	✅	⬜	✅	⬜
Create	Primary Key	✅	✅	⬜	⬜	⬜
Create	Cluster Key	✅	✅	✅	✅	✅
Load	MultiSource	✅	✅	✅	✅	✅
Load	Insert Strategy	✅	✅	✅	✅	✅
Load	Update Strategy	✅	⬜	⬜	⬜	⬜
Load	Unmatched Record Strategy	✅	✅	⬜	⬜	⬜
Load	Business Key	✅	✅	⬜	⬜	✅
Load	Last Modified Comparison	✅	✅	⬜	⬜	✅
Load	Change Tracking	✅	⬜	⬜	⬜	✅
Load	Exclude Columns from Merge	✅	✅	⬜	⬜	✅
Load	Truncate Before	✅	✅	✅	✅	✅
Load	Distinct	✅	✅	✅	✅	✅
Load	Group By All	✅	✅	✅	✅	✅
Load	Order By	✅	✅	✅	✅	✅
Load	Insert Zero Key Record	✅	⬜	⬜	⬜	⬜
Load	ASOF Join Options	⬜	⬜	⬜	✅	⬜
Load	Methods	MERGE INSERT/UPDATE	MERGE INSERT	MERGE	INSERT	MERGE INSERT
Others	Enable Tests	✅	✅	✅	✅	✅
Others	Pre-SQL	✅	✅	✅	✅	✅
Others	Post-SQL	✅	✅	✅	✅	✅

---

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:

• Node Properties
• Options

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:

• Table
• View
• Transient Table

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 Determines whether the table is truncated before execution. True: Table is truncated before the DML operation runs. False: The DML operation runs without truncation.
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 Determines whether the table is truncated before execution. True: Table is truncated before the DML operation runs. False: The DML operation runs without truncation.
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 Determines whether the table is truncated before execution. True: Table is truncated before the DML operation runs. False: The DML operation runs without truncation.
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:

1. Changing table names
2. Dropping existing columns
3. Altering column data types
4. 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:

1. Changes in business keys
2. Changes to change tracking keys
3. Changes in join clauses
4. Transformations made at column level
5. 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.

Node Type Switching

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

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

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:

• Node Properties
• Options

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:

• Table
• Transient Table

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.
Last Modified Comparison	True:When enabled we can do timestamp based CDC False:Regular CDC based on Change tracking columns is done
Last Modified Column(Enabled for Last Modified Comparison)	Timestamp/Incremental ID column can be chosen.Based on which CDC is done
Treat Null as Current timestamp(Enabled for Last Modified Comparison)	Records with NULL timestamp are updated in target
Type 2 Dimension(Enabled for Last Modified Comparison)	CDC is based on timestamp/ID column chosen above.Change tracking columns are not enabled for this scenario
Change tracking	Required column for Type 2
Truncate Before	Toggle: True/False Determines whether the table is truncated before execution. True: Table is truncated before the DML operation runs. False: The DML operation runs without truncation.
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
Last Modified Comparison	True:When enabled we can do timestamp based CDC False:Regular CDC based on Change tracking columns is done
Last Modified Column(Enabled for Last Modified Comparison)	Timestamp/Incremental ID column can be chosen.Based on which CDC is done
Treat Null as Current timestamp(Enabled for Last Modified Comparison)	Records with NULL timestamp are updated in target
Type 2 Dimension(Enabled for Last Modified Comparison)	CDC is based on timestamp/ID column chosen above.Change tracking columns are not enabled for this scenario
Change tracking	Required column for Type 2
Truncate Before	Toggle: True/False Determines whether the table is truncated before execution. True: Table is truncated before the DML operation runs. False: The DML operation runs without truncation.
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:

1. Changing table names
2. Dropping existing columns
3. Altering column data types
4. 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:

1. Changes in business keys
2. Changes to change tracking keys
3. Changes in join clauses
4. Transformations made at column level
5. 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

Node Type Switching

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

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

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

• Node Properties
• Options

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:

• Table
• Transient Table
• View

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.
Last Modified Comparison	True:When enabled we can do timestamp based CDC False:Regular CDC based on Change tracking columns is done
Last Modified Column(Enabled for Last Modified Comparison)	Timestamp/Incremental ID column can be chosen.Based on which CDC is done
Treat Null as Current timestamp(Enabled for Last Modified Comparison)	Records with NULL timestamp are updated in target
Type 2 Dimension(Enabled for Last Modified Comparison)	CDC is based on timestamp/ID column chosen above.Change tracking columns are not enabled for this scenario
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 Determines whether the table is truncated before execution. True: Table is truncated before the DML operation runs. False: The DML operation runs without truncation.
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
Last Modified Comparison	True:When enabled we can do timestamp based CDC False:Regular CDC based on Change tracking columns is done
Last Modified Column(Enabled for Last Modified Comparison)	Timestamp/Incremental ID column can be chosen.Based on which CDC is done
Treat Null as Current timestamp(Enabled for Last Modified Comparison)	Records with NULL timestamp are updated in target
Type 2 Dimension(Enabled for Last Modified Comparison)	CDC is based on timestamp/ID column chosen above.Change tracking columns are not enabled for this scenario
Change tracking	Required column for Type 2 Dimension
Truncate Before	Toggle: True/False Determines whether the table is truncated before execution. True: Table is truncated before the DML operation runs. False: The DML operation runs without truncation.
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:

1. Changing table names
2. Dropping existing columns
3. Altering column data types
4. 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:

1. Changes in business keys
2. Changes to change tracking keys
3. Changes in join clauses
4. Transformations made at column level
5. 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.

Node Type Switching

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

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

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

• Node Properties
• Options

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:

• Table
• Transient Table
• View

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.
Last Modified Comparison	True:When enabled we can do timestamp based CDC False:Regular CDC based on Change tracking columns is done
Last Modified Column(Enabled for Last Modified Comparison)	Timestamp/Incremental ID column can be chosen.Based on which CDC is done
Treat Null as Current timestamp(Enabled for Last Modified Comparison)	Records with NULL timestamp are updated in target
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 Determines whether the table is truncated before execution. True: Table is truncated before the DML operation runs. False: The DML operation runs without truncation.
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
Last Modified Comparison	True:When enabled we can do timestamp based CDC False:Regular CDC based on Change tracking columns is done
Last Modified Column(Enabled for Last Modified Comparison)	Timestamp/Incremental ID column can be chosen.Based on which CDC is done
Treat Null as Current timestamp(Enabled for Last Modified Comparison)	Records with NULL timestamp are updated in target
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 Determines whether the table is truncated before execution. True: Table is truncated before the DML operation runs. False: The DML operation runs without truncation.
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:

1. Changing table names
2. Dropping existing columns
3. Altering column data types
4. 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:

1. Changes in business keys
2. Changes to change tracking keys
3. Changes in join clauses
4. Transformations made at column level
5. 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

Node Type Switching

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

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

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

• Node Properties
• Options

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 Determines whether the table is truncated before execution. True: Table is truncated before the DML operation runs. False: The DML operation runs without truncation.
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:

1. Changing table names
2. Dropping existing columns
3. Altering column data types
4. 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:

1. Changes in business keys
2. Changes to change tracking keys
3. Changes in join clauses
4. Transformations made at column level
5. 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

---

Node Type Switching

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

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

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

---

Node Type Switching Logic

Current MaterializationType	Desired MaterializationType	Stage
Table	Table	1. Warning (if applicable) 2. Metadata Update(if applicable) 3. Alter
Transient Table	TransientTable	1. Warning (if applicable) 2. Metadata Update(if applicable) 3. Alter
View	View	1. Warning (if applicable) 2. Create
Any Other	Table	1. Warning (if applicable) 2. Drop 3. Create
Any Other	View	1. Warning (if applicable) 2. Drop 3. Create
Any Other	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.

---

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

Macros