Skip to main content

Fix Un-Linked References in Sub-Graphs in Coalesce

In Coalesce, Sub-Graphs are visual tools that help the user organize complex transformation workflows into smaller units. For broader Subgraph practices, see Using Subgraphs to Build Your Pipeline. In this short article we will resolve a common issue, that is unresolved visual references to nodes when referencing objects in Sub-Graphs.

In a scenario where tables feeding dimensions and fact tables are not consistently linked in the graph, users may encounter challenges despite following similar logic. Additionally, situations may arise where a Sub-Graph fails to display links between tables, even when referenced in the Pre-SQL code.

Solution​

You can solve this in two ways.

Use Ref Functions​

Use special macros called ref_link() and ref_no_link() create dependencies to the Nodes without writing the fully qualified name into the script, offering a targeted solution for the visualization challenges.

FROM {{ ref('TGT_STAGE', 'STG_X_TRANSACTIONS') }}
"STG_X_TRANSACTIONS"
WHERE 1=1
AND "DOCDATE" >= '2021-01-01'
{{ ref_link('TGT_STAGE', 'STG_X_TRANSACTIONS_DELETES') }}

Include the Table in the WHERE or FROM Clause​

Include the table in the WHERE or FROM clause of the join but commenting it out. This ensures that Sub-Graphs recognize commented-out tables as part of the metadata, even if they are not utilized in the join.

The Join tab for the DIM_STG_TRANSACTIONS node shows the SQL code referencing the TGT_STAGE.STG_X_TRANSACTIONS table. There is a filter on DOCDATE to include records from January 1, 2021. A commented-out line references the TGT_STAGE.STG_X_TRANSACTIONS_DELETES table, indicating it is not currently included in the query.