Overview
You may have a use case that requires selecting a distinct set of values for an incoming source or set of sources. In Coalesce, there are a few ways you can achieve this functionality.
- Use a View Node Type
- GROUP BY ALL Clause in Join
- Create a Custom Node Type with SELECT DISTINCT Behavior
Use a View Node Type
The out of the box View Node Type has a "Distinct" toggle in the options you can turn on/off to force the DISTINCT keyword to be added to the view definition.
Note: The View Node Type is disabled by default, but can be enabled in your Build Settings -> Node Types area.
GROUP BY ALL Clause in Join
If you want to use an existing Node Type like a Stage or another Node Type that doesn't offer the DISTINCT toggle, you can add a "GROUP BY ALL" clause in the Join tab of the Node Editor, which achieves the same behavior and, due to the way Snowflake operates, performance as DISTINCT.
Create a Custom Node Type with SELECT DISTINCT Behavior
If you wish to have a Node Type with this functionality built in so you can use it for many Nodes without having to complete one of the approaches detailed above, you can duplicate an existing Node Type and and edit the Create Template (for View behavior) and the Run Template (for Table behavior) to include the DISTINCT keyword in the SELECT, so that it is automatically included for all Nodes of this new Node Type.
Edit the Create Template to add a DISTINCT clause to the SELECT for the View creation.
Edit the Run Template to add a DISTINCT clause to the SELECT for the Table refresh.
Additional Information
- "GROUP BY ALL" Documentation by Snowflake