A header-less CSV extract gains a column in position 3 — what happens to a positional load?

## Why position binding is fragile A delimited file without a header carries no field names. The only way to map it to a target is by ordinal: the first delimited value goes to the first target column, the second to the second, and so on. That mapping lives in the loader configuration, not in the file, and the producer of the file has no way to know it exists. So when a producer adds a field — and producers naturally add fields next to related ones rather than at the end — every field after the insertion point moves one slot right, and the loader's ordinal map is now off by one for the entire tail of the row. ## What the corrupted row looks like Take an order extract whose target is `(order_id, customer_id, order_date, amount, currency)`. The source team inserts `channel` after `customer_id`: ``` before: 1001,55,2026-03-01,42.50,USD after: 1001,55,web,2026-03-01,42.50,USD ``` The loader writes `web` into `order_date`, `2026-03-01` into `amount`, `42.50` into `currency`, and has one value left over. Three columns are wrong and one is orphaned. ## Loud versus silent, and why type luck decides The failure mode is determined by the types of the shifted values, which is an uncomfortable thing to depend on. **Loud.** If `order_date` is a typed date column, `web` fails to cast and the load aborts. You get an error, you look at the file, you find the extra column. This is the good case. **Silent.** If the landing table is all strings — a common choice precisely because it is forgiving — nothing fails. Every row lands, the job reports success, and the corruption surfaces days later when a report shows currencies where dates should be. By then several partitions are affected and the correct source files may have aged out. **Half-loud.** Many loaders reject rows whose field count differs from the expected column count. That arity check is the one accidental protection a positional load has, and it catches exactly the insert-at-the-end case that was already harmless while sometimes catching the middle-insert case too. ## Detection you can actually rely on None of the above is detection; it is hope. Real detection for file-based ingestion means asserting the shape before you load a single row. **Require a header and load by name.** This is the single highest-value change. With names in the file, an inserted column is a new name the loader either ignores or reports, and the remaining columns still map correctly. Position stops mattering. **Pin the header string.** If a header exists, compare it byte-for-byte (or as an ordered name list) to the expected header recorded from the last accepted run, and reject the file on mismatch before loading. This turns any structural change into a clean, pre-load failure with an obvious message. **Assert field count per row.** Cheap, catches insertions and truncations, and works even without a header. It will not catch a same-arity reorder. **Validate a canary value.** A column with a strongly recognisable domain — an ISO date, a three-letter currency code, a known-enum status — can be pattern-checked on a sample of rows. If `order_date` stops looking like a date, stop. ## The structural fix Every mitigation above is a workaround for a format that does not describe itself. Where you control the extract, the durable answer is a self-describing format: a header-bearing CSV at minimum, or better a format where each field carries its name and type with it, so that adding a column is a non-event for existing consumers and a missing column is detectable by name rather than by counting commas. Where you do not control the extract — a partner drop, a legacy mainframe export, a fixed-width file from a system nobody maintains — you are stuck with positional loading, and the response is to make the contract explicit on your side: a recorded expected layout, a pre-load validation step that rejects anything that does not match it, and a quarantine location for files that fail so the batch can be inspected rather than lost. ## Fixed-width files Everything above applies more sharply to fixed-width layouts, where fields are identified by byte offset rather than delimiter count. An inserted field shifts every subsequent offset, and because the parse never fails on a delimiter count there is no accidental arity check at all. Fixed-width sources should always be validated against a recorded layout specification before parsing.