7 min read

The Files That Break Your Bulk Load

data-engineering etl data-quality duckdb schema-drift

You point a bulk load at a folder — a vendor drop, a partition tree, a nightly export — and treat the whole set as one dataset. It works for months. Then one morning a number is wrong, nothing failed, and you lose two days finding the single file that did it.

I’ve written about that exact failure before — the missing column that shifted a load without ever crashing it. This is the wider field guide: which files do it, why the failure is so often silent, and a small deterministic check that names the offenders before the load runs. Most of the cases below are documented behavior in tools you already use; I’ve linked the sources. The pattern under all of them is one line: a bulk load is only as good as its worst-conforming file, and the worst file usually loads without complaining.

There are two kinds of bad load. The loud one throws — your job goes red over one malformed file, which is annoying but at least visible. The silent one succeeds, writes wrong data downstream, and surfaces only when someone questions a total weeks later. The silent ones are the expensive kind, and the reason is structural: a loader’s whole job is to ingest, so when a file almost fits, its instinct is to make it fit — null-fill a missing column, widen a type, relabel by position — rather than stop. Every helpful coercion is a place the data goes wrong without a sound.

When the source changes and doesn’t tell you

Schema drift is the most common cause of production pipeline breakage, and it arrives in a small, documented set of shapes: columns added, removed, renamed, or retyped — on the fly, from a source you don’t control.

  • A column appears. The most innocent-looking and one of the most dangerous, because “a new column” is the default-allowed case almost everywhere. In Databricks Auto Loader a new column stops the stream with an UnknownFieldException — loud, recoverable, fine. But in a plain spark.read or a pandas concat, it’s absorbed without complaint: the earlier files get NaN-backfilled, and if the column landed in the middle of the file, everything after it can shift.
  • A column is renamed. customer_id becomes account_id upstream — same data, new label. Under a name-binding load this is the genuinely nasty one: pandas’ concat doesn’t error, it creates both columns and half-fills each with NaN.
  • A column is dropped, or changes type. The retype is the sneakiest of the set: quantity was always an integer, and this week one file carries the value "many", so the whole column infers as text. A real, documented case — an ID column of eight digits plus a trailing “D,” read as a double with the “D” trimmed off under inferSchema. The file loaded. The IDs were wrong. (I’ve told the encoding version of this — where it’s the bytes, not the value, that drift.)

When the files disagree with each other

This group is specific to bulk loads; it doesn’t exist when you load one file at a time. It’s about how per-file schemas combine, and it’s where the nastiest corruption lives. Databricks documents it as expected behavior, which is exactly what makes it dangerous: read multiple files together and the engine infers the schema from a sample. If some files are column-A-first and others column-B-first, and the sample only happened to see the A-first ones, the B-first files get mapped wrong — column B’s data loaded under column A’s name. No error. Their recommended fix tells you the whole shape of the problem: make sure all files have the same schema, or process each one individually. In other words — validate the set before you union it.

The positional mechanics of this — the header that wins, the columns that shift under it — get a post of their own. The point here is only that across a fileset it compounds: file read order across a directory isn’t guaranteed, so which header wins can change between runs, and the same load can corrupt differently on different nights.

When the file is broken before you reach the schema

These fail before any schema comparison, and a surprising number load anyway — wrongly.

  • Ragged rows. An unquoted comma inside a field shifts every column after it, for a handful of rows in a million. A shifted row is worse than a missing one: it looks valid, but every value is now in the wrong field.
  • A lying extension. A .csv that’s actually UTF-16 tab-delimited, or Latin-1 read as UTF-8 producing mojibake, or a BOM that mangles the first column name. Most CSV failures aren’t corrupt files at all — they’re the exporter and the importer disagreeing about encoding, delimiter, quoting, or date format.
  • Excel’s sabotage. Someone opens the CSV, saves it, and now the leading zeros are gone, the long numbers are in scientific notation, and "2-3" has become Feb 3. The file is perfectly valid CSV. The data is quietly wrong.
  • Truncation. An export died halfway, so one file is half its usual size — still parses, still loads, just missing three weeks of rows. Nothing about it is malformed; it’s merely incomplete, and incompleteness raises no parser error at all.

The one nothing structural can catch

There’s a failure at the bottom of this that no structural check — including the one below — can see, and being honest about it matters. If a file has a column truthfully named amount but the values under it are actually tax figures, nothing about the file’s structure is wrong: the count is right, the type is right, the name is right. Only the meaning is wrong, and meaning is exactly what structure can’t see. That’s the line between structural validation — bounded, deterministic, solvable — and semantic correctness, which needs business rules and is effectively unbounded. Saying plainly where a check stops, instead of pretending to see past it, is what keeps it worth trusting.

Catching them before the load

All of this happens before your load, in files sitting on disk. So the fix is a pre-flight step: fingerprint every file in the set, compare each against an expected shape, and print the exact names of the ones that won’t conform — a golden / broken partition — before you fire the bulk load.

Here’s the essence of it. It’s a script, not a framework — about fifteen lines of real logic. It leans on DuckDB to do the reading and type inference (native-speed C++, so Python only orchestrates), and the expected shape is the dumbest possible artifact: a text file, one name,type per line.

import duckdb


ALIASES = {"bigint": "int", "integer": "int", "int": "int", "double": "decimal",
           "float": "decimal", "decimal": "decimal", "varchar": "text", "text": "text",
           "boolean": "bool", "date": "date", "timestamp": "timestamp"}
coarse = lambda t: ALIASES.get(t.lower().split("(")[0], "text")


def fingerprint(con, path):
    # DuckDB sniffs the file; path is passed as a PARAMETER, never glued into SQL
    rows = con.execute("SELECT column_name, column_type FROM "
        "(DESCRIBE SELECT * FROM read_csv(?, sample_size=1000))", [path]).fetchall()
    return [(name, coarse(typ)) for name, typ in rows]


def check(observed, expected):                 # name-based load: bind by name
    obs, exp, out = dict(observed), dict(expected), []
    for name, etype in expected:
        if name not in obs:
            out.append(f"[ERROR] expected column '{name}' is missing")
        elif obs[name] != etype:
            out.append(f"[ERROR] '{name}': expected {etype}, found {obs[name]}")
    for name, _ in observed:
        if name not in exp:
            out.append(f"[ERROR] unexpected column '{name}'")
    return out

Point it at a folder seeded with the drift cases above and it names the offenders:

7 files: 3 conform, 4 broken, 0 unreadable


GOLDEN (3) — safe to load:
  ✓ orders_2026-01-05.csv
  ✓ orders_2026-01-06.csv
  ✓ orders_2026-01-07.csv


BROKEN (4) — fix or quarantine:
  ✗ orders_2026-01-08.csv
      [ERROR] unexpected column 'discount' (baseline doesn't declare it)
  ✗ orders_2026-01-09.csv
      [ERROR] expected column 'customer_id' is missing
      [ERROR] unexpected column 'account_id' (baseline doesn't declare it)
  ✗ orders_2026-01-10.csv
      [ERROR] expected column 'revenue' is missing
  ✗ orders_2026-01-11.csv
      [ERROR] column 'quantity': expected int, found text

Notice the renamed column (orders_2026-01-09.csv): the check reports it as two facts — customer_id missing and account_id unexpected — and does not guess that it’s a rename. It could try (same position, same type, one name changed — surely a rename?), but to confirm one you’d have to inspect the values and argue that two columns “look alike,” and two different ID columns can look identical while normal variation makes the same column look different. A confidently-wrong “these are the same column” is worse than silence. So the script shows the evidence and lets the human, who has the context it doesn’t, decide.

That restraint is the same one I keep building into tools on purpose: report structure, refuse to judge values, expose the evidence, withhold the verdict, and disclose the meaning-limit rather than fake past it. The detection is the easy part. The discipline is knowing what to leave out.

If there’s a spine to the whole thing, it’s three habits, and none of them is exotic. Validate the set before you union it — every documented multi-file corruption above is defeated by checking each file against an expected shape first. Check structure, and leave values to a data-quality layer — one bright line keeps the check bounded and trustworthy. And let your certainty mirror the load contract: fatal on what the load binds to, a warning on what it can’t see, and an honest shrug at the one thing structure physically can’t catch. It’s an afternoon of unshowy work that turns a two-day forensic hunt into a line of output before the load ever runs.

Companion code: ramwise-examples/preflight-check — the full script with name and position modes, a name,type baseline, and seven deliberately-broken order feeds to run it against.