Base Node Types

‹ View all packages

Package ID: @coalesce/snowflake/base-node-types

Supported Platform:

Latest Version: 1.5.0 (February 27, 2026)

data warehousedimension tabledimensional modelingeltfact tablestagingstar schematransformation

Overview

Coalesce base Node Types to construct facts and dimensions.

Installation

Copy the Package ID: @coalesce/snowflake/base-node-types
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

Coalesce Base Node Types Package

The Coalesce Base Node Types Package includes:

Work
Persistent Stage
Dimension
Fact
Factless Fact
View
SQL Stage
Code

Work

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 Node Configuration

The Work node type has two configuration groups:

Node Properties
Options

Fact_config

Work 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

Work Options

You can create the node as:

Table
View

Work Options Table

Work_options_table1

Property	Description
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 truncated before data load. True:Truncate table stage gets executed False: Table is not truncated before data load
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 is grouped by all columns for processing 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 column and sort order drop down are 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 Options View

Work_options_view1

Setting	Description
Override Create SQL	Toggle: True/False True: Customized Create SQL specified in the Create SQL space is executed. All other options are invisible except 'Enable Tests' False: Create view SQL based on options chosen are framed and executed
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 False: Single source node or multiple sources combined using a join
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 is grouped by all columns for processing False: DISTINCT is visible
ASOF Join	Toggle: True/False True: ASOF Join Options will be visible. False: ASOF Join Options will be invisible

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 Joins

Join conditions and other clauses can be specified in the join space next to mapping of columns in the UI.

work_join

📘 Specify Group by and Order by Clauses

You should 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 ASOF Joins

After selecting options for ASOF Join,Click on Generate join, use the 'Copy To Editor' to add the new ASOF join.

Work Deployment

Work Initial Deployment

When deployed for the first time into an environment the Work node of materialization type table 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 Redeployment

After the WORK node with materialization type table 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

A few types of column or table changes will result in an ALTER statement to modify the Work 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
Clone Table	Creates an internal table
Rename Table\| Alter Column \| Delete Column \| Add Column \| Edit table description	Alter table statement is executed to perform the alter operation
Swap Cloned Table	Upon successful completion of all updates, the clone replaces the main table ensuring that no data is lost
Delete Table	Drops the internal table

Recreating the Work Tables

If any of the following change are detected, then the table will be recreated using a CREATE or REPLACE.

Join clause
Adding transformation
Changes in configuration like adding distinct, group by, or order by

One of the following stages are executed:

Stage	Description
Create Table	Creates a new table
Replace Table	Replaces an existing table

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
Delete View	Removes existing view
Create View	Creates new view with updated definition

Work Undeployment

If a Work 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 WorkTable in the target environment will be dropped.

This is executed in two stages:

Stage	Description
Delete Table	Coalesce Internal table is dropped
Delete Table	Target table in Snowflake is dropped

If a Work Node of materialization type view is deleted from a Workspace, that Workspace is committed to Git and that commit deployed to a higher level environment then the WorkView in the target environment will be dropped.

The stage executed:

Stage	Description
Delete View	Drops the existing Work view from target environment

Persistent Stage

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 Node Configuration

The Persistent node type has two configuration groups:

Node Properties
Options

Fact_config

Persistent Stage 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

Persistent Stage Options

Option	Description
Create As	Table is the only option at this time
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 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 This determines whether a table will be truncated before data load. True:Truncate table stage gets executed False: Table is not truncated before data load
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 is grouped by all columns for processing 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 column and sort order drop down are invisible
Pre-SQL	SQL to execute before data insert operation
Post-SQL	SQL to execute after data insert operation

Persistent Stage Joins

Join conditions and other clauses can be specified in the join space next to mapping of columns in the UI.

pstage_join

📘 Specify Group by and Order by Clauses

You should 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 Deployment

Persistent Stage Initial Deployment

When deployed for the first time into an environment the Persistent node will execute the below stage:

Stage	Description
Create Persistent Table	This will execute a CREATE OR REPLACE statement and create a table in the target environment

Persistent Stage 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

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
Clone Table	Creates an internal table
Rename Table\| Alter Column \| Delete Column \| Add Column \| Edit table description	Alter table statement is executed to perform the alter operation
Swap Cloned Table	Upon successful completion of all updates, the clone replaces the main table ensuring that no data is lost
Delete Table	Drops the internal table

Recreating the Persistent Tables

If any of the following change are detected, then the table will be recreated using a CREATE or REPLACE.

Join clause
Adding transformation
Changes in configuration like adding distinct, group by, or order by

One of the following stages are executed:

Stage	Description
Create Table	Creates a new table
Replace Table	Replaces an existing table

Persistent Stage 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 two stages:

Stage	Description
Delete Table	Drops table
Drop Table or View	Removes the table

Dimension

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 Node Configuration

The Dimension node type has two configuration groups:

Node Properties
Options

Fact_config

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

Dimension Table Options

Options	Description
Create As	Table or View
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 False: Single source node or multiple sources combined using a join
Business key	Required column for both 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
Truncate Before	Toggle: True/False This determines whether a table will be truncated before data load. True:Truncate table stage gets executed False: Table is not truncated before data load
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 is grouped by all columns for processing 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 column and sort order drop down are 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 View Options

Work_options_view1

Options	Description
Override Create SQL	Toggle: True/False True: Customized Create SQL specified in the Create SQL space is executed. All other options are invisible except 'Enable Tests' False: Create view SQL based on options chosen are framed and executed
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 False: Single source node or multiple sources combined using a join
Business key	Required column for both Type 1 and Type 2 Dimensions
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 is grouped by all columns for processing False: DISTINCT is visible

Dimension Joins

Join conditions and other clauses can be specified in the join space next to mapping of columns in the UI.

Dimension_join

📘 Specify Group by and Order by Clauses

You should 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 Deployment

Dimension Initial Deployment

When deployed for the first time into an environment the Dimension node of materialization type table will execute the Create Dimension Table 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 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

A few types of column or table changes will result in an ALTER statement to modify the Dimension 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
Clone Table	Creates an internal table
Rename Table\| Alter Column \| Delete Column \| Add Column \| Edit table description	Alter table statement is executed to perform the alter operation
Swap Cloned Table	Upon successful completion of all updates, the clone replaces the main table ensuring that no data is lost
Delete Table	Drops the internal table

Recreating the Dimension Tables

If any of the following change are detected, then the table will be recreated using a CREATE or REPLACE.

Join clause
Adding transformation
Changes in configuration like adding distinct, group by, or order by

One of the following stages are executed:

Stage	Description
Create Table	Creates a new table
Replace Table	Replaces an existing table

Recreating the Dimension Views

Any of the following changes to views will result in deleting and recreating the Dimension view.

View defintion
Adding table description
Renaming view results

Dimension Undeployment

If a Dimension Node 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.

This is executed in two stages:

Stage	Description
Delete Table	Coalesce Internal table is dropped
Delete Table	Target table in Snowflake is dropped

If a Dimension Node of materialization type view is deleted from a Workspace, that Workspace is committed to Git and that commit deployed to a higher level environment then the Dimension View in the target environment will be dropped.

Stage	Description
Delete View	Drops the existing Dimension view from target environment.

Fact

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 Node Configuration

The Fact node has two configuration groups:

Node Properties
Options

Fact_config

Fact Node Properties

Properties	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

Fact Options

Options	Description
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 False: Single source node or multiple sources combined using a join
Business key	Required column for Fact table creation. 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
Truncate Before	Toggle: True/False This determines whether a table will be truncated before data load. True:Truncate table stage gets executed False: Table is not truncated before data load
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 is grouped by all columns for processing 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 column and sort order drop down are invisible
Pre-SQL	SQL to execute before data insert operation
Post-SQL	SQL to execute after data insert operation

Fact View

Work_options_view1

Setting	Description
Override Create SQL	Toggle: True/False True: Customized Create SQL specified in the Create SQL space is executed. All other options are invisible except 'Enable Tests' False: Create view SQL based on options chosen are framed and executed
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 False: Single source node or multiple sources combined using a join
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 is grouped by all columns for processing False: DISTINCT is visible

Fact Joins

Join conditions and other clauses like where, qualify can be specified in the join space next to mapping of columns in the UI.

fact_join

📘 Specify Group by and Order by Clauses

You should 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 Deployment

Fact Initial Deployment

When deployed for the first time into an environment the Fact node of materialization type table will execute the Create Fact Table 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 Redeployment

After the Fact node 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

A few types of column or table changes will result in an ALTER statement to modify the Fact 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
Clone Table	Creates an internal table
Rename Table\| Alter Column \| Delete Column \| Add Column \| Edit table description	Alter table statement is executed to perform the alter operation
Swap Cloned Table	Upon successful completion of all updates, the clone replaces the main table ensuring that no data is lost
Delete Table	Drops the internal table

Recreating the Fact Tables

If any changes like change in join clause, adding transformations, change in business key column, change in configs like adding distinct, group by or orderby, the Fact Table will be recreated by running a CREATE OR REPLACE statement.

If any of the following change are detected, then the table will be recreated using a CREATE or REPLACE.

Join clause
Adding transformation
Change in business key
Changes in configuration like adding distinct, group by, or order by

One of the following stages are executed:

Stage	Description
Create Table	Creates a new table
Replace Table	Replaces an existing table

Recreating the Fact Views

The following stages are executed:

Stage	Description
Delete View	Removes existing view
Create View	Creates new view with updated definition

Fact Undeployment

If a Fact Node 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
Delete Table	Coalesce Internal table is dropped
Delete Table	Target table in Snowflake is dropped

Factless Fact

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 Node Configuration

The Fact node has two configuration groups:

Node Properties
Options

Factless Fact Node Properties

Properties	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

Factless Fact Options

Factless_options

Options	Description
Create As	Table is the only option at this time
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 False: Single source node or multiple sources combined using a join
Truncate Before	Toggle: True/False This determines whether a table will be truncated before data load. True:Truncate table stage gets executed False: Table is not truncated before data load
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 is grouped by all columns for processing 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 column and sort order drop down are invisible
Pre-SQL	SQL to execute before data insert operation
Post-SQL	SQL to execute after data insert operation

Factless Fact Joins

Join conditions and other clauses like where, qualify can be specified in the join space next to mapping of columns in the UI.

fact_join

📘 Specify Group by and Order by Clauses

You should 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 Deployment

Factless Fact Initial Deployment

When deployed for the first time into an environment the Factless Fact node of materialization type table will execute the Create Fact Table stage.

Stage	Description
Create Fact Table	This will execute a CREATE OR REPLACE statement and create a table in the target environment

Factless Fact Redeployment

After the Fact node 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

A few types of column or table changes will result in an ALTER statement to modify the Factless Fact 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
Clone Table	Creates an internal table
Rename Table\| Alter Column \| Delete Column \| Add Column \| Edit table description	Alter table statement is executed to perform the alter operation
Swap Cloned Table	Upon successful completion of all updates, the clone replaces the main table ensuring that no data is lost
Delete Table	Drops the internal table

Recreating the Factless Fact Tables

If any of the following change are detected, then the table will be recreated using a CREATE or REPLACE.

Join clause
Adding transformations
Change in business key
Changes in configuration like adding distinct, group by, or order by

One of the following stages are executed:

Stage	Description
Create Table	Creates a new table
Replace Table	Replaces an existing table

Factless Fact Undeployment

If a Fact Node 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
Delete Table	Coalesce Internal table is dropped
Delete Table	Target table in Snowflake is dropped

View

The Coalesce View UDN is a versatile node that allows you to develop and deploy a View in Snowflake.

A view allows the result of a query to be accessed as if it were a table. Views serve a variety of purposes, including combining, segregating, and protecting data.

View Node Configuration

The View node type has two configuration groups:

Node Properties
Options

Fact_config

View Node Properties

Properties	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

View Options

Options	Description
Override Create SQL	Toggle: True/False True: Customized Create SQL specified in the Create SQL space is executed. All other options are invisible False: Create view SQL based on options chosen are framed and executed
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 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 is grouped by all columns for processing False: DISTINCT is visible
Secure	Toggle: True/False True: A secured view is created False: A normal view is created

View 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 Clauses

Best Practice is to specify group by clauses in this space if you are not opting for the group by all provided in OPTIONS config.

View Deployment

View Initial Deployment

When deployed for the first time into an environment the View node will execute the Create View stage.

Stage	Description
Create View	This will execute a CREATE OR REPLACE statement and create a View in the target environment

View Redeployment

The subsequent deployment of View node with changes in view definition, adding table description, adding secure option or renaming view results in deleting the existing view and recreating the view.

The following stages are executed:

Stage	Description
Delete View	Removes existing view
Create View	Creates new view with updated definition

View Undeployment

If a View Node is deleted from a Workspace, that Workspace is committed to Git and that commit deployed to a higher level environment then the View in the target environment will be dropped.

This is executed in the below stage:

Stage	Description
Delete View	Removes the view from the environment

SQL Stage

The SQL Stage Node is a powerful transformation tool within Coalesce that allows developers to write custom, hand-coded SQL instead of using the standard graphical column-mapping interface. It is ideal for complex transformations, advanced window functions, or multi-step logic that is difficult to represent via the standard UI. While it provides maximum flexibility, it shifts the responsibility of column definition and logic maintenance to the SQL author.

Prerequisites

Before using this node, ensure the following requirements are met:

Feature Flag: The isSQLEditorEnabled feature flag must be enabled for your Coalesce organization.
Database Permissions: The user role must have sufficient privileges to execute queries and create objects in the target environment.

Node Configuration

Node properties

Node Properties

Setting	Description
Storage Location	Storage Location where the work will be created

Usage Examples

The following patterns represent common ways to use the SQL Stage Node.

Basic Transformation & Cleaning
Standard pattern for renaming columns and handling nulls.

SELECT
"O_ORDERKEY",
"O_CUSTKEY",
UPPER("O_ORDERSTATUS") AS "O_ORDERSTATUS",
COALESCE("O_TOTALPRICE", 0) AS "O_TOTALPRICE",
"O_ORDERDATE"
FROM {{ ref('SOURCE_DATA', 'ORDERS') }}
WHERE "O_ORDERSTATUS" != 'F'

Using CTEs (Common Table Expressions)
For more complex, multi-step logic.

WITH priority_counts AS (
    SELECT 
        "O_ORDERPRIORITY",
        COUNT(*) as order_count
    FROM {{ ref('SOURCE_DATA', 'ORDERS') }}
    GROUP BY 1
)
SELECT * FROM priority_counts

Multi-CTE Transformation With Window Functions
Complex transformations that would otherwise require multiple nodes can be written as a single SQL statement. Coalesce tracks lineage through each CTE and down to the source tables

WITH ordered_orders AS (
-- CTE 1: Rank every order for each customer by date
SELECT
O_CUSTKEY,
O_ORDERKEY,
O_ORDERDATE,
O_TOTALPRICE,
O_ORDERSTATUS,
ROW_NUMBER() OVER (
PARTITION BY O_CUSTKEY
ORDER BY O_ORDERDATE ASC, O_ORDERKEY ASC
) AS order_rank
FROM {{ ref('SOURCE_DATA', 'ORDERS') }}
),
first_orders AS (
-- CTE 2: Filter to keep only the first order (rank 1) for each customer
SELECT
O_CUSTKEY,
O_ORDERKEY AS first_order_id,
O_ORDERDATE AS first_purchase_date,
O_TOTALPRICE AS first_order_value,
O_ORDERSTATUS
FROM ordered_orders
WHERE order_rank = 1
)
-- Final Select: Add metadata and return the results
SELECT
f.O_CUSTKEY,
f.first_order_id,
f.first_purchase_date,
f.first_order_value,
f.O_ORDERSTATUS,
CURRENT_TIMESTAMP() AS refreshed_at,
'Initial Customer Purchase' AS record_type
FROM first_orders f;

Using Recursive CTE

WITH RECURSIVE date_series AS (
    SELECT 
        MIN(O_ORDERDATE) AS report_date
    FROM {{ ref('SRC','ORDERS') }}

    UNION ALL

    SELECT 
        DATEADD(day, 1, report_date)
    FROM date_series
    WHERE report_date < (SELECT DATEADD(day, 10, MIN(O_ORDERDATE)) FROM {{ ref('SRC','ORDERS') }} )
)

SELECT 
    ds.report_date,
    COUNT(o.O_ORDERKEY) AS total_orders,
    SUM(o.O_TOTALPRICE) AS daily_revenue
FROM date_series ds
LEFT JOIN {{ ref('SRC','ORDERS') }} o 
    ON ds.report_date = o.O_ORDERDATE
GROUP BY 1
ORDER BY 1

Supported SQL functionality

Multi-Source Joins & Enrichment: The ability to reference and join multiple upstream nodes (e.g., Joining ORDERS and CUSTOMER) within a single stage to flatten data or create enriched wide tables while maintaining full lineage for every source.
Conditional Logic via CASE Statements: Support for complex business rules and data categorization using standard CASE WHEN syntax to create derived columns based on multiple logical conditions.
Flexible Projection (SELECT * with Expressions): Enhanced projection capabilities that allow for selecting all columns from a source (SELECT *) while simultaneously appending new calculated expressions, timestamps, or metadata in the same statement.
Nested Subqueries: Support for correlated and non-correlated subqueries within SELECT, FROM, or WHERE clauses, enabling granular filtering and complex lookups that don't require separate nodes.
Common Table Expressions (CTEs): Support for standard WITH clauses to break down complex, multi-step transformation logic into readable, modular blocks. Coalesce tracks lineage through each CTE and back to the source tables.
Recursive CTEs: Full support for WITH RECURSIVE logic, enabling the transformation of hierarchical data and the programmatic generation of data sequences within a single node.

Known Limitations

Users should be aware of the following technical constraints when using SQL:

Table Materialization Only: The node currently only supports materializing results as physical tables. Creating or deploying the output as a database view is not supported within this node type.
Parsable SQL Only: The node only supports SQL that can be fully parsed by the platform’s engine. Non-standard SQL or vendor-specific "semantic views" that bypass standard parsing will not work.
Lineage Tracking: Automated column-level lineage through complex CTEs may not be fully tracked. For critical lineage requirements, consider using standard nodes for the initial staging of data.
SELECT Statements Only:
This node only supports data retrieval and transformation logic. DML or DDL commands such as CREATE, MERGE, DELETE, UPDATE, or TRUNCATE are not supported and will cause execution failures.
Support for DISTINCT, UNION, and UNION ALL:
Keywords like DISTINCT, UNION, and UNION ALL are fully supported when used within Common Table Expressions (CTEs). However, if these keywords are used instandard SELECT statements, the platform will not return an error, but the keywords will not be "picked up" or reflected in the final output. To ensure these operations are functional, always implement them inside a CTE.
No Pre-SQL or Post-SQL Support:
Unlike standard Stage or Join nodes, the SQL Stage node does not support Pre-SQL or Post-SQL hook blocks. All logic and transformations must be contained entirely within the primary SQL script.
Automated Testing Disabled:
The "Enable Tests" feature (Data Quality tests) is not supported for this node type. Any data validation or quality checks must be implemented in downstream nodes or handled via separate manual queries

SQL Stage Deployment

SQL Stage Initial Deployment

When deployed for the first time into an environment the SQL Stage Node of materialization type table will execute the below stage:

Stage	Description
Create SQL Stage Table	This will execute a CREATE OR REPLACE statement and create a table in the target environment

SQL Stage Redeployment

After the SQL Stage Node with materialization type table has been deployed for the first time into a target environment, subsequent deployments may result in either altering the SQL Table or recreating the SQL table.

Altering the SQL Stage Tables

A few types of column or table changes will result in an ALTER statement to modify the SQL 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
Clone Table	Creates an internal table
Rename Table\| Alter Column \| Delete Column \| Add Column \| Edit table description	Alter table statement is executed to perform the alter operation
Swap Cloned Table	Upon successful completion of all updates, the clone replaces the main table ensuring that no data is lost
Delete Table	Drops the internal table

SQL Undeployment

If a SQL Stage 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 WorkTable in the target environment will be dropped.

This is executed in two stages:

Stage	Description
Delete Table	Coalesce Internal table is dropped

Code

Work Code

Persistent Stage Code

Dimension Code

Fact Code

Factless Fact Code

View Code

SQL Stage Code

Macrosdimensional modeling

Versions

Available versions of the package.

Version #	Release Date	Notes
1.5.0	February 27, 2026	get_clause() macro adapted to support column names with keywords
1.4.0	January 14, 2026	Adding Last Modified configuration
1.3.7	November 18, 2025	Added an else block to the RUN template to handle cases where the materializationType is View
1.3.6	November 04, 2025	Config UI changes

Overview​

Installation​

Description​

Coalesce Base Node Types Package

Work​

Work Node Configuration​

Work Node Properties​

Work Options​

Work Options Table​

Work Options View​

ASOF Join Options​

Work Joins​

Work ASOF Joins​

Work Deployment​

Work Initial Deployment​

Work Redeployment​

Altering the Work Tables​

Recreating the Work Tables​

Recreating the Work Views​

Work Undeployment​

Persistent Stage​

Persistent Stage Node Configuration​

Persistent Stage Node Properties​

Persistent Stage Options​

Persistent Stage Joins​

Persistent Stage Deployment​

Persistent Stage Initial Deployment​

Persistent Stage Redeployment​

Altering the Persistent Tables​

Recreating the Persistent Tables​

Persistent Stage Undeployment​

Dimension​

Dimension Node Configuration​

Dimension Node Properties​

Dimension Options​

Dimension Table Options​

Dimension View Options​

Dimension Joins​

Dimension Deployment​

Dimension Initial Deployment​

Dimension Redeployment​

Altering the Dimension Tables​

Recreating the Dimension Tables​

Recreating the Dimension Views​

Dimension Undeployment​

Fact​

Fact Node Configuration​

Fact Node Properties​

Fact Options​

Fact View​

Fact Joins​

Fact Deployment​

Fact Initial Deployment​

Fact Redeployment​

Altering the Fact Tables​

Recreating the Fact Tables​

Recreating the Fact Views​

Fact Undeployment​

Factless Fact​

Factless Fact Node Configuration​

Factless Fact Node Properties​

Factless Fact Options​

Factless Fact Joins​

Factless Fact Deployment​

Factless Fact Initial Deployment​

Factless Fact Redeployment​

Altering the Factless Fact Tables​

Recreating the Factless Fact Tables​

Factless Fact Undeployment​

View​

View Node Configuration​

View Node Properties​

View Options​

View Joins​

View Deployment​

View Initial Deployment​

View Redeployment​

View Undeployment​

SQL Stage​

Prerequisites​

Overview

Installation

Description

Work

Work Node Configuration

Work Node Properties

Work Options

Work Options Table

Work Options View

ASOF Join Options

Work Joins

Work ASOF Joins

Work Deployment

Work Initial Deployment

Work Redeployment

Altering the Work Tables

Recreating the Work Tables

Recreating the Work Views

Work Undeployment

Persistent Stage

Persistent Stage Node Configuration

Persistent Stage Node Properties

Persistent Stage Options

Persistent Stage Joins

Persistent Stage Deployment

Persistent Stage Initial Deployment

Persistent Stage Redeployment

Altering the Persistent Tables

Recreating the Persistent Tables

Persistent Stage Undeployment

Dimension

Dimension Node Configuration

Dimension Node Properties

Dimension Options

Dimension Table Options

Dimension View Options

Dimension Joins

Dimension Deployment

Dimension Initial Deployment

Dimension Redeployment

Altering the Dimension Tables

Recreating the Dimension Tables

Recreating the Dimension Views

Dimension Undeployment

Fact

Fact Node Configuration

Fact Node Properties

Fact Options

Fact View

Fact Joins

Fact Deployment

Fact Initial Deployment

Fact Redeployment

Altering the Fact Tables

Recreating the Fact Tables

Recreating the Fact Views

Fact Undeployment

Factless Fact

Factless Fact Node Configuration

Factless Fact Node Properties

Factless Fact Options

Factless Fact Joins

Factless Fact Deployment

Factless Fact Initial Deployment

Factless Fact Redeployment

Altering the Factless Fact Tables

Recreating the Factless Fact Tables

Factless Fact Undeployment

View

View Node Configuration

View Node Properties

View Options

View Joins

View Deployment

View Initial Deployment

View Redeployment

View Undeployment

SQL Stage

Prerequisites

Node Configuration