Auto Loader vs COPY INTO vs Lakeflow Connect: What the Databricks Exam Wants

The decision rule objective 2.6 is testing

Data Ingestion and Loading is 21 per cent of the exam. Objective 2.6 asks you to prioritise between Auto Loader, Lakeflow Connect, partner connectors and other ingestion methods based on volume, frequency, data types and governance needs. A scenario usually gives you those signals in a sentence or two and expects you to rank the options the way Databricks would.

Start with the source. Files landing in cloud object storage point to Auto Loader or COPY INTO. A SaaS application such as Salesforce, HubSpot, Jira or Workday, or an operational database such as MySQL, PostgreSQL or SQL Server, points to Lakeflow Connect, whose database connectors read changes through change data capture.

For files, the tiebreak is volume over time, and Databricks puts it plainly: if you are going to ingest files in the order of thousands over time, you can use COPY INTO, and if you expect millions or more, use Auto Loader. The words to watch are over time. A small daily drop into a directory that keeps growing is judged on the total the directory will reach, so read the scenario for how long the source runs as well as how big each batch is.

What Auto Loader records in its checkpoint

Auto Loader is a Structured Streaming source called cloudFiles that processes new files incrementally as they arrive in cloud storage. As it discovers files, it stores their metadata in a scalable key-value store, RocksDB, inside the stream's checkpoint location, and that record is what lets Databricks guarantee each file is processed exactly once. Databricks says Auto Loader can process billions of files and ingest millions of files per hour in near real time.

Two exam behaviours follow from the checkpoint. A stream that restarts after a failure picks up from the files it has already recorded, so it neither skips nor repeats them. And running Auto Loader on a schedule keeps the same incremental behaviour: each run reads only the files that arrived since the last one. It reads JSON, CSV, XML, Parquet, Avro, ORC, text and binary files, so file format is seldom the signal that decides a question.

The five schema evolution modes, and which ones stop the stream

Schema questions catch people out because Auto Loader's default behaviour looks like a failure. Setting cloudFiles.schemaLocation enables schema inference and evolution, and the evolution mode decides what happens when a file arrives with a column the schema has never seen.

addNewColumns is the default when you let Auto Loader infer the schema. When a new column appears, Auto Loader adds it to the schema and then fails the stream with an UnknownFieldException. Restarting the stream resumes processing with the updated schema. The failure is by design, and Databricks recommends running Auto Loader streams in Lakeflow Jobs so they restart automatically. addNewColumnsWithTypeWidening behaves the same way and also widens supported types, such as int to long.

The other three modes each do something different. rescue never evolves the schema and keeps the stream running, recording the unexpected data in the rescued data column. failOnNewColumns fails the stream and keeps it failed until you update the provided schema or remove the offending file. none ignores new columns, and it is the default when you supply a schema yourself. When Auto Loader infers the schema it adds a _rescued_data column automatically, and schema hints apply only when you have left the schema to inference.

The common trap sits between the default and rescue. A stream that stopped when a new column appeared and then succeeded on restart is the default mode doing its job. A stream that keeps running with an unchanged schema is rescue, and you get rescue only by setting it.

Directory listing versus file notification

Auto Loader finds new files in one of two ways. Directory listing mode is the default, and it lists the input path to see what has arrived. Databricks recommends file notification mode using file events for most workloads, and file events need Databricks Runtime 14.3 LTS or above.

Treat the choice as a scale signal. A question about a large or fast-growing input directory that asks how to make file discovery more efficient is pointing at file notification with file events. A modest path with nothing special about it is fine on the default. Objective 2.3 names both modes, so knowing which one is the default is in scope.

When COPY INTO is the better answer

COPY INTO is a SQL command that loads data from a file location into a Delta table. It is retriable and idempotent: files it has already loaded are skipped on later runs, so rerunning the same statement over the same path adds no duplicate rows. It loads from Unity Catalog volumes and can merge schema changes through its options.

Databricks' own comparison gives COPY INTO two clear wins. The first is volume, meaning thousands of files over time rather than millions. The second is re-uploads: loading a subset of re-uploaded files is easier to manage with COPY INTO, because Auto Loader makes it harder to reprocess a chosen subset of files.

The same comparison explains why Auto Loader wins elsewhere. It has better primitive data types around schema inference and evolution, which matters when a schema changes often, and it is less expensive and more efficient at scale. For SQL users, Databricks calls CREATE STREAMING TABLE the recommended alternative to COPY INTO. So when an option offers COPY INTO as the SQL answer for a directory that grows without end, look for a streaming table among the other options.

Lakeflow Connect: managed versus standard connectors

Lakeflow Connect has two kinds of connector, and the exam expects you to know how much each one does for you. Managed connectors produce ingestion pipelines that are governed by Unity Catalog and powered by serverless compute and Lakeflow pipelines. They cover SaaS applications and databases, read database changes through change data capture, and retry a failed run automatically with exponential backoff. The engineer sets up the connection, the ingestion pipeline and the destination tables; the connector handles incremental capture and retries.

Standard connectors trade some of that automation for broader source support and more customisation. Databricks' guidance is to start with the most managed layer and drop down a layer only when it falls short, for example when it does not support your source.

That ordering answers most Lakeflow Connect questions. When a scenario names a supported SaaS application or database and asks for the least operational effort, the managed connector is the answer. Custom notebook code with a JDBC or REST client, which objective 2.5 covers, belongs lower down: use it when no connector reaches the source or the team needs control a connector does not offer.

Reading the scenario: the words that decide it

Most ingestion questions plant one or two phrases that settle the answer. A growing directory, millions of files or near real time points to Auto Loader. Thousands of files, a subset of re-uploaded files or a SQL-only team points to COPY INTO, unless a streaming table is on offer. A named SaaS application, an operational database, change data capture or minimal maintenance points to a Lakeflow Connect managed connector.

Schema wording works the same way. A stream that failed and then succeeded on restart is the default addNewColumns mode. A stream that keeps running and captures the unexpected values is rescue. A stream that refuses to continue until someone updates the schema is failOnNewColumns. Match the phrase to the documented behaviour, and pick the answer that describes what Databricks does over the one that merely sounds sensible.