Auto Loader vs COPY INTO: how would you ingest files on Databricks?

Detailed explanation

	COPY INTO	Auto Loader
Interface	SQL command, run from a job or warehouse	spark.readStream.format("cloudFiles"), or read_files in a streaming table
Remembers loaded files in	The target Delta table’s metadata	The stream’s checkpoint (RocksDB)
Discovery	Lists the source path each run	Directory listing, or file notifications / file events for large, busy directories
Schema	Fixed or inferred per run; mergeSchema option	Inferred, stored in schemaLocation, evolved by mode (addNewColumns default, rescue, failOnNewColumns, none)
Bad values	Fail or NULL depending on options	Kept in _rescued_data
Scale	Thousands of files per load	Millions of files
Re-run behaviour	Already-loaded files are skipped (unless force)	Files recorded in the checkpoint are skipped

Example

COPY INTO, rerunnable as often as you like:

CREATE TABLE IF NOT EXISTS prod.bronze.orders;

COPY INTO prod.bronze.orders
FROM '/Volumes/prod/landing/orders/'
FILEFORMAT = JSON
FORMAT_OPTIONS ('inferSchema' = 'true')
COPY_OPTIONS ('mergeSchema' = 'true');

Auto Loader, scheduled hourly as an incremental batch:

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

With addNewColumns, a file with a new column stops the stream after the new schema is recorded; a job retry restarts it with the column added. That is designed behaviour, so configure retries on the task.

Trade-offs and pitfalls

• The checkpoint is the memory. Deleting it, or pointing a new query at the same target with a new checkpoint, reloads every file. To reprocess one file, delete or fix the affected rows and load that file with a separate batch read, rather than resetting the stream.
• Files changed in place are not picked up again by default; landing zones should be write-once (new files, new names).
• Listing cost. Directory listing on a huge prefix is slow and costs storage API calls; switch to notifications or file events, or partition the landing path by date.
• Simplicity. For a few files a day and a SQL-only team, COPY INTO (or a streaming table with read_files) is simpler to operate.

Common mistakes

1. Using plain spark.read on the whole landing folder every run and deduplicating afterwards.
2. Sharing one checkpoint between two streams.
3. Ignoring _rescued_data, so values that failed to parse silently vanish from downstream metrics.
4. Treating the schema-change stop as an outage instead of configuring retries.