Logical and Physical Data Layers in Tableau

Course: Tableau Training

Topic: Logical and Physical Data Layers

Objective: Understand Tableau's two-layer data model, learn how logical tables and physical tables work, differentiate relationships from joins, and build accurate data models for business analysis.

Course Links: Explore Tableau Training | Register for a Tableau Course Demo

1. Introduction to Tableau Data Modeling

Tableau is a data visualization and business intelligence tool that connects to data stored in Excel files, CSV files, relational databases, cloud platforms, and other supported systems. Before creating charts and dashboards, users often need to organize information from different tables into a meaningful data model.

A data model defines how tables are organized and how Tableau understands the connections between them. It helps Tableau determine which records and fields should be used when answering analytical questions.

Since Tableau 2020.2, the data model for supported data sources has two main layers: the logical layer and the physical layer. The logical layer connects logical tables using relationships, while the physical layer combines physical tables using joins and unions.

Why Are Data Layers Important?

• Data organization: They help structure data from multiple tables.
• Accurate calculations: They help reduce incorrect totals caused by inappropriate table combinations.
• Flexible analysis: They support analysis across tables with different levels of detail.
• Efficient data modeling: They allow analysts to choose relationships, joins, and unions according to business requirements.
• Better reporting: They provide a reliable foundation for worksheets, dashboards, and reports.
• Data integrity: They help preserve the intended meaning and structure of source data.

2. What Is the Logical Layer in Tableau?

The logical layer is the top-level layer of Tableau's data model. It is the default canvas displayed on the Data Source page. Tables placed on this canvas are called logical tables, and relationships connect them.

A relationship describes how two tables are associated through corresponding fields without physically merging them into one flat table in the data model.

For example, a company may have separate Orders, Customers, and Products tables. The logical layer can connect these tables through fields such as Customer ID and Product ID while allowing each table to retain its own structure and level of detail.

Key Characteristics of the Logical Layer

• It is the default view of the Data Source page in modern Tableau versions.
• It contains logical tables.
• Logical tables are connected using relationships, often represented by curved lines called noodles.
• It does not require users to select a traditional join type for every relationship.
• Related tables remain distinct rather than being permanently flattened into a single physical table.
• Each logical table can contain one or more physical tables.
• Tableau uses the fields in a visualization and the defined relationships to determine how to query the relevant data.
• It is useful for data models containing multiple tables, including tables with different levels of detail.

Example of the Logical Layer

Consider an online store that maintains three tables.

Orders Table

Customers Table

Products Table

In the logical layer, Tableau can model the tables as follows:

             Customers
                 |
        Relationship: Customer ID
                 |
               Orders
                 |
         Relationship: Product ID
                 |
              Products

The diagram illustrates the conceptual connections between the tables. The tables remain separate in the model, and Tableau determines the appropriate query behavior for the fields used in a worksheet.

Benefits of the Logical Layer

• Preserves the original structure of separate tables.
• Reduces the need to decide on a fixed join type for every table combination.
• Supports analysis at different levels of detail.
• Can help prevent incorrect aggregation caused by flattening tables with different levels of detail.
• Supports flexible data models, including many star-schema and multi-fact scenarios.
• Makes it easier to adjust a data model as reporting requirements change.

3. What Is the Physical Layer in Tableau?

The physical layer is the layer inside a logical table where users combine physical tables using joins and unions. To open it, double-click a logical table on the Data Source page.

Tables placed in the physical layer are called physical tables. When physical tables are joined or unioned, Tableau treats the resulting combination as the contents of one logical table.

Key Characteristics of the Physical Layer

• It is accessed by opening a logical table in the logical-layer canvas.
• It contains physical tables.
• It supports joins between physical tables.
• It supports unions between compatible tables.
• It can contain one physical table or multiple combined physical tables.
• Joins merge columns and records according to join conditions and join types.
• Unions append records from compatible tables.
• The combination can change the number of rows and the level of detail of the resulting table.

Example of the Physical Layer

Suppose a company has an Orders table and a Customer Details table containing the same Customer ID values for matching customers. The physical layer can join these tables to create a combined table.

Orders Table

Customer Details Table

Result of an Appropriate Join

The physical layer creates a combined table in which the order fields and customer details appear together. The actual result depends on the join type and the matching records.

4. Difference Between Logical and Physical Layers

5. Understanding Relationships in the Logical Layer

A relationship defines how two logical tables are connected through matching fields. It tells Tableau which fields relate the tables, while allowing the tables to remain separate in the data model.

Relationships are useful when a business needs to analyze information from several tables without committing to a single fixed join operation at the beginning.

Example

A Sales table contains transaction-level records, while a Customers table contains one row per customer. A relationship can connect them through Customer ID.

Sales.Customer ID = Customers.Customer ID

When a worksheet uses Sales and Customer Name, Tableau uses the relationship to query the relevant information. It determines the query behavior according to the data model and the fields in the view.

How to Create a Relationship

1. Open Tableau and connect to a supported data source.
2. Open the Data Source page.
3. Drag the first table onto the logical-layer canvas.
4. Drag another table onto the canvas near the first table.
5. Tableau may automatically identify a relationship using matching fields or key information.
6. If the relationship is not configured correctly, select the relationship line and edit the field pairs.
7. Choose the corresponding fields, such as Customer ID in both tables.
8. Check that the matched fields have compatible underlying data types.
9. Repeat the process for any additional related tables.
10. Open a worksheet and validate the results using relevant fields from the connected tables.

Relationship Settings

Depending on the Tableau version and connection, relationship configuration can include options that describe the cardinality of the relationship and referential integrity.

• Cardinality: Describes whether records are one-to-one, one-to-many, or many-to-many across the related tables.
• Referential integrity: Describes whether records in one table are expected to have matching records in the other table.
• Matching fields: Specify the field pairs that establish the relationship.

Use accurate settings based on the actual data. Incorrect assumptions about uniqueness or matching records can lead to inaccurate results or inefficient queries.

6. Understanding Joins in the Physical Layer

A join combines physical tables by matching records according to one or more join conditions. Unlike a relationship, a join merges the tables into a combined physical table before the worksheet analysis is performed.

Types of Joins

6.1 Inner Join

An inner join returns records that have matching values in both tables.

Table A INNER JOIN Table B
Returns matching records from both tables.

Example: Return orders only when a matching customer record exists.

6.2 Left Join

A left join returns all records from the left table and matching records from the right table. If a record has no match in the right table, the right-side fields are null.

Table A LEFT JOIN Table B
Keeps all rows from Table A.

Example: Display every order, even if some orders do not have a matching customer record.

6.3 Right Join

A right join returns all records from the right table and matching records from the left table. If a record has no match in the left table, the left-side fields are null.

Table A RIGHT JOIN Table B
Keeps all rows from Table B.

Example: Display every customer, including customers who have not placed an order, when the Customers table is on the right.

6.4 Full Outer Join

A full outer join returns matching records and unmatched records from both tables. Fields without a matching record are populated with null values.

Table A FULL OUTER JOIN Table B
Keeps matched and unmatched rows from both tables.

Example: Compare customer records from two systems and retain records that appear in only one system.

Important Join Considerations

• Duplicate keys can create multiple matching rows and increase the row count.
• Unmatched records may be excluded or retained depending on the join type.
• Joining tables at different levels of detail can duplicate measures.
• Null values can appear when a join retains unmatched records.
• Always validate row counts and aggregated measures after creating a join.

7. Understanding Unions in the Physical Layer

A union appends rows from one table to another. It is appropriate when tables contain similar types of records and compatible columns.

Example

A company maintains sales records in separate January and February tables. Both tables contain Order ID, Product, and Sales. A union combines the records into one table.

January Sales + February Sales
             |
             v
       Combined Sales
       (Rows appended)

When to Use a Union

• Combining monthly or quarterly transaction files.
• Appending regional records that use the same schema.
• Consolidating historical data stored across worksheets.
• Combining similarly structured datasets into a single logical table.

When Not to Use a Union

Do not use a union when the goal is to add descriptive columns by matching records. For example, combining Orders with Customer Details using Customer ID generally requires a relationship or join, not a union.

8. How the Two Layers Work Together

The logical and physical layers are complementary parts of the same data model. They are not alternative modes that must be used separately.

A logical table may contain one physical table or multiple physical tables joined or unioned together. At the logical layer, that table can then be related to other logical tables.

Example Data Model

Imagine a retail business has two sales files, a customer table, and a product table.

LOGICAL LAYER
+-------------------+     +-------------------+
| Sales             |-----| Customers         |
| Logical Table     |     | Logical Table     |
+-------------------+     +-------------------+
          |
          |
          | Relationship
          |
+-------------------+
| Products          |
| Logical Table     |
+-------------------+

PHYSICAL LAYER INSIDE SALES
+-------------------+
| January Sales     |
+-------------------+
          |
       UNION
          |
+-------------------+
| February Sales    |
+-------------------+

The Sales logical table contains the combined
records from January and February.

In this example, January and February sales are unioned inside the physical layer of the Sales logical table. The Sales logical table is then related to Customers and Products at the logical layer.

Benefits of Using Both Layers

• Consolidate similar records with unions.
• Join physical tables when a single combined table is required.
• Relate logical tables to preserve their distinct structures.
• Build data models that support multiple business questions.
• Reduce the need to create separate data sources for every analytical requirement.

9. Level of Detail in Logical and Physical Layers

Level of detail refers to what one row represents in a dataset. Understanding the level of detail is essential for accurate calculations in Tableau.

Example: Orders and Order Items

Suppose the Orders table contains one row per order, while the Order Items table contains one row per product line within an order.

Orders Table

Order Items Table

The Orders table has one row per order. The Order Items table has one row per order item.

What Happens If These Tables Are Joined?

If Orders is joined to Order Items using Order ID, the order total for order 501 appears on two rows because that order has two items.

Summing Order Total from this joined table produces 2500, although the original order totals add up to 1500. This happens because the order-level measure is repeated for each matching item row.

How Relationships Can Help

When the tables are modeled as separate logical tables connected through a relationship, Tableau can query them at their respective levels of detail according to the fields used in the visualization. This can help avoid the unintended duplication illustrated above.

Important: Relationships do not automatically correct every data-quality problem. Incorrect relationship keys, duplicated records within a source, and inaccurate data-model assumptions can still lead to unexpected results.

10. Step-by-Step: Working with Logical and Physical Layers

Part A: Create a Data Connection

1. Open Tableau Desktop or a supported Tableau authoring environment.
2. Choose a connection type, such as Microsoft Excel or a supported database.
3. Select the relevant file or database.
4. Open the Data Source page.
5. Inspect the available tables and fields before building the model.

Part B: Build the Logical Layer

1. Drag the primary table, such as Orders, onto the top-level canvas.
2. Drag another table, such as Customers, onto the canvas.
3. Allow Tableau to detect a relationship if possible.
4. Select the relationship and verify the matching fields.
5. Repeat the process for additional related tables.
6. Check the relationship configuration against the actual data.

Part C: Open the Physical Layer

1. Double-click a logical table, such as Orders.
2. Tableau opens the physical-layer Join/Union canvas.
3. Inspect the physical tables contained within the logical table.
4. Drag another compatible physical table into the canvas if needed.
5. Create a join or union based on the requirement.
6. Verify the combined rows, columns, and field types.
7. Return to the logical-layer canvas when finished.

Part D: Validate the Data Model

1. Open a worksheet.
2. Use fields from the relevant tables to create a basic visualization.
3. Compare totals against the original source data.
4. Check for duplicate records and unexpected null values.
5. Verify that measures are aggregated at the intended level of detail.
6. Save the workbook when the results are correct.

11. Practical Example: Retail Sales Data Model

Business scenario: A retail company wants to analyze revenue by product, customer, region, and month. The data is stored in separate tables.

Source Tables

• Orders: Order ID, Customer ID, Product ID, Order Date, Sales.
• Customers: Customer ID, Customer Name, City, Region.
• Products: Product ID, Product Name, Category.
• Targets: Region, Target Month, Sales Target.

Step 1: Connect the Data

Connect Tableau to the data source containing the four tables.

Step 2: Build the Logical Model

Place Orders, Customers, Products, and Targets on the logical-layer canvas. Define relationships using appropriate matching fields, such as Customer ID, Product ID, and a suitable combination of Region and month for target analysis.

Ensure that the target data has a clearly defined grain. For example, if Targets contains one record per region per month, the relationship should reflect both region and month where necessary.

Step 3: Use the Physical Layer When Required

If the Orders data is divided into multiple monthly tables with compatible structures, open the Orders logical table and union those tables in the physical layer. If an additional physical table needs to be merged with Orders, create an appropriate join only after confirming the matching keys and expected row cardinality.

Step 4: Create a Sales Analysis Worksheet

1. Drag Order Date to Columns.
2. Drag Sales to Rows.
3. Choose an appropriate date level, such as Month.
4. Confirm that Sales is aggregated as SUM(Sales).
5. Use Product Category on Color or Filters if appropriate.

Step 5: Create a Regional Performance Worksheet

1. Drag Region to Rows.
2. Drag Sales to Columns.
3. Sort the bars in descending order.
4. Add labels and tooltips to show relevant values.

Step 6: Compare Sales with Targets

Use the relationship between the sales and target data to build a suitable comparison. Ensure that both measures are aggregated at a compatible level of detail before calculating performance against target.

A basic percentage-of-target calculation is:

SUM([Sales]) / SUM([Sales Target])

Format the result as a percentage. The calculation is appropriate only when the sales and target values are correctly aligned at the intended analytical level. If the target table contains repeated target values or unmatched periods, revise the data model before relying on the result.

Step 7: Build the Dashboard

Combine sales trends, regional performance, product category analysis, and target performance into one dashboard. Add clear titles, filters, tooltips, and consistent formatting.

Expected Business Benefits

• Monitor revenue trends over time.
• Identify high-performing regions and products.
• Compare actual performance with sales targets.
• Reduce errors caused by inappropriate joins.
• Support data-driven business decisions.

12. Logical Layer vs Physical Layer: When Should You Use Each?

13. Common Mistakes and How to Avoid Them

Mistake 1: Treating Relationships and Joins as Identical

Problem: A relationship and a join may connect tables using common fields, but they operate differently in the data model.

Solution: Use relationships to connect logical tables while preserving their distinct structures. Use joins when you specifically need to combine physical tables into one table.

Mistake 2: Joining Tables Without Checking the Level of Detail

Problem: A one-to-many join may repeat measure values and inflate totals.

Solution: Check the uniqueness of join keys, inspect the resulting row count, and compare measures with the source data.

Mistake 3: Using the Wrong Matching Fields

Problem: Relationships or joins based on incorrect fields may connect unrelated records.

Solution: Confirm the business meaning and data types of each matching field before defining the connection.

Mistake 4: Assuming Relationships Eliminate Duplicate Source Data

Problem: Duplicate records that already exist in a source table can still affect analysis.

Solution: Clean the source data and validate key uniqueness where expected.

Mistake 5: Using a Join Instead of a Union

Problem: Monthly sales data is joined horizontally when the objective is to append transactions.

Solution: Use a union for compatible monthly tables that need to be combined vertically.

Mistake 6: Ignoring Null Values

Problem: Unmatched records or mismatched fields produce unexpected nulls.

Solution: Investigate the cause and determine whether the values represent missing data, unmatched records, or information that is not applicable.

Mistake 7: Misunderstanding the Data Source Canvas

Problem: A user attempts to create a join in the logical-layer canvas.

Solution: Remember that the top-level canvas is the logical layer. Double-click a logical table to open the physical layer for joins and unions.

14. Best Practices for Data Modeling in Tableau

• Understand the source tables before building a data model.
• Document what one row represents in each table.
• Prefer relationships when independent tables need to retain their native levels of detail.
• Use physical joins only when their row-level consequences are understood.
• Use unions to consolidate compatible records.
• Verify field names, data types, keys, and relationship conditions.
• Review relationship cardinality and referential-integrity assumptions carefully.
• Validate totals against the source data before publishing dashboards.
• Check how dimensions and measures from multiple tables affect aggregation.
• Use meaningful names for logical tables and fields.
• Keep the data model as simple as the business requirements allow.
• Use Tableau Prep or other data-preparation tools when complex cleaning is required.
• Test the model using several business questions rather than a single worksheet.

15. Practice Exercise

Scenario: You are building a sales dashboard using three tables: Orders, Customers, and Products.

Orders

Customers

Products

Tasks

1. Connect Tableau to the source data.
2. Create the logical-layer relationships between Orders and Customers and between Orders and Products.
3. Verify the relationship fields.
4. Create a worksheet showing sales by customer.
5. Create a worksheet showing sales by product.
6. Compare the aggregated results against the original Orders table.
7. Open a logical table's physical layer and inspect how a join would change the data structure.
8. Explain why a union would not be the appropriate operation for adding customer names to the Orders table.
9. Build a dashboard containing the customer and product sales worksheets.

Expected Sales by Customer

Expected Sales by Product

16. Interview Questions and Answers

Q1. What are the logical and physical layers in Tableau?

Answer: The logical layer is the top-level data-model canvas where logical tables are connected through relationships. The physical layer is inside each logical table and is used to combine physical tables through joins and unions.

Q2. What is a logical table?

Answer: A logical table is a table represented on the logical-layer canvas. It can contain a single physical table or multiple physical tables combined through joins or unions.

Q3. What is a physical table?

Answer: A physical table is an individual source table within a logical table's physical-layer structure. Physical tables can be joined or unioned with other physical tables.

Q4. How do you access the physical layer?

Answer: Double-click a logical table on the Data Source page to open its physical-layer Join/Union canvas.

Q5. What is the main difference between a relationship and a join?

Answer: A relationship connects logical tables while preserving their separate structures. A join combines physical tables into a single physical table based on matching conditions.

Q6. Why are relationships useful in Tableau?

Answer: Relationships allow tables to maintain their native levels of detail and enable Tableau to determine query behavior based on the fields used in the visualization.

Q7. Can a logical table contain multiple physical tables?

Answer: Yes. A logical table can contain multiple physical tables joined or unioned together in the physical layer.

Q8. What is the purpose of a union?

Answer: A union appends rows from compatible tables, such as monthly sales tables, into one combined table.

Q9. What is the difference between an inner join and a left join?

Answer: An inner join returns matching records from both tables. A left join retains every row from the left table and includes matching right-table values where available.

Q10. What is meant by level of detail?

Answer: Level of detail describes what one row represents, such as one order, one customer, or one order item. It is important for choosing suitable data-modeling techniques and avoiding incorrect aggregations.

Q11. Can a join cause duplicated measure values?

Answer: Yes. When one record matches multiple records in another table, values from the first table can be repeated in the joined result. This can inflate totals if the measure is aggregated without accounting for the changed level of detail.

Q12. Which layer should be used to combine monthly sales files?

Answer: Use a union in the physical layer when the monthly tables contain compatible fields and their records need to be appended.

Q13. Does a relationship physically merge tables?

Answer: No. A relationship keeps logical tables distinct in the data model. Tableau uses the relationship to query relevant data during analysis.

Q14. Which layer was available in older Tableau data models?

Answer: Before Tableau 2020.2, the data model used a single physical layer for joins and unions. Tableau 2020.2 introduced the logical layer for relationships above the physical layer.

Q15. How can you validate a data model?

Answer: Compare totals and row counts with source data, inspect null values, check key uniqueness, verify relationship fields, and test measures at different levels of detail.

17. Quick Revision Notes

• Logical layer: The top-level Relationships canvas.
• Physical layer: The Join/Union canvas inside a logical table.
• Logical tables: Tables displayed in the logical layer.
• Physical tables: Individual tables inside the physical layer.
• Relationships: Connect logical tables without merging them into one physical table.
• Joins: Combine physical tables horizontally using matching conditions.
• Unions: Append records vertically.
• Level of detail: Describes what one row represents in a table.
• Join risk: Duplicate matches can inflate measures, and unmatched records may be filtered or retained depending on the join type.
• Relationship advantage: Supports flexible analysis across tables with different levels of detail.
• Access physical layer: Double-click a logical table.
• Best practice: Validate the model and important calculations before sharing a dashboard.

18. Conclusion

The logical and physical layers are fundamental parts of Tableau's data model. The logical layer organizes logical tables and connects them through relationships, while the physical layer combines physical tables through joins and unions. Both layers work together to provide a flexible structure for analyzing data from multiple tables.

Understanding these layers helps Tableau developers select the appropriate data-combination method, preserve meaningful levels of detail, avoid inflated totals, and create more reliable dashboards. Always consider the structure of the source data, the intended level of detail, and the business question before choosing relationships, joins, or unions.

Continue Learning: Explore Tableau Training | Register for a Tableau Course Demo

19. Official Tableau Learning Resources

• The Tableau Data Model: Logical and Physical Layers
• Create and Define Relationships
• Questions About Relationships, the Data Model, and Data Sources
• Join Your Data in Tableau