Skip to main content

Base Node Types Advanced Deploy

‹ View all packages

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

Overview​

The Coalesce Advanced Deploy Nodes are Node Types that allows you to develop and deploy objects in Snowflake.

Installation​

  • Copy the Package ID  @coalesce/base-node-types-advanced-deploy
  • In Coalesce, open the Workspace where you wish to install the package.
  • Go to the Build Setting of the Workspace, tab Packages, and click the Install button on the top right of the page.
  • Paste the Package ID, and proceed with the installation process.

Description​

Base Node Types Advanced Deploy

The Coalesce Base Node Types Package includes:

Work Advanced Deploy

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:

Fact_config

Work Advanced Deploy Node Properties

  • 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
  • Create As: Table
  • Cluster key: True/False to determine whether Dimension is to be 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: 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: 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.
View
  • Create As: View
  • Cluster key: True/False to determine whether Dimension is to be 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: 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: [Missing Content]
    • 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
Transient Table
  • Create As: Transient Table
  • Cluster key: True/False to determine whether Dimension is to be 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: 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: 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.

Work Advanced Deploy 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

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

Work Advanced Deploy 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 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​

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:

  • Rename Table| Alter Column | Delete Column | Add Column | Edit table description: Alter table statement is executed to perform the alter operation.

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:

  • Create View

Work Advanced Deploy Drop and Recreate Work View/Table/Transient Table​

When the materialization type of Work node is changed from View to table/transient table,the following stages are executed:

  • Drop view
  • Create or Replace Work table/transient table

When the materialization type of Work node is changed from table/transient table to View,the below stages are executed:

  • Drop table/transient table
  • Create Work view

When the materialization type of Work node is changed from table to transient table or transient table to table,the below stages are executed:

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

CreateSQL

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:

  • Drop table/view

Persistent Stage Advanced Deploy

The Coalesce Persistent Stage Nodes element, serving as an intermediary object, is frequently utilized to maintain data persistence across multiple execution cycles.

It plays a crucial role in tracking the historical changes of columns linked to business keys.

This functionality is particularly beneficial when the objective is to retain raw data for prolonged durations.

Persistent Stage Advanced Deploy Node Configuration​

The Persistent node type has two configuration groups:

Persistent Stage Advanced Deploy Node Properties

  • 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
  • Create As: Table
  • Cluster key: True/False to determine whether Dimension is to be 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: 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.
Transient Table
  • Create As: Table
  • Cluster key: True/False to determine whether Dimension is to be 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: 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 Advanced Deploy 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

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:

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​

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:

  • Rename Table| Alter Column | Delete Column | Add Column | Edit table description: Alter table statement is executed to perform the alter operation accordingly

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

  • Drop table/transient table
  • Create or Replace Persistent stage table/transient table

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:

  • Drop Table or View

Dimension Advanced Deploy

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

A dimension table or dimension entity is a table or entity in a star, snowflake, or starflake schema that stores details about the facts.Dimension tables describe the different aspects of a business process

Dimension Advanced Deploy Node Configuration​

Dimension Advanced Deploy Node Properties

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

You can create the node as:

Table
  • Create As: Table
  • 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.
  • Cluster key: True/False to determine whether Dimension is to be 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.
  • 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.
Transient Table
  • Create As: Transient Table
  • 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.
  • Cluster key: True/False to determine whether Dimension is to be 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.
  • 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.
View
  • Create As: View
  • 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.
  • Business key: It is a required column for both Type 1 and Type 2.
  • 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

Dimension Advanced Deploy 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

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 will execute the below 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 below stage:

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

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

  • Rename Table| Alter Column | Delete Column | Add Column | Edit table description: Alter table statement is executed to perform the alter operation.

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​

When the materialization type of Dimension node is changed from View to table/transient table,the following stages are executed:

  • Drop view
  • Create Dimension table/transient table

When the materialization type of Dimension node is changed from table/transient table to View,the following stages are executed:

  • Drop table/transient table
  • Create Dimension view

When the materialization type of Dimension node is changed from table to transient table or transient table to table,the following stages are executed:

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

CreateSQL

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:

  • Drop table/view

Fact Advanced Deploy

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

A fact table or a fact entity is a table or entity in a star or snowflake schema that stores measures that measure the business, such as sales, cost of goods, or profit. Fact tables and entities aggregate measures , or the numerical data of a business.

Fact Advanced Deploy Node Configuration​

Fact Advanced Deploy Node Properties

  • 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 Advanced Deploy Dimension Options

You can create the node as:

Table
  • Create As: Table
  • 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.
  • Cluster key: True/False to determine whether Dimension is to be 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.
  • 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.
Transient Table
  • Create As: Transient Table
  • 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.
  • Cluster key: True/False to determine whether Dimension is to be 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.
  • 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.
View
  • Create As: View
  • 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 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.

fact_join

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

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

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

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:

  • Rename Table| Alter Column | Delete Column | Add Column | Edit table description: Alter table statement is executed to perform the alter operation.

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:

  • Create View

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:

  • Drop table/view

Drop and recreate Fact View/Table/Transient​

When the materialization type of Fact node is changed from View to table/transient table,the following stages are executed:

  • Drop view
  • Create Fact table/transient table

When the materialization type of Fact node is changed from table/transient table to View,the following stages are executed:

  • Drop table/transient table
  • Create Fact view

When the materialization type of Fact node is changed from table to transient table or transient table to table, the following stages are executed:

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

CreateSQL

Factless Fact Advanced Deploy

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

A factless fact table is used to record events or situations that have no measures, and it has the same level of detail as the dimensions

Factless Fact Advanced Deploy Node Configuration​

Factless Fact Advanced Deploy Node Properties

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

Factless_adv_deploy

  • Create As:Provides option to choose materialization type.

    • Table
    • Transient Table

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

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

fact_join

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

  • 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 Fact Tables/Transient 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

  • Rename Table| Alter Column | Delete Column | Add Column | Edit table description: Alter table statement is executed to perform the alter operation accordingly

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

  • Drop table/transient table
  • Create Factless Fact table/transient table

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:

  • Drop table/view

Code​

Work Advanced Deploy​

Persistent Stage Advanced Deploy​

Dimension Advanced Deploy​

Fact Advanced Deploy​

Factless Fact Advanced Deploy​

Macros

Versions​

Available versions of the package.

Version #Release DateNotes
1.1.2August 12, 2024
 Bug fix for multi-source functionality for Dimension
1.1.1July 31, 2024
 Fix for missing current state storage location
1.1.0June 12, 2024
 Default options modified
1.0.0May 16, 2024
 Initial release

Support

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