Jaco van der Laan

Keep UNION ALL Pure (Separation of Concerns)

11/09/2025  ·  Data Engineering

‹ ›

We want a UNION ALL to be “pure”: it should only concatenate already-prepared, schema-aligned datasets.
All typing, cleaning, filtering, deduping, and enrichment (including lineage flags) must happen before the union.
So a UNION ALL should only union data. No casts, no filters, no expressions, no new columns inside the UNION ALL block.
Do all transformations in preceding, per-source CTEs.

Why

  • Separation of concerns: Each step has a single responsibility: prepare per source → combine sources → consume combined.
  • Testability: You can SELECT * from each “prepared_*” CTE to validate transformations independently.
  • Consistency: Every branch is prepared to the identical schema before union—fewer mismatches and silent coercions.
  • Planner clarity: Filters/partitions stay close to their source scans; the union is straightforward.

Anti-pattern (transforming inside the union)

-- ❌ Avoid: casting, filtering, and literals inside the union
WITH combined_orders AS (
SELECT order_id, customer_id, CAST(order_ts AS TIMESTAMP) AS order_ts, revenue::NUMERIC AS revenue, 'orders_2025' AS _src
FROM bronze.orders_2025
WHERE order_ts >= DATE '2025-01-01'
UNION ALL

SELECT order_id, customer_id, CAST(order_ts AS TIMESTAMP), revenue::NUMERIC, 'legacy_orders'FROM bronze.legacy_ordersWHERE order_ts >= DATE '2025-01-01')
SELECT * FROM combined_orders;

Best practice (pure union; all prep done earlier)

WITH orders_2025_prepared AS (
SELECTorder_id,customer_id,CAST(order_ts AS TIMESTAMP) AS order_ts,CAST(revenue AS NUMERIC) AS revenue,'orders_2025' AS _srcFROM bronze.orders_2025WHERE order_ts >= DATE '2025-01-01'),legacy_orders_prepared AS (SELECTorder_id,customer_id,CAST(order_ts AS TIMESTAMP) AS order_ts,CAST(revenue AS NUMERIC) AS revenue,'legacy_orders' AS _srcFROM bronze.legacy_ordersWHERE order_ts >= DATE '2025-01-01'),
combined_orders AS (-- ✅ Pure union: no casts, no filters, no expressionsSELECT order_id, customer_id, order_ts, revenue, _src FROM orders_2025_preparedUNION ALLSELECT order_id, customer_id, order_ts, revenue, _src FROM legacy_orders_prepared)
SELECT customer_id, SUM(revenue) AS total_revFROM combined_ordersGROUP BY customer_id;

Three-way union (events) with pure union

WITH web_events_prepared AS (SELECT user_id, CAST(event_ts AS TIMESTAMP) AS event_ts, event_name, 'web' AS _srcFROM raw.web_eventsWHERE event_date >= CURRENT_DATE - 30),app_events_prepared AS (SELECT user_id, CAST(event_ts AS TIMESTAMP) AS event_ts, event_name, 'app' AS _srcFROM raw.app_eventsWHERE event_date >= CURRENT_DATE - 30),pos_events_prepared AS (SELECT user_id, CAST(event_ts AS TIMESTAMP) AS event_ts, event_name, 'pos' AS _srcFROM raw.pos_eventsWHERE event_date >= CURRENT_DATE - 30),combined_events AS (SELECT user_id, event_ts, event_name, _src FROM web_events_preparedUNION ALLSELECT user_id, event_ts, event_name, _src FROM app_events_preparedUNION ALLSELECT user_id, event_ts, event_name, _src FROM pos_events_prepared),ranked AS (SELECT*,ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_ts DESC) AS rnFROM combined_events)
SELECT user_id, event_ts, event_name, _srcFROM rankedWHERE rn <= 5;

Schema drift normalization before union (currency example)

WITH payments_v2_prepared AS (SELECTpayment_id,customer_id,(amount_cents / 100.0)::NUMERIC AS amount,currency,CAST(settled_at AS TIMESTAMP) AS settled_at,'v2' AS _srcFROM bronze.payments_v2WHERE settled_at::DATE >= CURRENT_DATE - 90),payments_v1_prepared AS (SELECTpayment_id,customer_id,CAST(amount AS NUMERIC) AS amount,'USD' AS currency,CAST(settled_at AS TIMESTAMP) AS settled_at,'v1' AS _srcFROM bronze.payments_v1WHERE settled_at::DATE >= CURRENT_DATE - 90),
combined_payments AS (SELECT payment_id, customer_id, amount, currency, settled_at, _src FROM payments_v2_preparedUNION ALLSELECT payment_id, customer_id, amount, currency, settled_at, _src FROM payments_v1_prepared)SELECT customer_id, SUM(amount) AS amount_90dFROM combined_paymentsGROUP BY customer_id;

Dedupe first, then prepare, then pure union

WITH v1_dedup AS (SELECT *FROM (SELECT *,ROW_NUMBER() OVER (PARTITION BY payment_id ORDER BY _ingested_at DESC) AS rnFROM bronze.payments_v1) dWHERE rn = 1),v2_dedup AS (SELECT *FROM (SELECT *,ROW_NUMBER() OVER (PARTITION BY payment_id ORDER BY _ingested_at DESC) AS rnFROM bronze.payments_v2) dWHERE rn = 1),v1_prepared AS (SELECTpayment_id,customer_id,CAST(amount AS NUMERIC) AS amount,CAST(settled_at AS TIMESTAMP) AS settled_at,'v1' AS _srcFROM v1_dedupWHERE settled_at >= CURRENT_DATE - INTERVAL '90 days'),v2_prepared AS (SELECTpayment_id,customer_id,(amount_cents/100.0)::NUMERIC AS amount,CAST(settled_at AS TIMESTAMP) AS settled_at,'v2' AS _srcFROM v2_dedupWHERE settled_at >= CURRENT_DATE - INTERVAL '90 days'),combined AS (SELECT payment_id, customer_id, amount, settled_at, _src FROM v1_preparedUNION ALLSELECT payment_id, customer_id, amount, settled_at, _src FROM v2_prepared)SELECT customer_id, COUNT(*) AS n_payments, SUM(amount) AS total_amountFROM combinedGROUP BY customer_id;

Joining after a pure union (final SELECT stays thin)

WITH a_prepared AS (SELECT id, cust_id, CAST(ts AS TIMESTAMP) AS ts, metric::NUMERIC AS metric, 'A' AS _srcFROM src.aWHERE ts >= DATE '2025-01-01'),b_prepared AS (SELECT id, cust_id, CAST(ts AS TIMESTAMP) AS ts, metric::NUMERIC AS metric, 'B' AS _srcFROM src.bWHERE ts >= DATE '2025-01-01'),combined_ab AS (SELECT id, cust_id, ts, metric, _src FROM a_preparedUNION ALLSELECT id, cust_id, ts, metric, _src FROM b_prepared),active_customers AS (SELECT customer_id FROM dim.customer WHERE is_active = TRUE)SELECT c.cust_id, SUM(c.metric) AS metric_sumFROM combined_ab cJOIN active_customers ac ON ac.customer_id = c.cust_idGROUP BY c.cust_id;

Quick checklist for “Pure UNION ALL”

  • Create per-source*_prepared CTEs that:
    • Apply filters/partition predicates
    • Cast to final types
    • Normalize strings/categories
    • Add lineage (_src, _load_at) if needed
    • Dedupe if necessary
  • The union CTE:
    • Uses no expressions (no casts, no literals, no filters)
    • Aligns schemas (same columns, order, and types)
    • Ideally lists columns explicitly (avoid SELECT * across sources)
  • Downstream CTEs do windowing/aggregation/joins.
  • Final SELECT is thin (projection and light filtering only).