Tableau / Basic: fields, models and views / Lesson 3

Relationships, joins, unions and grain

Choose logical relationships or physical combination from the analytical grain and verify keys before trusting totals.

About 4 min readContent reviewed 2026-10-28

On this page

Lesson goals

Distinguish logical relationships from physical joins.
Predict duplication and omission in joins and unions.
Treat cardinality and matching assumptions as factual claims.

Separate logical and physical layers

Modern Tableau data sources can relate logical tables while preserving their native analytical detail until fields in a view determine needed queries. Physical joins combine tables within a logical table using a chosen join type. A union stacks compatible rows. These are not interchangeable icons for connect everything. Start with each table’s grain and the question to be answered. Sales lines and monthly targets are different observations; flattening them can repeat the target for every line. A relationship still needs valid matching fields and truthful assumptions rather than being a general repair for bad source keys.

Read a model contract

The first record relates order-line facts to a unique product table and a separate target table at month-product grain. It is a conceptual model, not a saved Tableau data source. Verify the exact fields, data types and intended many/one behavior before setting performance options. Declaring uniqueness or all-records-match incorrectly can affect query results, not merely speed. Retain unmatched-key cases and source reconciliations. A data-source canvas that looks tidy does not prove the relationship contract, and matching column names do not guarantee that their values identify the same business entity.

text Logical modeling review record

Sales Lines: one row per order line
Products: one row per approved product key
Monthly Targets: one row per month and product
review: relationship fields + native grain + actual matching evidence

The diagram defines responsibilities and no relationship or performance option is configured.

Trace physical join multiplication

A physical join can multiply facts when one left key matches several right rows. The second program gives Tea two attribute rows, so the original three facts become five joined rows and Sales rises from 60 to 102. This is an intentional defect illustration. An inner join can instead remove unmatched facts, so review both row inflation and lost coverage. Do not patch the inflated total with SUM DISTINCT over amounts: two legitimate sales can have the same amount. Resolve the key, historical-version or composite-key contract that caused the mismatch, then reconcile original fact identities and values.

python Synthetic physical-join inflation

sales = [("Tea", 12), ("Coffee", 18), ("Tea", 30)]
attributes = [("Tea", "x"), ("Tea", "y"), ("Coffee", "z")]
joined = [(product, amount, label) for product, amount in sales for key, label in attributes if product == key]
print(len(joined))
print(sum(amount for _, amount, _ in joined))

Expected output

5
102

Five rows and 102 expose duplicated Tea amounts. This Python comprehension is not a Tableau relationship or join-engine test.

Review union schema and lineage

A union adds observations and therefore needs compatible column meanings, not only a similar appearance. A renamed field can produce null-filled mismatches, and a repeated input file can duplicate an entire period. Retain source/file lineage in an approved form and define how replacement extracts differ from new events. Do not combine a total row with its own detail as another fact. After every combination, compare counts, key coverage and monetary totals at the intended grain. Correct-looking individual marks cannot reveal all missing or duplicated records in an underlying combined source.

Verify model behavior per view

Use views containing only facts, facts plus a dimension and fields from several logical tables. Include duplicate and unmatched keys with explicit expected outcomes. Relationships are context-aware, so verify the actual analytical combinations rather than assuming one total covers every field mix. This course creates no connection or model and executes only the Python defect oracle. Logical relationship queries, connector support and performance options remain untested. A complete review identifies native grains, matching evidence, combination choice and reconciliation for each important view before a dashboard presents the numbers as trusted analysis.

Common errors and fixes

Monthly targets repeat on every sales line

Why it happens: Different grains were flattened without an allocation contract.
How to fix it: Keep native analytical grains or define a justified allocation.
Prevent it next time: Document table grain before combination.

Cardinality options are set from appearance rather than evidence

Why it happens: Uniqueness and matching were assumed, not verified.
How to fix it: Audit actual keys and use truthful supported relationship assumptions.
Prevent it next time: Retain duplicate and unmatched-key tests.

A repeated extract doubles an appended period

Why it happens: Input lineage and replacement rules were omitted.
How to fix it: Identify the approved input versions and reconcile fact identities.
Prevent it next time: Keep a source ledger with every union.

Check your understanding

1. Why does giving Tea two lookup rows raise 60 to 102 in the join oracle?

Show worked solution

Tea’s 12 and 30 each appear twice, while Coffee 18 appears once:24+60+18=102. The expression sums the joined rows correctly, but their grain is wrong. Repair the key/version relationship instead of changing a valid sales aggregation to mask duplicated facts.

2. Does a relationship eliminate the need to check unmatched product keys?