Auto Loader feeding on a 40-million-object bucket with a changing vendor schema

New nested field shipping.carrier_code - addNewColumns evolves the schema once the stream restarts on a new schema version. Rows seen before evolution stay rescued; they are not retroactively fixed. Accept the column, but backfill the rescued rows with a targeted reprocessing pass.

order_total type change - this is a conflict, not a new column. Auto Loader will not overwrite an existing column's type, so the offending values go to _rescued_data. Fix at the contract level: declare order_total STRING in cloudFiles.schemaHints (or a full DDL schema) so both formats land intact, and parse in silver with try_cast after stripping the currency suffix. Widening to string is safe for existing numeric data; note that it changes what bronze contains, so downstream casts must be reviewed.

UserId vs user_id - inference treats this as a new column, so user_id is NULL in those files and the value lands in a new column (or in rescue if the schema has not evolved yet). Neither the rescue column nor inference fixes this reliably. Normalise header casing before the bronze write, or add a silver step that coalesces user_id and UserId, then drops the duplicate. Also make column naming part of the vendor contract.

Finally, alert on rescued-row rate as a first-class quality metric rather than reading it after the fact.

Why the stream ignores them Auto Loader tracks paths it has already ingested. Re-uploading a corrected object at the same path makes it invisible to the stream; file notification mode does not emit events for overwrites either. Switching to directory listing does not help and hurts: with 40 million objects, each listing pass is expensive and slow, and re-ingesting would also duplicate rows. Row-level dedup on the path does not exist by default, so you cannot rely on the checkpoint to sort this out.

Safe path

1. Run a separate one-off batch job (not the stream) that reads only the 30-day window from the landing bucket, using a path prefix or modificationTime filter so it touches a bounded slice rather than the whole bucket.
2. Land it into a staging Delta table, keeping input_file_name() and file modification time.
3. MERGE staging into bronze/silver keyed on order_id, taking the row with the greatest file modification time. This makes the backfill idempotent and re-runnable.
4. Only after bronze is clean, re-derive the affected silver/gold partitions.

Separately Switch discovery to file notification mode so the listing cost and discovery latency collapse; this requires SQS/SNS plus an IAM role configured by your cloud admin. Do not restart the stream with a fresh checkpoint as a shortcut - that reprocesses everything and duplicates data unless the sink is idempotent.

Treat bronze as an owned contract rather than inference output:

• Declare an explicit schema (DDL or schemaHints) for known columns and let only the genuinely unknown parts evolve. Unstable inference is the main reason rescue volumes drift.
• Set schemaEvolutionMode deliberately: addNewColumns for additive change, and a pipeline warning (not failOnNewColumns, which stops the stream and requires a restart) when a new column appears.
• Quarantine rows with non-empty _rescued_data into a separate errors table that keeps the source path and file modification time, so fixing them is a targeted replay rather than a full backfill.
• Add an expectation on the rescue ratio (for example fail or drop the batch above 0.5%) and chart it per ingestion day.
• Once rescue is near zero, keep the alert in place permanently; that metric is your early warning that the vendor changed something again.