Skip to main content
Sign in

How To: Self-Join a Coalesce Node to Itself

  • Updated

Overview

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 but are not limited to connecting records in a hierarchical structure, comparing records to other records in the same table, debugging a table to find gaps/missing elements, etc.

Due to the one-directional nature of a directed acyclic graph (DAG, or Graph) this use case may seem difficult to implement; luckily, Coalesce makes it easy to write joins with circular references by using our “{{ ref_no_link () }}” function.

This article details how to implement a self-join within the join tab in a Node in Coalesce.

 

Steps to Self-Join a Node to Itself

The example 

  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 key(s) according to the aliasing configured in in step 3.

Example Syntax:

FROM {{ ref('SRC', 'ROLE_HIERARCHY' }} "ROLE HIERARCHY1"
LEFT JOIN {{ ref_no_link('SRC', 'ROLE_HIERARCHY' }} 
ON "ROLE_HIERARCHY1"."ROLE_ID" = "ROLE_HIERARCHY"."ROLE_ID"