Data Ingestion and Loading · Topic 2 of 3

COPY INTO and Auto Loader

Databricks DEA: COPY INTO idempotent file loads, Auto Loader cloudFiles options, directory listing vs file notification, schema inference and evolution.

5 min

In a nutshell

COPY INTO is idempotent; 'force' = 'true' reloads files and can create duplicates.
COPY INTO suits thousands of files; Auto Loader suits millions and has better schema evolution.
Auto Loader uses format("cloudFiles"), a cloudFiles.schemaLocation and a checkpoint.

What it is

COPY INTO and Auto Loader both load files incrementally from cloud object storage (S3, ADLS, GCS) or a Unity Catalog volume into a Delta table. Both remember which files they already loaded, so a rerun picks up only new files.

COPY INTO is a single SQL statement that suits thousands of files. Auto Loader is a Structured Streaming source, cloudFiles, that scales to millions of files and handles schema change better.

How it works

COPY INTO

COPY INTO main.bronze.orders
FROM '/Volumes/main/raw/landing/orders/'
FILEFORMAT = JSON
FORMAT_OPTIONS ('multiLine' = 'true', 'mergeSchema' = 'true')
COPY_OPTIONS ('mergeSchema' = 'true');

COPY INTO is idempotent: files already loaded are skipped on later runs, so the statement can run on a schedule. Key clauses:

Clause or option	Effect
`FILEFORMAT`	CSV, JSON, XML, Avro, ORC, Parquet, text or binary
`PATTERN`	Glob pattern that selects files, such as `'*.csv'`
`FILES`	Explicit list of file names, up to 1000; cannot be combined with `PATTERN`
`COPY_OPTIONS ('mergeSchema' = 'true')`	Lets the target schema evolve to match new columns
`COPY_OPTIONS ('force' = 'true')`	Disables idempotency and reloads files already loaded
`VALIDATE`	Checks parsing and schema without writing data

You can create an empty table with no columns and let mergeSchema infer the schema on the first load. Unity Catalog needs read access to the source path, through an external location or a volume, before the statement runs.

Auto Loader

(spark.readStream.format("cloudFiles")
  .option("cloudFiles.format", "json")
  .option("cloudFiles.schemaLocation", "/Volumes/main/raw/chk/orders")
  .load("/Volumes/main/raw/landing/orders/")
  .writeStream.option("checkpointLocation", "/Volumes/main/raw/chk/orders")
  .trigger(availableNow=True)
  .toTable("main.bronze.orders"))

The checkpoint records which files were processed, which gives exactly once ingestion. With trigger(availableNow=True) the stream processes every new file and then stops, which is how Auto Loader runs as a scheduled batch in a Lakeflow Job. Without that trigger it runs continuously.

File detection modes

Mode	How it finds files	Notes
Directory listing (default)	Lists the input directory	No extra permissions; works everywhere
File notification	Subscribes to cloud notification and queue services, `cloudFiles.useNotifications`	Scales better for large directories
File notification with file events	Uses file events on the external location, `cloudFiles.useManagedFileEvents`	Recommended for most workloads; no extra setup per stream

Any mode works with a batch (available now) or a continuous trigger. You can switch modes across restarts and keep exactly once processing.

Schema inference and enforcement

cloudFiles.schemaLocation is where Auto Loader stores the inferred schema and its changes. For JSON, CSV and XML, columns are inferred as strings unless cloudFiles.inferColumnTypes is true; Parquet and Avro use the types in the file. cloudFiles.schemaHints forces types for named columns.

Values that do not match the schema, such as a wrong type, go to the _rescued_data column instead of being lost. This is how Auto Loader enforces a schema without dropping data.

Schema evolution modes

`cloudFiles.schemaEvolutionMode`	On a new column
`addNewColumns` (default with no schema provided)	Stream stops; the restart includes the new column
`rescue`	Schema never changes; new column data goes to `_rescued_data`
`failOnNewColumns`	Stream fails and stays failed until the schema or file is fixed
`none` (default when a schema is provided)	New columns are ignored

In production, a Lakeflow Job with retries restarts the stream automatically after an addNewColumns stop.

How the exam asks about this

"A scheduled COPY INTO runs again over the same folder. What happens to old files?" They are skipped; only new files load.
"A source adds columns and the stream stopped with an unknown field error." Expected with addNewColumns; restart and the new column is included.
"Keep the schema fixed but do not lose unexpected columns." Use rescue mode, which writes them to _rescued_data.

What to remember for the exam

COPY INTO is idempotent; 'force' = 'true' reloads files and can create duplicates.
COPY INTO suits thousands of files; Auto Loader suits millions and has better schema evolution.
Auto Loader uses format("cloudFiles"), a cloudFiles.schemaLocation and a checkpoint.
trigger(availableNow=True) turns Auto Loader into an incremental batch that stops when done.
Directory listing is the default; file notification with file events is recommended at scale.
Default evolution mode addNewColumns stops the stream on a new column and the restart picks it up.

Watch out

Each Auto Loader stream needs its own checkpoint. Sharing a checkpoint between two sources, or deleting it, breaks the record of processed files and causes missed or duplicate loads.

Common mistakes

Reading a stopped stream as a bug. With addNewColumns, stopping on a new column is the designed behavior. The fix is a restart, ideally by job retries.

Expecting typed columns from JSON. Without inferColumnTypes or schema hints, JSON and CSV columns arrive as strings.

Using force to fix a failed load. Failed files were never recorded as loaded, so a normal rerun loads them. force reloads everything and duplicates data.

Check yourself

Why can COPY INTO run on a schedule against the same folder?
It is idempotent and skips files it already loaded, so each run loads only new files unless force is set to true.
What does the rescue schema evolution mode do with a new column?
It keeps the schema unchanged and writes the new column's values to the _rescued_data column, without failing the stream.
Which Auto Loader setting makes a stream process all new files and then stop?
The trigger availableNow=True, which lets a scheduled job run Auto Loader as an incremental batch.

Topic 2 of 3 in Data Ingestion and Loading

Test yourself on Data Ingestion and Loading

The full Databricks Data Engineer Associate question bank has 598 questions, with timed practice exams and an explanation for every answer.

Get Full Access Try Sample Exam

This study guide is independent and is not affiliated with, endorsed by, or sponsored by Databricks. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.