Dev.to AI 🤖 Ai 👁 0 📖 8 min read

How IntaLink for dbt Understands JOINs: Relationship Discovery, Lineage Inheritance, and Fanout Detection

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 developm

How IntaLink for dbt Understands JOINs: Relationship Discovery, Lineage Inheritance, and Fanout Detection

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
        |
        v
IntaLink Cloud
        |
        | Metadata and value analysis
        v
Candidate Relationships
        |
        | Review and seal
        v
Sealed Relationship Sets
        |
        | Synchronization
        v
VS Code Extension
        |
        v
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_ref
          |
          v
stg_orders.customer_id
          |
          v
int_customer_orders.customer_key
          |
          v
fct_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
  • CAST operations
  • 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_id
customers.customer_id

These may represent a genuine key relationship.

But consider two unrelated tables containing sequential integer identifiers:

support_tickets.id
product_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_id
order_amount

items
-----
item_id
order_id
item_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:

Item Requirement
Editor VS Code 1.96 or later
dbt project Must contain target/manifest.json
Manifest Run dbt parse if 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.

📰 Read the original article on Dev.to AI

Originally published by Dev.to AI. Aggregated on AIWithGhost for educational purposes — full credit and traffic to the original publisher.