Articles
Tableau Relationships vs Joins Interview Questions: Grain, Aggregation, and Duplicates
Prepare Tableau relationships vs joins interview questions with grain checks, a worked fanout example, aggregation traps, and cardinality validation.
A dashboard shows sales of 450 for a customer who bought only 150 of products. The workbook refresh succeeded, the join keys look reasonable, and every visible row contains real data. The error is not necessarily bad arithmetic. It may be a mismatch between the grain of the tables being combined.
For Tableau relationships vs joins interview questions, start by defining one row in each source and one mark in the intended view. Then predict how the model changes the contribution of each record. Practise that decision with Choose Between JOIN, BLEND, and RELATIONSHIP in Tableau.
The worked dataset below is an original exercise. Its SQL fanout arithmetic can be reproduced independently; the discussion of Tableau behavior is grounded in current product documentation, not a claim that this exact workbook has been executed in Tableau.
What is the practical difference between a relationship and a join?
A relationship connects logical tables without declaring one fixed physical join up front. Tableau builds queries in the context of the fields used in the analysis. A physical join combines tables inside a logical table using specified join conditions and a join type. Tableau's relationship-versus-join documentation explains this distinction.
That difference matters when measures originate at different grains. Keeping tables logically separate lets Tableau preserve their native detail during appropriate aggregations. A physical join can multiply a measure before the visualization aggregates it. It can also exclude unmatched records, depending on the join and later filters.
Do not answer that relationships “never use joins.” Joins can still be part of the generated query. Nor should you say relationships repair arbitrary bad data. Duplicate business records, incorrect keys, and unsupported analytical assumptions remain your responsibility.
The Tableau data-model guide distinguishes the logical and physical layers. In an interview, name which layer you are changing. A logical relationship can coexist with physical joins inside one of its tables; those internal joins can already have altered the source grain before the relationship helps anything.
Define the grain before touching the canvas
Suppose Sales has one row per sale and Tickets has one row per support ticket. Both contain customer_id, but that shared field is not unique on either side.
| Sales: customer | sale_id | amount |
|---|---|---|
| A | S1 | 100 |
| A | S2 | 50 |
| B | S3 | 80 |
| Tickets: customer | ticket_id |
|---|---|
| A | T1 |
| A | T2 |
| A | T3 |
| C | T4 |
The independent source totals are sales 230 and tickets 4. For customer A, the expected values are sales 150 and tickets 3. Customer B has sales but no ticket, and C has a ticket but no sale. Those unmatched cases are intentional tests, not dirty data to discard automatically.
Write the analytical requirement next: “Show sales amount and ticket count by customer, retaining customers with activity in either fact table.” This requirement differs from “Show only customers who opened tickets” or “Allocate sales to support categories.” Changing the question legitimately changes the desired result.
Before building a chart, check whether sale_id and ticket_id are unique as promised. A field called ID is not proof. Also specify whether IDs are globally unique or only unique within a tenant, region, or source system. A correct relationship on an incomplete composite key is still the wrong relationship.
Why does a physical join inflate the total?
An inner join on customer_id creates every matching sale-ticket pair. Customer A has two sales and three tickets, so it contributes six joined rows:
| sale_id | amount | ticket_id |
|---|---|---|
| S1 | 100 | T1 |
| S1 | 100 | T2 |
| S1 | 100 | T3 |
| S2 | 50 | T1 |
| S2 | 50 | T2 |
| S2 | 50 | T3 |
Now SUM(amount) is 450, and counting non-null ticket IDs gives 6. These are mathematically correct aggregates of the wrong analytical row set. B and C disappear from this inner join because they have no match on the other side.
Switching to a left join from Sales preserves B but does not remove A's multiplication. The sales total becomes 530, not 230. Ticket count remains 6; C is still absent. A different join icon has changed retention, not solved the many-to-many fanout. See Tableau's physical join guidance for how join types affect records.
| Model/result | Sales total | Ticket count | Important loss or duplication |
|---|---|---|---|
| Independent source totals | 230 | 4 | Baseline before combining |
| Raw inner join on customer | 450 | 6 | A multiplied; B and C lost |
| Raw Sales-left-Tickets join | 530 | 6 | A multiplied; C lost |
| Intended customer-level answer | 230 | 4 | Must preserve both fact domains |
The useful explanation is “two times three,” not simply “the join creates duplicates.” None of the six pairs is necessarily a duplicate row. Each pair is distinct. The duplication is in the contribution of each sale amount and ticket to the requested measure.
How would relationships change this exercise?
Keep Sales and Tickets as separate logical tables related by the valid customer key. Start with conservative relationship settings, then build a customer-level view and verify each measure against its own source baseline. Tableau can aggregate the relevant facts at suitable levels before combining results rather than forcing every analysis through the six raw pairs.
Do not promise an identical SQL query for every sheet. The actual fields, dimensions, filters, and relationship configuration influence the query. Customer-level sales, a sales-only total, and sales by ticket category are different analytical requests even when they use the same data source.
For a complete customer domain, consider an authoritative customer dimension containing A, B, and C, with a model appropriate to the Tableau version and analytical topology. Selecting a customer field only from Sales can omit C from the domain you show. Preserving measures and choosing the displayed domain are related but distinct decisions.
Also ask whether sales can meaningfully be broken down by a ticket attribute. Customer A's sales cannot automatically be allocated among three ticket categories merely because both tables share a customer. The business must define attribution, or the view must clearly communicate repeated membership rather than additive allocation.
Are cardinality settings data-quality guarantees?
No. They describe assumptions Tableau uses to optimize relationship queries; they do not enforce source uniqueness. If the keys are not unique, selecting One can produce incorrect aggregate values. Similarly, claiming All Records Match when unmatched keys exist can remove values. The official Performance Options documentation recommends conservative defaults when you do not know the data shape.
In the fixture, A repeats on both fact tables, so a one-to-one claim on customer would be false. B and C also disprove complete matching between the two tables. You should be able to demonstrate both facts with small source queries before proposing an optimization.
SELECT customer_id, COUNT(*) AS rows_per_customer
FROM sales
GROUP BY customer_id
HAVING COUNT(*) > 1;
This query is not a duplicate-sale detector. It tests uniqueness of the proposed relationship field. A legitimately repeating customer is precisely why One would be the wrong claim. Separately test sale identity if that is the source's uniqueness contract.
For referential integrity, compare distinct key sets in both directions, count null keys, and inspect unmatched examples. One direction matching completely does not prove the reverse. Recheck these assumptions after source changes; yesterday's clean extract is not a permanent constraint on tomorrow's ingestion pipeline.
Why not fix the dashboard with COUNTD or FIXED?
COUNTD(ticket_id) can recover three tickets for A from the six joined pairs, assuming ticket IDs uniquely identify tickets. It does not repair SUM(amount). Applying distinctness to amounts would also be wrong: two separate sales can have the same price. Deduplicate business identities, not coincidentally equal measure values.
Likewise, MIN(amount) per customer would return 50 for A and discard a valid sale. A calculation that makes one total look plausible is not evidence that it preserves the required grain. Add a second sale of the same amount and see whether the proposed repair still works.
An LOD expression may solve a specific calculation requirement, but it is not a universal undo button for a poorly modeled join. You must define the LOD result's grain, how that result is subsequently aggregated, and which filters it respects. A repeated per-sale LOD value can still be summed repeatedly if the final calculation is wrong.
Tableau's LOD filter documentation explains that FIXED is evaluated before ordinary dimension filters, with context and earlier filters affecting the outcome. Keep deeper filter-order exercises in the Tableau LOD interview guide. Here, first repair or explicitly justify the data model.
When is a physical join the right choice?
A physical join can be appropriate when the required analysis genuinely needs one combined row structure and the relationship between rows is understood. Joining sale lines to a unique product dimension may preserve the sale-line grain. A historical dimension with several versions per product, however, needs a time-valid matching rule; joining only on product ID can recreate fanout.
Another option is deliberate pre-aggregation. Aggregate Sales and Tickets independently to customer, then combine those customer-level results with a domain-preserving join strategy. That can produce the intended customer summary. It also removes detail: you cannot later ask an arbitrary sale-level or ticket-level question from the already aggregated result.
Explain this trade-off explicitly. A smaller fixed reporting table can improve predictability for a narrow dashboard, but a flexible exploratory workbook may need detail preserved in separate logical tables. The correct choice depends on supported questions, correctness, maintainability, and measured performance—not a slogan that joins are always bad.
How do you validate the finished workbook?
Create baseline sheets for each logical table before mixing fields. Record row counts, distinct entity counts, additive totals, null keys, and unmatched populations. Then add one field or filter at a time and predict the change before looking at the result.
For this fixture, test A, B, and C separately. Test a sales-only view, a tickets-only view, a combined customer view, and a filtered subset. Include zero-activity and unmatched cases. If a filter intentionally changes the population, compare against a baseline using the same business definition rather than insisting every sheet keep the original grand total.
Inspect both the detailed marks and the grand total. A displayed total may be recomputed rather than simply equal the sum of visible marks, depending on the calculation. A matching total can conceal incorrect distribution across customers; matching customer rows can coexist with an inappropriate overall calculation.
If performance becomes the follow-up, capture the actual slow sheet and generated-query behavior before changing cardinality settings. Document the model, source data, filters, and Tableau version. This guide's SQL fixture verifies relational arithmetic only; it does not substitute for a workbook-level regression test or a measurement of Tableau query performance.