UNION transform failures caused by extract column order changes

Introduction

UNION transform failures can happen when source extract tables change column order and downstream SQL relies on positional column matching. In Kleene, this can occur when extracted table structures are regenerated or serialised in a way that does not preserve the original column order. The data may still be present, but the transform can fail or produce incorrect results if the SQL assumes columns appear in a fixed sequence.

Issue description

A transform failed because extract table structures changed and no longer matched the column ordering expected by the SQL. The transform used UNION logic that relied on positional column matching rather than explicitly selecting columns in a consistent order.

In SQL, UNION matches columns by position, not by column name. If two SELECT statements return the same column names but in a different order, the database will still align the first column with the first column, the second with the second, and so on. This can cause type mismatches, invalid results, or transform failures.

Signs

You may be dealing with this issue if a transform starts failing after an extract schema or table structure changes, especially when the transform uses UNION or UNION ALL across multiple extract tables.

Common signs include column mismatch errors, datatype mismatch errors, unexpected values appearing under the wrong fields, or failures that appear after an extract process changes even though the business logic in the transform has not changed.

Basic troubleshooting steps

Start with the following checks to narrow down the cause of the issue:

• Review the failing transform and identify any UNION or UNION ALL statements.
• Check whether the SELECT statements use SELECT *.
• Compare the column order of each extract table used in the union.
• Confirm whether all SELECT statements return the same number of columns.
• Confirm whether each column position contains the same logical field and compatible datatype.
• Check whether the extract process recently regenerated or changed the source table structure.
• Rewrite the transform to explicitly select columns in the required order.

Common causes and how to fix them

UNION logic relying on column position

UNION operations align columns by position. If the first SELECT returns customer_id, created_at, status and the second returns created_at, customer_id, status, the fields will be matched incorrectly even though the same column names exist.

How to fix it: explicitly list columns in the same order in every SELECT statement used by the union.

Use of SELECT * in unioned queries

SELECT * is risky in transforms that combine multiple tables because it depends on the physical or generated column order of each table. If that order changes, the transform can fail without the SQL text changing.

How to fix it: avoid SELECT * in union logic. Replace it with a fixed column list that matches the intended output schema.

Extract column order changed during serialisation

Column ordering is not always guaranteed through AVRO serialisation or extraction processes. A regenerated extract table may contain the same fields but expose them in a different order.

How to fix it: do not rely on extract column order. Treat extract column order as unstable and enforce the order inside the transform SQL.

Datatype mismatch between column positions

If column order changes, a string field may be matched with a date field, or a numeric field may be matched with a text field. This can cause SQL errors or incorrect outputs.

How to fix it: compare each SELECT statement position by position. Add explicit casts where needed, but only after confirming the fields are aligned correctly.

Practical troubleshooting workflow

1. Open the failing transform SQL.
2. Search for UNION and UNION ALL statements.
3. Check whether any part of the union uses SELECT *.
4. Inspect the source extract tables and compare their column order.
5. Identify the intended final output columns and their correct order.
6. Rewrite each SELECT statement to use the same explicit column list.
7. Add NULL placeholders for columns that exist in one source but not another.
8. Add explicit casts where column types need to be standardised.
9. Re-run the transform and confirm the union no longer depends on source column order.

Best practices to avoid UNION failures from column order changes

• Avoid SELECT * in production transforms.
• Always specify column names explicitly in UNION and UNION ALL queries.
• Keep the column order identical across every SELECT statement in a union.
• Add NULL placeholders for missing fields so each SELECT returns the same structure.
• Use clear aliases to standardise column names across sources.
• Cast fields deliberately when combining data from different extract tables.
• Treat extract column order as unreliable, especially when AVRO or schema regeneration is involved.

Additional information

For this issue, the extract table structures changed and no longer matched the source column ordering expected by the transform. The root cause was that the SQL union relied on positional column matching.

The resolution is to update downstream transforms so they explicitly specify column names and column order before the UNION. Avoid relying on implicit column ordering from extract tables, because column order is not guaranteed through AVRO serialisation or extraction processes.