The export gained a column overnight and the parser did not notice

A vendor adding a column to a nightly export is ordinary maintenance, and you will not be told. The defect is on your side: a loader reading fields by position takes every value to the right of the new column and writes it one field out. Odometer readings land in the price column, dates in the mileage column, and none of it errors, because a string fits in a string field. The check that finds it compares the header line and column count against the last known-good file before parsing a row.

Shifted data is worse than missing data for one reason: it is plausible. A truncated file makes a report look obviously wrong and somebody says so by lunchtime. A shifted file puts numbers of roughly the right magnitude in the wrong fields, so it passes the smell test and is found weeks later, when a manager asks why a run of vehicles all show the same in-service date.

Compare the header before you parse a row

This is a 10-minute check and it is worth running against the last 30 files you have kept, not just today's. Schema drift is usually visible in retrospect as the exact night the numbers started being wrong.

1. Take the raw file, not the loaded table. If you no longer have the raw file the diagnosis is over before it starts — keeping every drop is the precondition for all of this.
2. Read the first line and count delimiters. A file that had 84 columns on Monday and 85 on Tuesday has answered the question.
3. Diff the header text itself, not just the count. A rename or a reorder keeps the count identical and moves the meaning, which is the version of this fault that survives longest.
4. Count delimiters on every row, not just the header. A file whose header is fine and whose row 3,180 has 2 extra delimiters has an unescaped separator inside a free-text field.
5. Check the first bytes for a byte-order mark and the file for its encoding. A file that switched from UTF-8 to a Windows code page corrupts accented names and any currency or degree symbol, and the first column header is where a stray BOM shows up.
6. Spot-check 3 value domains that cannot legitimately change type: a VIN column that is not 17 characters, a date column that no longer parses, a numeric column carrying letters. Any one of the three confirms a shift without needing the header at all.

The last step matters because it works even when you have no known-good file to compare against. Values have shapes, and a shifted column almost always puts a value of one shape into a field expecting another.

Four ways the shape changes, and what each does to a row

The locale row is the one that gets missed. Swap day-first for month-first order and 144 of the 365 dates in a year still parse — every date whose day is 12 or lower is a valid date either way. The other 221 are your evidence: if a month-first parser suddenly rejects nothing and previously rejected a steady trickle, or starts rejecting a fifth of the file, the order changed. A decimal comma has the same shape of failure and a worse consequence, because a value read as thousands separators changes magnitude rather than meaning.

Positional trust is the actual defect

Most dealership loaders were written against one sample file, and the mapping from column 14 to the field it feeds got frozen at that moment. That is a contract with nobody on the other side of it. The vendor never agreed to keep column 14 where it is, and in a broker-supplied feed the schema you receive is the broker's interpretation rather than the source system's — the layer in the middle is described in the authorised aggregator between you and the store, and it changes on its own schedule too.

Three assertions, run before anything is written

These are cheap to implement, run in seconds on files of a few hundred thousand rows, and between them catch every failure in the table above.

• A header contract. Store the hash of the normalised header line — lowercased, trimmed, with the delimiter preserved — alongside every load. Compare it before parsing. A mismatch stops the load and reports which names were added, removed or moved, which turns a week of confusion into a sentence in an alert.
• A row-shape assertion. Every row must carry exactly as many fields as the header, evaluated after quoting rules are applied. This is the only check that catches an unescaped delimiter in a customer's address, which affects too few rows to move any total and is exactly the kind of corruption that makes a matching key fail for one household.
• Value-domain assertions on the columns you actually join on. Identifier length and character set, dates inside a plausible window, numerics without letters, enumerated codes drawn from the set you have seen before. Assert the joins first — the fields your reports group by are where a shift does the most damage, and the composite keys those joins really need are covered in the repair-order number is not a primary key.

A column that arrives unannounced is a nuisance. A column that leaves unannounced is a report that reads zero and looks fine doing it.

Fail the file, not the row

The tempting design is to skip bad rows and load the rest. It is the wrong default for a system of record, because a partial load leaves you with a table that is neither yesterday's state nor today's, and no way to tell which rows are which. Fail closed instead: the file loads entirely or not at all.

1. Land the file untouched and keep it. Every later question — was it the vendor or was it us — is answered from the raw drop or not at all.
2. Run the header, row-shape and domain checks against the landed copy before any write.
3. On any failure, move the file to quarantine, alert with the specific difference, and leave yesterday's data in place as last-known-good.
4. Make replay a single operation. When the vendor confirms the new schema, update the contract, re-run the quarantined file and expect the same result as a first-time load — the row-hash and idempotency rules for that are in building a change stream from a full nightly file.
5. Record which schema version each load ran under, so a report covering March and April knows it is spanning two shapes of the same file.

The landing zone that makes those five steps routine — detection in minutes, replay without database surgery — is its own design, set out in receiving a scheduled export without losing a day. This page adds the rule about the shape of the file rather than its arrival.

The change you should not assert your way out of

Not every domain violation is a fault. A shop that starts taking high-voltage work generates operation codes, skill flags and job types that have never appeared in the feed before, and an enumerated-value assertion built from history will reject them. That assertion is not protecting you at that point; it is describing last year. The same is true of a store that opens a second brand, or adds a department. Widening the domain is the correct response, and the operational side of that expansion is in adding EV work to an existing shop flow.

So separate the two classes deliberately. Structural assertions — header, row shape, identifier format, type — are absolute and should fail the load. Enumerated-value assertions are advisory: quarantine for review rather than block, because the business changes and the feed is how you find out. Keeping them apart is what stops a team disabling the checks after the third false alarm.

One more thing worth saying plainly, because it is not a technical fix. Schema changes arrive without notice for the same reason credentials stop working without notice — you are downstream of a system somebody else administers, and the dealer, not you, is the party with standing to ask for advance notice of a format change. That negotiation belongs in the same conversation as the integration that ran under a person's login. Until it lands, your canonical model is the thing that absorbs the vendor's decisions, which is why the read-only-first playbook puts owning that model before anything else. Loaders and landing zones of this kind are the sort of work we scope under internal tools and ops; this page sits inside DMS, CRM and shop-system integration, part of our automotive retail and aftermarket work.

• data feeds
• schema drift
• dms integration
• validation