Base Node Types Advanced Deploy
Overview
The Coalesce Advanced Deploy Nodes are Node Types that allows you to develop and deploy objects in Snowflake.
Installation
- In Coalesce, open the Workspace where you wish to install the package.
- Go to the Build Setting of the Workspace, tab Packages, and click the Install button on the top right of the page.
- Paste the Package ID, and proceed with the installation process.
Description
Base Node Types Advanced Deploy
The Coalesce Base Node Types Package includes:
- Work Advanced Deploy
- Persistent Stage Advanced Deploy
- Dimension Advanced Deploy
- Fact Advanced Deploy
- Factless Fact Advanced Deploy
- Code
Work Advanced Deploy
The Coalesce Work Node is a versatile node that allows you to develop and deploy a Work table/view in Snowflake.
A Work node serves as an intermediary object and is commonly employed to store raw data before undergoing the crucial phases of transformation and loading into the main tables of the data warehouse.
This pivotal step ensures that the raw data is processed and structured effectively.
Work Advanced Deploy Node Configuration
The Work node type has two configuration groups:
Work Advanced Deploy Node Properties
| Setting | Description |
|---|---|
| Storage Location | Storage Location where the WORK will be created |
| Node Type | Name of template used to create node objects |
| Description | A description of the node's purpose |
| Deploy Enabled | If TRUE the node will be deployed / redeployed when changes are detected If FALSE the node will not be deployed or will be dropped during redeployment |
Work Advanced Deploy Options
You can create the node as:
Work Advanced Deploy Create as Table
| Setting | Description |
|---|---|
| Create As | Table |
| Cluster key | Toggle: True/False If the dimension is clustered or not. True: Allows you to specify the column based on which clustering is to be done. - Allow Expressions Cluster Key: Allows to add an expression to the specified cluster key False:No clustering done |
| Multi Source | Toggle: True/False Implementation of SQL UNIONs True: Combine multiple sources in a single node True Options: - UNION: Combines with duplicate elimination - UNION ALL: Combines without duplicate elimination - INSERT: Individual insert for each source False: Single source node or multiple sources combined using a join. |
| Truncate Before | Toggle: True/False This determines whether a table will be overwritten each time a task executes. True: Uses INSERT OVERWRITE False: Uses INSERT to append data |
| Enable tests | Toggle: True/False Determines if tests are enabled |
| Distinct | Toggle: True/False True: Group by All is invisible. DISTINCT data is chosen for processing. False: Group by All is visible. |
| Group by All | Toggle: True/False True: DISTINCT is invisible, data grouped by all columns False: DISTINCT is visible |
| Order By | Toggle: True/False True: Sort column and sort order drop down are visible and are required to form order by clause. False: Sort options invisible |
| Pre-SQL | SQL to execute before data insert operation |
| Post-SQL | SQL to execute after data insert operation |
Work Advanced Deploy Create as View
| Setting | Description |
|---|---|
| Create As | View |
| Cluster key | Toggle: True/False If the dimension is clustered or not. True: Allows you to specify the column based on which clustering is to be done. - Allow Expressions Cluster Key: Allows to add an expression to the specified cluster key False:No clustering done |
| Multi Source | Toggle: True/False Implementation of SQL UNIONs True: Combine multiple sources in a single node True Options: - UNION: Combines with duplicate elimination - UNION ALL: Combines without duplicate elimination - INSERT: Individual insert for each source False: Single source node or multiple sources combined using a join. |
| Truncate Before | Toggle: True/False This determines whether a table will be overwritten each time a task executes. True: Uses INSERT OVERWRITE False: Uses INSERT to append data |
| Enable tests | Toggle: True/False Determines if tests are enabled |
| Distinct | Toggle: True/False True: Group by All is invisible. DISTINCT data is chosen for processing. False: Group by All is visible. |
| Group by All | Toggle: True/False True: DISTINCT is invisible, data grouped by all columns False: DISTINCT is visible |
Work Advanced Deploy Create as Transient Table
| Setting | Description |
|---|---|
| Create As | Transient Table |
| Cluster key | Toggle: True/False If the dimension is clustered or not. True: Allows you to specify the column based on which clustering is to be done. - Allow Expressions Cluster Key: Allows to add an expression to the specified cluster key False:No clustering done |
| Multi Source | Toggle: True/False Implementation of SQL UNIONs True: Combine multiple sources in a single node True Options: - UNION: Combines with duplicate elimination - UNION ALL: Combines without duplicate elimination - INSERT: Individual insert for each source False: Single source node or multiple sources combined using a join. |
| Truncate Before | Toggle: True/False This determines whether a table will be overwritten each time a task executes. True: Uses INSERT OVERWRITE False: Uses INSERT to append data |
| Enable tests | Toggle: True/False Determines if tests are enabled |
| Distinct | Toggle: True/False True: Group by All is invisible. DISTINCT data is chosen for processing. False: Group by All is visible. |
| Group by All | Toggle: True/False True: DISTINCT is invisible, data grouped by all columns False: DISTINCT is visible |
| Order By | Toggle: True/False True: Sort column and sort order drop down are visible and are required to form order by clause. False: Sort options invisible |
| Pre-SQL | SQL to execute before data insert operation |
| Post-SQL | SQL to execute after data insert operation |
Work Advanced Deploy Joins
Join conditions and other clauses can be specified in the join space next to mapping of columns in the UI.
📘 Specify Group by and Order by Clauses
Best Practice is to specify group by and order by clauses in this space if you are not opting for the group by all and order by provided in OPTIONS config.
Work Advanced Deploy Deployment
Work Advanced Deploy Initial Deployment
When deployed for the first time into an environment the Work node of materialization type table or view will execute the below stage:
| Stage | Description |
|---|---|
| Create Work Table | This will execute a CREATE OR REPLACE statement and create a table in the target environment |
| Create Work View | This will execute a CREATE OR REPLACE statement and create a view in the target environment |
Work Advanced Deploy Redeployment
After the WORK node with materialization type table/transient table/view has been deployed for the first time into a target environment, subsequent deployments may result in either altering the WORK Table or recreating the WORK table.
Altering the Work Tables and Transient Tables
A few types of column or table changes will result in an ALTER statement to modify the Persistent Table in the target environment, whether these changes are made individually or all together:
- Changing table names
- Dropping existing columns
- Altering column data types
- Adding new columns
The following stages are executed:
| Stage | Description |
|---|---|
| Rename Table| Alter Column | Delete Column | Add Column | Edit table description | Alter table statement is executed to perform the alter operation |
Work Advanced Deploy Recreating the Work Views
The subsequent deployment of Work node of materialization type view with changes in view definition, adding table description or renaming view results in deleting the existing view and recreating the view.
The following stages are executed:
| Stage | Description |
|---|---|
| Create View | Creates a new view with updated definition |
Work Advanced Deploy Drop and Recreate Work View/Table/Transient Table
| Change | Stages Executed |
|---|---|
| View to table/transient table | Drop view Create or Replace Work table/transient table |
| Table/transient table to View | Drop table/transient table Create Work view |
| Table to transient table or vice versa | Drop table/transient table Create or Replace Work table/transient table |
📘 Materialization Work Node
When the materialization type of Work node is changed from table/transient table to View and use Override Create SQL for view creation. This ensures that the following change is made in the stage function in Create SQL tab so that the order of deployment is maintained.
Work Advanced Deploy Undeployment
If a Work Node of materialization type table/view/transient table are deleted from a Workspace, that Workspace is committed to Git and that commit deployed to a higher level environment then the WorkTable in the target environment will be dropped.
This is executed in below stage:
| Stage | Description |
|---|---|
| Drop table/view | Removes the table or view from the environment |
Persistent Stage Advanced Deploy
The Coalesce Persistent Stage Nodes element, serving as an intermediary object, is frequently utilized to maintain data persistence across multiple execution cycles.
It plays a crucial role in tracking the historical changes of columns linked to business keys.
This functionality is particularly beneficial when the objective is to retain raw data for prolonged durations.
Persistent Stage Advanced Deploy Node Configuration
The Persistent node type has two configuration groups:
Persistent Stage Advanced Deploy Node Properties
| Setting | Description |
|---|---|
| Storage Location | Storage Location where the WORK will be created |
| Node Type | Name of template used to create node objects |
| Description | A description of the node's purpose |
| Deploy Enabled | If TRUE the node will be deployed / redeployed when changes are detected If FALSE the node will not be deployed or will be dropped during redeployment |
Persistent Stage Advanced Deploy Options
You can create the node as:
Persistent Stage Table
| Setting | Description |
|---|---|
| Create As | Table |
| Cluster key | Toggle: True/False If the dimension is clustered or not. True: Allows you to specify the column based on which clustering is to be done. - Allow Expressions Cluster Key: Allows to add an expression to the specified cluster key False:No clustering done |
| Multi Source | Toggle: True/False Implementation of SQL UNIONs True: Combine multiple sources in a single node True Options: - UNION: Combines with duplicate elimination - UNION ALL: Combines without duplicate elimination - INSERT: Individual insert for each source False: Single source node or multiple sources combined using a join. |
| Business key | Required column for both Type 1 and Type 2 |
| Change tracking | Required column for Type 2 |
| Truncate Before | Toggle: True/False This determines whether a table will be overwritten each time a task executes. True: Uses INSERT OVERWRITE False: Uses INSERT to append data |
| Enable tests | Toggle: True/False Determines if tests are enabled |
| Distinct | Toggle: True/False True: Group by All is invisible. DISTINCT data is chosen for processing. False: Group by All is visible. |
| Group by All | Toggle: True/False True: DISTINCT is invisible, data grouped by all columns False: DISTINCT is visible |
| Order By | Toggle: True/False True: Sort column and sort order drop down are visible and are required to form order by clause. False: Sort options invisible |
| Pre-SQL | SQL to execute before data insert operation |
| Post-SQL | SQL to execute after data insert operation |
Persistent Stage Transient Table
| Setting | Description |
|---|---|
| Create As | Table |
| Cluster key | Toggle: True/False If the dimension is clustered or not. True: Allows you to specify the column based on which clustering is to be done. - Allow Expressions Cluster Key: Allows to add an expression to the specified cluster key False:No clustering done |
| Multi Source | Toggle: True/False Implementation of SQL UNIONs True: Combine multiple sources in a single node True Options: - UNION: Combines with duplicate elimination - UNION ALL: Combines without duplicate elimination - INSERT: Individual insert for each source False: Single source node or multiple sources combined using a join. |
| Business key | Required column for both Type 1 and Type 2 |
| Change tracking | Required column for Type 2 |
| Truncate Before | Toggle: True/False This determines whether a table will be overwritten each time a task executes. True: Uses INSERT OVERWRITE False: Uses INSERT to append data |
| Enable tests | Toggle: True/False Determines if tests are enabled |
| Distinct | Toggle: True/False True: Group by All is invisible. DISTINCT data is chosen for processing. False: Group by All is visible. |
| Group by All | Toggle: True/False True: DISTINCT is invisible, data grouped by all columns False: DISTINCT is visible |
| Order By | Toggle: True/False True: Sort column and sort order drop down are visible and are required to form order by clause. False: Sort options invisible |
| Pre-SQL | SQL to execute before data insert operation |
| Post-SQL | SQL to execute after data insert operation |
Persistent Stage Advanced Deploy Joins
Join conditions and other clauses can be specified in the join space next to mapping of columns in the UI.
📘 Specify Group by and Order by Clauses
Best Practice is to specify group by and order by clauses in this space if you are not opting for the group by all and order by provided in OPTIONS config.
Persistent Stage Advanced Deploy Deployment
Persistent Stage Advanced Deploy Initial Deployment
When deployed for the first time into an environment the Persistent node will execute the below stage:
| Stage | Description |
|---|---|
| Create Persistent Stage Table | This will execute a CREATE OR REPLACE statement and create a table in the target environment |
Persistent Stage Advanced Deploy Redeployment
After the Persistent node has been deployed for the first time into a target environment, subsequent deployments may result in either altering the Persistent Table or recreating the Persistent table.
Altering the Persistent Tables/Transient Tables
A few types of column or table changes will result in an ALTER statement to modify the Persistent Table in the target environment, whether these changes are made individually or all together:
- Changing table names
- Dropping existing columns
- Altering column data types
- Adding new columns
The following stages are executed:
| Stage | Description |
|---|---|
| Rename Table| Alter Column | Delete Column | Add Column | Edit table description | ALTER table statement is executed to perform the alter operation accordingly |
Drop and Recreate Persistent Stage Table/Transient Table
When the materialization type of Persistent stage node is changed from table to transient table or transient table to table, the below stages are executed:
| Stage | Description |
|---|---|
| Drop table/transient table | Removes existing table |
| Create or Replace Persistent stage table/transient table | Creates new table with updated configuration |
Persistent Stage Advanced Deploy Undeployment
If a Persistent Node is deleted from a Workspace, that Workspace is committed to Git and that commit deployed to a higher level environment then the Persistent Table in the target environment will be dropped.
This is executed in the stages:
| Stage | Description |
|---|---|
| Drop Table or View | Removes the table from the environment |
Dimension Advanced Deploy
The Coalesce Dimension UDN is a versatile node that allows you to develop and deploy a Dimension table in Snowflake.
A dimension table or dimension entity is a table or entity in a star, snowflake, or starflake schema that stores details about the facts. Dimension tables describe the different aspects of a business process.
Dimension Advanced Deploy Node Configuration
Dimension Advanced Deploy Node Properties
| Property | Description |
|---|---|
| Storage Location | Storage Location where the WORK will be created |
| Node Type | Name of template used to create node objects |
| Description | A description of the node's purpose |
| Deploy Enabled | If TRUE the node will be deployed / redeployed when changes are detected If FALSE the node will not be deployed or will be dropped during redeployment |
Dimension Advanced Deploy Options
You can create the node as:
Dimension Advanced Deploy Table
| Setting | Description |
|---|---|
| Create As | Table |
| Insert Zero Key Record | Toggle: True/False Insert Zero Key Record to Dimention if enabled |
| Multi Source | Toggle: True/False Implementation of SQL UNIONs True: Combine multiple sources in a single node True Options: - UNION: Combines with duplicate elimination - UNION ALL: Combines without duplicate elimination - INSERT: Individual insert for each source False: Single source node or multiple sources combined using a join. |
| Business key | Required column for Type 1 and Type 2 Dimensions |
| Change tracking | Required column for Type 2 Dimension |
| Truncate Before | Toggle: True/False This determines whether a table will be overwritten each time a task executes. True: Uses INSERT OVERWRITE False: Uses INSERT to append data |
| Enable tests | Toggle: True/False Determines if tests are enabled |
| Cluster key | True: Allows you to specify the column based on which clustering is to be done Allow Expressions Cluster Key: Allows to add an expression to the specified cluster key False: No clustering done |
| Distinct | Toggle: True/False True: Group by All is invisible. DISTINCT data is chosen for processing. False: Group by All is visible. |
| Group by All | Toggle: True/False True: DISTINCT is invisible, data grouped by all columns False: DISTINCT is visible |
| Order By | Toggle: True/False True: Sort column and sort order drop down are visible and are required to form order by clause. False: Sort options invisible |
| Pre-SQL | SQL to execute before data insert operation |
| Post-SQL | SQL to execute after data insert operation |
Dimension Advanced Deploy Transient Table
| Setting | Description |
|---|---|
| Create As | Transient Table |
| Multi Source | Toggle: True/False Implementation of SQL UNIONs True: Combine multiple sources in a single node True Options: - UNION: Combines with duplicate elimination - UNION ALL: Combines without duplicate elimination - INSERT: Individual insert for each source False: Single source node or multiple sources combined using a join. |
| Business key | Required column for Type 1 and Type 2 Dimensions |
| Change tracking | Required column for Type 2 Dimension |
| Truncate Before | Toggle: True/False This determines whether a table will be overwritten each time a task executes. True: Uses INSERT OVERWRITE False: Uses INSERT to append data |
| Enable tests | Toggle: True/False Determines if tests are enabled |
| Cluster key | True: Allows you to specify the column based on which clustering is to be done Allow Expressions Cluster Key: Allows to add an expression to the specified cluster key False: No clustering done |
| Distinct | Toggle: True/False True: Group by All is invisible. DISTINCT data is chosen for processing. False: Group by All is visible. |
| Group by All | Toggle: True/False True: DISTINCT is invisible, data grouped by all columns False: DISTINCT is visible |
| Order By | Toggle: True/False True: Sort column and sort order drop down are visible and are required to form order by clause. False: Sort options invisible |
| Pre-SQL | SQL to execute before data insert operation |
| Post-SQL | SQL to execute after data insert operation |
Dimension Advanced Deploy View
| Setting | Description |
|---|---|
| Create As | View |
| Override Create SQL | Toggle: True/False True: Custom Create SQL False: Generated view SQL |
| Multi Source | Toggle: True/False Implementation of SQL UNIONs True: Combine multiple sources in a single node True Options: - UNION: Combines with duplicate elimination - UNION ALL: Combines without duplicate elimination - INSERT: Individual insert for each source False: Single source node or multiple sources combined using a join. |
| Business key | Required column for Type 1 and Type 2 Dimensions |
| Distinct | Toggle: True/False True: Group by All is invisible. DISTINCT data is chosen for processing. False: Group by All is visible. |
| Group by All | Toggle: True/False True: DISTINCT is invisible, data grouped by all columns False: DISTINCT is visible |
| Order By | Toggle: True/False True: Sort column and sort order drop down are visible and are required to form order by clause. False: Sort options invisible |
Dimension Advanced Deploy Joins
Join conditions and other clauses can be specified in the join space next to mapping of columns in the UI.
📘 Specify Group by and Order by Clauses
Best Practice is to specify group by and order by clauses in this space if you are not opting for the group by all and order by provided in OPTIONS config.
Dimension Advanced Deploy Deployment
Dimension Advanced Deploy Initial Deployment
When deployed for the first time into an environment the Dimension node of materialization type table or view will execute the following stage:
| Stage | Description |
|---|---|
| Create Dimension Table | This will execute a CREATE OR REPLACE statement and create a table in the target environment |
| Create Dimension View | This will execute a CREATE OR REPLACE statement and create a view in the target environment |
Dimension Advanced Deploy Redeployment
After the Dimension node of materialization type table has been deployed for the first time into a target environment, subsequent deployments may result in either altering the Dimension Table or recreating the Dimension table.
Altering the Dimension Tables and Transient Tables
A few types of column or table changes will result in an ALTER statement to modify the Persistent Table in the target environment, whether these changes are made individually or all together:
- Changing table names
- Dropping existing columns
- Altering column data types
- Adding new columns
The following stages are executed:
| Stage | Description |
|---|---|
| Rename Table| Alter Column | Delete Column | Add Column | Edit table description | Alter table statement is executed to perform the alter operation |
Recreating the Dimension Views
The subsequent deployment of Dimension node of materialization type view with changes in view definition, adding table description or renaming view results in recreating the dimension view.
Drop and Recreate Dimension View/Table/Transient
| Change | Stages Executed |
|---|---|
| View to table/transient table | Drop view Create Dimension table/transient table |
| Table/transient table to View | Drop table/transient table Create Dimension view |
| Table to transient table or vice versa | Drop table/transient table Create Dimension table/transient table |
📘 Materialization type of Dimension node
When the materialization type of Dimension node is changed from table/transient table to View and use Override Create SQL for view creation to ensure that the below change is made in the stage function in Create SQL tab so that the order of deployment is maintained.
Dimension Advanced Deploy Undeployment
If a Dimension Node of materialization type table is deleted from a Workspace, that Workspace is committed to Git and that commit deployed to a higher level environment then the Dimension Table in the target environment will be dropped.
The stage executed:
| Stage | Description |
|---|---|
| Drop table/view | Removes the table or view from the environment |
Fact Advanced Deploy
The Coalesce Fact UDN is a versatile node that allows you to develop and deploy a Fact table in Snowflake.
A fact table or a fact entity is a table or entity in a star or snowflake schema that stores measures that measure the business, such as sales, cost of goods, or profit. Fact tables and entities aggregate measures, or the numerical data of a business.
Fact Advanced Deploy Node Configuration
Fact Advanced Deploy Node Properties
| Setting | Description |
|---|---|
| Storage Location | Storage Location where the view will be created |
| Node Type | Name of template used to create node objects |
| Description | A description of the node's purpose |
| Deploy Enabled | If TRUE: node will be deployed/redeployed when changes are detected If FALSE: node will not be deployed or will be dropped during redeployment |
Fact Advanced Deploy Options
You can create the node as:
Fact Advanced Deploy Table
| Setting | Description |
|---|---|
| Create As | Table |
| Multi Source | Toggle: True/False Implementation of SQL UNIONs True: Combine multiple sources in a single node True Options: - UNION: Combines with duplicate elimination - UNION ALL: Combines without duplicate elimination - INSERT: Individual insert for each source False: Single source node or multiple sources combined using a join. |
| Business key | Required column for Type 1 and Type 2 Dimensions |
| Truncate Before | Toggle: True/False This determines whether a table will be overwritten each time a task executes. True: Uses INSERT OVERWRITE False: Uses INSERT to append data |
| Enable tests | Toggle: True/False Determines if tests are enabled |
| Cluster key | True: Allows you to specify the column based on which clustering is to be done Allow Expressions Cluster Key: Allows to add an expression to the specified cluster key False: No clustering done |
| Distinct | Toggle: True/False True: Group by All is invisible. DISTINCT data is chosen for processing. False: Group by All is visible. |
| Group by All | Toggle: True/False True: DISTINCT is invisible, data grouped by all columns False: DISTINCT is visible |
| Order By | Toggle: True/False True: Sort column and sort order drop down are visible and are required to form order by clause. False: Sort options invisible |
| Pre-SQL | SQL to execute before data insert operation |
| Post-SQL | SQL to execute after data insert operation |
Fact Advanced Deploy Transient Table
| Setting | Description |
|---|---|
| Create As | Transient Table |
| Multi Source | Toggle: True/False Implementation of SQL UNIONs True: Combine multiple sources in a single node True Options: - UNION: Combines with duplicate elimination - UNION ALL: Combines without duplicate elimination - INSERT: Individual insert for each source False: Single source node or multiple sources combined using a join. |
| Business key | Required column for Type 1 and Type 2 Dimensions |
| Truncate Before | Toggle: True/False This determines whether a table will be overwritten each time a task executes. True: Uses INSERT OVERWRITE False: Uses INSERT to append data |
| Enable tests | Toggle: True/False Determines if tests are enabled |
| Cluster key | True: Allows you to specify the column based on which clustering is to be done Allow Expressions Cluster Key: Allows to add an expression to the specified cluster key False: No clustering done |
| Distinct | Toggle: True/False True: Group by All is invisible. DISTINCT data is chosen for processing. False: Group by All is visible. |
| Group by All | Toggle: True/False True: DISTINCT is invisible, data grouped by all columns False: DISTINCT is visible |
| Order By | Toggle: True/False True: Sort column and sort order drop down are visible and are required to form order by clause. False: Sort options invisible |
| Pre-SQL | SQL to execute before data insert operation |
| Post-SQL | SQL to execute after data insert operation |
Fact Advanced Deploy View
| Setting | Description |
|---|---|
| Create As | View |
| Override Create SQL | Toggle: True/False True: Executes custom Create SQL False: Creates view based on chosen options |
| Multi Source | Toggle: True/False Implementation of SQL UNIONs True: Combine multiple sources in a single node True Options: - UNION: Combines with duplicate elimination - UNION ALL: Combines without duplicate elimination - INSERT: Individual insert for each source False: Single source node or multiple sources combined using a join. |
| Distinct | Toggle: True/False True: Group by All is invisible. DISTINCT data is chosen for processing. False: Group by All is visible. |
| Group by All | Toggle: True/False True: DISTINCT is invisible, data grouped by all columns False: DISTINCT is visible |
Fact Advanced Deploy Joins
Join conditions and other clauses like where, qualify can be specified in the join space next to mapping of columns in the Coalesce app.
📘 Specify Group by and Order by Clauses
Best Practice is to specify group by and order by clauses in this space if you are not opting for the group by all and order by provided in OPTIONS config.
Fact Advanced Deploy Deployment
Fact Advanced Deploy Initial Deployment
When deployed for the first time into an environment the Fact node of materialization type table will execute the below stage:
| Stage | Description |
|---|---|
| Create Fact Table | This will execute a CREATE OR REPLACE statement and create a table in the target environment |
| Create Fact View | This will execute a CREATE OR REPLACE statement and create a view in the target environment |
Fact Advanced Deploy Redeployment
After the Fact node of materialization type table has been deployed for the first time into a target environment, subsequent deployments may result in either altering the Fact Table or recreating the Fact table.
Altering the Fact Tables/Transient Tables
A few types of column or table changes will result in an ALTER statement to modify the Persistent Table in the target environment, whether these changes are made individually or all together:
- Changing table names
- Dropping existing columns
- Altering column data types
- Adding new columns
The following stages are executed:
| Stage | Description |
|---|---|
| Rename Table| Alter Column | Delete Column | Add Column | Edit table description | Alter table statement is executed to perform the alter operation |
Recreating the Fact Advanced Deploy Views
The subsequent deployment of Fact node of materialization type view with changes in view definition, adding table description or renaming view results recreating the view.
The following stages are executed:
| Stage | Description |
|---|---|
| Create View | Creates a new view with updated definition |
Fact Advanced Deploy Undeployment
If a Fact Node of materialization type table is deleted from a Workspace, that Workspace is committed to Git and that commit deployed to a higher level environment then the Fact Table in the target environment will be dropped.
This is executed in stages:
| Stage | Description |
|---|---|
| Drop table/view | Removes the table or view from the environment |
Drop and recreate Fact View/Table/Transient
| Change | Stages Executed |
|---|---|
| View to table/transient table | Drop view Create Fact table/transient table |
| Table/transient table to View | Drop table/transient table Create Fact view |
| Table to transient table or vice versa | Drop table/transient table Create Fact table/transient table |
📘 Materialization Type of Dimension node
When the materialization type of Dimension node is changed from table/transient table to View and use Override Create SQL for view creation, ensure that the below change is made in the stage function in Create SQL tab so that the order of deployment is maintained.
Factless Fact Advanced Deploy
The Coalesce Fact UDN is a versatile node that allows you to develop and deploy a Fact table in Snowflake.
A factless fact table is used to record events or situations that have no measures, and it has the same level of detail as the dimensions.
Factless Fact Advanced Deploy Node Configuration
Factless Fact Advanced Deploy Node Properties
| Setting | Description |
|---|---|
| Storage Location | Storage Location where the view will be created |
| Node Type | Name of template used to create node objects |
| Description | A description of the node's purpose |
| Deploy Enabled | If TRUE: node will be deployed/redeployed when changes are detected If FALSE: node will not be deployed or will be dropped during redeployment |
Factless Fact Advanced Deploy Options
| Setting | Description |
|---|---|
| Create As | Table or Transient Table |
| Multi Source | Toggle: True/False Implementation of SQL UNIONs True: Combine multiple sources in a single node True Options: - UNION: Combines with duplicate elimination - UNION ALL: Combines without duplicate elimination - INSERT: Individual insert for each source False: Single source node or multiple sources combined using a join. |
| Truncate Before | Toggle: True/False This determines whether a table will be overwritten each time a task executes. True: Uses INSERT OVERWRITE False: Uses INSERT to append data |
| Enable tests | Toggle: True/False Determines if tests are enabled |
| Distinct | Toggle: True/False True: Group by All is invisible. DISTINCT data is chosen for processing. False: Group by All is visible. |
| Group by All | Toggle: True/False True: DISTINCT is invisible, data grouped by all columns False: DISTINCT is visible |
| Order By | Toggle: True/False True: Sort column and sort order drop down are visible and are required to form order by clause. False: Sort options invisible |
| Pre-SQL | SQL to execute before data insert operation |
| Post-SQL | SQL to execute after data insert operation |
Factless Fact Advanced Deploy Joins
Join conditions and other clauses like where, qualify can be specified in the join space next to mapping of columns in the Coalesce app.
📘 Specify Group by and Order by Clauses
Best Practice is to specify group by and order by clauses in this space if you are not opting for the group by all and order by provided in OPTIONS config.
Factless Fact Advanced Deploy Deployment
Factless Fact Advanced Deploy Initial Deployment
When deployed for the first time into an environment the Factless Fact node of materialization type table will execute the below stage:
| Stage | Description |
|---|---|
| Create Fact Table | This will execute a CREATE OR REPLACE statement and create a table in the target environment |
Factless Fact Advanced Deploy Redeployment
After the Fact node of materialization type table has been deployed for the first time into a target environment, subsequent deployments may result in either altering the Fact Table or recreating the Fact table.
Altering the Factless Fact Tables/Transient Tables
A few types of column or table changes will result in an ALTER statement to modify the Persistent Table in the target environment, whether these changes are made individually or all together:
- Changing table names
- Dropping existing columns
- Altering column data types
- Adding new columns
The following stages are executed:
| Stage | Description |
|---|---|
| Rename Table| Alter Column | Delete Column | Add Column | Edit table description | Alter table statement is executed to perform the alter operation accordingly |
Drop and Recreate Factless Fact Table/Transient Table
When the materialization type of Factless Fact node is changed from table to transient table or transient table to table, the below stages are executed:
| Stage | Description |
|---|---|
| Drop table/transient table | Removes existing table |
| Create Factless Fact table/transient table | Creates new table with updated configuration |
Factless Fact Advanced Deploy Undeployment
If a Fact Node of materialization type table is deleted from a Workspace, that Workspace is committed to Git and that commit deployed to a higher level environment then the Fact Table in the target environment will be dropped.
This is executed in two stages:
| Stage | Description |
|---|---|
| Drop table/view | Removes the table or view from the environment |
Code
Work Advanced Deploy Code
| Component | Link |
|---|---|
| Node definition | definition.yml |
| Create Template | create.sql.j2 |
| Run Template | run.sql.j2 |
Persistent Stage Advanced Deploy Code
| Component | Link |
|---|---|
| Node definition | definition.yml |
| Create Template | create.sql.j2 |
| Run Template | run.sql.j2 |
Dimension Advanced Deploy Code
| Component | Link |
|---|---|
| Node definition | definition.yml |
| Create Template | create.sql.j2 |
| Run Template | run.sql.j2 |
Fact Advanced Deploy Code
| Component | Link |
|---|---|
| Node definition | definition.yml |
| Create Template | create.sql.j2 |
| Run Template | run.sql.j2 |
Factless Fact Advanced Deploy Code
| Component | Link |
|---|---|
| Node definition | definition.yml |
| Create Template | create.sql.j2 |
| Run Template | run.sql.j2 |
Versions
Available versions of the package.
| Version # | Release Date | Notes |
|---|---|---|
| 1.1.7 | December 12, 2024 | |
| 1.1.6 | December 02, 2024 | |
| 1.1.5 | November 14, 2024 | |
| 1.1.4 | November 07, 2024 | |
Support
If you need help, please see our support section or contact us.