Handling a CSV Provider Who Changes Columns Without Warning

The file that arrives on Tuesday has an extra column called currency. Or it has customer_name where Monday's had customer. Or the columns are the same and in a different order because someone upgraded the exporter. None of these was announced, and each fails differently: the first loads fine and nobody notices, the second stops the job with a message about a missing column, and the third loads every row into the wrong fields without any error. The third is the one that makes this an article, since the first two are noticed and the last may run for a month. This article shows a header check that tells the cases apart, a policy for each and the one habit, reading by name, that removes most of the risk. It is the "schema drift" part of automating CSV imports into a database.

The four ways a header changes

A header can change in four ways, and each wants a different response.

The worst of them is the reorder, because it needs a provider to do nothing malicious at all. An exporter that builds the column list from a dictionary, a library upgrade that sorts keys, a report designer who drags a column: any of them changes the order and keeps every name. A loader that uses COPY orders FROM ... CSV HEADER into a table with columns in the old order loads the new file into the wrong columns, because HEADER skips the line and does not read it. If the types are compatible it succeeds silently. If they are not, you get a type error on a row, which at least points at the file.

A header check that names the change

The check compares the actual header to the expected list and prints each kind of difference separately:

import csv, sys

EXPECTED = ["order_id", "customer", "placed_on", "total"]

def diff(actual):
    missing = [c for c in EXPECTED if c not in actual]
    extra = [c for c in actual if c not in EXPECTED]
    order_changed = not missing and not extra and actual != EXPECTED
    return missing, extra, order_changed

for path in sys.argv[1:]:
    with open(path, newline="", encoding="utf-8-sig") as f:
        actual = next(csv.reader(f))
    missing, extra, reordered = diff(actual)
    if not (missing or extra or reordered):
        print(f"{path}: header matches")
        continue
    print(f"{path}:")
    if missing:
        print("  missing :", ", ".join(missing))
    if extra:
        print("  new     :", ", ".join(extra))
    if reordered:
        print("  same columns, different order:", ", ".join(actual))

Run against four files, one clean and one for each change:

h_ok.csv: header matches
h_new.csv:
  new     : currency
h_renamed.csv:
  missing : customer
  new     : customer_name
h_reordered.csv:
  same columns, different order: customer, order_id, total, placed_on

Each message tells a person what happened without opening the file. The rename case is recognizable as one, since it shows one missing and one new name side by side, which is a prompt to add an alias. The file opens with utf-8-sig so that a byte order mark does not make the first expected name look missing, which is a false alarm that appears the first time a provider's tool starts writing one (the UTF-8 BOM in CSV files).

Policy: stop, ignore or map

Write the policy down, per feed, before the first change arrives. Mine is usually this.

Missing required column: stop. The file cannot be loaded because the target needs the value, and a default would be invented data. Move the file to a failed folder with the message above, alert, and wait for the provider or a decision to map it.

New column: ignore and log. If you read by name, an extra column does no harm. The job logs "new column currency" once per change and carries on. Someone should still ask what it means, because a new column sometimes changes the meaning of existing ones (an amount that is now in a currency that varies).

Renamed column: map if known. An alias table keeps a known rename from stopping the job:

ALIASES = {"customer_name": "customer", "client": "customer", "total_amount": "total"}

def normalize(header):
    return [ALIASES.get(h.strip().lower(), h.strip().lower()) for h in header]

Lowercasing and trimming also absorb the quiet variants: Order ID versus order_id, a trailing space, a capital letter. Header variants and normalizing column names covers that part in more detail. Keep the alias list short and in version control. A growing alias list means the provider's format is unstable, which is a conversation to have with them.

Reordered: harmless if you read by name. With a csv.DictReader, or with an explicit column list in COPY, order does not matter. The check above flags it as information and the load proceeds. If your loader cannot read by name (a fixed-position COPY with no column list, or LOAD DATA with positional variables), then reordering is fatal and the check should stop the file.

Read by name, always

The habit that removes most of the risk is to map columns by header name at the point of loading. In Python that is csv.DictReader. In PostgreSQL, load into a staging table that has text columns named in the header and copy to the target by name. In DuckDB, read_csv returns columns by name, and you select the ones you need:

INSERT INTO orders
SELECT order_id, customer, placed_on, total
FROM read_csv('incoming/orders-2026-10-02.csv', header=true, all_varchar=true);

Selecting by name makes an added column irrelevant and a reordered one harmless, and a missing one raises a clear Binder Error naming the column. Mapping CSV columns to table columns shows the same idea for MySQL, where LOAD DATA takes an explicit column list. The point is that a name is a promise and a position is a coincidence.

Keep evidence

When a header changes, save the new file's first lines, with the date, in the repository next to the loader's tests. It documents what the provider sent and when, and turns into a test fixture: a later bug in your alias handling will be caught by the same file. A folder of header samples named by the date they first appeared is also the answer to "when did this column start appearing?" asked a year later during an audit.

Add a version to the feed's description when you can see one. Some providers put a version in the file name or a first comment line; check it, and treat an unknown version as a stop. If they do not, push for it.

Types change as well

Names are not the only thing that drifts. A column that held integers begins to carry N/A, a date starts to include a time, an amount gains a currency symbol. The header check cannot see these. Row-level validation does, and so does a typed load that fails loudly. A useful extra is a type profile of each file compared to the last one: for each column, the share of values that parse as an integer, a date or a decimal, and a flag when the share changes by more than a few percent (detecting columns that changed type between files). The profile is a few lines of SQL per column and catches the changes the header does not.

When the provider should change, not you

Every alias you add is a bet that the provider's format is stable from now on. If changes arrive monthly, you are paying an integration tax that belongs to them. The better fix is a contract: a documented schema, a version number, a notice period for changes (two weeks is a reasonable ask) and a sample file for any change before it goes live (making a CSV a contract with the data provider). Providers who control their exporter can usually add a version column or a stable header in an afternoon, once someone asks in writing.

Accommodate changes yourself only when the provider cannot or will not, such as a public dataset or a government feed. Then the header check, the alias table and the type profile are your defense, and you run them on every file. The format gives you no help: RFC 4180 says the header is optional and its fields are ordinary text, so anything stronger than that has to come from an agreement.