Databricks Auto Loader Interview Questions: Schema Changes, Rescued Data, and Recovery

An Auto Loader interview question often sounds like a configuration quiz: a new column arrives, a stream stops, or a file is corrected and uploaded again. The harder part is explaining which state changed and what your proposed recovery will preserve. A restart, a schema update, and a fresh checkpoint are different operations.

This guide focuses on cloudFiles ingestion from files. Its thesis is simple: separate the input contract from ingestion progress before proposing a fix. We use original JSON fixtures and a recovery worksheet to make the distinction concrete. The reference scope is Databricks on AWS, with a DBR 16.4 LTS preparation profile. No Databricks cloud execution was performed; local checks validate the fixtures, while platform outcomes below are documentation-based expectations.

Start with Explain ETL schema changes and ensure integrity if you want to practice explaining the contract before choosing an option.

Which details should you clarify first?

Ask what format is arriving, how the schema is established, which evolution mode is selected, and whether files are immutable. Then establish the failed operation: discovery, parsing, transformation, or writing the target. An answer about new columns does not explain a destination permission error.

Official behavior: The AWS schema guide distinguishes an inferred schema from a supplied schema. Without a supplied schema, addNewColumns is the default; providing a schema changes the default to none. A schema hint is not equivalent to supplying the full schema. Default JSON inference uses strings, while cloudFiles.inferColumnTypes changes that behavior.

For an interview, translate those facts into a question: “Are we testing inference, or enforcing a known input contract?” If the expected quantity field is already a string, a later textual quantity does not demonstrate an integer type mismatch. Write down the starting schema before predicting the result.

The examples here explicitly select rescue, a supplied integer quantity field, and a case-sensitive reader. They exclude automatic type widening. That keeps the exercise about three distinct input changes rather than about preview features or unspecified runtime defaults.

Predict three files under one fixed contract

Our original contract is id STRING, qty INT, with a rescued-data column enabled. The following are three separate files arriving in the displayed order. The numeric values and event IDs are deliberately small so that you can inspect every record.

{"id":"e1","qty":2,"region":"west"}

{"id":"e2","qty":"many"}

{"id":"e3","QTY":4}

A conceptual configuration for a disposable practice workspace is:

incoming = (
    spark.readStream.format("cloudFiles")
    .option("cloudFiles.format", "json")
    .schema("id STRING, qty INT")
    .option("cloudFiles.schemaEvolutionMode", "rescue")
    .option("rescuedDataColumn", "_rescued_data")
    .option("readerCaseSensitive", "true")
    .load(source_path)
)

This is a preparation sketch, not an executed notebook or complete production writer. It assumes valid paths, permissions, and a compatible target. A writer would also need its own durable checkpoint configuration.

The Spark API options reference documents rescue behavior for schema/type mismatches and reader case sensitivity. Applying those rules gives these expected logical outputs, not captured runtime output:

File	Ordinary fields	Rescued field
1	id=e1, qty=2	region=west
2	id=e2, qty=NULL	qty=many
3	id=e3, qty=NULL	QTY=4

After each file, the logical schema remains the same: the two declared fields and the rescued-data column. The table shows the unexpected field/value content, not the exact serialized rescue JSON. The actual payload also carries source-file information, so compare parsed content rather than assuming a particular key order or path representation.

We ran five local fixture checks covering JSON round-trips and the intended type/case differences. They passed. Those checks do not validate Auto Loader parsing, checkpoint behavior, or target writes. Keeping that boundary explicit prevents a tiny Python script from becoming misleading evidence of a cloud integration test.

What changes if the mode is different?

Official behavior: The schema guide describes addNewColumns as updating schema information and then failing the stream when a new column is encountered; a restart uses the updated schema. rescue keeps the schema fixed. failOnNewColumns requires intervention rather than automatically updating it. none does not evolve the schema, and preserving unexpected fields depends on rescue configuration.

Do not change only the mode in the preceding sketch and claim an equivalent experiment. A fully supplied schema and an inferred schema have different supported combinations. For an additive-evolution exercise, start a separate disposable workload with inference or suitable hints, establish the initial schema, and introduce the new field afterward.

The original prediction to test would be: the first new field changes schema state before a successful continuation, while an existing field’s incompatible value needs separate handling. Record the schema snapshot, exception, restart, and resulting row. If you upload every fixture before initial inference, you have changed the experiment: the supposedly “new” field may already be visible during setup.

Is rescued data the same as malformed JSON?

No. A valid object containing "qty":"many" is structurally different from a truncated object with no closing brace. The first is our intentional contract mismatch. The second cannot be interpreted as the intended complete JSON record.

Official distinction: Databricks documents separate parser treatment for malformed records and rescued type mismatches. Do not infer that rescue makes every corrupt file acceptable. Check the parser mode and corrupt-record handling for your specific workload, using the schema documentation alongside the options reference.

An original interview follow-up is to ask what the downstream quantity metric should do with e2. Counting the row as an event may be appropriate; summing its quantity as zero without explanation may conceal a data-quality problem. Preserve the raw value, identify affected records, and define a correction rule with the data owner. Ingestion continuity and analytical correctness are separate success criteria.

Similarly, changing case-insensitive behavior might make QTY acceptable, but the decision belongs to the contract. Is casing accidental producer drift, or can two differently cased fields have distinct meanings? A setting should implement that decision, not substitute for it.

Diagnose three kinds of state before touching them

The following map is an original troubleshooting aid. Although schema and checkpoint data can share a physical directory, their responsibilities remain different.

Evidence to inspect	Question it answers
Source files and arrival history	What bytes and names were available?
Schema location and schema history	What input structure was learned?
Checkpoint and stream progress	What processing state can this workload resume?

Official operational guidance: Databricks’ production documentation describes checkpoint state and warns against lifecycle policies that remove it. Its cloud_files_state interface exposes discovered-file information using a checkpoint path.

For a missing-record scenario, start by locating the exact source object and the relevant run. Determine whether the file was discovered, whether parsing preserved the record, and whether the target write completed. If the record exists only in rescued data, re-discovering the same file will not by itself resolve the contract mismatch.

For a stopped-stream scenario, capture the exception and relevant configuration before changing anything. Compare the schema before and after the failure. If a new field has already been incorporated, a planned restart is a different response from rebuilding ingestion state. The diagnosis should explain why the selected action addresses the observed failure.

How are restart, overwrite, and rebuild different?

Official facts: The Auto Loader FAQ says a different checkpoint represents a new stream. It also explains that enabling cloudFiles.allowOverwrites can reprocess an entire changed file and requires duplicate handling; intermediate file versions are not guaranteed to be processed. Immutable files are the recommended general pattern.

Use the following original decision worksheet when discussing recovery:

Proposed action	Evidence needed first
Restart with existing state	Failure cause resolved; state and target remain compatible
Replace a source file	Overwrite policy, version expectations, duplicate strategy
Rebuild ingestion	Replay scope, new state, target reconciliation, retained inputs

Consider a corrected file containing 100 events, of which 99 were already accepted. Reprocessing the whole file into an append-only target could introduce repeated business events. Even if file discovery behaves correctly, that does not establish uniqueness by event ID. Define the business key and correction semantics separately.

For our fixtures, id is the proposed event key. A correction workflow might reconcile a corrected quantity for e2 against that key in a staging step. Before suggesting an upsert, ask whether IDs are globally unique, whether later versions carry an ordering field, and whether deletions must be represented. “Use MERGE” is incomplete without a rule for competing versions and duplicates within the incoming batch.

A rebuild needs an additional acceptance check. Compare the replayed result with an expected set of event IDs and values before making it authoritative. Record which original files are retained and how a missing or replaced object affects reproducibility. The cleanest new checkpoint cannot reconstruct bytes that no longer exist.

What would you monitor after the fix?

Monitor the failure you intended to resolve and the side effects your fix could introduce. For the three-file exercise, inspect rescued-row counts and the distribution of rescued field names. A sudden increase in QTY points to a different producer change than widespread unparseable quantity values.

The production guide identifies outstanding-file and outstanding-byte metrics for ingestion backlog. Pair those operational signals with data-quality evidence. A shrinking backlog is encouraging, but it does not prove that the quantity column is usable.

An original acceptance checklist for this scenario is: all three IDs accounted for, the expected rescue cases visible, no unexpected schema growth, and no duplicate business IDs after any correction replay. In a real environment, add target-specific checks and record the runtime and options. This is stronger evidence than observing that a notebook cell completed without an exception.

A useful final follow-up is to change one assumption at a time. Ask what happens if the producer sends the same event in a second, uniquely named file, or if the corrected record arrives after a downstream report has already been published. The first question tests whether you distinguish file identity from event identity. The second tests whether your repair includes consumers, rather than ending at the ingestion table. Explain what evidence would reveal the problem and who owns the correction. Neither question is answered simply by selecting an evolution mode.

Five practice questions for the underlying decisions

Practice question	Focus
Explain ETL schema changes and ensure integrity	Contract changes and downstream checks
Design a Conversation Log Ingestion Pipeline	Preserve inputs and account for failures
Design Incremental Load Process for Large Relational Table	Separate progress from business correctness
Detect Recent Duplicate Records with Bounded Memory	Define keys and retention boundaries
Prevent Duplicate Request Processing	Explain retry and deduplication behavior

Use the schema-integrity exercise to rehearse a concise answer: name the starting contract, predict the changed record, identify the evidence, and choose a recovery that preserves the state you still trust.

Sources and Further Reading

• Databricks AWS: Schema inference and evolution — modes and schema boundaries.
• Databricks AWS: Spark API options — parsing, rescue, and reader options.
• Databricks AWS: Auto Loader FAQ — overwrite and checkpoint semantics.
• Databricks AWS: Production workloads — state protection and monitoring.