Interview/Databricks

Databricks interview questions: Auto Loader

Interactive Databricks interview questions on Auto Loader. Practice with the matching lesson. cloudFiles watches a landing zone incrementally — notifications, checkpoint, schema evolution — instead of listing the whole prefix.

Lesson · Simulation

beginner
Auto Loader

What problem does Auto Loader solve?

Answer it out loud, then reveal. Play steps through like the simulators.

Production scenario

Auto Loader

Job lists 40 million objects each hour

Symptoms

  • No cloudFiles
  • Landing bucket is the whole org dump

All questions on this page

Indexed as FAQ. Open any item if you prefer a list to Play.

Showing 9 of 9 questions.

beginner

What problem does Auto Loader solve?

Auto Loader · tap to open the answer

Say this first: Incrementally ingest new files without listing the whole bucket every run.

Why: cloudFiles source + notification/listing + checkpoint. It is not a replacement for Delta MERGE logic — it lands bronze.

Common mistake: Every job does dbutils.fs.ls on the landing zone.

Follow-up they will ask: Where is Auto Loader's progress stored?

Lesson · Simulation

intermediate

Directory listing vs file notification mode — when do you need notifications?

Auto Loader · tap to open the answer

Say this first: Huge landing zones: listing is slow and expensive. Notifications scale.

Why: File events (SNS/SQS, Event Grid, Pub/Sub) tell you what landed. Listing is fine for small prefixes. Misconfigured notifications skip files or duplicate if you also copy.

Common mistake: Notification mode without the cloud event pipeline actually wired.

Follow-up they will ask: How does schema inference evolve with rescued data?

Lesson · Simulation

senior

Auto Loader missed a day of files. How do you debug without reprocessing the lake?

Auto Loader · tap to open the answer

Say this first: Checkpoint, cloud event backlog, and whether files arrived in a different prefix.

Why: Don't reset the checkpoint as step 1 — you'll duplicate bronze. Compare source listing vs bronze _metadata.file_path. Replay a prefix with a bounded backfill stream.

Senior depth: Idempotent bronze (path as key) or a separate backfill table you merge once.

Common mistake: rm checkpoint and 'just rerun'.

Follow-up they will ask: How do you backfill without doubles?

Lesson · Simulation

beginner

A new column shows up in the incoming JSON. What are your options for how the stream reacts, and which one deliberately stops it?

Auto Loader · tap to open the answer

Say this first: cloudFiles.schemaEvolutionMode picks between addNewColumns, rescue, failOnNewColumns and none — and both of the failing modes stop the stream, for different reasons.

Remember these

  • addNewColumns fails once, widens the stored schema, then succeeds on restart
  • rescue keeps the schema fixed and diverts new columns to the rescued column
  • failOnNewColumns blocks until a human intervenes
  • cloudFiles.schemaHints pins types so inference cannot drift

Why: With addNewColumns, the stream fails when it sees an unknown column, writes the widened schema to the schema location, and the next run picks the column up — which means it only works if something restarts the job, and people read that first failure as a bug. With rescue, the schema never changes and unexpected columns are captured in the rescued data column instead, so the stream keeps running and the new field is a JSON blob nobody notices. failOnNewColumns fails and does not update the stored schema, so it stays failed until a human decides what to do, which is what you want when a schema change downstream must be a reviewed event. none ignores new columns entirely. Schema hints are the companion option: cloudFiles.schemaHints pins the type of specific columns so inference cannot decide your amount column is a string this week and a double next week.

.option('cloudFiles.schemaEvolutionMode', 'addNewColumns')
.option('cloudFiles.schemaHints', 'order_id BIGINT, amount DECIMAL(18,2)')

Common mistake: Seeing the single failure from addNewColumns, assuming data was lost, and deleting the schema location to 'start clean'.

Follow-up they will ask: Which mode would you pick for a bronze table feeding a regulated report?

Lesson · Simulation

intermediate

cloudFiles.schemaLocation and checkpointLocation — what lives in each, and what breaks if someone points both at the same scratch path?

Auto Loader · tap to open the answer

Say this first: The schema location holds the inferred schema and its evolution history; the checkpoint holds stream offsets and the registry of files already ingested. Losing either has a different, equally bad failure.

Remember these

  • schemaLocation: inferred schema plus its evolution history
  • checkpointLocation: offsets plus the registry of ingested file paths
  • Losing the checkpoint duplicates; losing the schema re-infers types
  • Both must be durable paths, never a scratch or temp directory

Why: The schema location is where Auto Loader persists the schema it inferred, versioned as it evolves, so restarts agree with the previous run about column names and types. The checkpoint location holds the streaming offsets plus the file registry that makes ingestion exactly-once — the record of which paths have already been committed. Delete the checkpoint and the stream treats the whole landing zone as new, which duplicates bronze rather than losing it. Delete the schema location and the next run re-infers from a sample, which can change types and quietly route mismatched values into the rescued column. They must therefore be separate, durable paths that outlive clusters: a workspace temp directory or anything a cleanup policy can reach is how teams lose both at once. If a declarative pipeline owns the stream the service manages both for you, which is one of the stronger arguments for using it for ingest.

(spark.readStream.format('cloudFiles')
  .option('cloudFiles.format', 'json')
  .option('cloudFiles.schemaLocation', '/Volumes/ops/ingest/schema/orders')
  .load(landing)
 .writeStream
  .option('checkpointLocation', '/Volumes/ops/ingest/ckpt/orders')
  .trigger(availableNow=True).table('ops.bronze.orders_raw'))

Common mistake: Deriving both paths from one variable so they end up in the same directory, then losing the schema and the offsets in a single cleanup.

Follow-up they will ask: The checkpoint is gone and bronze has no deduplication. What is your recovery plan?

Lesson · Simulation

intermediate

The cloud notification queue dropped events during a provider incident and a few hours of files were never ingested. How do you pick them up without resetting the checkpoint?

Auto Loader · tap to open the answer

Say this first: cloudFiles.backfillInterval makes Auto Loader periodically list the directory to catch files notifications missed, and the file registry stops anything being ingested twice.

Remember these

  • Notification mode permanently misses a file whose event was lost
  • cloudFiles.backfillInterval adds a periodic listing as a safety net
  • The file registry makes backfills idempotent, not duplicate-prone
  • includeExistingFiles only governs the stream's very first start

Why: File notification mode is efficient because it reacts to events rather than listing, which also means a lost event is a permanently missed file. cloudFiles.backfillInterval tells the stream to run an asynchronous directory listing on a cadence and pick up anything its registry has not seen, so the gap closes on the next backfill without any manual intervention. The registry is what makes this safe: files already committed are not re-ingested, so a backfill is not a duplicate risk. For a one-off recovery you can also run a separate availableNow stream with its own checkpoint pointed at the affected prefix, land it in a staging table, and MERGE it into bronze keyed on the source file path from the _metadata column. What you should not do is delete the checkpoint, which reprocesses the entire landing zone. Note that cloudFiles.includeExistingFiles is a different knob: it decides whether files already present when a stream first starts count as new.

.option('cloudFiles.useNotifications', 'true')
.option('cloudFiles.backfillInterval', '1 day')
.option('cloudFiles.includeExistingFiles', 'true')

Common mistake: Running notification mode with no backfill interval and treating the cloud queue as a lossless system of record.

Follow-up they will ask: How would you detect that a file was missed before a consumer notices the gap?

Lesson · Simulation

advanced

What is the rescued data column, and what kind of row ends up in it?

Auto Loader · tap to open the answer

Say this first: It is a JSON string column holding whatever did not fit the schema — unknown columns, values that failed the column's type, and case-mismatched names — instead of failing the batch.

Remember these

  • Holds unknown columns, type mismatches, and case-mismatched names
  • Carries the source file path so you can trace the bad file
  • Named via cloudFiles.rescuedDataColumn, _rescued_data by default
  • Nothing alerts on it: a type change looks like silent nulls

Why: Auto Loader will not throw away data it cannot place, so when a value does not parse as the column's type, or a field is not in the schema, or a name differs only by case, the raw content goes into the rescued data column as JSON along with the source file path. The column name defaults to _rescued_data and is configurable with cloudFiles.rescuedDataColumn. That design is why ingestion is robust and also why a field can vanish from silver with no error anywhere: an upstream type change from integer to a string with a currency symbol moves every value into the rescued column and leaves the real column null. Nothing alerts on that by default. So treat the rescued column as a monitored metric — count non-null rows per batch, alert on a threshold, and inspect the JSON to see which field is drifting. On a regulated table a growing rescued count should break a build, not sit in a dashboard.

SELECT count(*) AS rescued, min(_metadata.file_path) AS example
FROM ops.bronze.orders_raw
WHERE _rescued_data IS NOT NULL

Senior depth: The rescued column is the ingest layer's error channel, so it needs the same treatment as a dead-letter queue: a count per batch, a threshold alert, and an owner. I would expose it as a data-quality expectation on the bronze table so a rising rate fails or flags the pipeline update rather than living in a chart. The failure mode worth naming in an interview is that nothing ever errors — downstream aggregates just get quieter — which is exactly the class of bug that survives a quarter.

Common mistake: Dropping the rescued column in the bronze-to-silver transformation because it is 'just noise', which deletes the only evidence of schema drift.

Follow-up they will ask: How would you turn the rescued column into something on-call actually sees?

Lesson · Simulation

advanced

Your Auto Loader stream has been running for two years over a landing zone that is never cleaned up. What degrades, and which option addresses it?

Auto Loader · tap to open the answer

Say this first: Both the per-trigger discovery and the checkpoint's file registry grow with the number of files ever seen; cloudFiles.maxFileAge bounds the registry, and notifications bound the discovery.

Remember these

  • The checkpoint's RocksDB file registry grows with files ever seen
  • Listing mode cost tracks total objects, not new ones
  • cloudFiles.maxFileAge bounds state but allows reprocessing of very late files
  • cloudFiles.cleanSource can archive or delete files after ingest

Why: Auto Loader keeps the set of already-ingested file paths in a RocksDB-backed store inside the checkpoint, and that state grows with every file ever processed. In directory listing mode the stream also has to discover files in a prefix that now holds millions of objects, so each trigger pays a listing cost that has nothing to do with how much new data arrived. cloudFiles.maxFileAge puts an upper bound on how long a path is retained in the registry, which keeps state bounded — at the cost of a real tradeoff, because a file that arrives later than that window is no longer known to have been seen and can be reprocessed. The complementary fixes are structural: file notification mode makes discovery proportional to new files instead of total files, a date-based prefix layout keeps any listing small, and cloudFiles.cleanSource can move or delete source files after ingestion so the landing zone stops growing at all. Set maxFileAge from the measured worst-case lateness of the source, not from a round number.

.option('cloudFiles.useNotifications', 'true')
.option('cloudFiles.maxFileAge', '90 days')
.option('cloudFiles.cleanSource', 'MOVE')
.option('cloudFiles.cleanSource.moveDestination', archive_path)

Senior depth: State growth and discovery cost are two problems people merge into one. Discovery is fixed structurally — notifications plus a date-partitioned prefix — and that is the change I make first because it is the one with no correctness implications. maxFileAge is the one with teeth: it trades bounded state for a reprocessing window, so I would set it from the observed p99 lateness with headroom, and make bronze idempotent on the source file path so a late reprocess converges instead of duplicating.

Common mistake: Setting maxFileAge low to shrink the checkpoint and reintroducing duplicates for any source that occasionally delivers a week late.

Follow-up they will ask: How would you measure the real worst-case lateness of this source?

Lesson · Simulation

senior

An upstream team overwrites yesterday's file in place with a corrected version — same path, same name. What does Auto Loader ingest?

Auto Loader · tap to open the answer

Say this first: Nothing. The registry keys on file path, so a rewritten file at an already-processed path is treated as seen and its new content never lands.

Remember these

  • Dedup is by file path, so a rewritten path looks already-ingested
  • The correction is lost silently, with no error anywhere
  • cloudFiles.allowOverwrites reprocesses changed files and duplicates rows
  • Immutable file names are the only fix that does not need dedup downstream

Why: Auto Loader's exactly-once guarantee comes from remembering which paths it has committed, and that model assumes the landing zone holds immutable objects. Overwrite a path it has already processed and the corrected content is simply never read — no error, no warning, and bronze keeps the wrong version indefinitely. cloudFiles.allowOverwrites makes the stream reprocess files whose content has changed, which fixes the missed correction and immediately creates the next problem: the corrected rows are appended on top of the originals, so bronze now has both versions. That is only safe if the downstream path deduplicates, which in practice means silver MERGEs on a business key rather than appending. The durable answer is to make the contract immutable — new file names for corrections, ideally with a version or timestamp in the name — because every alternative here is a workaround for a mutable landing zone.

.option('cloudFiles.allowOverwrites', 'true')
-- bronze now holds both versions; silver must MERGE on the business key

Senior depth: This is an interface problem dressed as a configuration problem. The ingestion contract should say files are immutable and corrections arrive as new objects, because that makes replay, audit and lineage all work by construction. If I cannot change the upstream, I enable allowOverwrites and pair it with a silver MERGE keyed on the business key and sequenced by the file's modification time, so a reprocessed file converges instead of duplicating — and I would say out loud that bronze is then no longer a faithful append-only record of what arrived.

Common mistake: Enabling allowOverwrites as the fix and shipping it with an append-only bronze-to-silver step, which doubles the corrected rows.

Follow-up they will ask: How would you detect, today, whether any file in the landing zone has been overwritten since ingestion?

Lesson · Simulation