Study guide
1.2 The Tableau Data Model: Relationships vs. Physical Joins
Salesforce Certified Tableau Data Analyst Exam
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Connecting to Data & The Logical Data Model
1.1 Data Connections: Live Connections vs. Hyper Extracts 1.2 The Tableau Data Model: Relationships vs. Physical Joins 1.3 Combining Data: Data Blending vs. Cross-Database Joins
2 Chapter 2: Tableau Prep Builder & Data Wrangling
2.1 Tableau Prep Architecture & Data Cleaning 2.2 Reshaping Data in Prep: Pivots, Splits & Wildcard Unions 2.3 Joins, Aggregations & Output Destinations in Prep
3 Chapter 3: Data Quality, Metadata & Field Configuration
3.1 Data Types, Roles, Organization & Default Properties 3.2 In-Desktop Data Cleaning: Splits, Data Interpreter & Source Replacement 3.3 Managing Slowly Changing Dimensions & Data Quality Warnings
4 Chapter 4: Tableau Fundamentals & Calculations
4.1 Dimensions vs. Measures & Discrete vs. Continuous 4.2 Mastering Dates: Discrete Date Parts vs. Continuous Date Truncations 4.3 Calculations: Logic, Strings, Nulls & Type Conversion 4.4 Aggregation Rules, Granularity & Dealing with the Asterisk (ATTR)
5 Chapter 5: Level of Detail (LOD) Expressions
5.1 Level of Detail Architecture & Scoping Syntax 5.2 FIXED LOD Expressions: Scoping & Baseline Benchmarks 5.3 INCLUDE & EXCLUDE LOD Expressions: Dynamic Granularity 5.4 Real-World LOD Patterns: Cohorts, First/Last Dates & Binning
6 Chapter 6: Table Calculations & Directionality
6.1 Table Calculation Mechanics: Addressing vs. Partitioning 6.2 Essential Table Calculations: Running, Window & Percent of Total 6.3 Navigation & Ranking Functions: LOOKUP, INDEX & RANK 6.4 Secondary & Nested Table Calculations: Multi-Level Scoping
7 Chapter 7: Statistical Analytics, Filtering & Order of Operations
7.1 The Tableau Order of Operations: From Extract to Display Filters 7.2 Sets, Set Actions & Combined Sets vs. Groups 7.3 Parameters, Parameter Actions & Dynamic What-If Analysis 7.4 Analytics Pane: Totals, References, Trends & Forecasts
8 Chapter 8: Chart Types & Comparative Visualizations
8.1 Comparison Visualizations: Bar Charts, Highlight Tables & Bullet Graphs 8.2 Part-to-Whole & Correlation Charts: Treemaps, Scatter Plots & Bubbles 8.3 Trend & Distribution Visualizations: Dual-Axis, Combos & Histograms
9 Chapter 9: Advanced Visualizations & Spatial Analysis
9.1 Specialized Visualizations: Waterfall, Gantt, Sparklines & Bump Charts 9.2 Spatial Data & Mapping: Geographic Layers & Spatial Functions 9.3 Visual Polish: Custom Palettes, Annotations & Viz in Tooltip
10 Chapter 10: Dashboard Design & Interactive Actions
10.1 Dashboard Layout, Devices & Visibility 10.2 Filter, Highlight & URL Actions 10.3 Advanced Interactivity: Parameter, Set & Navigation Actions 10.4 Stories & Visual Storytelling: Sequencing Analytical Narratives
11 Chapter 11: Tableau Server & Cloud: Architecture & Publishing
11.1 Tableau Server & Cloud: Sites, Projects, Roles & Permissions 11.2 Publishing Workbooks, Data Sources & Flows
12 Chapter 12: Data Governance, Security & Operations
12.1 Certification, Data Quality, Lineage & Permissions 12.2 Row-Level Security, Schedules, Subscriptions & Alerts
1.2 The Tableau Data Model: Relationships vs. Physical Joins
Key Takeaways Explain This Topic AI
The Tableau Data Model features two layers: the upper Logical Layer where tables are connected via flexible Relationships ('noodles'), and the lower Physical Layer where tables are merged via traditional Joins and Unions.
Relationships preserve each table's native level of detail (LOD) and defer joining until runtime, querying each table independently to prevent measure inflation and row fan-out.
Smart aggregation automatically adapts query generation at runtime based on the specific fields present in the active visualization, querying only necessary tables and applying dynamic join types.
Performance Options allow authors to specify Cardinality (One-to-One, One-to-Many, Many-to-Many) and Referential Integrity (Some records match, All records match) to optimize database query generation.
Setting Referential Integrity to 'All records match' enables Join Culling (omitting unreferenced tables from SQL queries), but will silently eliminate unmatched records if orphan rows exist.
Last updated: September 2026
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):
- Order ID Order Total Item ID Item Price
- 101 $100 Item A $25
- 101 $100 Item B $50
- 101 $100 Item C $25
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:
Both tables remain separate logical entities rather than being flattened into one physical table.
When you build a view using only SUM(Order Total) and Region , Tableau queries only the Orders table. The result is exactly $100 .
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:
Spatial Joins : Joining spatial data based on geographic intersection ( ST_Intersects or intersecting shapefiles) must be configured in the Physical Layer using physical joins.
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.
Row-Level Security (RLS) Entitlement Merging : When security entitlement tables must filter dimension rows at the physical row level before logical relationships are evaluated.
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
- Feature Relationships (Logical Layer) Physical Joins (Physical Layer)
- Canvas Location Default, top-level canvas Accessed by double-clicking a logical table
- Visual Representation Flexible curved lines ("noodles") Join Venn diagrams (Inner, Left, Right, Outer)
Designation Timing Dynamic; join logic determined at runtime per viz Static; join structure fixed at design time
Row Duplication Risk Reduced for ordinary multi-table aggregates because tables retain native grain; many-to-many models still require validation Physical joins can duplicate parent values when joining 1-to-many
LOD Workaround Need Rarely needed for basic multi-table aggregations Frequently required ( {FIXED} to correct sums)
Join Culling Automatic; queries only tables used in the view Relies on foreign keys and referential integrity
Handling Differing Grains Keeps logical tables at their native grains and generates context-sensitive queries Merges fields into a single row-level structure
- 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.