The Union That Silently Reordered Columns
A monthly reporting workflow unions 14 source files (one per region) into a single stream, then feeds a Select and a formula that computes margin = revenue - cost. Last month the finance team flagged that total margin was negative for two regions that are normally profitable. The 14 files are produced by different teams and are "supposed" to have the same columns, but one team added a new column in the middle and another renamed cost to cost_usd. The Union tool is configured by position (the default when field names don't line up). No errors were raised; the workflow ran green.
1. Explain how a by-position Union plus a renamed column produces negative margin with no error.
Read the worked answer
A by-position Union aligns columns by their ordinal position, not by name. When one team inserts a new column in the middle, every column after it shifts by one position. If the downstream Select and formula reference fields by name, the names may still exist but now point at the wrong physical column - or, if the formula references positionally resolved fields, revenue and cost swap or pick up the wrong data.
Separately, the team that renamed cost to cost_usd means that file's cost column no longer matches by name. Under by-position union it still lands somewhere, but the downstream margin = revenue - cost may compute against a column that is actually something else (e.g., a quantity or a shifted field), producing nonsense - including negative margins for normally profitable regions.
The critical failure is that by-position union does not error on schema drift; it happily concatenates mismatched shapes. Green workflow, wrong numbers.
2. What configuration and guardrails prevent this class of silent corruption?
Read the worked answer
Make the union schema-explicit and fail loudly on drift:
- Union by name, not position. Configure the Union tool to match on field names so a renamed or inserted column becomes a visible mismatch rather than a silent shift.
- Normalize names upstream. Standardize
cost_usd→cost(or vice versa) per source with a Select/Rename before the union, so all 14 files share one schema. - Enforce a canonical schema. Define the expected field list once and validate each input against it. A Field Info or Select configured to a fixed field set, plus a check that rejects unexpected columns, catches the inserted middle column.
- Assert on output. Add a Test tool or a row/aggregate check: e.g., total margin within an expected band, and no negative margin for regions flagged profitable. Fail the run instead of publishing.
- Document column order as non-contractual. Treat names as the contract; never rely on ordinal position across team-owned files.
The guardrail principle: schema drift must break the workflow, not the numbers.