The Coalesce Functional Node Types Package includes:
The Coalesce Date Dimension Table provides a comprehensive breakdown of date-related attributes, enabling efficient handling of date operations across various use cases. The table typically includes columns such as day, month, year, day of the week, week of the year, quarter, and flags like day is weekday or weekend. Additional columns like fiscal year, fiscal quarter, holiday indicators can also be included, depending on the requirements.
The Date Dimension node type has two configuration groups:
| 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 |
| Setting | Description |
|---|---|
| Starting Date | A date from where the date values should be added in the date table.Default is :DATEADD(DAY, -730, CURRENT_DATE) |
| **Number of Days To Generate ** | Numeric value indicating how many days' records should be generated from the Starting Date. |
| Generated Date Column Name | Metadata column name used in the SQL generated for inserting records into the table. |
You can create the node as:
| Setting | Description |
|---|---|
| Create As | Table |
| 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 |
| Insert Zero Key Record | Toggle: True/False Insert Zero Key Record to Dimention if enabled |
| Business key | Required column for Type 1 Dimensions |
| Default String Value | If Insert Zero Key Record toggle is True then add a default value for columns with datatype string |
| Default Surrogate Key Value | If Insert Zero Key Record toggle is True then add a default value for surrogate key column |
| Default Date Value (Date Format DD-MM-YYYY) | If Insert Zero Key Record toggle is True then add a default value for date key column in the format DD-MM-YYYY |
| Enable tests | Toggle: True/False Determines if tests are enabled |
| Pre-SQL | SQL to execute before data insert operation |
| Post-SQL | SQL to execute after data insert operation |
| Setting | Description |
|---|---|
| Create As | View |
| Enable tests | Toggle: True/False Determines if tests are enabled |
| Override Create SQL | Toggle: True/False True: View is created by overriding the SQL False: Nodetype defined create view SQL will execute |
| Setting | Description |
|---|---|
| Create As | Transient Table |
| 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 |
| Insert Zero Key Record | Toggle: True/False Insert Zero Key Record to Dimention if enabled |
| Business key | Required column for Type 1 Dimensions |
| Default String Value | If Insert Zero Key Record toggle is True then add a default value for columns with datatype string |
| Default Surrogate Key Value | If Insert Zero Key Record toggle is True then add a default value for surrogate key column |
| Default Date Value (Date Format DD-MM-YYYY) | If Insert Zero Key Record toggle is True then add a default value for date key column in the format DD-MM-YYYY |
| Enable tests | Toggle: True/False Determines if tests are enabled |
| Pre-SQL | SQL to execute before data insert operation |
| Post-SQL | SQL to execute after data insert operation |
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.
When deployed for the first time into an environment the Date node of materialization type table or view will execute the below stage:
| Stage | Description |
|---|---|
| Create Date Table | This will execute a CREATE OR REPLACE statement and create a table in the target environment |
| Create Date View | This will execute a CREATE OR REPLACE statement and create a view in the target environment |
After the Date 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 Date Table or recreating the Date table.
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 |
Sometimes, changes to config can result in metadata changes from node edits, DML changes, or storage updates. A few cases are listed below:
- Changes in business keys
- Changes in join clauses
- Transformations made at column level
- Changing DML options like Truncate, Insert Zero key
And many more. Most of the time, specific ‘is’ and ‘was’ values will be displayed to specifically show what changed.
The following stages are executed:
| Stage | Description |
|---|---|
| Metadata Update | Business Keys | Truncate | Insert Zero Key | Transformation | Join | A dummy statement would execute with specific changes listed in comments |
The subsequent deployment of Date 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 |
| Change | Stages Executed |
|---|---|
| View to table/transient table | Drop view Create or Replace Date table/transient table |
| Table/transient table to View | Drop table/transient table Create Date view |
| Table to transient table or vice versa | Drop table/transient table Create or Replace Date table/transient table |
📘 Materialization Date Dimension
When the materialization type of Date 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.
If the nodes are redeployed with no changes compared to previous deployment,then no stages are executed
If a Date Dimension Node of materialization type table/view/transient table are deleted from a Datespace, that Datespace is committed to Git and that commit deployed to a higher level environment then the DateTable 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 |
Pivoting ia crucial feature of data transformation.The Pivot node in Coalesce transforms a table by turning the unique values from one column in the input expression into multiple columns and aggregating results where required on any remaining column values. This operation is specified in the FROM clause after the table name or subquery.
It is especially useful for converting narrow tables, such as one with columns for empid, month, and sales,
into wider tables, for example, empid, jan_sales, feb_sales, and mar_sales.
Pivot has three configuration groups:
- Currently, the PIVOT semantic doesn’t allow multiple aggregations
- A pivot query that doesn’t use dynamic pivot can return output with duplicate columns. We recommend avoiding output with duplicate columns. A dynamic pivot query deduplicates duplicate columns.
- A pivot query that doesn’t use dynamic pivot might fail if it attempts to CAST a VARIANT column to a different data type. Dynamic pivot queries don’t have this limitation.
| Property | Description |
|---|---|
| Storage Location | (Required) Storage Location where the Pivot Table will be created |
| Node Type | (Required) 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 |
| Options | Description |
|---|---|
| Create As | Choose 'table', 'view' or 'transient table' |
| Truncate | True/False toggle to enable or disable truncating the output columns |
| Enable tests | Toggle: True/False Determines if tests are enabled |
| Options | Description |
|---|---|
| Infer structure of Pivot table | Toggle: True/False True,it is the first run and the pivot table structure is yet to be determined False,when the pivot table is created and generated columns have been Re-synced in Coalesce |
| Pivot column | Pivot column(Dropdown) Pivot column(textbox)The column from the source table or subquery that will be aggregated and turned into new columns. |
| Single value column | Toggle: True Determines which if analysis of single or multiple value columns to be added.Value column is the column from the source table or subquery that contains the values from which column names will be generated. |
| Value Column | -Value Column(Dropdown) -Value Column(textbox) Values you want to populate in the new columns. |
| Aggregate Functions | Aggregation you want to apply, like AVG, COUNT, MAX, MIN, and SUM. |
| Subquery -PIVOT column values | Not mandatory.A sql query is expected.When a query is mentioned,pivot happens on all values found in the subquery |
| Filter Column Values(comma separated list of column values-Ex 'Q1','Q2') | Not mandatory.Specified list of column values for the pivot column |
| Exclude Columns | Not mandatory.To specifically exclude columns from a pivot query |
| Default value for NULL | Replace all NULL values in the pivot result with the specified default value. The default value can be any scalar expression that does not depend on the pivot and aggregation column |
| Options | Description |
|---|---|
| Infer structure of Pivot table | Toggle: True/False True,it is the first run and the pivot table structure is yet to be determined.False,when the pivot table is created and generated columns have been Re-synced in Coalesce |
| Pivot column | Pivot column(Dropdown) Pivot column(textbox) The column from the source table or subquery that will be aggregated and turned into new columns. |
| Pivot operation on same column values | Toggle:True/False - True If pivot is to applied to same pivot column values for multiple value columns - False If pivot is to applied to differnt pivot column values for each value column |
| Single value column | Toggle:False Determines which if analysis of single or multiple value columns to be done.Value column is the column from the source table or subquery that contains the values from which column names will be generated. |
| Value Column | -Value Column(Dropdown) -Value Column(textbox) Values you want to populate in the new columns. -Aggregate Functions Aggregation you want to apply, like AVG, COUNT, MAX, MIN, and SUM. -Column Values Enabled if the Pivot operation on same column values is false |
| Filter Column Values(comma separated list of column values-Ex 'Q1','Q2') | Specified list of column values for the pivot column |
| Default value for NULL | Replace all NULL values in the pivot result with the specified default value. The default value can be any scalar expression that does not depend on the pivot and aggregation column |
- Add a Pivot node on top of source node
- Add the pivot columns,value columns ,aggregation operation from config
- When you choose the pivot and value dropdown,ensure that the textbox alongside the dropdown is entered with Column name.This textBox information is required once the pivot table structure is synced into Coalesce.
- The toggle 'Infer Structure of Pivot Data' is required to be true when the node is created for the first time.
- The toggle 'Single value column' is set to false, if you want a multi-dimensional pivot
- Once the pivot table is created,the 'Re-Sync Columns' can be used to sync the structure of pivot table into Coalesce mapping grid.
- After Re-sync,recreate the table with 'Infer Structure of Pivot Data' set to false
- If the above works, it should be deployable as is. Deploy will simply take the columns and execute a create table.
- Hit run to insert data into table keeping the 'Infer Structure of Pivot Data' set to false
- Create table with ‘Infer PIVOT structure’ toggle enabled
- Re-Sync columns to the mapping grid
- Deploy with ‘Infer PIVOT structure’ toggle set to false
- Repeat the above steps if you see changes in column of table during redeployment.It is fine to skip for change in materialization type,change in target location or change in node name
- Ensure the new columns added or dropped are part of the inferred PIVOT structure and not added/dropped directly in the mapping grid.The deployment will succeed but insert will fail
📘 Deployment
Ensure 'Infer Pivot structure' set to false before deployment
When deployed for the first time into an environment the Pivot node of materialization type table or view will execute the below stage:
| Stage | Description |
|---|---|
| Create Pivot Table | This will execute a CREATE OR REPLACE statement and create a table in the target environment |
| Create Pivot View | This will execute a CREATE OR REPLACE statement and create a view in the target environment |
After the Pivot 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 Pivot Table or recreating the Pivot table. Pivot
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 |
Sometimes, changes to config can result in metadata changes from node edits, DML changes, or storage updates. A few cases are listed below:
- Changes in join clauses
- Transformations made at column level
- Default NULL values - Config changes
And many more. Most of the time, specific ‘is’ and ‘was’ values will be displayed to specifically show what changed.
The following stages are executed:
| Stage | Description |
|---|---|
| Metadata Update | Transformation | Default NULL| Join | A dummy statement would execute with specific changes listed in comments |
Note: A few configuration changes are not recommended without re-inferring the table before deployment. For more details, please refer to this document - Metadata Prevention
The subsequent deployment of Pivot 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 Pivot View | Creates a new view with upPivotd definition |
| Change | Stages Executed |
|---|---|
| View to table/transient table | Drop view Create or Replace Pivot table/transient table |
| Table/transient table to View | Drop table/transient table Create Pivot view |
| Table to transient table or vice versa | Drop table/transient table Create or Replace Pivot table/transient table |
If the nodes are redeployed with no changes compared to previous deployment,then no stages are executed
If a Pivot 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 PivotTable 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 |
The Unpivot node in Coalesce rotates a table by transforming columns into rows. UNPIVOT is not exactly the reverse of PIVOT because it cannot undo aggregations made by PIVOT.
This operator can be used to transform a wide table (e.g. empid, jan_sales, feb_sales, mar_sales) into a narrower table (e.g. empid, month, sales).
- It cannot reverse aggregations performed by PIVOT
- It requires that all columns have the same data type.In case if the columns from source have diffrent data types,ensure the data types are type casted in an upstream node before adding a UNPIVOT node.
- UNPIVOT cannot be used in dynamic tables or stored procedures
- Ensure that your data is structured and formatted correctly, as any inconsistencies may affect the unpivoting process. It's important to check for any missing values, duplicate entries, or data types that are not compatible with the unpivot function.
Unpivot has three configuration groups:
| Property | Description |
|---|---|
| Storage Location | (Required) Storage Location where the Pivot Table will be created |
| Node Type | (Required) 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 |
| Options | Description |
|---|---|
| Create As | Choose 'table', 'view' or 'transient table' |
| Truncate | True/False toggle to enable or disable truncating the output columns |
| Enable tests | Toggle: True/False Determines if tests are enabled |
| Options | Description |
|---|---|
| Infer structure of Pivot table | Toggle: True/False True,it is the first run and the pivot table structure is yet to be determined.False,when the pivot table is created and generated columns have been Re-synced in Coalesce |
| Value-Coulmn | Column that will hold the values from the unpivoted columns |
| Name-column | Column that will hold the names of the unpivoted columns |
| Column-list | The names of the columns in the source table or subquery that will be rotated into a single pivot column |
| Include NULLS | Specifies whether to include or exclude rows with NULLs |
- Add a Unpivot node on top of source node
- Add the Unpivot column list ,value column,name column in config
- When you choose the Unpivot and value dropdown,ensure that the textbox alongside the dropdown is entered with Column name.This textBox information is required once the Unpivot table structure is synced into Coalesce.
- The toggle 'Infer Structure of Unpivot Data' is required to be true when the node is created for the first time.
- The toggle 'Single value column' is set to false, if you want a multi-dimensional Unpivot
- Once the Unpivot table is created,the 'Re-Sync Columns' can be used to sync the structure of Unpivot table into Coalesce mapping grid.
- After Re-sync,recreate the table with 'Infer Structure of Unpivot Data' set to false
- If the above works, it should be deployable as is. Deploy will simply take the columns and execute a create table.
- Hit run to insert data into table keeping the 'Infer Structure of Pivot Data' set to false
- Create table with ‘Infer UNPIVOT structure’ toggle enabled
- Re-Sync columns to the mapping grid
- Deploy with ‘Infer UNPIVOT structure’ toggle set to false
- Repeat the above steps if you see changes in column of table during redeployment.It is fine to skip for change in materialization type,change in target location or change in node name
- Ensure the new columns added or dropped are part of the inferred UNPIVOT structure and not added/dropped directly in the mapping grid.The deployment will succeed but insert will fail
📘 Deployment
Ensure 'Infer Unpivot structure' set to false before deployment
When deployed for the first time into an environment the Unpivot node of materialization type table or view will execute the below stage:
| Stage | Description |
|---|---|
| Create Unpivot Table | This will execute a CREATE OR REPLACE statement and create a table in the target environment |
| Create Unpivot View | This will execute a CREATE OR REPLACE statement and create a view in the target environment |
After the Unpivot 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 Unpivot Table or recreating the Unpivot table. Unpivot
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 |
Sometimes, changes to config can result in metadata changes from node edits, DML changes, or storage updates. A few cases are listed below:
- Changes in join clauses
- Transformations made at column level
- Default NULL values - Config changes
And many more. Most of the time, specific ‘is’ and ‘was’ values will be displayed to specifically show what changed.
The following stages are executed:
| Stage | Description |
|---|---|
| Metadata Update | Transformation | Default NULL| Join | A dummy statement would execute with specific changes listed in comments |
Note: A few configuration changes are not recommended without re-inferring the table before deployment. For more details, please refer to this document - Metadata Prevention
The subsequent deployment of Unpivot 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 Unpivot View | Creates a new view with upUnpivotd definition |
| Change | Stages Executed |
|---|---|
| View to table/transient table | Drop view Create or Replace Unpivot table/transient table |
| Table/transient table to View | Drop table/transient table Create Unpivot view |
| Table to transient table or vice versa | Drop table/transient table Create or Replace Unpivot table/transient table |
If the nodes are redeployed with no changes compared to previous deployment,then no stages are executed
If a Unpivot Node of materialization type table/view/transient table are deleted from a Unpivotspace, that Unpivotspace is committed to Git and that commit deployed to a higher level environment then the UnpivotTable 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 |
The Match Recognize node type uses Snowflake's SQL MATCH_RECOGNIZE clause to identify and process patterns in datasets(https://docs.snowflake.com/en/sql-reference/constructs/match_recognize). It helps identify events, trends, and anomalies by analyzing rows in sequence. It is useful for tasks like fraud detection, session tracking, and clickstream analysis, with options to customize patterns for different needs.
Match Recognize has three configuration groups:
| Property | Description |
|---|---|
| Storage Location | (Required) Storage Location where the Match Recognize Table will be created |
| Node Type | (Required) 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 |
| Options | Description |
|---|---|
| Create As | Choose 'table', 'view' or 'transient table' |
| Truncate | True/False toggle to enable or disable truncating the output columns |
| Enable tests | Toggle: True/False Determines if tests are enabled |
| Options | Description |
|---|---|
| Infer structure of Match Recognize table | Toggle: True/False True,it is the first run and the Match Recognize table structure is yet to be determined False,when the Match Recognize table is created and generated columns have been Re-synced in Coalesce |
| Options | Description |
|---|---|
| Partition By | Toggle: True/False True,Enable the Column Dropdown and Textbox to add columns for partitioning False,Disable the Dropdown and Textbox to add columns |
| Match Recognize Column | Match Recognize column(Dropdown) Match Recognize column(textbox) The column from the source table or subquery that will be added in Partition By Clause of the Match Recognize Query |
| Order By | Toggle: True/False True,Enable the Column Dropdown and Textbox to add columns for adding in order by clause False,Disable the Dropdown and Textbox to add columns |
| Match Recognize Column | Match Recognize column(Dropdown) Match Recognize column(textbox)The column from the source table or subquery that will be added in Order By Clause of the Match Recognize Query |
| Options | Description |
|---|---|
| Expression | Expression Textbox A function name to be added as part of major for e.g MATCH_NUMBER,COUNT etc |
| Column Name (OR Value to pass to Expression) | Column Textbox Acolumn name or the value to the function mentioned in the expression of measures of the Match Recognize Query |
| Alias of Expression Column Name | Alias Expression Textbox Alias of the expression of measures of the Match Recognize Query |
| Options | Description |
|---|---|
| Rows Per Match | (Dropdown) Specifies which rows are returned for a successful match.Select ONE ROW PER MATCH , ALL ROWS PER MATCH or BLANK if not required |
| All Rows Per Match | (Dropdown) Returns a row for each row that is part of the match. Select SHOW EMPTY MATCHES, OMIT EMPTY MATCHES , WITH UNMATCHED ROWS or BLANK if not required |
| After Match Skip | (Dropdown) Specifies where to continue after a match. Select PAST LAST ROW , TO NEXT ROW, TO or BLANK (if not required ) |
| To | (Dropdown) Continue matching at the first or last (default) row that was matched to the given symbol. Select FIRST or LAST. |
| After match skip variable name | (Textbox) Add an alias name for the after match skip clause. |
| Options | Description |
|---|---|
| Pattern | (Textbox) The pattern defines a valid sequence of rows that represents a match. The pattern is defined like a regular expression. (regex) and is built from symbols, operators, and quantifiers. |
| Options | Description |
|---|---|
| Expression | (Textbox) Defining symbols (also known as “pattern variables”) are the building blocks of the pattern.A symbol is defined by an expression.The |
| Column Name | (Textbox) It is the symbol name of the expression |
| Options | Description |
|---|---|
| Select Query Functions | Toggle: True/False True,Enable Expression and columns to add aggregate functions to Select Query False,Disable the option |
| Expression | Expression Textbox A function name to be added as part of major for e.g AVG,COUNT etc |
| Column Name (OR Value to pass to Expression) | Column Textbox Acolumn name or the value to the function mentioned in the expression of select quey of the Match Recognize Query |
| Alias of Expression Column Name | Alias Expression Textbox Alias of the expression of select query of the Match Recognize Query |
| Select Query Order By | Toggle: True/False True,Enable the Column Dropdown and Textbox to add columns for adding in order by clause of select query False,Disable the Dropdown and Textbox to add columns |
| Select Query Order By Column | Select Query Order By column(Dropdown) Select Query Order By column(textbox).The column from the source table or subquery that will be added in Order By Clause of the Select Query |
- Add a Match Recognize node on top of source node
- Add the Match Recognize options from config
- When you choose the Match Recognize and value dropdown,ensure that the textbox alongside the dropdown is entered with Column name.This textBox information is required once the Match Recognize table structure is synced into Coalesce.
- The toggle 'Infer Structure of Match Recognize Data' is required to be true when the node is created for the first time.
- Once the Match Recognize table is created,the 'Re-Sync Columns' can be used to sync the structure of Match Recognize table into Coalesce mapping grid.
- For further Match Recognize operations,keep the 'Infer Structure of Match Recognize Data' set to false
- Create table with ‘Infer Structure of Match Recognize Data’ toggle enabled
- Re-Sync columns to the mapping grid
- Deploy with ‘Infer Structure of Match Recognize Data’ toggle set to false
- Repeat the above steps if you see changes in column of table during redeployment.It is fine to skip for change in materialization type,change in target location or change in node name
- Ensure the new columns added or dropped are part of the inferred Match Recognize structure and not added/dropped directly in the mapping grid.The deployment will succeed but insert will fail.
When deployed for the first time into an environment the Match Recognize node of materialization type table or view or transient table will execute the below stage:
| Stage | Description |
|---|---|
| Create Match Recognize Table/transient table/view | This will execute a CREATE OR REPLACE statement and create a Match Recognize table in the target environment |
When the Match Recognize node is redeployed with any changes in table or config changes result in re-creating the node
The below stage is executed:
| Stage | Description |
|---|---|
| Create Match Recognize Table/transient table/view | This will execute a CREATE OR REPLACE statement and create a Match Recognize table in the target environment |
| Change | Stages Executed |
|---|---|
| View to table/transient table | Drop view Create or Replace Match Recognize table/transient table |
| Table/transient table to View | Drop table/transient table Create Match Recognize view |
| Table to transient table or vice versa | Drop table/transient table Create or Replace Match Recognize table/transient table |
If the nodes are redeployed with no changes compared to previous deployment,then no stages are executed
If a Match Recognize 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 Match Recognize node in the target environment will be dropped.
This is executed in below stage:
| Stage | Description |
|---|---|
| Drop table/view/transient table | Removes the table or view from the environment |
The Coalesce View-Qualify advanced deploy UDN is a versatile node that allows you to develop and deploy a View in Snowflake with an added QUALIFY filter.
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.In a SELECT statement, the QUALIFY clause filters the results of window functions.
QUALIFY does with window functions what HAVING does with aggregate functions and GROUP BY clauses.
The View node type has two configuration groups:
| Properties | Description |
|---|---|
| Storage Location | Storage Location where the WORK will be created |
| Node Type | Name of template used to create node objects |
| Description | A description of the node's purpose |
| Deploy Enabled | If TRUE the node will be deployed / redeployed when changes are detected If FALSE the node will not be deployed or will be dropped during redeployment |
| Options | Description |
|---|---|
| QUALIFY filter | Toggle: True/False True: Quaify filter on a window function is added to the view definition and the configs related to qualify filter are displayed False: A simple view is created |
| Window functions | A drop down with the list of window functions is visible.Window function to be used in QULAIFY filter predicate is chosen. |
| Window function column name | The window fucntion is applied to this specific column.This is not required for window functions like 'ROW_NUMBER','RANK','DENSE_RANK','PERCENT_RANK','NTILE' |
| Constant value-desired number of buckets | Enabled whn the window function chosen is "NTILE" |
| Partition By | Toggle: True/False True: Lists the columns to be used for partitioning window function False: Partition by column drop down is invisible.Check preferences for more info |
| Order By | Toggle: True/False True: Sort column and sort order drop down are visible and are required to form order by clause False: Sort column and sort order drop down are invisible.Check preferences for more info |
| Operator | A drop with the list of comparison operators are listed down |
| Compare return value of function with window column | Toggle: True/False True: The return value of window function is compared with the column chosen for window function False: Compared with an expected value entered in the config below |
| Expected value | The expected value possibly an integer to compare the results of window function |
- PARTITION BY is optional.You can omit PARTITION BY if you want to treat the entire result set as one partition.
- ORDER BY is also optional.But it is required for ranking or cumulative functions where order matters (e.g., ROW_NUMBER(), RANK(), LAG(), LEAD())
- ORDER BY is optional for functions like AVG(), SUM(), etc., unless you want cumulative behavior.
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.
When deployed for the first time into an environment the View node will execute the Create View stage.
| Stage | Description |
|---|---|
| Create View | This will execute a CREATE OR REPLACE statement and create a View in the target environment |
The subsequent deployment of View node with changes in node name or node location results in deleting the existing view and recreating the view.
The following stages are executed:
| Stage | Description |
|---|---|
| Drop View | Removes existing view |
| Create View | Creates new view with updated definition |
Changes in QUALIFY filter or view definition results in recreating the view.
The following stages are executed:
| Stage | Description |
|---|---|
| Create View | Creates new view with updated definition |
If a View Node is deleted from a Workspace, that Workspace is committed to Git and that commit deployed to a higher level environment then the View in the target environment will be dropped.
This is executed in the below stage:
| Stage | Description |
|---|---|
| Drop View | Removes the view from the environment |
| Component | Link |
|---|---|
| Node definition | definition.yml |
| Create Template | create.sql.j2 |
| Run Template | run.sql.j2 |
| Component | Link |
|---|---|
| Node definition | definition.yml |
| Create Template | create.sql.j2 |
| Run Template | run.sql.j2 |
| Component | Link |
|---|---|
| Node definition | definition.yml |
| Create Template | create.sql.j2 |
| Run Template | run.sql.j2 |
| Component | Link |
|---|---|
| Node definition | definition.yml |
| Create Template | create.sql.j2 |
| Run Template | run.sql.j2 |
| Component | Link |
|---|---|
| Node definition | definition.yml |
| Create Template | create.sql.j2 |