Skip to main content

UnionTransformer

Overview​

Transforms Tableau Union nodes to SQL UNION ALL statements. Combines multiple inputs with automatic NULL padding for mismatched fields.

Node Type: .v1.SimpleUnion / .v2018_2_3.SuperUnion Output: SQL UNION ALL

Capabilities​

  • Combines 2 or more input datasets
  • UNION ALL (preserves duplicates)
  • Automatic NULL padding for missing fields
  • Handles mismatched schemas

Tableau Node Example​

Input (Tableau format)​

{
  "id": "a02f726a-5afb-4188-a02c-178d5555cc6a",
  "name": "Union Sales Sources",
  "node_type": ".v2018_2_3.SuperUnion",
  "base_type": "superNode",
  "actionNode": {
    "nodeType": ".v1.SimpleUnion",
    "namespaceFieldMappings": [
      {
        "fieldMappings": {},
        "namespaceName": "Union-Namespace-1"
      },
      {
        "fieldMappings": {},
        "namespaceName": "Union-Namespace-2"
      },
      {
        "fieldMappings": {},
        "namespaceName": "Union-Namespace-3"
      },
      {
        "fieldMappings": {},
        "namespaceName": "Union-Namespace-4"
      },
      {
        "fieldMappings": {},
        "namespaceName": "Union-Namespace-5"
      }
    ]
  }
}

Input Schemas​

Input 1 - 16 fields:

[
  {"name": "year", "type": "integer"},
  {"name": "month", "type": "integer"},
  {"name": "city", "type": "string"},
  {"name": "customer_name", "type": "string"},
  {"name": "sales_amount", "type": "real"}
]

Input 2 - 21 fields (has additional fields):

[
  {"name": "year", "type": "integer"},
  {"name": "month", "type": "integer"},
  {"name": "city", "type": "string"},
  {"name": "customer_name", "type": "string"},
  {"name": "sales_amount", "type": "real"},
  {"name": "material_hier_4_name", "type": "string"},
  {"name": "standard_cost", "type": "real"},
  {"name": "units", "type": "integer"}
]

Input 3 - 18 fields (has different additional fields):

[
  {"name": "year", "type": "integer"},
  {"name": "month", "type": "integer"},
  {"name": "city", "type": "string"},
  {"name": "customer_name", "type": "string"},
  {"name": "sales_amount", "type": "real"},
  {"name": "number_of_rows_aggregated", "type": "integer"},
  {"name": "adjustment_factor", "type": "real"}
]

Generated SQL Output​

SELECT
  year,
  month,
  city,
  customer_name,
  customer_number,
  division,
  invoice_date,
  material_description,
  material_number,
  order_type_description,
  region_manager_number,
  sales_region_manager_name,
  sales_rep_name,
  sales_rep_number,
  state_regioncode,
  sales_amount,
  NULL AS material_hier_4_name,
  NULL AS material_hier_4_number,
  NULL AS standard_cost,
  NULL AS units,
  NULL AS units_eaches,
  NULL AS number_of_rows_aggregated,
  NULL AS adjustment_factor
FROM cash_basis_V0S8

UNION ALL

SELECT
  year,
  month,
  city,
  customer_name,
  customer_number,
  division,
  invoice_date,
  material_description,
  material_number,
  order_type_description,
  region_manager_number,
  sales_region_manager_name,
  sales_rep_name,
  sales_rep_number,
  state_regioncode,
  sales_amount,
  material_hier_4_name,
  material_hier_4_number,
  standard_cost,
  units,
  units_eaches,
  NULL AS number_of_rows_aggregated,
  NULL AS adjustment_factor
FROM cut_off_V0S12

UNION ALL

SELECT
  year,
  month,
  city,
  customer_name,
  customer_number,
  division,
  invoice_date,
  material_description,
  material_number,
  order_type_description,
  region_manager_number,
  sales_region_manager_name,
  sales_rep_name,
  sales_rep_number,
  state_regioncode,
  sales_amount,
  NULL AS material_hier_4_name,
  NULL AS material_hier_4_number,
  NULL AS standard_cost,
  NULL AS units,
  NULL AS units_eaches,
  number_of_rows_aggregated,
  adjustment_factor
FROM adjustments_V0S15

UNION ALL

SELECT
  year,
  month,
  city,
  customer_name,
  customer_number,
  division,
  invoice_date,
  material_description,
  material_number,
  order_type_description,
  NULL AS region_manager_number,
  NULL AS sales_region_manager_name,
  NULL AS sales_rep_name,
  NULL AS sales_rep_number,
  state_regioncode,
  sales_amount,
  NULL AS material_hier_4_name,
  NULL AS material_hier_4_number,
  NULL AS standard_cost,
  NULL AS units,
  NULL AS units_eaches,
  NULL AS number_of_rows_aggregated,
  adjustment_factor
FROM clean_4_V0S18

UNION ALL

SELECT
  year,
  month,
  city,
  customer_name,
  customer_number,
  division,
  invoice_date,
  material_description,
  material_number,
  order_type_description,
  region_manager_number,
  sales_region_manager_name,
  sales_rep_name,
  sales_rep_number,
  state_regioncode,
  sales_amount,
  NULL AS material_hier_4_name,
  NULL AS material_hier_4_number,
  NULL AS standard_cost,
  NULL AS units,
  NULL AS units_eaches,
  NULL AS number_of_rows_aggregated,
  NULL AS adjustment_factor
FROM clean_5_V0S22

How It Works​

1. Collect All Unique Fields​

Scans all input schemas to build complete field list.

2. Generate NULL Padding​

For each input, adds NULL AS field_name for fields not present in that input.

3. Combine with UNION ALL​

Stacks all SELECT statements with UNION ALL (preserves all rows including duplicates).

Union Strategy in Tableau Prep​

⚠️ Important: Use Positional Union (Not By Name)​

When configuring Union nodes in Tableau Prep, you should prefer Positional union instead of By Name union.

Reason: Snowflake's UNION ALL BY NAME syntax doesn't work properly in certain scenarios:

  • Complex nested queries
  • Large numbers of fields
  • Type mismatches across inputs

The converter generates standard positional UNION ALL with explicit NULL padding, which is more reliable.

Limitations​

  • ⚠️ Minimum 2 inputs - Union requires at least 2 input datasets
  • ⚠️ Type compatibility required - Matching field names must have compatible types
  • ⚠️ UNION ALL only - Does not remove duplicates
  • ⚠️ No ORDER BY - Output order is not guaranteed
  • AggregateTransformer - For grouping/aggregating unioned data
  • ContainerTransformer - For filtering or transforming before/after union
  • JoinTransformer - For combining datasets horizontally (not vertically)