Auto Loader at 40,000 files per hour: latency, schema drift, and silent row loss
A manufacturer lands telemetry in an ADLS Gen2 container: roughly 40,000 JSON files per hour, each 20-60 KB, plus one 900 MB Parquet export per night from an ERP. A single Auto Loader stream in a Lakeflow pipeline writes both feeds into one bronze streaming table in Unity Catalog and uses directory listing for file discovery. Hourly latency has grown from 6 minutes to 55 minutes, and storage billing shows list operations dominating cost. Last week the ERP added two columns; the pipeline failed with a schema mismatch and stayed down for nine hours until an engineer restarted it with a wider schema. Separately, about 3% of telemetry messages never reach bronze: the gateway omits device_id on those messages, while _rescued_data is populated only for genuinely malformed JSON. The team wants the hourly feed under 10 minutes and no manual intervention on schema change.
1. Why is directory listing the wrong discovery mode at this file volume, and what must exist in Azure before you can switch? Be specific about permissions and failure behavior.
Read the worked answer
Directory listing makes every trigger list the whole prefix hierarchy and compare it with the checkpoint. At 40,000 files/hour that is close to a million objects per day, so each microbatch pays an O(number of objects) listing cost in both latency and storage transactions. Raising trigger frequency makes it worse, not better.
File notification replaces listing with an Event Grid subscription on the container plus a Storage Queue that Auto Loader drains. Databricks can create these resources for you when the credential used for the external location has rights to create Event Grid subscriptions and queue resources on the storage account; otherwise an administrator pre-creates the queue and subscription and you supply them. Constraints to plan for: the subscription is per container, so each new landing container needs its own subscription; if notification is misconfigured Auto Loader falls back to listing, which is silent degradation rather than an error; and cloudFiles.backfillInterval is your safety net for files written while the subscription was broken. Keep the checkpoint and schema location unchanged when you switch modes so you resume from the same offset instead of reprocessing.
2. Explain why the ERP change took the pipeline down and compare `addNewColumns`, `failOnNewColumns`, and `rescue`. What configuration and operational change makes this self-healing?
Read the worked answer
The default schemaEvolutionMode is addNewColumns: Auto Loader detects a new column, updates the schema stored in schemaLocation, and deliberately fails the stream once so that a human notices and restarts it. The restart resumes from the checkpoint with the wider schema. The nine-hour outage was not a bug; it was the default contract being ignored.
failOnNewColumns never recovers until you change the schema yourself - appropriate only for regulated feeds where an unexpected column is an incident. rescue never fails: known columns load normally, unknown columns and values that fail to parse land in _rescued_data as JSON you can query and promote later.
For a nightly ERP extract where the two new columns are usually benign, use rescue plus explicit schemaHints for the columns whose types Spark would infer badly, and a weekly job that scans _rescued_data to promote fields into the declared schema. Pair it with task-level retries with a delay so the single addNewColumns failure restarts automatically. Note the limit that neither mode fixes a changed type, a renamed column, or a dropped column - those still need an explicit schema decision.
3. The 3% of messages with a missing `device_id` disappear even though rescue is configured. Diagnose where they are actually lost and propose a durable fix.
Read the worked answer
Rescue only captures two situations: a field present in the file that is absent from the schema, and a value that cannot be cast to the declared type. A file that is not valid JSON at all puts the raw text into _rescued_data and nulls the rest. A well-formed message that simply omits an optional field is none of these - device_id becomes NULL and the row is written to bronze correctly. Since _rescued_data is null for these rows, ingestion is not dropping them.
So look downstream. The usual culprits are a MERGE into silver keyed on device_id (NULL never matches a source key, so the row silently never lands), a WHERE device_id IS NOT NULL filter added to satisfy a not-null constraint, or a join to a device dimension that inner-joins away unmatched keys.
Fix it at the modeling layer: decide the business rule for anonymous devices, generate a deterministic surrogate key, change the dimension join to a left join with an unknown member, and add a quarantine table plus a row-count reconciliation between bronze and silver per hour so a 3% loss shows up as a metric instead of a support ticket.