1.2 The Tableau Data Model: Relationships vs. Physical Joins

1.2 The Tableau Data Model: Relationships vs. Physical Joins

Prior to Tableau 2020.2, combining multiple tables required creating physical joins (Inner, Left, Right, Full Outer) or unions on a single flat canvas. While straightforward, physical joins permanently fused tables at the row level before analysis began, frequently introducing row duplication, distorted aggregations, and necessitating complex Level of Detail (LOD) expressions. In Tableau 2020.2, Tableau overhauled this paradigm by introducing the modern Tableau Data Model, establishing a clear separation between the Logical Layer and the Physical Layer.

---

The Two-Layer Architecture

The Tableau Data Model is structured into two distinct hierarchical layers:

+-----------------------------------------------------------------------+
|                            LOGICAL LAYER                              |
|   [ Orders Table ] <========== "Noodle" ==========> [ Customers Table ]|
|                   (Retains native grain; no row merge)                 |
+-----------------------------------------------------------------------+
                                    |
                         (Double-click to open)
                                    v
+-----------------------------------------------------------------------+
|                            PHYSICAL LAYER                             |
|         [ Orders ] ------- (INNER JOIN) ------- [ Order Details ]     |
|                    (Row-level merge; fixed at design time)            |
+-----------------------------------------------------------------------+

1. The Logical Layer (Upper Canvas)

When you connect to data or drag tables onto the default canvas, you are working in the Logical Layer. Tables in this layer are linked using flexible connectors affectionately known as noodles.

In the Logical Layer:

• Tables do not merge into a single flat table.
• Each logical table retains its native level of detail (grain).
• No join types (Inner, Left, etc.) are chosen during design time.
• Row duplication does not occur.

2. The Physical Layer (Lower Canvas)

To view or edit the Physical Layer, double-click any logical table or right-click it and select Open. The canvas changes to show physical join and union controls.

In the Physical Layer:

• Tables are combined using traditional database join types (Inner, Left, Right, Full Outer) or Unions.
• Joins and unions define one flattened logical table for query-time analysis; creating the model does not itself materialize every live-source row in Tableau.
• The resulting physical table represents and defines the contents of that parent logical table.

---

The Fan-Out Problem: How Relationships Eliminate Row Duplication

To understand why Relationships represent a major advancement, consider a classic sales dataset containing two tables with differing granularities:

• Orders Table: 1 row per order. (Order 101, Total = $100, Customer = Alice)
• Order Items Table: 1 row per item. Order 101 contains 3 distinct items.

What Happens with a Traditional Physical Join

If you combine these tables using an Inner Join on Order ID in the physical layer, the physical row count expands (fans out):

Because the order row repeats three times, dragging SUM(Order Total) onto the view produces $300, instead of the true total of $100! In the legacy model, analysts had to write cumbersome Level of Detail calculations to correct this:

// Workaround required in physical joins to eliminate fan-out
{ FIXED [Order ID] : MIN([Order Total]) }

How Relationships Handle Differing Granularity

When you relate Orders and Order Items in the Logical Layer:

1. Both tables remain separate logical entities rather than being flattened into one physical table.
2. When you build a view using only SUM(Order Total) and Region, Tableau queries only the Orders table. The result is exactly $100.
3. If you subsequently drag Item ID into the view, Tableau queries each table at its native grain and performs a context-dependent aggregate join at runtime. The measure from Orders is computed accurately without fan-out or duplication.

---

Runtime Smart Aggregation

Relationships are dynamic and context-aware. Instead of executing a fixed join, Tableau analyzes the fields placed on shelves and in filters, generating SQL queries that adapt to the view:

• Table Pruning (Join Culling): If a worksheet contains fields exclusively from the Customers logical table, Tableau does not query or join the related Orders table at all. Unneeded tables are culled from the SQL.
• Dynamic Join Behaviors: If an analyst places a dimension from Table A and a measure from Table B into the view, Tableau automatically performs outer joins as needed to ensure dimension values without matching measures still appear (unless filtered).
• Preservation of Unmatched Data: If a customer has never placed an order, they are not silently dropped from customer lists, avoiding the accidental data loss common with physical Inner Joins.

---

Configuring Performance Options

When you click on a relationship noodle connecting two logical tables, the Performance Options pane opens in the Data Source tab. While Tableau sets intelligent defaults, author intervention can optimize database queries.

Performance Options:
Cardinality:          (o) Many-to-Many    ( ) One-to-Many    ( ) One-to-One
Referential Integrity: (o) Some records match   ( ) All records match

1. Cardinality Settings

Cardinality describes the uniqueness of key values between the two related tables:

• Many-to-Many (Default): Tableau assumes that neither table has unique keys on the relationship field. Tableau generates defensive queries, pre-aggregating both sides before joining.
• One-to-Many: Specifies that the primary table has unique key values (e.g., Customer ID in Customers has exactly one row per customer), while the related table has multiple records (e.g., multiple purchases in Orders). This allows Tableau to streamline aggregate queries.
• One-to-One: Declares that both tables contain unique, non-repeating key values. Tableau can join tables directly without pre-aggregation steps.

2. Referential Integrity Settings

Referential integrity informs Tableau whether every foreign key in one table has a matching primary key in the other:

• Some records match (Default): Informs Tableau that unmatched keys exist (e.g., orders without registered customer accounts, or customers who have not made a purchase). Tableau uses full/left outer joins or defensive checks to ensure unmatched values are not lost.
• All records match (Enables Join Culling): Guarantees that every record in Table A has an exact counterpart in Table B. This allows Tableau to generate faster INNER JOIN statements and, crucially, to cull the join entirely when fields from the related table are not in the view.

[!CAUTION] Critical Exam Warning: If you set Referential Integrity to 'All records match' when unmatched records actually exist in your database, Tableau will execute inner joins or cull tables. As a result, unmatched records will be silently excluded from your visualization without warning. Never set 'All records match' unless referential integrity constraints are strictly enforced in the source schema.

---

When to Use Physical Joins or Unions

Despite the power of Relationships, specific analytical requirements still demand physical layer joins or unions:

1. Spatial Joins: Joining spatial data based on geographic intersection (ST_Intersects or intersecting shapefiles) must be configured in the Physical Layer using physical joins.
2. Unions: Appending identically structured tables (such as 12 monthly transaction CSV files: Jan2026.csv, Feb2026.csv...) into a single tall table must be performed via Union inside the Physical Layer.
3. Row-Level Security (RLS) Entitlement Merging: When security entitlement tables must filter dimension rows at the physical row level before logical relationships are evaluated.
4. Intentional Row Multiplication: Analytical models like market basket analysis or co-occurrence matrices that rely on Cartesian cross-joins to evaluate combinations of items.

---

Comparison Matrix: Relationships vs. Physical Joins

---

Practical Exam Scenarios

Scenario 1: Sales Actuals vs. Sales Quotas

An analyst must report on quarterly sales performance. Sales Actuals are recorded at the daily transaction grain, whereas Sales Quotas are defined at the quarterly sales rep grain.

• The Incorrect Approach: A physical Left Join on Rep_ID and Quarter. The quota repeats for every daily transaction of that rep, inflating the quota by 90x.
• The Correct Approach: Relate Actuals and Quotas in the Logical Layer on Rep_ID and Quarter. Tableau aggregates daily actuals and quarterly quotas independently, comparing them accurately without calculation workarounds.