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
COPY INTO is simpler. For most new production file ingestion, Databricks recommends Auto Loader inside Lakeflow pipelines. Databricks decision guide01 · 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.
Incremental discovery
File discovery plus checkpoint state finds new files. It can read what already exists, then continue from there.
Change-aware ingestion
Inference, evolution and rescued data keep drifting sources from silently breaking a fixed contract.
Recoverable progress
It holds stream progress and file state. It is operational state, not a cache, so keep lifecycle rules away from it.
Managed production path
Pipelines manage schema and checkpoint details and add orchestration, expectations, autoscaling and an event log.
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.
| Question | Auto Loader | COPY INTO |
|---|---|---|
| Pattern | Incremental discovery, micro-batch or continuous | Idempotent SQL batch load into Delta |
| Scale | Built for very large and growing directories | Good for controlled file sets |
| Schema drift | Inference, evolution modes, hints, rescued data | Schema merge options, no rescued data workflow |
| State | Checkpoint tracks files | Load history skips files already loaded |
| Reloading a subset | More work | Easy with FILES or PATTERN |
| Language | Python, Scala, SQL via Lakeflow | SQL |
STREAM read_files() in a Lakeflow pipeline is the current direction, see the Lakeflow section below.A 30-second decision tree
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.
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.
| Need | Start with | Why |
|---|---|---|
| Standard Bronze ingestion | Auto Loader in Lakeflow pipelines | Managed state and pipeline operations |
| Custom Python or Scala processing | Auto Loader with Structured Streaming | Control over query, trigger and sink |
| Rare arrivals, minutes or hours of delay is fine | availableNow on a schedule or pipeline trigger | Compute stops after the backlog |
| Hard sub-second latency | Continuous with notifications | Costs 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).
| Mode | New column appears | Use when |
|---|---|---|
addNewColumns | Schema updates, stream stops with UnknownFieldException, restart resumes | Additive change expected and restarts are automated |
addNewColumnsWithTypeWidening | Same, plus compatible type widening | Types drift. Check runtime support first |
rescue | Schema fixed, extras go to _rescued_data | Must keep running while you investigate |
failOnNewColumns | Stream fails | Strict contract |
none | New columns ignored | Ignoring is a deliberate decision |
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.
Keeps both catch columns. Expectations count non-null values.
Rows where
_corrupt_record is null. Feeds Silver.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.
| Mode | How it works | Use when |
|---|---|---|
| Directory listing | Scans the source path. The default. | Small paths, one-time backfills, prototypes |
| Classic notification | Cloud queues and event notifications you set up | You need events but manage the cloud resources yourself |
| Managed file events | Databricks file events on an external location, cached | Most 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.
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
/Volumes/main/landing/ingest/ordersNo checkpoint or schema files here./Volumes/main/ops/autoloader/orders_checkpointNo lifecycle expiry.main.bronze.orders_raw- Treat the checkpoint as critical state. Durable, protected, outside the source folder.
- One checkpoint per stream. Never share one between two readers.
- Do not trust file order. Use an event timestamp or sequence number in the payload.
- Prefer immutable file names.
cloudFiles.allowOverwritesrereads whole files and cannot guarantee every intermediate version. - Be careful with cleanSource. It deletes or moves source files. Do not use it if another stream or client reads the same folder.
- Set maxFileAge. It stops file state growing forever. Databricks suggests 90 days at minimum, since a shorter window can re-read old files.
- Match the trigger to freshness. File arrival or
availableNowon a schedule for most feeds. Continuous only for a measured low-latency need. - 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
12 · Operate it
Monitoring Databricks Auto Loader and first fixes
| Signal | Meaning | Check first |
|---|---|---|
numFilesOutstanding | Files waiting | Rising over time means falling behind |
numBytesOutstanding | Bytes waiting | Many small files or a few large ones |
durationMs.latestOffset | Discovery time | High means consider file events |
durationMs.addBatch | Processing and commit time | Transforms, joins, sizing |
_rescued_data not null | Schema drift | Sample 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
| Symptom | Likely cause | Response |
|---|---|---|
| Stops after a new field arrives | addNewColumns raised UnknownFieldException | Confirm the change, restart with the same checkpoint, tell downstream owners |
| Old files read again | Checkpoint replaced or lost, or overwrites enabled | Check the checkpoint path and allowOverwrites, reconcile the target before cleanup |
| New files seen late | Big directory listing, file events misconfigured, trigger delay | Check discovery mode, external location events, trigger and metrics |
| Unexpected nulls | Type mismatch, nested change, bad serialization, case mismatch | Inspect rescued and corrupt fields and raw files before casting to Silver |
| Checkpoint error after cleanup | A lifecycle rule or a person removed state | Stop the cleanup, restore from backup, else reinitialize with reconciliation |
| Cost rises with no files | Always-on compute or repeated scans | File 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.
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
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
- What is Auto Loader?
- Auto Loader best practices
- Schema inference and evolution
- Auto Loader with file events
- Production configuration
- Monitor and observe Auto Loader
- Auto Loader FAQ
- Ingest data from cloud object storage
- Get started using COPY INTO
- Build an ETL pipeline with Lakeflow
- COPY INTO reference
- Load data in Lakeflow pipelines
Related on Ultimate Info Guide
- Databricks Data Engineer Associate Practice Test — exam domains, original questions and explanations.
- Databricks Interview Questions — production scenarios, SQL and PySpark drills.
- Databricks Free Edition vs Free Trial — learning environments, limits and costs.
- Databricks Certification Guide — exams, costs and career paths.
Independent practitioner guide, not an official Databricks publication. Check feature availability and cost in your own workspace.




