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. 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.