Skip to main content

Base Node Types

‹ View all packages

Latest Version: 
1.1.2
Release Date: 
August 12, 2024
Package ID: 
@coalesce/base-node-types 
Coalesce Certified Package

Overview​

Coalesce base Node Types to construct facts and dimensions.

Installation​

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

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:

Fact_config

Work Node Properties

There are four configs within the Node Properties group.

  • 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

Your available options will change depending on table or view.

Create As Table

Work_options_table1

  • Multi Source: True / False toggle that is Coalesce implementation of SQL UNIONs.
    • True - Multiple sources can be combined in a single node. The sources are combined using the option specified in the Multi Source Strategy.
      • UNION - Combines with duplicate elimination.
      • UNION ALL - Combines without duplicate elimination.
      • INSERT - Runs an individual insert query for each source.
    • False - Single source node or multiple sources combined using a join.
  • Truncate Before: True / False toggle that determines whether or not a table is overwritten each time a task executes.
    • True - INSERT OVERWRITE is used to overwrite existing data with new data loaded by task
    • False - INSERT is used to append new data into target table
  • Enable tests:
  • Distinct: True/False toggle that determines whether to add DISTINCT to SQL Query.
    • True - Group by All is invisible. DISTINCT data is chosen for processing
    • False- Group by All is visible
  • Group by All: True/False toggle that determines whether to add GROUP BY ALL to SQL Query.
    • True - DISTINCT is invisible. Data is grouped by all columns for processing
    • False- DISTINCT is visible
  • Order By : True/False toggle that determines whether to add “ORDER BY” to SQL Query along with the column and sort order
    • 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: Any SQL to be executed as a predecessor to data insert operation can be mentioned here
  • Post-SQL: Any SQL to be executed post the data insert operation can be specified here

Create As view

Work_options_view1

  • Override Create SQL:True/False that determines whether a customized Create SQL is required to be executed.
    • True-Customized Create SQL specified in the Create SQL space is executed.All other options are invisible.
    • False-Create view SQL based on the options chosen are framed and executed.
  • Multi Source: True / False toggle that is Coalesce implementation of SQL UNIONs.
    • True - Multiple sources can be combined in a single node. The sources are combined using the option specified in the Multi Source Strategy.
      • UNION - Combines with duplicate elimination.
      • UNION ALL - Combines without duplicate elimination.
    • False - Single source node or multiple sources combined using a join.
  • Distinct: True/False toggle that determines whether to add DISTINCT to SQL Query.
    • True - Group by All is invisible. DISTINCT data is chosen for processing
    • False- Group by All is visible
  • Group by All: True/False toggle that determines whether to add GROUP BY ALL to SQL Query.
    • True - DISTINCT is invisible. Data is grouped by all columns for processing
    • False- DISTINCT is 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 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:

Create Work Table This will execute a CREATE OR REPLACE statement and create a table in the target environment.

When deployed for the first time into an environment the Work node of materialization type view will execute the below stage:

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​

There are few column or table changes like Change in table name,Dropping existing column, Alter Column data type,Adding a new column if made in isolation or all-together will result in an ALTER statement to modify the Work Table in the target environment.

The following stages are executed:

  • 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 changes like change in join clause,adding transformations,change in configs like adding distinct,group by or orderby ,the Work Table will be recreated by running a CREATE OR REPLACE statement.

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:

  • Delete View
  • Create View

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:

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

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:

Fact_config

Persistent Stage Node Properties

There are four configs within the Node Properties group.

  • 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

Create As Table

pstage_options1

  • Create As: Table is the only option at this time.
  • Multi Source: True / False toggle that is Coalesce implementation of SQL UNIONs.
    • True - Multiple sources can be combined in a single node. The sources are combined using the option specified in the Multi Source Strategy.
      • UNION: Combines with duplicate elimination.
      • UNION ALL: Combines without duplicate elimination.
    • False - Single source node or multiple sources combined using a join.
  • Business key: It is a required column for both Type 1 and Type 2.
  • Change tracking: It is a required column for Type 2 .
  • Truncate Before: True / False toggle that determines whether or not a table is overwritten each time a task executes.
    • True - INSERT OVERWRITE is used to overwrite existing data with new data loaded by task
    • False - INSERT is used to append new data into target table
  • Enable tests: Provides option to specify tests in Testing section to check the data quality.
  • Distinct: True/False toggle that determines whether to add DISTINCT to SQL Query.
    • True - Group by All is invisible. DISTINCT data is chosen for processing
    • False- Group by All is visible
  • Group by All: True/False toggle that determines whether to add GROUP BY ALL to SQL Query.
    • True - DISTINCT is invisible. Data is grouped by all columns for processing
    • False- DISTINCT is visible
  • Order By : True/False toggle that determines whether to add “ORDER BY” to SQL Query along with the column and sort order
    • 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: Any SQL to be executed as a predecessor to data insert operation can be mentioned here
  • Post-SQL:Any SQL to be executed post the data insert operation can be specified here.

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:

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​

There are few column or table changes like Change in table name,Dropping existing column, Alter Column data type,Adding a new column if made in isolation or all-together will result in an ALTER statement to modify the Persistent Table in the target environment.

The following stages are executed:

  • 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 changes like change in join clause,adding transformations,change in business key column,change in configs like adding distinct,group by or orderby ,the Persistent Table will be recreated by running a CREATE OR REPLACE statement.

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:

  • Delete Table
  • Drop Table or View

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:

Fact_config

Dimension Node Properties

There are four configs within the Node Properties group.

  • 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

Create As Table

pstage_options1

  • Create As: Table is the only option at this time.
  • Multi Source: True / False toggle that is Coalesce implementation of SQL UNIONs.
    • True - Multiple sources can be combined in a single node. The sources are combined using the option specified in the Multi Source Strategy.
      • UNION: Combines with duplicate elimination.
      • UNION ALL: Combines without duplicate elimination.
    • False - Single source node or multiple sources combined using a join.
  • Business key: It is a required column for both Type 1 and Type 2 Dimensions.
  • Change tracking: It is a required column for Type 2 Dimension.
  • Truncate Before: True / False toggle that determines whether or not a table is overwritten each time a task executes.
    • True - INSERT OVERWRITE is used to overwrite existing data with new data loaded by task
    • False - INSERT is used to append new data into target table
  • Enable tests: Provides option to specify tests in Testing section to check the data quality.
  • Distinct: True/False toggle that determines whether to add DISTINCT to SQL Query.
    • True - Group by All is invisible. DISTINCT data is chosen for processing
    • False- Group by All is visible
  • Group by All: True/False toggle that determines whether to add GROUP BY ALL to SQL Query.
    • True - DISTINCT is invisible. Data is grouped by all columns for processing
    • False- DISTINCT is visible
  • Order By : True/False toggle that determines whether to add “ORDER BY” to SQL Query along with the column and sort order
    • 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: Any SQL to be executed as a predecessor to data insert operation can be mentioned here
  • Post-SQL:Any SQL to be executed post the data insert operation can be specified here

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 theCreate Dimension Table stage.

Create Dimension Table: This will execute a CREATE OR REPLACE statement and create a table in the target environment.

When deployed for the first time into an environment the Dimension node of materialization type view will execute the Create Dimension View stage.

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​

There are few column or table changes like Change in table name,Dropping existing column, Alter Column data type,Adding a new column if made in isolation or all-together will result in an ALTER statement to modify the Dimension Table in the target environment.

The following stages are executed

  • 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 changes like change in join clause,adding transformations,change in business key column,change in configs like adding distinct,group by or orderby ,the Dimension Table will be recreated by running a CREATE OR REPLACE statement.

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 deleting the existing dimension view and recreating the dimension view

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

This is executed in two stages:

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

The stage executed:

  • 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 type has two configuration groups:

Fact_config

Fact Node Properties

There are four configs within the Node Properties group.

  • 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

Your available options will change depending on table or view.

Create As Table

fact_options

  • Multi Source: True / False toggle that is Coalesce implementation of SQL UNIONs.
    • True - Multiple sources can be combined in a single node. The sources are combined using the option specified in the Multi Source Strategy.
      • UNION - Combines with duplicate elimination.
      • UNION ALL - Combines without duplicate elimination.
    • False - Single source node or multiple sources combined using a join.
  • Business key: It is a required column for Fact table creation.
  • Truncate Before: True / False toggle that determines whether or not a table is overwritten each time a task executes.
    • True - INSERT OVERWRITE is used to overwrite existing data with new data loaded by task
    • False - INSERT is used to append new data into target table
  • Enable tests:
  • Distinct: True/False toggle that determines whether to add DISTINCT to SQL Query.
    • True - Group by All is invisible. DISTINCT data is chosen for processing
    • False- Group by All is visible
  • Group by All: True/False toggle that determines whether to add GROUP BY ALL to SQL Query.
    • True - DISTINCT is invisible. Data is grouped by all columns for processing
    • False- DISTINCT is visible
  • Order By : True/False toggle that determines whether to add “ORDER BY” to SQL Query along with the column and sort order
    • 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: Any SQL to be executed as a predecessor to data insert operation can be mentioned here
  • Post-SQL: Any SQL to be executed post the data insert operation can be specified here

Create As view

Work_options_view1

  • Override Create SQL:True/False that determines whether a customized Create SQL is required to be executed.
    • True-Customized Create SQL specified in the Create SQL space is executed.All other options are invisible.
    • False-Create view SQL based on the options chosen are framed and executed.
  • Multi Source: True / False toggle that is Coalesce implementation of SQL UNIONs.
    • True - Multiple sources can be combined in a single node. The sources are combined using the option specified in the Multi Source Strategy.
      • UNION - Combines with duplicate elimination.
      • UNION ALL - Combines without duplicate elimination.
    • False - Single source node or multiple sources combined using a join.
  • Distinct: True/False toggle that determines whether to add DISTINCT to SQL Query.
    • True - Group by All is invisible. DISTINCT data is chosen for processing
    • False- Group by All is visible
  • Group by All: True/False toggle that determines whether to add GROUP BY ALL to SQL Query.
    • 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.

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

When deployed for the first time into an environment the Fact node of materialization type view will execute the Create Fact View stage.

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

There are few column or table changes like Change in table name,Dropping existing column, Alter Column data type,Adding a new column if made in isolation or all-together will result in an ALTER statement to modify the Fact Table in the target environment.

The following stages are executed

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

Recreating the Fact Views​

The subsequent deployment of Fact 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

  • Delete View
  • Create View

Fact 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:

  1. Delete Table: Coalesce Internal table is dropped.
  2. Delete Table: Target table in Snowflake is dropped

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

The stage executed:

  • Delete View: Drops the existing Fact view from target environment.

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 type has two configuration groups:

Factless Fact Options​

Create As Table

Factless_options

  • Create As: Table is the only option at this time.
  • Multi Source: True / False toggle that is Coalesce implementation of SQL UNIONs.
    • True - Multiple sources can be combined in a single node. The sources are combined using the option specified in the Multi Source Strategy.
      • UNION - Combines with duplicate elimination.
      • UNION ALL - Combines without duplicate elimination.
    • False - Single source node or multiple sources combined using a join.
  • Truncate Before: True / False toggle that determines whether or not a table is overwritten each time a task executes.
    • True - INSERT OVERWRITE is used to overwrite existing data with new data loaded by task.
    • False - INSERT is used to append new data into target table.
  • Enable tests: Provides option to specify tests in Testing section to check the data quality.
  • Distinct: True/False toggle that determines whether to add DISTINCT to SQL Query.
    • True - Group by All is invisible. DISTINCT data is chosen for processing.
    • False- Group by All is visible.
  • Group by All: True/False toggle that determines whether to add GROUP BY ALL to SQL Query.
    • True - DISTINCT is invisible. Data is grouped by all columns for processing.
    • False- DISTINCT is visible.
  • Order By : True/False toggle that determines whether to add “ORDER BY” to SQL Query along with the column and sort order.
    • 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: Any SQL to be executed as a predecessor to data insert operation can be mentioned here.
  • Post-SQL:Any SQL to be executed post the data insert operation can be specified here.

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.

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

There are few column or table changes like Change in table name,Dropping existing column, Alter Column data type,Adding a new column if made in isolation or all-together will result in an ALTER statement to modify the Factless fact Table in the target environment.

The following stages are executed:

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

Factless Fact 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:

  1. Delete Table: Coalesce Internal table is dropped.
  2. 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:

Fact_config

View Node Properties

There are four configs within the Node Properties group.

  • 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

There are many configs within the Options group.

  • Override Create SQL:True/False that determines whether a customized Create SQL is required to be executed.
    • True: Customized Create SQL specified in the Create SQL space is executed.All other options are invisible.
      • UNION - Combines with duplicate elimination.
      • UNION ALL - Combines without duplicate elimination.
    • False: Create view SQL based on the options chosen are framed and executed.
  • Multi Source: True / False toggle that is Coalesce implementation of SQL UNIONs.
    • True - Multiple sources can be combined in a single node. The sources are combined using the option specified in the Multi Source Strategy.
    • False - Single source node or multiple sources combined using a join.
  • Distinct: True/False toggle that determines whether to add DISTINCT to SQL Query.
    • True - Group by All is invisible. DISTINCT data is chosen for processing
    • False- Group by All is visible
  • Group by All: True/False toggle that determines whether to add GROUP BY ALL to SQL Query.
    • True - DISTINCT is invisible. Data is grouped by all columns for processing
    • False- DISTINCT is visible
  • Secure:True/False toggle that determines whether to create secure view or not
    • 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.

Create View: This stage 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

Recreating the View​

The following stages are executed:

  • Delete View
  • Create View

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:

  • Delete View

Code

Work​

Persistent Stage​

Dimension​

Fact​

Factless Fact​

View​

Macros

Versions​

Available versions of the package.

Version #Release DateNotes
1.1.2August 12, 2024
 Bug fix for multi-source functionality of Dimension node
1.1.1June 27, 2024
 Default settings modified
1.1.0June 12, 2024
 Default options modified
1.0.0May 14, 2024
 Initial Version
- Work
- Persistent Stage
- Dimension
- Fact
- Factless Fact
- View

Support

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