Skip to main content

Self-Join a Coalesce Node to Itself

When building transformation pipelines with Coalesce, one may need to join a table or view with itself, known as a “self-join”. Some typical use cases for self-joins include connecting records in a hierarchical structure, comparing records to other records in the same table, debugging a table to find gaps/missing elements, etc.

DAGs are normally one directional, but using {{ ref_no_link () }} makes this possible.

Self-Join a Node​

  1. In your Workspace, open the Node you want to add the self-join to and navigate to the Join tab.
  2. Configure the join by specifying the same Node within the {{ ref_no_link () }} function.
  3. Be mindful of to alias both mentions of the same table under a different name to prevent ambiguous reference errors when running the query.
  4. Set the appropriate join keys according to the aliasing configured in step 3.
The image shows a configuration interface for a node named STG_ROLE_HIERARCHY. The Join tab is selected and highlighted with a red circle. The interface displays SQL-like code for joining tables, including a FROM clause and a LEFT JOIN operation on a ROLE_HIERARCHY table. 
FROM {{ ref('SRC', 'ROLE_HIERARCHY' }} "ROLE HIERARCHY1"
LEFT JOIN {{ ref_no_link('SRC', 'ROLE_HIERARCHY' }} 
ON "ROLE_HIERARCHY1"."ROLE_ID" = "ROLE_HIERARCHY"."ROLE_ID"