2.3 Joins, Aggregations & Output Destinations in Prep

2.3 Joins, Aggregations & Output Destinations in Prep

Once raw datasets have been cleansed, normalized, and restructured, they must often be integrated with other enterprise data sources, summarized to appropriate business levels of detail, and routed to production environments. In Tableau Prep Builder, data integration is governed by the Join step, granularity management is handled by the Aggregate step, and dissemination is configured through the Output step and Tableau Prep Conductor.

Understanding the distinction between physical joins in Prep versus logical relationships in Desktop, diagnosing join drop-offs, managing granularity mismatches, and configuring enterprise output targets are critical competencies for the Salesforce Certified Tableau Data Analyst examination.

---

Joins in Tableau Prep Builder

While Tableau Desktop 2020.2+ emphasizes logical relationships (the "noodle" layer) that remain flexible and delay join execution until visualization time, Tableau Prep Builder executes physical joins. Physical joins materialize a concrete, merged table directly within the pipeline flow.

The Interactive Venn Diagram Interface

When two branches are connected into a Join step, Prep presents an interactive Venn diagram control. Analysts configure the join type simply by clicking the diagram segments:

                 TABLEAU PREP VENN DIAGRAM JOIN TYPES

     (  [Left]  ( [Intersection] )  [Right]  )

  1. Inner Join:           Only the intersecting center shaded.
  2. Left Outer Join:      Left shaded + intersecting center shaded.
  3. Right Outer Join:     Right shaded + intersecting center shaded.
  4. Full Outer Join:      Left shaded + center shaded + right shaded.
  5. Left Only (Anti):     Left shaded ONLY (center & right clear).
  6. Right Only (Anti):    Right shaded ONLY (center & left clear).
  7. Not Both (Symmetric): Left shaded + Right shaded (center clear).

Detailed Join Type Reference

Join Clauses & Operators

By default, Prep inspects column headers and data types to suggest equality join conditions (e.g., [Customer_ID] = [Customer_ID]). Analysts can add multiple join clauses using + and choose non-equality operators (!=, <, <=, >, >=). Non-equality joins are frequently utilized in financial and HR analytics, such as matching employee hire dates against historical salary grade effective date ranges ([Hire_Date] >= [Start_Date] AND [Hire_Date] <= [End_Date]).

---

Join Profiling & Join Result Diagnostics

A signature feature of Tableau Prep Builder is the Join Result summary. Located directly below the Venn diagram, this pane provides instant visual diagnostics on the integrity of the join:

• Summary Bar Charts: Displays exact row counts and percentages for:
  • Included Rows: Rows successfully passing the join conditions into the output.
  • Excluded Rows (Left): Rows from the left dataset that found no match and were dropped.
  • Excluded Rows (Right): Rows from the right dataset that found no match and were dropped.
• Color Coding: Prep assigns distinct color themes (typically blue for left, orange for right) to visually trace the provenance of every column and value in the Profile cards.

Spotting Join Duplication (Cartesian Explosion / Fan-Out)

A common data quality trap occurs when joining tables with mismatched cardinalities (e.g., joining a Customers table to an Orders table on Customer_ID, where a customer has multiple orders). If the left table contains duplicates on the join key, rows multiply, causing measure values (such as account balances or budgets) to inflate.

Prep highlights this immediately in the Join Result summary: if the count of joined rows significantly exceeds the row count of the input tables, an unintended 1-to-many or many-to-many fan-out has occurred.

---

Managing Granularity with the Aggregate Step

To resolve join duplication and align datasets with differing grains, analysts insert an Aggregate step prior to joining.

+-----------------------------------------------------------------------------------+
|                             AGGREGATE STEP INTERFACE                              |
+------------------------------------+----------------------------------------------+
| Grouped Fields (Dimensions / Grain) | Aggregated Fields (Measures / Summaries)     |
| - Department ID                    | - Sales Amount  -> SUM(Sales Amount)         |
| - Order Year                       | - Order ID      -> COUNTD(Order ID)          |
| - Order Month                      | - Discount      -> AVG(Discount)             |
+------------------------------------+----------------------------------------------+

Mechanics of the Aggregate Step

1. Grouped Fields: Dimensional fields dragged into this zone define the new level of detail (granularity) of the output. All unique combinations of values across these fields form the individual rows of the aggregated table.
2. Aggregated Fields: Measures or dimensions dragged into this zone are evaluated using a specified mathematical aggregation function:
   • Numeric Measures: SUM, AVG, MEDIAN, MIN, MAX, STDEV, VAR.
   • Distinct & Non-Distinct Counts: COUNT, COUNTD (Count Distinct).

Practical Granularity Alignment Scenario

Suppose you must join an Order_Line_Items table (grain: individual product SKU per order) with a Monthly_Targets table (grain: department per calendar month) on Department ID. Joining line items directly to monthly targets causes target numbers to duplicate across every line item, artificially multiplying target figures by hundreds of times.

One solution: Insert an Aggregate step after Order_Line_Items. Group by Department ID and Order Month, aggregate Sales Amount with SUM(), and join to Monthly_Targets on both fields. Confirm that Monthly Targets is unique at that key; only then is the join one-to-one and safe from fan-out. If line-item detail is still required, preserve it in another branch or use a different model.

---

Output Destinations & Step Options

An Output step writes the prepared result, and a flow can branch to more than one output. Tableau Prep Builder provides three broad output categories:

+-----------------------------------------------------------------------------------+
|                         OUTPUT STEP DESTINATION TYPES                             |
+-----------------------+-----------------------------------------------------------+
| 1. Save to File       | - Tableau Data Extract (.hyper)                           |
|                       | - Comma-Separated Values (.csv)                           |
|                       | - Microsoft Excel Spreadsheet (.xlsx)                     |
+-----------------------+-----------------------------------------------------------+
| 2. Publish as Data    | Publishes a data source to Tableau Server or Tableau Cloud |
|    Source             | subject to project permissions and authentication.          |
+-----------------------+-----------------------------------------------------------+
| 3. Write to Database  | Writes directly to external tables in Snowflake, Redshift,|
|    Table              | PostgreSQL, SQL Server, BigQuery, Oracle, or MySQL.       |
+-----------------------+-----------------------------------------------------------+

Detailed Output Destination Capabilities

1. Save to File (.hyper): Generates a Tableau Hyper extract file for efficient Tableau analysis and portable local output. Hyper is Tableau's extract technology; do not assume the entire extract permanently resides in RAM.
2. Save to File (.csv / .xlsx): Writes CSV or, in supported Prep Builder workflows, an Excel worksheet. Excel output can create, append, or replace worksheet data under its documented constraints; it is not a generic promise to preserve workbook formulas or formatting.
3. Publish as a Data Source: Publishes the curated dataset directly to a specified Project folder on Tableau Server or Tableau Cloud. Key configuration options include:
   • Project Selection: Specifying the governance folder.
   • Name & Description: Naming the published asset.
   • Credentials: Authentication appropriate to the destination and later refreshes.
   • Governance follow-up: Certification is a separate action performed by an authorized user after the output is reviewed; publishing alone does not certify it.
4. Write to Database (External Table Output): Writes transformed rows to a connector and destination that support Prep output. Available targets and write modes vary by version and environment.

Database Write Options

For supported database outputs, distinguish these operations and confirm connector-specific availability:

During table mapping, Prep provides a visual interface allowing analysts to map Prep flow field names to destination database column names, automatically warning if data types are incompatible.

---

Enterprise Flow Orchestration: Tableau Prep Conductor

While Tableau Prep Builder provides interactive desktop design, enterprise organizations deploy Tableau Prep Conductor—a service component of the Tableau Data Management add-on—to automate, govern, and scale data preparation.

Key Capabilities of Prep Conductor

• Flow Publishing: Analysts publish flow definitions (.tfl) or packaged flows including file extracts (.tflx) directly to Tableau Server or Tableau Cloud.
• Scheduled Execution: Flows can be scheduled to run at specific intervals (hourly, daily, weekly) or triggered programmatically via REST API calls.
• Linked Tasks: Where supported, orchestrates sequential server-side flow tasks so a downstream flow starts after its prerequisite succeeds.
• Monitoring and notifications: Administrators can inspect flow-task status and failures and configure the notification options supported by the deployment.
• Governance response: A failed flow should trigger investigation of affected outputs and dependencies. Apply an appropriate data quality warning when users must be told that downstream data is stale; do not assume every failure automatically creates such a warning.

---

Common Exam Traps & Best Practices

• Exam Trap: Overwriting Production Tables via 'Replace Table': In the Output step targeting an external database, selecting "Create Table" on an existing production table drops the table and recreates it. Use Append to table only when each run contains new rows and duplicates are controlled; use Create table when a complete replacement is intended.
• Exam Trap: Joining at Mismatched Granularity: Joining a line-item transaction table directly to a monthly summary table causes metric duplication. A common solution is to insert an Aggregate step to equalize the required grains before the Join step; another model may be appropriate when detail must be preserved.
• Exam Trap: Anti-Joins without SQL: Exam questions frequently test how to find unreturned orders or non-purchasing customers. In Prep, this does not require a complex LEFT JOIN ... WHERE right.key IS NULL query; it is achieved by selecting Left Only on the interactive Venn diagram.
• Flow formats (.tfl vs. .tflx): A .tfl is a flow definition that can reference external files and connections. A packaged .tflx can include eligible local files with the flow for portability. Packaging does not embed every database or cloud source, and credentials still require appropriate handling.