Before this: Profile before you change anything
The idea
Combining monthly exports looks trivial until February adds a column and March renames one. A declared schema says which columns are required, which are optional, and that nothing else is allowed.
A file that lacks a required column, or contains an unexpected one, is rejected as a whole. Mixing it in would put values with an unknown meaning next to good rows, which is worse than reporting a rejected file.
Add a source column (the file name) to every row. Lineage turns "this total looks wrong" into "the March file has the problem".
Worked example
Input: jan(id, amount), feb(id, amount, channel), bad(id, currency)
- Visual
Append tables: tables feb and bad, required id and amount, optional channel, source column source_file
- SQL (DuckDB)
SELECT id, amount, NULL AS channel, 'jan' AS source_file FROM jan UNION ALL SELECT id, amount, channel, 'feb' FROM feb -- bad is rejected
- pandas
pd.concat([jan.assign(source_file="jan"), feb.assign(source_file="feb")], ignore_index=True)
Result: jan rows get a missing channel; feb rows keep theirs; the bad file is rejected because it lacks amount and adds currency.
Common mistakes
- Making optional columns required.
- Silently dropping unexpected columns.
- Losing which file each row came from.
Check yourself
jan has no channel column. Should it be rejected?
No. channel is optional; jan's rows get a missing channel.
Why reject a whole file rather than dropping its unexpected column?
An unexpected column signals a changed export; its other columns may have changed meaning too.
Practice it
Append the files under the declared schema and keep the source file of every row.