How IntaLink for dbt Understands JOINs: Relationship Discovery, Lineage Inheritance, and Fanout Detection
Beyond SQL autocomplete: how relationship evidence flows from discovered database metadata into your dbt development workflow.
SQL autocomplete is not a new idea. Most developers already use editors that suggest table names, column names, and SQL keywords. But there is a difference between completing a column name and understanding whether two columns should actually be joined.
In a large dbt project, the difficult questions are often:
- Which table contains the data I need?
- Which field connects these two models?
- Does the relationship still exist after several dbt transformations?
- Is the suggested relationship reliable or just a statistical coincidence?
- Could this JOIN multiply rows and distort an aggregation?
These are not ordinary autocomplete problems. They require information about the relationships between physical data objects.
I recently explored IntaLink for dbt, a VS Code extension that brings discovered table relationships into the dbt SQL development workflow. Rather than treating it as another SQL assistant, I wanted to understand how its relationship information is discovered, transferred, inherited, and used while writing queries.
Here are five engineering details worth exploring.
1. Relationship Discovery Happens Before SQL Completion
The first thing to understand is the architecture. IntaLink for dbt has two separate components:
IntaLink Cloud
The cloud service connects to an authorized data source, reads table and column metadata, compares column values, and discovers candidate relationships.
VS Code Extension
The extension reads sealed relationship sets from IntaLink and makes them available inside the local dbt project.
The workflow looks like this:
- Authorized Database
- IntaLink Cloud
- Metadata and value analysis
- Candidate Relationships
- Review and seal
- Sealed Relationship Sets
- Synchronization
- VS Code Extension
- dbt SQL Completion and Diagnostics
This separation is important. The extension itself does not perform cloud relationship discovery, connect directly to the warehouse, or read warehouse row data during normal synchronization. It consumes previously discovered relationship information.
Figure 1. The relationship graph provides a visual overview of connected tables, fields, and relationship review states.
For developers, this means the work of understanding the broader data structure can happen before an individual SQL query is written. You can inspect the relationship graph, review candidate connections, and then use the available information in your dbt models.
2. JOIN Completion Uses Relationship Information, Not Just Column Names
Let's look at the actual development experience. Suppose we are writing a dbt model that combines customer support interactions with support tickets.
SELECT interactions.cs_log_id AS interaction_id,
interactions.ticket_ref AS ticket_id,
tickets.customer_id,
tickets.ticket_status
FROM {{ source('commerce', 'support_interactions') }} AS interactions
LEFT JOIN
A conventional autocomplete tool might suggest tables based on their names. IntaLink can suggest related models and sources based on its discovered relationship sets. After selecting a related table, it can also suggest the JOIN condition using the aliases already defined in the query.
For example:
LEFT JOIN {{ source('commerce', 'support_tickets') }} AS tickets
ON interactions.ticket_ref = tickets.ticket_id
The key difference is that the completion is informed by previously discovered relationship information. The extension supports two useful completion points:
- After
JOIN, suggest related models and sources. - After
ON, suggest candidate join conditions.
Figure 2. Related models and candidate JOIN conditions appear directly in the SQL editor.
This is especially useful when working with schemas where column names are inconsistent or where foreign keys are not fully declared. However, completion should not be confused with automatic business validation. The developer still decides whether the proposed relationship is appropriate for the model being built.
3. Relationship Lineage Can Survive dbt Transformations
This is one of the more interesting technical capabilities. In dbt, source columns do not always appear under their original names. A column might pass through several staging and intermediate models:
source.orders.customer_refstg_orders.customer_idint_customer_orders.customer_keyfct_customer_revenue.customer_key
If a relationship discovery system only understands the original physical table, it may struggle to provide useful suggestions in downstream models. IntaLink for dbt supports relationship lineage inheritance. When columns pass through unchanged in a way the extension can trace, their discovered relationships can be inherited through:
- Column renames
CASToperations- CTEs
SELECT *- Nested dbt
ref()calls
For example:
-- staging model
SELECT customer_ref AS customer_id,
CAST(order_amount AS DECIMAL(18, 2)) AS amount
FROM {{ source('commerce', 'orders') }}
A downstream model can then use:
SELECT * FROM {{ ref('stg_orders') }}
The extension can trace eligible columns back through these transformations and make their inherited relationship information available.
There is an important boundary: calculated columns are not traced by this lineage mechanism. This is a practical distinction. A simple rename or type conversion preserves the underlying field relationship in a traceable way. A calculated expression may create a new business meaning and should not automatically inherit the original relationship.
The goal is not to assume every transformed column is equivalent. It is to preserve relationship context where the lineage can be established.
4. Relationship Evidence Helps Distinguish Real Keys from Coincidences
Finding two columns with similar values is not enough to establish a reliable relationship. For example:
orders.customer_idcustomers.customer_id
These may represent a genuine key relationship. But consider two unrelated tables containing sequential integer identifiers:
support_tickets.idproduct_categories.id
Their values may overlap significantly simply because both systems generate IDs starting from similar ranges. A relationship discovery system needs to distinguish these situations.
IntaLink provides several relationship classifications, including:
- Key relationship
- Direction corrected
- Shared values
- Likely coincidence
It also exposes supporting evidence:
- Suggested relationship:
interactions.ticket_ref = support_tickets.ticket_id - Inclusion: 100%
- Distinct values: 1,200 → 1,200
- Cardinality: N:1 (inferred from statistics)
- Review: Confirmed
Figure 3. Relationship evidence includes inclusion, distinct values, inferred cardinality, classification, and review status.
The distinction between a key relationship and shared values is important. A shared-value relationship can be useful for investigation, but it should not automatically be treated as a valid production JOIN.
The extension also allows developers to confirm a relationship or stop recommending it. These decisions are saved in:
.intalink/relationships.json
This makes relationship review decisions reusable across the dbt project. Instead of every developer independently investigating the same relationship, the team can share what has already been reviewed.
5. JOIN Fanout Detection: A Query Can Be Valid but Still Wrong
One of the most useful diagnostics is potential JOIN fanout detection. Consider two tables:
- orders
order_idorder_amount
- items
item_idorder_iditem_amount
An order may contain several item records. If we write:
SELECT o.order_status,
SUM(o.order_amount) AS total_amount
FROM orders o
LEFT JOIN items i ON o.order_id = i.order_id
GROUP BY o.order_status;
The order amount may be repeated once for every matching item. For example:
- Order 1001: 100
- Matching items: Item A, Item B, Item C
- Joined order amount: 100 + 100 + 100 = 300
The query runs successfully. But the result is incorrect if the intention was to count each order amount only once.
IntaLink can identify certain JOINs where the joined column is non-unique and mark the potential row multiplication risk while writing SQL. The warning can explain why rows may repeat and suggest aggregating the relevant records by the join key before joining.
Figure 4. The fanout diagnostic identifies a non-unique join column and suggests aggregation before joining.
A possible approach is:
WITH item_summary AS (
SELECT order_id,
SUM(item_amount) AS total_item_amount
FROM items
GROUP BY order_id
)
SELECT o.order_status,
SUM(o.order_amount) AS total_amount,
SUM(i.total_item_amount) AS item_total
FROM orders o
LEFT JOIN item_summary i ON o.order_id = i.order_id
GROUP BY o.order_status;
This is only one possible solution. The correct approach depends on the intended analytical grain and business logic. A one-to-many relationship is not inherently wrong. The important question is whether the measures being aggregated are safe for that relationship.
This is why a warning should be treated as a diagnostic, not an automatic declaration that the JOIN must be removed.
6. A Few Details About the Development Environment
For anyone considering testing the extension, there are a few requirements:
- Editor: VS Code 1.96 or later
- dbt project: Must contain
target/manifest.json - Manifest: Run
dbt parseif needed - IntaLink account: At least one sealed relationship set
- Tested environments: dbt 1.7 + MySQL; dbt 1.10 + PostgreSQL
The extension reads sealed relationship sets and maintains local cached information for completion and hover details. There is also an optional Verify with dbt action. When the developer explicitly triggers it, the user's own dbt connection queries the minimum, maximum, and distinct count of the two columns. The results are displayed locally.
For environments where development and production schemas differ, the extension also supports mapping the dbt schema to the schema used for relationship discovery. For example:
{
"intalinkForDbt.mapping.schemas": {
"dbt_dev": "analytics"
}
}
This helps when the discovery process operates on a shared analytics schema while dbt builds models into a development schema.
The extension also allows configuration of the minimum inclusion ratio for completion and whether pending relationships should be included. These options are useful because different projects may prefer different levels of strictness.
Before connecting production data sources, teams should review the cloud discovery permissions and data-processing scope.
7. When Is This Actually Useful?
I would consider this extension particularly useful in three situations.
Working with an unfamiliar project
You need to understand how existing tables connect without spending hours searching historical SQL.
Building models across multiple systems
You need to find candidate JOIN paths even when schemas and naming conventions are inconsistent.
Debugging incorrect analytical results
Your SQL executes, but totals don't reconcile. You need to inspect relationship cardinality and possible fanout risks.
The extension does not replace dbt tests, business validation, or the developer's responsibility for correct analytical grain. It focuses on making relationship discovery and review more accessible during SQL development.
Final Thoughts
One thing I find interesting about data engineering is how much work happens before a query is actually written. Finding the right table. Tracing the original source of a renamed column. Understanding which JOIN condition is supported by data evidence. Checking whether a relationship could multiply rows.
These tasks are often repetitive, especially in large enterprise projects. Bringing relationship intelligence into the editor is a practical way to reduce some of that investigation.
If you want to explore it, search for IntaLink for dbt in the VS Code Extensions Marketplace.
Disclosure: I'm affiliated with the team developing IntaLink for dbt. This article describes the actual extension workflow and technical capabilities, rather than presenting itself as an independent third-party review.
SQL autocomplete helps you write faster. Relationship intelligence helps you understand what you're writing.
Comments
No comments yet. Start the discussion.