skip to content

In Tableau, how does a relationship in the logical layer differ from a join?

level: middleimportance: must knowfreq 70%

answer

  1. one merges immediately, one waits
  2. which layer does the noodle live in?
  3. the header value repeats once per line
  4. each measure keeps its own grain
  5. cardinality and referential integrity are hints

basics

~20 s

A join flattens two tables into one wide result immediately, duplicating the coarser table's values. A relationship merges nothing up front: Tableau keeps each table at its own grain and generates a query per measure at the grain that measure belongs to.

solid answer

~50 s

Joins live in Tableau's **physical layer**, inside a single logical table. They execute as written, so joining an order header to its line items repeats the header's freight value once per line and `SUM(Freight)` comes out inflated. A **relationship**, drawn as the line between tables on the logical canvas, is a declaration of how the tables relate, not an instruction to merge them. At query time Tableau looks at which fields the view actually uses and builds a query per measure at that measure's native grain, so `SUM(Freight)` is still summed at order grain while `SUM(Amount)` is summed at line grain. Relationships also preserve unmatched rows in ways an inner join does not, and you can supply cardinality and referential-integrity hints as optimisations. Use relationships by default; drop to a join when you genuinely want one flattened table.

code

text · 13 lines
text
Orders(order_id, freight)        Lines(order_id, sku, amount)
  1001, 10.00                      1001, A, 30.00
                                   1001, B, 20.00

-- physical-layer INNER JOIN result
  1001, 10.00, A, 30.00
  1001, 10.00, B, 20.00
  SUM(freight) = 20.00   <- inflated
  SUM(amount)  = 50.00

-- logical-layer RELATIONSHIP
  SUM(freight) = 10.00   <- aggregated at Orders grain
  SUM(amount)  = 50.00   <- aggregated at Lines grain

go deeper

for a junior

Recall that the lines on Tableau's main canvas are relationships and that joins are one level down, inside a logical table. Know that joins can repeat values from the coarser table.

for a middle

Explain the mechanic: a join flattens at model time, a relationship makes Tableau aggregate each measure at its own table's grain at query time. Predict both totals for a header-to-lines model.

for a senior

Diagnose an inflated total in an existing workbook, decide whether to remodel with a relationship or keep the join with a level-of-detail fix, and treat cardinality options as data assertions you verify first.

for a principal

Own the modelling convention across the estate: certified data sources built on the logical canvas, when a denormalised join is sanctioned, and how model changes are validated before they reach dashboards people trust.

## Two layers, two mechanisms Since Tableau 2020.2 a data source has two levels. The **logical layer** is the canvas you land on: each box is a logical table, and the lines between them are **relationships** (often called noodles). Double-clicking a logical table opens the **physical layer** inside it, where the classic **joins** and unions live. That structure encodes the difference. A join is a physical operation: the tables are merged into one result set, and every downstream calculation sees only that flattened result. A relationship is a *contract*: it tells Tableau which fields match, but it does not merge anything until a specific view asks a specific question. ## The duplication problem joins create The canonical failure is a fact table joined to a finer-grained child. Orders holds one row per order with a freight charge; Lines holds one row per item. Inner-join them and the order's freight repeats once per line. `SUM(Freight)` now double-counts, and nothing in the view warns you. Teams have historically worked around this with `MIN(Freight)` tricks, `{FIXED [Order Id] : MIN([Freight])}` level-of-detail expressions, or by never mixing the two grains in one view. With a relationship, the workaround is unnecessary. Tableau knows Freight belongs to Orders. When a view asks for `SUM(Freight)` by Region, Tableau builds a query that aggregates Freight at the Orders grain and joins only what it needs to reach Region. When the same view also asks for `SUM(Amount)` from Lines, Tableau builds a second aggregation at the Lines grain and stitches the results together at the view's level of detail. Each measure is aggregated exactly once, at its own table's grain. ## Contextual joins and unmatched rows A relationship's join type is not fixed by you; Tableau chooses it per query based on which fields the view uses. Drag only fields from one table and Tableau may not touch the other table at all. This has a visible consequence: measure values from rows that have no match on the other side still appear when the view does not require the other table, whereas an inner join would have silently dropped them at model time. The same behaviour is why a relationship can show nulls where a join would have shown nothing — the rows survive long enough to be counted. ## Performance options are assertions, not settings Each relationship carries two optional performance options: **cardinality** (whether each side is many or one) and **referential integrity** (whether all records on a side are guaranteed to have a match, or only some). Left at their defaults, Tableau plays it safe and generates queries that preserve correctness. Tightening them lets Tableau skip work — for example, avoiding a join it can prove is unnecessary. They are assertions *you* make about the data. If you claim every fact row matches a dimension row and some do not, you will get wrong results, not an error. Treat them as tuning to apply after you have verified the data, not as boxes to tick. ## When a join is still the right answer Relationships are the default, but joins have not been deprecated and are correct in several cases. When two tables genuinely describe the same grain — a wide dimension split across two tables keyed one-to-one — a join is simpler and produces one clean logical table. When you need a specific join type and specific unmatched-row behaviour, for instance a left join that deliberately keeps rows with no match so you can count them as nulls, a join expresses that intent precisely. When you are building a curated, denormalised extract on purpose, a join is what materialises it. ## What an interviewer is checking The question is really: do you understand that the noodle is not a join? Candidates who answer "a relationship is just a smarter join" usually cannot explain why `SUM(Freight)` differs between the two, and that number is the whole point. Be ready to describe the model, predict both totals, and say which one the business wanted. ## Practical guidance Build the logical canvas from your star: the fact table in the middle, dimensions related to it. Reach into the physical layer only when a single logical entity happens to be stored across several tables. Verify totals against a known-good source the first time you add a table at a new grain — the failure mode of a wrong model here is not an error, it is a plausible number that is too large.

  • If a relationship does not merge tables, what does Tableau actually send to the database?
    One aggregate query per measure at that measure's own grain, plus whatever joins are needed to reach the dimensions in the view, with the results combined at the view's level of detail. Fields that no measure in the view needs may never be queried at all, which is why adding a table to the model does not automatically slow every sheet.
  • What goes wrong if you assert many-to-one cardinality on a relationship that is really many-to-many?
    Tableau will optimise on the strength of the assertion and can return duplicated or dropped values with no warning. Cardinality and referential integrity are promises about the data, not validations of it, so verify with a row-count check before tightening them.
  • When would you deliberately choose a join over a relationship?
    When the two tables share a grain and describe one entity — a one-to-one split dimension — or when you need a precise join type and unmatched-row behaviour, such as a left join that keeps unmatched facts visible as nulls. Also when you are materialising a denormalised extract on purpose.

A join is stapling two lists together before anyone asks a question; a relationship is keeping both lists on the desk and answering each question from whichever list actually holds the answer.

saying these in an interview costs you the question

  • Calls a relationship just a nicer way to draw a join
  • Cannot explain why joining header to lines inflates a total
  • Sets cardinality options assuming Tableau validates them
  • Thinks joins are deprecated and must never be used
  • Believes the relationship's join type is fixed when you draw it

context

resolving links…