Auto Loader Backlog, Schema Drift, and a Duplicate-Producing Checkpoint Reset

Listing latency. The 11-14 minute runs are dominated by directory listing of a prefix with hundreds of thousands of objects. Switch to file notification mode (cloudFiles.useNotifications = true), which registers file events with a cloud queue and removes per-run listing entirely. It requires a one-time permission grant so Auto Loader can create the notification resources, and it must be enabled with the stream stopped and the same checkpoint retained. Directory listing remains the right choice only when the file volume is low or permissions cannot be granted.

Schema drift. cloudFiles.schemaEvolutionMode = addNewColumns (the default) lets new fields like promo_code land as nullable columns on the next inference, and unparseable records land in _rescued_data rather than failing the run. Adding cloudFiles.schemaHints for fields that would otherwise be inferred as strings avoids later type churn.

The reset. The checkpoint is what records which files Auto Loader has already consumed. Deleting it discards that state, so every file still sitting in the landing zone is re-read. The schema location can be re-inferred far more cheaply than the checkpoint can be rebuilt, so a schema change should never be the reason to throw the checkpoint away.

Treat bronze as append-only raw history and deduplicate on the way to silver, not by deleting bronze rows.

1. Identify the key. If events carry a stable event id, dedupe with row_number() over a window partitioned by event id ordered by ingestion time, or a streaming dropDuplicatesWithinWatermark if an event-time watermark exists. If they do not, _metadata.file_path plus an offset distinguishes physically distinct rows, but it will not catch the same logical event arriving in two different files.
2. Apply it as a MERGE into silver so the operation is idempotent and re-running it changes nothing.
3. Prevent recurrence structurally: keep the checkpoint directory stable and out of reach of ad-hoc cleanups; use cloudFiles.cleanSource (move or delete after processing) so consumed files are not available to be re-read; and make every downstream write a MERGE rather than an append, so a reprocessing event is absorbed instead of duplicated.

A one-off repartition of the landing zone during the fix also works, since anything removed cannot be re-consumed.

Use write-time compaction on the bronze table rather than a separate scheduled job that competes with the stream:

• delta.optimizeWrite = true coalesces the shuffle output of each micro-batch, reducing the number of files written per trigger.
• delta.autoCompact = true runs a small compaction at the end of a write once the table crosses the threshold, merging small files up to the target size.
• cloudFiles.maxFilesPerTrigger caps how much each trigger consumes, which keeps trigger latency predictable and stops a backlog from producing oversized bursts.

These are table properties, so they survive restarts and apply to every writer, which matters if more than one stream feeds bronze. A periodic OPTIMIZE is still useful for older data that predates the settings, and predictive optimization can handle it on managed tables, but compaction should not be the primary mechanism during active ingestion.

Note the trade-off: optimizeWrite adds a shuffle step, so validate trigger duration after enabling it on the busiest stream.