DEV Community

Arisyn
Arisyn

Posted on

From Schema Hunting to Relationship-Aware JOINs: A Better dbt Workflow in VS Code

How to discover related tables, suggest JOIN conditions, and inspect relationship evidence without constantly leaving your SQL editor.

Writing a JOIN in SQL is easy.

Finding the right JOIN in an unfamiliar database is a different story.

If you work with dbt, you've probably spent time searching through schemas, inspecting column names, tracing existing models, or asking colleagues how two tables are connected.

This becomes particularly frustrating when:

  • Foreign keys are missing or incomplete.
  • Different systems use inconsistent column names.
  • Several tables contain similar identifiers.
  • Relationships pass through multiple dbt models.
  • You need to verify whether a candidate JOIN is actually reliable.

The SQL syntax is rarely the bottleneck.

Understanding the relationships behind the SQL is often the real challenge.

We built IntaLink for dbt, a VS Code extension designed to bring discovered database relationships directly into the dbt development workflow.

In this article, I'll walk through how this works from an engineering perspective.

1. Start with Relationship Discovery, Not Column Guessing

Imagine you're building a customer support analytics model.

You have two sources:

support_interactions
--------------------
cs_log_id
ticket_ref
interaction_type
interaction_time

support_tickets
---------------
ticket_id
customer_id
ticket_status
created_at
Enter fullscreen mode Exit fullscreen mode

You suspect that ticket_ref connects to ticket_id.

But how do you verify that?

A conventional approach might involve:

  1. Inspecting both table schemas.
  2. Searching existing SQL models.
  3. Comparing column values.
  4. Checking whether the relationship is consistent.
  5. Investigating whether multiple records match the same key.

IntaLink approaches this problem before the developer starts writing the JOIN.

IntaLink Cloud analyzes authorized database metadata and column-value evidence to identify candidate relationships.

Developers can then inspect the discovered relationships in a graph, review their supporting evidence, and make reviewed relationship sets available to the VS Code extension.

The important distinction is that relationship discovery happens separately from SQL completion.

The extension consumes previously discovered and reviewed relationship information rather than independently scanning the database every time a developer types JOIN.

Figure 1. A visual relationship graph helps developers explore connections between tables and fields.

2. Use Relationship-Aware Completion While Writing SQL

Now let's return to the dbt model.

You start writing:

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
Enter fullscreen mode Exit fullscreen mode

Normally, you would need to find the target table yourself.

With IntaLink for dbt, typing JOIN can surface related models and sources based on the available relationship information.

You can inspect the suggestions and select the relevant table.

This is more useful than simply matching table names because the suggestions are informed by previously discovered relationships.

Figure 2. Related tables are suggested directly in the VS Code SQL editor.

3. Find the JOIN Condition Using Existing SQL Aliases

Finding the table is only half the job.

You still need to identify the correct fields.

After selecting a candidate table, start typing ON.

IntaLink can suggest eligible relationship conditions using the aliases already defined in the SQL statement.

For example:

LEFT JOIN {{ source('commerce', 'support_tickets') }} AS tickets
    ON interactions.ticket_ref = tickets.ticket_id
Enter fullscreen mode Exit fullscreen mode

Notice that the condition uses the aliases interactions and tickets, rather than requiring you to manually reconstruct the relationship from the original database table names.

This is particularly helpful in queries containing several tables with similar column names.

Instead of leaving your editor to investigate the relationship again, you can inspect the available JOIN condition suggestions in the same development context.

The result is a more continuous workflow:

Write FROM
    |
    v
Type JOIN
    |
    v
Inspect related tables
    |
    v
Select a candidate
    |
    v
Type ON
    |
    v
Inspect JOIN conditions
    |
    v
Review and complete SQL
Enter fullscreen mode Exit fullscreen mode

One important point: a suggested JOIN condition is not automatically a validated business rule. Developers still need to determine whether the relationship is appropriate for the intended model.

4. Inspect Relationship Evidence Before Trusting a Suggestion

Autocomplete becomes much more useful when developers can understand why a relationship was suggested.

For example, IntaLink provides supporting information such as:

  • Relationship classification.
  • Value inclusion rate.
  • Distinct-value counts.
  • Inferred cardinality.
  • Review status.

Consider this example:

Relationship:
interactions.ticket_ref
    =
support_tickets.ticket_id

Inclusion:
100%

Distinct values:
1,200 → 1,200

Cardinality:
N:1

Review:
Confirmed
Enter fullscreen mode Exit fullscreen mode

These statistics help developers evaluate the candidate relationship.

A high inclusion rate suggests that values from one column are represented in the other. Cardinality provides additional information about the relationship's structure.

But there is an important limitation.

Two unrelated fields can share values. A statistical match does not automatically establish that they represent the same business entity.

That's why relationship classification and review status matter.

IntaLink distinguishes different relationship types and allows developers to confirm relationships or stop recommending them.

Figure 3. Relationship evidence makes the basis of a suggested JOIN more transparent.

The objective isn't to remove developer judgment.

It's to give developers better information before making a JOIN decision.

5. What Happens When dbt Models Transform the Original Columns?

Another challenge appears when relationships move through multiple dbt models.

Consider:

source.orders.customer_ref
          |
          v
stg_orders.customer_id
          |
          v
int_customer_orders.customer_key
          |
          v
fct_customer_revenue.customer_key
Enter fullscreen mode Exit fullscreen mode

The original column name may no longer exist in downstream models.

If relationship information is only available at the physical source table, developers may lose useful relationship context as the project evolves.

IntaLink for dbt supports relationship lineage inheritance through eligible transformations, including:

  • Column renames.
  • CAST operations.
  • CTEs.
  • SELECT *.
  • Nested dbt ref() calls.

This allows relationship information to remain available across supported transformation paths.

Calculated expressions are not automatically treated as equivalent to their original source columns.

That distinction is important: a derived field may have a different meaning and should not blindly inherit the original relationship.

6. What Changes for a Data Engineering Team?

The benefit is not that developers no longer need to understand their database.

They still need to understand the data, validate business logic, and consider the intended analytical grain.

The difference is that they have less repetitive investigation to do.

Without relationship intelligence With IntaLink for dbt
Search schemas for related tables Inspect discovered relationship suggestions
Search existing SQL for JOIN conditions Get candidate conditions in VS Code
Manually investigate matching columns Review relationship evidence
Rediscover relationships in downstream models Reuse eligible inherited relationship information
Rely heavily on individual experience Make reviewed relationship information available to the team

For teams maintaining large dbt projects, this can make the development workflow more consistent.

It also makes previously investigated relationships easier to reuse.

7. Try It in Your Existing dbt Project

IntaLink for dbt is available as a VS Code extension.

The basic requirements include:

  • VS Code 1.96 or later.
  • An existing dbt project.
  • A generated target/manifest.json.
  • An IntaLink account with at least one sealed relationship set.

The initial workflow involves discovering and reviewing relationships in IntaLink Cloud, then synchronizing the reviewed relationship set into the VS Code extension.

Normal synchronization transfers relationship information and supporting statistics, not business data rows. Cloud discovery itself uses the authorized data source to inspect metadata and compare column values.

For production environments, teams should review the relevant access permissions and data-processing scope before connecting their sources.

You can find the extension here:

IntaLink for dbt — VS Code Marketplace

Final Thoughts

The most time-consuming part of writing a JOIN is often not the SQL itself.

It's understanding which tables belong together, which fields establish the relationship, and why that relationship should be trusted.

We believe this information should be available where developers actually write SQL.

That's what we're building with IntaLink for dbt.

Less schema hunting. More informed JOIN decisions.

Disclosure: I'm part of the team developing IntaLink for dbt. This article describes the actual product workflow and capabilities.


Top comments (0)