Unions — Create unions (append rows) and validate schema alignment - Tableau

SECTION 1

Historical Context & Motivation

Data rarely arrives in a single, monolithic table. Organizations routinely partition records across multiple files—monthly sales logs, regional inventory snapshots, quarterly survey exports—each sharing an identical or nearly identical schema. The need to vertically concatenate these fragments into a unified dataset predates visual analytics tools by decades, rooted in the relational algebra concept of the UNION operator introduced by E. F. Codd in his foundational 1970 paper on the relational model. Tableau adopted this operator as a first-class data-connection feature to let analysts combine structurally similar tables without writing SQL, but understanding the underlying semantics—especially schema alignment—remains essential for avoiding silent data-quality issues.

1970 • Codd's Relational Model

E. F. Codd publishes "A Relational Model of Data for Large Shared Data Banks," formalizing UNION as one of the fundamental relational algebra operators that combine two union-compatible relations by appending their tuples.

1986 • SQL Standard Adopted

ANSI adopts the first SQL standard (SQL-86), codifying UNION and UNION ALL as keywords. The distinction between duplicate-eliminating UNION and duplicate-preserving UNION ALL becomes a cornerstone of query design.

2003 • Tableau 1.0 Released

Tableau's earliest versions focus on drag-and-drop visualization from single tables. Combining multiple data sources requires manual preprocessing in databases or spreadsheet tools.

2016 • Union Support in Tableau 10.0

Tableau 10.0 introduces native union capabilities in the Data Source pane, allowing users to drag tables onto each other to append rows. Wildcard unions enable pattern-based file matching across directories.

2020+ • Relationships & Prep Enhancements

Tableau Prep Builder adds visual union steps with automatic field-name matching, mismatched-field indicators, and data-type coercion warnings—making schema validation an interactive, exploratory workflow.

The central question this lesson addresses is straightforward yet deceptively nuanced: when you stack rows from two or more tables, how do you guarantee that the resulting dataset is structurally coherent? Mismatched column names, differing data types, and missing fields can produce a union that looks complete but silently corrupts downstream analysis. Mastering union creation and schema validation is therefore a prerequisite for reliable data pipelines in Tableau.

SECTION 2

Core Principles & Definitions

Before creating unions in Tableau, it is important to internalize several foundational concepts that govern how row-appending operations behave. Unlike a join, which combines columns from different tables based on a key, a union combines rows from tables that share a compatible structure. The following principles capture the essential mechanics and potential pitfalls.

Union = Append Rows

A union vertically concatenates two or more tables. If Table A has m rows and Table B has n rows, the unioned result has m + n rows (assuming UNION ALL semantics, which Tableau uses by default).

Schema Alignment

For a valid union, columns must be matched by name (or alias) and compatible data type. Tableau performs automatic name-based matching and adds mismatched fields as separate columns filled with NULLs.

Union-Compatibility

Two relations are union-compatible when they have the same degree (number of columns) and each pair of corresponding attributes shares a compatible domain. Tableau relaxes this by allowing mismatches but flags them visually.

Generated Fields

Tableau appends metadata fields—Sheet (source file/sheet name) and Table Name—to every unioned row, enabling analysts to trace each record back to its origin table for auditing and filtering.

Wildcard Unions

Wildcard unions use pattern matching (e.g., sales_*.csv) to automatically include all files in a directory that match a naming convention, streamlining unions across dozens or hundreds of files.

✦

KEY TAKEAWAY

Think of a union like appending pages to a spreadsheet: if every page has the same column headers in the same order, stacking is trivial. But if one page renames "Revenue" to "Sales" or stores dates as strings instead of date objects, you get a messy stack of misaligned data. Schema validation is the process of checking those headers before you staple the pages together.

SECTION 3

Visual Explanation — Union vs. Join

The most common source of confusion for newcomers is the distinction between unions and joins. The diagram below illustrates how a union appends rows vertically while a join extends columns horizontally. Each operation serves a fundamentally different data-integration purpose, and selecting the wrong one can produce a Cartesian explosion or a table riddled with NULLs.

Left: a union stacks Table A (2 rows) on top of Table B (2 rows), producing 4 rows with the same 3 columns. Appended rows from Table B are shown in cyan. Right: a join on ID merges columns from Table A and Table C side by side, keeping the row count at 2 but expanding to 3 columns.

As the diagram makes clear, choosing between a union and a join depends on the structural relationship between your tables. If two tables describe the same entity with the same attributes but different records (e.g., January sales and February sales), a union is appropriate. If they describe different attributes of the same entities (e.g., customer demographics and customer orders linked by a customer ID), a join is the correct operation. Conflating the two produces either an inflated row count or a sparse, NULL-riddled table.

SECTION 4

How Unions Work — The Schema Alignment Process

When Tableau executes a union, it performs several internal operations that mirror classical relational algebra. Understanding these mechanics allows you to anticipate and debug the mismatches that commonly arise. The process can be decomposed into three phases: field enumeration, name-based matching, and type coercion.

Phase 1 — Field Enumeration

Tableau scans every table in the union and builds a master column list. Formally, if table T_i has column set C_i, the output schema S is the union of all column sets: S = C₁ ∪ C₂ ∪ … ∪ C_k. Any column that exists in one table but not another will appear in the result, with NULL values populating the missing cells.

OUTPUT SCHEMA

S = C₁ ∪ C₂ ∪ … ∪ Cₖ

where S is the result schema, C_i is the column set of table i, and k is the number of tables in the union. The vertical bar count of the result is |S| = |C₁ ∪ C₂ ∪ … ∪ Cₖ|.

Phase 2 — Name-Based Matching

Tableau matches columns across tables using case-insensitive name comparison. If Table A has a column named "Revenue" and Table B has "revenue," Tableau treats them as the same field. However, if Table B instead names it "Sales," Tableau creates two separate columns in the result—"Revenue" (NULL for Table B rows) and "Sales" (NULL for Table A rows). This is where manual field merging becomes necessary: in the Data Source pane, you can right-click mismatched columns and select "Merge Mismatched Fields" to unify them under a single header.

Phase 3 — Type Coercion

When matched columns have differing data types—for instance, one table stores "Price" as an integer and another as a float—Tableau applies implicit type promotion following a widening hierarchy: Boolean → Integer → Float → String. A more serious mismatch, such as a Date column in one table and a String in another, results in the broader type (String) being selected. This can silently degrade analytical functionality—string dates, for example, cannot be used with Tableau's date functions without explicit type conversion. Schema validation should therefore always include checking the resulting data types in the Data Source pane's metadata grid after performing a union.

ROW COUNT IDENTITY

|R| = |T₁| + |T₂| + … + |Tₖ|

The total row count |R| of a UNION ALL result equals the sum of row counts across all k input tables. No deduplication occurs (Tableau uses UNION ALL semantics). If duplicates need to be removed, post-union filtering or LOD expressions are required.

SECTION 5

Schema Validation — Detecting and Resolving Mismatches

Schema validation is the disciplined process of ensuring that all tables in a union share the expected structure before and after the concatenation. Tableau provides several visual cues to facilitate this process, but a systematic approach—especially in production data pipelines—requires understanding the taxonomy of mismatches and their resolution strategies.

The schema validation workflow proceeds through four stages: enumerating fields from each source table, identifying name and type mismatches, resolving them via merging or type correction, and producing a validated union result. Red NULL values indicate fields that exist only in a subset of source tables. The Source column (generated by Tableau as "Table Name") enables provenance tracking.

Mismatch Type	Example	Resolution in Tableau
Column Name	`Amt` vs. `Amount`	Right-click → Merge Mismatched Fields, or rename in Tableau Prep
Data Type	Integer in T1, String in T2	Tableau promotes to broader type (String). Fix by changing type in metadata grid or preprocessing.
Missing Column	"Region" exists only in T3	Column appears in result; rows from other tables receive NULL. Use IFNULL() or ZN() for defaults.
Column Order	Columns in different positions across tables	Tableau matches by name, not position. Column order differences are handled automatically.
Case Variation	`Revenue` vs. `revenue`	Matched automatically (case-insensitive comparison). No action needed.

Common schema mismatches and their resolution strategies in Tableau Desktop and Tableau Prep.

SECTION 6

Worked Example — Unioning Monthly Sales Files

Suppose you are a data analyst at a retail company. The sales team exports monthly transaction data as separate CSV files: sales_jan.csv, sales_feb.csv, and sales_mar.csv. Each file should contain columns for OrderID, Product, Quantity, UnitPrice, and OrderDate. However, an upstream schema change in March renamed "UnitPrice" to "Price" and added a "Channel" column. Your task: union all three files and validate the schema.

Creating and Validating a Multi-File Union in Tableau Desktop

1
Step 1 — Connect to the First File

Open Tableau Desktop and select "Text file" as the connector. Navigate to the directory containing your CSV files and open sales_jan.csv. Tableau displays the file in the Data Source pane with its five columns: OrderID, Product, Quantity, UnitPrice, OrderDate.

Initial table displayed with 5 columns and January data rows.
2
Step 2 — Create the Union via Drag-and-Drop

In the left panel, locate sales_feb.csv and sales_mar.csv. Drag sales_feb.csv directly onto the existing table in the canvas until the "Drag table to union" prompt appears; drop it. Repeat with sales_mar.csv. Alternatively, click "New Union" in the left panel and add all three files. Tableau creates a union icon in the canvas.

Union created with 3 tables. The data preview now shows combined rows from all three files.
3
Step 3 — Inspect the Metadata Grid for Mismatches

Switch to the metadata view (grid icon at the top of the Data Source pane). You will observe 7 columns instead of the expected 6 (5 original + Table Name). The mismatch: "UnitPrice" appears with NULLs for March rows, and "Price" appears with NULLs for January and February rows. Additionally, the "Channel" column is present only for March rows. The "Table Name" field shows the source file for each row.

Identified 2 schema issues: UnitPrice/Price name mismatch, and Channel as a March-only field.
4
Step 4 — Merge Mismatched Fields

Select both "UnitPrice" and "Price" columns in the metadata grid (Ctrl+click), then right-click and choose "Merge Mismatched Fields." Tableau consolidates them into a single column named "UnitPrice" (the first-encountered name is retained). Verify in the data preview that all rows now have a non-NULL value in this column.

UnitPrice and Price merged into a single "UnitPrice" column with no NULLs.
5
Step 5 — Validate Data Types and Handle Extra Columns

Confirm that OrderDate is recognized as a Date type (not String) for all rows by checking the type icon in the metadata grid. For the "Channel" column—which has NULL values for January and February rows—decide whether to keep it (useful for channel-based analysis in March onward) or create a calculated field using IFNULL([Channel], 'Unknown') to assign a default. Finally, verify the total row count equals the sum of rows across all three source files.

Validated union: 6 columns (OrderID, Product, Quantity, UnitPrice, OrderDate, Channel), correct data types, row count = |Jan| + |Feb| + |Mar|.

💡

Wildcard Union Alternative

For scenarios with many identically named files (e.g., sales_*.csv), use a wildcard union. In the New Union dialog, switch to the "Wildcard (automatic)" tab and specify the matching pattern and directory. Tableau will automatically union all matching files, including any added in the future—ideal for automated data refresh workflows.

SECTION 7

Unions Compared — Join, Blend, and Relationship Alternatives

Tableau offers four primary mechanisms for combining data: unions, joins, blends, and relationships. Selecting the correct mechanism depends on the structural relationship between your data sources and the granularity requirements of your analysis. The table below provides a systematic comparison across key dimensions to guide this decision.

Dimension	Union	Join	Blend
Direction	Vertical (adds rows)	Horizontal (adds columns)	Virtual horizontal link
Schema Requirement	Same or compatible columns	Shared key column(s)	Linking field defined per sheet
Data Sources	Same connection only	Same connection only	Cross-connection (e.g., SQL + Excel)
Row Count Impact	Sum of all table row counts	Varies by join type (inner, left, etc.)	Primary source determines row count
Duplicate Handling	UNION ALL — retains duplicates	Can multiply rows (fan-out)	Aggregates secondary source
Best Use Case	Combining partitioned files (monthly logs, regional exports)	Enriching records with attributes from related tables	Combining data from different database systems

Comparative analysis of Tableau data combination methods.

🔀

WHEN TO USE EACH METHOD

Think of data combination methods as construction techniques: a union is like adding more floors to a building (same blueprint, more stories), a join is like adding a wing (extending the footprint), and a blend is like running a skybridge between two separate buildings (connecting independent structures at specific points). Each requires different structural compatibility checks.

SECTION 8

Connection to Advanced Data Engineering

Tableau's union feature provides an accessible entry point into concepts that scale up significantly in production data engineering. Understanding where Tableau unions sit on the continuum from ad-hoc analysis to enterprise ETL pipelines will help you recognize when to graduate beyond the desktop tool.

Feature	Tableau Desktop Union	Production ETL / Data Engineering
Schema Evolution	Manual merge of mismatched fields; no versioning	Schema registries (e.g., Apache Avro, Confluent Schema Registry) with backward/forward compatibility rules
Deduplication	Not built in (UNION ALL); requires post-hoc filtering	SQL UNION (with dedup), or tools like dbt with unique key tests
Data Quality Testing	Visual inspection in metadata grid	Automated assertions (Great Expectations, dbt tests) checking schema, nulls, ranges, uniqueness
Scale	Tens to hundreds of files; in-memory processing	Millions of partitions; distributed engines (Spark, BigQuery) with lazy evaluation
Automation	Wildcard unions with scheduled extract refresh	Orchestration frameworks (Airflow, Prefect) with dependency graphs and retry logic

Tableau unions vs. production-grade data engineering approaches.

For Computer Science students, the concepts introduced here—union compatibility, type coercion hierarchies, and schema validation—map directly onto topics in database theory (relational algebra), type theory (type lattices and widening), and software engineering (contract-based design). Tableau Prep's union step, for instance, is functionally equivalent to a pandas pd.concat() call with axis=0 and join='outer', where column alignment follows the same outer-union logic described in this lesson. Recognizing these parallels strengthens both your Tableau proficiency and your data engineering fundamentals.

SECTION 9

Practice Problems

Question 1: CONCEPTUAL

Explain the fundamental difference between a union and a join in Tableau. In what scenario would using a join instead of a union produce incorrect or misleading results?

Show answer

A union appends rows vertically from tables with compatible schemas, while a join combines columns horizontally based on a shared key. If two tables represent partitions of the same entity (e.g., Q1 and Q2 transactions with identical columns), using a join would either produce a Cartesian product (if no key is specified) or fail to match rows meaningfully (since the records are disjoint). The union preserves all records in a single, taller table, which is the correct semantic interpretation. A join would be appropriate only if the tables describe different attributes of overlapping entities.

Question 2: BASIC CALCULATION

Table A has 1,200 rows and 8 columns. Table B has 850 rows and 8 columns (same column names). Table C has 1,500 rows and 9 columns (8 matching columns plus one extra column called "Status"). After performing a union of all three tables, how many rows and columns will the result contain? What values will the "Status" column hold for rows from Tables A and B?

Show answer

The result will contain 1,200 + 850 + 1,500 = 3,550 rows. The column count follows the set union of all column sets: 8 shared columns + 1 extra column ("Status") = 9 columns. Additionally, Tableau generates metadata fields ("Table Name" and possibly "Sheet"), bringing the total to approximately 10–11 columns depending on the data source type. The "Status" column will contain NULL values for all 2,050 rows originating from Tables A and B, since those tables do not include that field. Analysts should use IFNULL([Status], 'N/A') or a similar expression to handle these NULLs in calculations and visualizations.

Question 3: INTERMEDIATE

You are unioning four CSV files exported from different regional offices. After creating the union, you notice that the metadata grid shows two columns: "Transaction_Date" (Date type) and "Trans_Date" (String type). The first column has NULLs for two regions, and the second has NULLs for the other two. Describe the sequence of steps you would take in Tableau Desktop to resolve this issue and ensure all date values are properly typed.

Show answer

First, recognize that the two columns represent the same semantic field with a name mismatch. Select both "Transaction_Date" and "Trans_Date" in the metadata grid, right-click, and choose "Merge Mismatched Fields." Tableau will unify them under one column name. However, because one source stored dates as strings, the merged column may default to String type (following the widening hierarchy). After merging, click the data type icon for the merged column and manually change it from String (Abc) to Date. Tableau will attempt to parse the string values—if parsing fails for some rows, those cells will show NULL. To diagnose parsing failures, filter on the Table Name field to isolate the problematic region's rows and inspect the date format (e.g., MM/DD/YYYY vs. DD-MM-YYYY). If the format is non-standard, preprocessing with Tableau Prep's clean step or a calculated field using DATEPARSE() may be necessary.

Question 4: APPLIED

A data engineering team stores sensor readings as daily CSV files named sensor_YYYYMMDD.csv in a shared directory. Each file has columns: SensorID (int), Timestamp (datetime), Reading (float), and Unit (string). A new firmware update on 2024-03-01 changed the output format: "Reading" was renamed to "Value" and a new column "Calibrated" (boolean) was added. Design a Tableau data connection strategy using wildcard unions that handles both the pre- and post-update file formats. Specify what schema validation steps are needed.

Show answer

Create a new data source connection pointing to the shared directory and configure a wildcard union with the pattern sensor_*.csv. This will automatically include all daily files. After Tableau loads the union, open the metadata grid and identify the expected mismatches: "Reading" and "Value" will appear as separate columns, and "Calibrated" will be present only for files from 2024-03-01 onward. Merge "Reading" and "Value" using the Merge Mismatched Fields option. Verify that the merged column retains the Float data type. For the "Calibrated" column, create a calculated field: IFNULL([Calibrated], FALSE), under the assumption that pre-update sensors were uncalibrated. Validate the union by checking that the total row count matches the sum of rows across all files (verifiable via a COUNT on the Table Name field). Additionally, create a scatter plot of Reading vs. Timestamp colored by Table Name to visually detect any discontinuities around the firmware change date. Schedule the extract to refresh daily so new files are automatically incorporated.

Question 5: CRITICAL THINKING

Tableau's union operation uses UNION ALL semantics (no deduplication). Suppose two source tables contain overlapping records—rows that are identical across all columns. Propose two different strategies for detecting and removing these duplicates after the union. For each strategy, analyze its computational complexity and discuss under what data conditions it might fail or produce incorrect results.

Show answer

Strategy 1: Use a LOD (Level of Detail) expression combined with a row-numbering approach. Create a calculated field like {FIXED [OrderID], [Product], [Date]: MIN([Table Name])} and filter to keep only rows where [Table Name] equals this minimum. This has O(n log n) complexity due to the grouping operation and works well when a natural composite key exists. It fails when rows are truly identical across all fields including the Table Name (i.e., duplicates within a single source table), since the MIN filter cannot distinguish them. Strategy 2: Export the unioned data to a Tableau Prep flow and use the "Remove Duplicates" clean step, which performs exact-row comparison. This requires O(n × m) time where m is the number of columns (for hash computation) plus O(n) for hash-table-based deduplication. This approach correctly handles all-column duplicates but may incorrectly merge records that are legitimately identical (e.g., two separate $10 purchases of the same product on the same date by the same customer). In such cases, a surrogate key or row identifier from the source system is needed to distinguish true duplicates from legitimate repeated events. The choice between strategies depends on whether a reliable primary key exists and whether the analysis can tolerate false-positive duplicate removal.

SUMMARY

Lesson Summary

Summary

A union in Tableau appends rows from two or more tables that share a compatible structure, producing a result whose row count equals the sum of all source row counts (UNION ALL semantics). Unlike a join, which extends columns horizontally via a key, unions extend rows vertically. Tableau supports both manual unions (drag-and-drop in the Data Source pane) and wildcard unions (pattern-based file matching for scalable, automated ingestion).

Schema validation is the critical step that distinguishes a reliable union from a corrupted one. It involves checking for column name mismatches (resolved via Merge Mismatched Fields), data type inconsistencies (resolved via type coercion or manual type changes), and extra or missing columns (handled via NULL-aware expressions). Mastering these techniques prepares you for production-grade data engineering workflows using SQL, pandas, and orchestration tools like dbt and Airflow.