Before this: Append files under a declared schema
The idea
Real deliveries vary: a channel column appears in February, a currency column sneaks into a broken file.
Declare the schema: required columns must exist in every file, optional ones may be missing and are filled with missing values, and unexpected columns are either rejected or ignored by an explicit rule.
Align by name and check types after appending.
Worked example
Input: jan(id, amount), feb(id, amount, channel), bad(id, currency)
- Visual
Add step → Append tables → schema: id, amount required; channel optional
- SQL (DuckDB)
SELECT * FROM jan UNION ALL BY NAME SELECT * FROM feb -- bad rejected before the union
- pandas
pd.concat([jan, feb]) # after checking each file's columns against the schema
Result: jan rows get channel = missing; bad is rejected (no amount)
Common mistakes
- Appending by position.
- Accepting files missing required columns.
- Silently keeping unexpected columns.
Check yourself
What happens to an optional column for files that don't have it?
It is filled with missing values for those rows.
Practice it
Append monthly files under a declared schema.