Ultimate Info Guide · Databricks · Data engineering

Databricks Auto Loader Tutorial: Production Ingestion, Schema Evolution and COPY INTO

How to ingest files from cloud storage incrementally, keep the pipeline alive when producers change their data, and decide when plain COPY INTO is the better tool. Code is in Python and SQL, with Lakeflow pipelines as the main path.

Reviewed November 18, 2026 · Works with S3, ADLS, GCS and Unity Catalog volumes

Short answer: files arrive all day, schemas drift, and the job must restart safely: use Auto Loader. A bounded SQL batch over a modest file set: COPY INTO is simpler. For most new production file ingestion, Databricks recommends Auto Loader inside Lakeflow pipelines. Databricks decision guide
Version note: The examples use Unity Catalog volumes and current Lakeflow names. Swap the sample catalog, schema, volume and cloud paths for your own. Some settings depend on cloud, workspace setup and Databricks Runtime, so check the linked docs for your deployment before you promote code.

01 · The concept

What is Databricks Auto Loader?

Auto Loader is the cloudFiles source for Structured Streaming. Point it at a storage path and it finds new files, reads them, and records progress in a checkpoint so a restart picks up where it stopped. It works with JSON, CSV, XML, Parquet, Avro, ORC, text and binary files.

It fits patterns that repeated batch reads handle badly: a vendor dropping hourly CSVs, an app writing JSON events, several systems landing Parquet extracts through the day. It does not have to run forever. With availableNow it processes whatever is waiting and then stops, which keeps compute cost down for hourly or daily feeds.

cloudFiles

Incremental discovery

File discovery plus checkpoint state finds new files. It can read what already exists, then continue from there.

Schema

Change-aware ingestion

Inference, evolution and rescued data keep drifting sources from silently breaking a fixed contract.

Checkpoint

Recoverable progress

It holds stream progress and file state. It is operational state, not a cache, so keep lifecycle rules away from it.

Lakeflow

Managed production path

Pipelines manage schema and checkpoint details and add orchestration, expectations, autoscaling and an event log.

Terminology: Auto Loader is not a job that must run forever. Run it continuously, or process what is waiting with availableNow. Freshness needs and compute cost decide which.

02 · Pick the tool

Advertisements

Auto Loader vs COPY INTO

Databricks gives a rule of thumb: COPY INTO for thousands of files over time, Auto Loader when you may reach millions. Treat it as guidance, not a hard limit.

QuestionAuto LoaderCOPY INTO
PatternIncremental discovery, micro-batch or continuousIdempotent SQL batch load into Delta
ScaleBuilt for very large and growing directoriesGood for controlled file sets
Schema driftInference, evolution modes, hints, rescued dataSchema merge options, no rescued data workflow
StateCheckpoint tracks filesLoad history skips files already loaded
Reloading a subsetMore workEasy with FILES or PATTERN
LanguagePython, Scala, SQL via LakeflowSQL
If the source system has a managed Lakeflow Connect connector, look at that before writing your own file ingestion. For SQL teams that need scale, STREAM read_files() in a Lakeflow pipeline is the current direction, see the Lakeflow section below.

A 30-second decision tree

Files land in storageS3, ADLS, GCS or a volume
How many over time?Thousands or millions
Need incremental scale?Drift or frequent arrivals
Prefer SQL batch?Known set, simple reloads
Pick the patternAuto Loader or COPY INTO
Auto Loader: growing file counts, evolving schemas, checkpointed state, event-driven ingestion.
COPY INTO: a manageable file set, SQL-first, selective reloads, simple scheduled batch.

The tree simplifies the choice. Latency, source mutability, permissions, table design and team skills can change the answer.

03 · Architecture

How the pieces fit

Running example: an order system writes JSON files to storage. Auto Loader reads them into a raw Bronze table, then a separate step validates and deduplicates for Silver.

Landing zoneJSON, CSV or Parquet files
Auto LoaderDiscovery, schema, checkpoint
BronzeRaw rows plus file metadata
SilverTyped, deduplicated, quarantined
OperateJobs, alerts, Unity Catalog

Before any code: one folder per producer, immutable and uniquely named files, one checkpoint per stream, and storage access through Unity Catalog volumes or external locations, never keys pasted into a notebook.

04 · Watch it

Advertisements

Official Databricks videos

All three come from the official Databricks YouTube channel. Start with the short comparison, then watch the engineering session when you plan a real pipeline.

Auto Loader and COPY INTO: two minute overview

The clip Databricks embeds on its own ingestion page. Quick way to see the difference before you pick a tool.

Databricks · about 2 minutes

Data Ingestion using Auto Loader

Incremental processing of JSON, CSV and Parquet from cloud storage. Older names in the narration map to current Lakeflow features.

Databricks · April 2023 · 5 minutes

Lakeflow Connect: Smarter, Simpler File Ingestion With the Next Generation of Auto Loader

Data + AI Summit 2025 session from the Auto Loader engineers: observability, schema management, new formats and performance.

Databricks · uploaded July 2025 · Data + AI Summit session

05 · Hands-on

Build your first Databricks Auto Loader pipeline

Assume JSON files, one object per line, land in a volume. Paths and table names are examples, so swap in your own.

source_path = "/Volumes/main/landing/ingest/orders"
schema_path = "/Volumes/main/ops/autoloader/orders_schema"
checkpoint_path = "/Volumes/main/ops/autoloader/orders_checkpoint"

orders = (
    spark.readStream
        .format("cloudFiles")
        .option("cloudFiles.format", "json")
        .option("cloudFiles.schemaLocation", schema_path)
        .option("cloudFiles.schemaEvolutionMode", "addNewColumns")
        .option("cloudFiles.inferColumnTypes", "true")
        .load(source_path)
)

(orders.writeStream
    .option("checkpointLocation", checkpoint_path)
    .trigger(availableNow=True)
    .toTable("main.bronze.orders_raw"))

Two pieces of state matter here: schema history and stream progress. Without inferColumnTypes, JSON and CSV columns come in as strings. To keep traceability, add _metadata.file_path, _metadata.file_modification_time and an ingest timestamp to every row, so you can always answer which file produced a row.

Keep lineage on every row

from pyspark.sql.functions import current_timestamp, col

orders_with_lineage = (orders
    .withColumn("_source_file", col("_metadata.file_path"))
    .withColumn("_source_modified_at", col("_metadata.file_modification_time"))
    .withColumn("_ingested_at", current_timestamp()))

Store the metadata fields you need for debugging, audit and replay, not every field blindly. Decide how rescued data, malformed rows, timestamps and business keys sit in the Bronze schema.

Drop a new file in, run again with the same checkpoint, and check the stream progress and row counts. A finished notebook cell is not proof the file was read.

Validate the output

SELECT COUNT(*) AS row_count,
       COUNT(DISTINCT order_id) AS distinct_orders,
       MIN(order_ts) AS first_order_ts,
       MAX(order_ts) AS last_order_ts
FROM main.bronze.orders_raw;

Row counts alone cannot prove correctness when files can repeat business events. Compare against a source manifest, account for rejected rows, and test duplicates.

06 · Recommended path

Databricks Auto Loader inside Lakeflow pipelines

Lakeflow manages schema and checkpoint locations for you and adds expectations, autoscaling and an event log. Do not copy the manual checkpoint options from the notebook example into a pipeline.

from pyspark import pipelines as dp

@dp.table(name="bronze_orders")
def bronze_orders():
    return (spark.readStream.format("cloudFiles")
        .option("cloudFiles.format", "json")
        .load("/Volumes/main/landing/ingest/orders"))
CREATE OR REFRESH STREAMING TABLE bronze_orders
AS SELECT * FROM STREAM read_files(
  '/Volumes/main/landing/ingest/orders',
  format => 'json');

Use Python when you need PySpark logic, SQL when the work is relational. Both get incremental file tracking. A plain non-streaming read does not.

06b · More control

When direct Structured Streaming fits better

Lakeflow is the usual start. Use the source directly when you need custom stream lifecycle, an existing streaming app, a special sink, or APIs the pipeline framework does not expose. You then own checkpoint and schema locations, restarts, monitoring, orchestration and permissions. Take that on only when it solves a real need.

NeedStart withWhy
Standard Bronze ingestionAuto Loader in Lakeflow pipelinesManaged state and pipeline operations
Custom Python or Scala processingAuto Loader with Structured StreamingControl over query, trigger and sink
Rare arrivals, minutes or hours of delay is fineavailableNow on a schedule or pipeline triggerCompute stops after the backlog
Hard sub-second latencyContinuous with notificationsCosts more, so prove the latency need first

availableNow is not a full reload. With the same checkpoint it stays incremental and keeps its place for the next run.

07 · Schema

Inference and evolution modes

Auto Loader samples files on the first run (by default the first 50 GB or 1,000 files) and stores the result in the schema location. Parquet and Avro carry types. JSON, CSV and XML do not, so use cloudFiles.schemaHints for the fields you care about, such as order_id STRING, amount DECIMAL(18,2).

ModeNew column appearsUse when
addNewColumnsSchema updates, stream stops with UnknownFieldException, restart resumesAdditive change expected and restarts are automated
addNewColumnsWithTypeWideningSame, plus compatible type wideningTypes drift. Check runtime support first
rescueSchema fixed, extras go to _rescued_dataMust keep running while you investigate
failOnNewColumnsStream failsStrict contract
noneNew columns ignoredIgnoring is a deliberate decision
Default trap: with no schema given, the default mode is addNewColumns. Once you supply a schema, it becomes none.

08 · Keep Bronze honest

Bad data, rescued data and Variant

_rescued_data holds fields that did not match the schema. _corrupt_record holds rows that could not be parsed, and you must switch it on. In a healthy feed both stay null, so a value in either one is your earliest sign a producer changed something.

Bronze stream
Keeps both catch columns. Expectations count non-null values.
Clean view
Rows where _corrupt_record is null. Feeds Silver.
Quarantine table
Bad rows for the data owner to review.
import dlt

@dlt.table(name="bronze_orders")
@dlt.expect("no_rescued_data", "_rescued_data IS NULL")
@dlt.expect("no_corrupt_records", "_corrupt_record IS NULL")
def bronze_orders():
    return (spark.readStream.format("cloudFiles")
        .option("cloudFiles.format", "json")
        .option("cloudFiles.schemaHints", "_corrupt_record string")
        .option("columnNameOfCorruptRecord", "_corrupt_record")
        .load("/Volumes/main/landing/ingest/orders"))

@dlt.table(name="orders_quarantine")
def orders_quarantine():
    return dlt.read_stream("bronze_orders").where("_corrupt_record IS NOT NULL")

Databricks prefers columnNameOfCorruptRecord over badRecordsPath, because the path option can miss records in a race. This example uses the dlt import as the docs do.

When the shape keeps changing: Variant

Land the whole record in one Variant column, then promote the fields you use into typed columns in Silver. Queries on Variant are slower than typed columns, so keep it to Bronze.

.option("singleVariantColumn", "data")

SELECT data:order_id::string AS order_id,
       data:amount::decimal(18,2) AS amount
FROM main.bronze.vendor_events;

09 · Scale and cost

Directory listing, notifications and file events

Directory listing scans the folder to find new files and gets slower and costlier as the folder grows. Notification modes learn about files from cloud events instead. Managed file events do this through Unity Catalog and are the usual pick for large production loads.

Cloud LIST cost (Databricks ranking, relative, not measured)
Directory listing
Highest
Classic notification
Medium
Managed file events
Lowest
Setup effort
Directory listing
None
Classic notification
21+ cloud options
Managed file events
One-time grant
ModeHow it worksUse when
Directory listingScans the source path. The default.Small paths, one-time backfills, prototypes
Classic notificationCloud queues and event notifications you set upYou need events but manage the cloud resources yourself
Managed file eventsDatabricks file events on an external location, cachedMost large production loads
.option("cloudFiles.useManagedFileEvents", "true")
  • Needs Databricks Runtime 14.3 LTS or above, and file events enabled on the external location first.
  • One queue per external location, shared by all streams. Create an external volume per load path and use the volume path.
  • Expect a full listing at the first run, on migration, after more than seven days idle, and every 24 hours after the last scan while a stream uses file events.
  • Pick one mode per stream. Community reports say enabling file events while an old notification stream still reads the queue leaves that stream silent. Migrate the stream when you enable the location.

10 · SQL batch

COPY INTO caveats

CREATE TABLE IF NOT EXISTS main.bronze.orders_batch (
  order_id STRING, customer_id STRING, order_ts TIMESTAMP,
  amount DECIMAL(18,2), status STRING);
COPY INTO main.bronze.orders_batch
FROM '/Volumes/main/landing/ingest/orders'
FILEFORMAT = JSON
FORMAT_OPTIONS ('multiLine' = 'false');

It skips files it already loaded, supports FILES and PATTERN, and is easy to retry. COPY_OPTIONS ('force' = 'true') reloads everything and will create duplicates unless you plan for them.

Modified files are not reloaded. A path that was loaded once counts as loaded, even if the file changed. Use a new path or a separate backfill.

If schema merging fits your batch, set it explicitly and test the incoming data. Reader options depend on the file format.

For SQL users who need scalable, robust incremental ingestion, Databricks points to streaming tables. So the choice is not just “Auto Loader for Python, COPY INTO for SQL”. Consider STREAM read_files() in a Lakeflow pipeline when you want SQL with incremental scale. COPY INTO reference

11 · Hardening

Decisions to make before go-live

Landing volumeProducers write, the stream reads./Volumes/main/landing/ingest/ordersNo checkpoint or schema files here.
Ops volumeOne schema and checkpoint pair per stream./Volumes/main/ops/autoloader/orders_checkpointNo lifecycle expiry.
Bronze tableRaw rows plus file path, file time, ingest time.main.bronze.orders_raw
  1. Treat the checkpoint as critical state. Durable, protected, outside the source folder.
  2. One checkpoint per stream. Never share one between two readers.
  3. Do not trust file order. Use an event timestamp or sequence number in the payload.
  4. Prefer immutable file names. cloudFiles.allowOverwrites rereads whole files and cannot guarantee every intermediate version.
  5. Be careful with cleanSource. It deletes or moves source files. Do not use it if another stream or client reads the same folder.
  6. Set maxFileAge. It stops file state growing forever. Databricks suggests 90 days at minimum, since a shorter window can re-read old files.
  7. Match the trigger to freshness. File arrival or availableNow on a schedule for most feeds. Continuous only for a measured low-latency need.
  8. Plan backfills separately. Do not delete the production checkpoint to replay. Use a separate stream and checkpoint, write to staging, check, then merge.

Recovery playbook

Alert firesStream failed or backlog grew
ClassifySchema, permissions, source or compute
Preserve stateKeep checkpoint and source files
Fix and restartSame checkpoint for normal recovery
Prove recoveryCounts, backlog, sample rows

12 · Operate it

Monitoring Databricks Auto Loader and first fixes

SignalMeaningCheck first
numFilesOutstandingFiles waitingRising over time means falling behind
numBytesOutstandingBytes waitingMany small files or a few large ones
durationMs.latestOffsetDiscovery timeHigh means consider file events
durationMs.addBatchProcessing and commit timeTransforms, joins, sizing
_rescued_data not nullSchema driftSample the payload, call the producer
SELECT * FROM cloud_files_state('/Volumes/main/ops/autoloader/orders_checkpoint');

Files read twice usually mean a replaced checkpoint or allowOverwrites. Slow discovery on a big folder points to directory listing. A stream that stops after a new field is addNewColumns doing its job: confirm the change and restart with the same checkpoint.

Common incidents

SymptomLikely causeResponse
Stops after a new field arrivesaddNewColumns raised UnknownFieldExceptionConfirm the change, restart with the same checkpoint, tell downstream owners
Old files read againCheckpoint replaced or lost, or overwrites enabledCheck the checkpoint path and allowOverwrites, reconcile the target before cleanup
New files seen lateBig directory listing, file events misconfigured, trigger delayCheck discovery mode, external location events, trigger and metrics
Unexpected nullsType mismatch, nested change, bad serialization, case mismatchInspect rescued and corrupt fields and raw files before casting to Silver
Checkpoint error after cleanupA lifecycle rule or a person removed stateStop the cleanup, restore from backup, else reinitialize with reconciliation
Cost rises with no filesAlways-on compute or repeated scansFile arrival trigger, availableNow, file events, slower schedule

12b · Governance

Unity Catalog, cloud access and security

Auto Loader cannot read a path unless the running identity can. Use Unity Catalog volumes and external locations with controlled storage credentials, and keep access keys and SAS tokens out of notebooks.

  • Give the pipeline identity only the storage and table privileges it needs.
  • Keep landing, operations and curated storage in separate zones.
  • Make schema and checkpoint paths writable by the stream identity and closed to other workloads.
  • Use service principals for production automation where your setup supports it.
  • Audit grants on catalogs, schemas, volumes and external locations. A table grant does not give access to the source files.
Lakeflow manages pipeline state, but identity, retention, network access and audit still need your platform and security team.

12c · Real decisions

Four scenarios and the design I would pick

A: A retailer gets 10,000 small sales files every hour

Start: Auto Loader in a Lakeflow pipeline with file events on the external location, a file arrival trigger if freshness allows, file metadata kept, and a Bronze to Silver quality step. The load is recurring and will grow, so tracked state beats repeated listings. Watch file backlog and event queue health. Do not keep a cluster running just because files come hourly. Compare an event trigger or availableNow schedule with the freshness target.

B: Finance sends one weekly folder of 600 Parquet files

Start: COPY INTO from a scheduled job if the schema is stable. A bounded file set with an idempotent batch is simpler to run than a stream. Revisit when volume grows, drift becomes frequent, or latency and monitoring needs justify Auto Loader.

C: A vendor adds a nested JSON field without warning

Start: choose an evolution mode on purpose. If the field is welcome, use an additive mode and make sure the orchestrator restarts after the schema update. If the contract is strict, fail and send the change to the data owner. Check rescued data, compare source payloads, test downstream readers, and record when the field first appeared.

D: A producer overwrites yesterday’s file with corrections

Start: fix the contract. Ask for versioned objects or a uniquely named correction file, then merge downstream on a business key and a version column. Enabling overwrites rereads the whole file but cannot promise every intermediate version, and it defines no business rule for correcting old rows.

13 · Release

Go-live checklist

✓ Source folder holds one file family
✓ Overwrite behavior documented
✓ Grants for volumes and tables in place
✓ Schema and checkpoint paths protected
✓ One checkpoint per stream
✓ Evolution mode matches the contract
✓ Discovery mode fits scale and cost
✓ Rescued and corrupt rows have an owner
✓ Backfill procedure tested
✓ Backlog alerts configured
✓ Trigger type meets the freshness SLA
✓ Source to Bronze reconciliation is automated

Test a duplicate arrival, a schema change, a malformed file, a late file, a restart and a backfill. A clean first run proves very little.

14 · Questions

Databricks Auto Loader FAQs

Is Auto Loader the same as Structured Streaming?

No. It is a Databricks source for Structured Streaming that adds file discovery, schema inference and evolution on top of the normal checkpoint model.

Does it guarantee exactly-once processing?

Into Delta Lake under the documented conditions, yes. Do not assume it for outside side effects, overwritten files or a deleted checkpoint.

Does it process files in arrival order?

No. Carry an event timestamp or sequence number and order downstream.

schemaLocation or checkpointLocation?

The first holds schema history, the second holds stream progress. Lakeflow pipelines manage both for you.

Can I delete the checkpoint to reprocess everything?

Not on the production stream. A new checkpoint makes old files look new. Run a separate backfill stream.

When should I use Variant?

When the payload has no stable shape. Move the fields you filter and join on into typed Silver columns.

Should it run continuously?

Only if latency requires it. For hourly or daily files, use a file arrival trigger or availableNow on a schedule.

Should I use Auto Loader or COPY INTO?

Auto Loader when files arrive incrementally at scale, schemas move, or you need checkpointed streaming. COPY INTO for a simple SQL batch over a manageable set, or selective reloads. Thousands versus millions of files is guidance, not a hard line.

Can Auto Loader handle schema changes?

Yes, through inference, hints, evolution modes and rescued data. Choose accept, rescue or fail based on your contract. With addNewColumns the stream stops after evolving and must restart.

Directory listing or file events?

Listing is easy to start with and fine for small paths or backfills. Databricks recommends file events for most production loads, since they avoid repeated full scans. They need the external location and cloud setup done first.

Does it work with S3, ADLS and Google Cloud Storage?

Yes, and with Unity Catalog volumes. Confirm the URI form, credentials and cloud-specific settings in your workspace.

Can I use Auto Loader from SQL?

Yes. In a Lakeflow pipeline, use a streaming table with STREAM read_files(). For a plain SQL batch, use COPY INTO.

15 · Sources

Official references

Related on Ultimate Info Guide

Independent practitioner guide, not an official Databricks publication. Check feature availability and cost in your own workspace.

Scroll to Top