Union
Union stacks datasets vertically — twelve monthly files into one table.
The configuration that matters is how fields are matched:
- Auto by name — safest; matches on field name whatever the order
- Auto by position — matches column 1 to column 1; dangerous if column order differs
- Manually configure — explicit mapping, for when names differ
If one file has its columns in a different order, matching by position puts city values into the region field with no error at all. Use by name unless you have a specific reason not to.
Mismatched fields
When one input has a field the others lack, Union outputs it with nulls for the inputs that did not have it. Set the tool to error or warn on that if the schemas ought to be identical — silence is not what you want when a source has changed shape.
Append Fields
Append Fields attaches every row of the source input to every row of the target — a Cartesian product.
10 target rows and 5 source rows gives 50. That is deliberate and useful for adding a single row of parameters (a rate, a report date) to every row of data.
Append Fields with more than one source row multiplies your data. It warns above a threshold, but the warning is easy to miss. If you meant to match rows to rows, you wanted Join.
Which to use
| Need | Tool |
|---|---|
| Stack similar datasets | Union |
| Match rows by a key | Join |
| Add one row of values to every row | Append Fields |