09. Auto Loader
1. Why Auto Loader exists
Cloud object storage can contain millions of files arriving continuously, out of order and with changing schemas.
A naive ingestion pattern repeatedly lists the whole directory:
LIST EVERYTHING
|
v
COMPARE WITH PREVIOUS RUN
|
v
READ NEW FILES
As object counts grow, discovery itself becomes an architectural concern.
Auto Loader provides incremental file discovery and ingestion for cloud object storage.
The core model is:
CLOUD STORAGE
|
v
FILE DISCOVERY
|
v
AUTO LOADER
|
+---- CHECKPOINT / FILE STATE
|
+---- SCHEMA STATE
|
v
DELTA / BRONZE
2. What Auto Loader is
Auto Loader is an incremental file source for Spark Structured Streaming.
It is designed for:
- cloud object storage;
- continuously arriving files;
- batch-like file drops;
- streaming file ingestion;
- very large file populations.
It is not a message broker, database CDC engine, replacement for Delta Lake, or complete workflow orchestrator.
Place it in the ingestion layer.
3. The four-part mental model
Remember:
DISCOVERY
|
v
STATE
|
v
SCHEMA
|
v
SINK
Discovery
How do I find new files?
State
How do I know which files were already processed?
Schema
What happens when the source changes?
Sink
Where does trusted data land?
If these four decisions are clear, the ingestion design is usually on solid ground.
4. Basic Python pattern
A standard Auto Loader source looks like:
df = (
spark.readStream
.format("cloudFiles")
.option("cloudFiles.format", "json")
.load("/path/to/source")
)
Auto Loader then becomes the streaming source for downstream processing.
5. Auto Loader with Lakeflow
A common modern architecture is:
CLOUD STORAGE
|
v
AUTO LOADER
|
v
LAKEFLOW STREAMING TABLE
|
v
BRONZE
|
v
QUALITY
|
v
SILVER
|
v
GOLD
This combines scalable discovery with declarative pipeline management.
6. File discovery modes
Auto Loader supports two broad discovery models:
- directory listing;
- file notification / file events.
Directory listing
The service discovers new files by listing the source location.
Advantages:
- simple;
- minimal cloud-side setup;
- quick to start.
Trade-off:
- repeated listing becomes less attractive as object counts grow.
File events
The service uses file-event information to discover new objects incrementally.
Advantages:
- more scalable discovery;
- lower discovery overhead;
- better fit for large object populations.
Databricks currently recommends managed file events for suitable new production workloads.
7. Managed file events
The modern pattern is:
CLOUD STORAGE
|
v
EXTERNAL LOCATION
|
v
MANAGED FILE EVENTS
|
v
AUTO LOADER
An Auto Loader stream can use managed file events with:
.option("cloudFiles.useManagedFileEvents", "true")
This centralizes file-event management at the external-location level.
8. File events vs directory listing
| Characteristic | Directory listing | Managed file events |
|---|---|---|
| Setup simplicity | High | Moderate |
| Discovery scalability | Lower | Higher |
| Large object populations | Less ideal | Preferred |
| Cloud notification management | None | Managed |
| Incremental discovery | Yes | Yes |
| Exactly-once file processing | Supported | Supported |
Choose based on scale, latency, governance and cloud configuration.
9. File ordering
Auto Loader does not guarantee business ordering of discovered files.
Example:
file_001
file_003
file_002
file_005
file_004
Never interpret file discovery order as business event order.
If business sequence matters, carry an explicit event timestamp, sequence number or source transaction identifier.
10. Exactly-once processing
Auto Loader can provide exactly-once processing semantics for file ingestion when used with appropriate streaming checkpoints and supported sinks.
Conceptually:
FILE
|
v
DISCOVERED
|
v
FILE STATE
|
v
PROCESSED
|
v
CHECKPOINT UPDATED
On restart, persisted state allows the stream to distinguish already processed files from new input.
11. Checkpoints
A checkpoint is the stream's operational memory.
It can contain progress, discovered-file state and streaming state.
STREAM
|
+--> TARGET DATA
|
+--> CHECKPOINT
|
+--> progress
+--> file state
+--> stream state
Treat checkpoints as production state.
They need durable storage, controlled permissions, protected lifecycle and documented recovery procedures.
12. Checkpoint anti-patterns
Never casually:
- delete the checkpoint;
- reuse a checkpoint for an unrelated stream;
- apply a lifecycle policy that can unexpectedly remove it;
- move it without understanding recovery implications.
For independent ingestion workloads use independent streaming state.
Safe pattern:
SOURCE A ---> CHECKPOINT A ---> TARGET
SOURCE B ---> CHECKPOINT B ---> TARGET
13. Schema inference
Auto Loader can infer source schemas.
Conceptually:
FILES
|
+--> SAMPLE
|
+--> INFER
|
v
SCHEMA STATE
This is useful when onboarding semi-structured or vendor-controlled sources.
Schema inference is not the same thing as a data contract.
14. Schema evolution
Consider:
Initial:
order_id
customer_id
amount
Later:
order_id
customer_id
amount
currency
The platform must decide whether to add the column, rescue the unexpected data or reject the change.
Schema evolution is a business-contract decision, not merely a technical setting.
15. Schema evolution modes
Important modes include:
| Mode | Meaning |
|---|---|
| addNewColumns | Add new columns; stream can require restart |
| addNewColumnsWithTypeWidening | Add columns and widen supported types |
| rescue | Preserve unexpected data in rescued data |
| failOnNewColumns | Fail on unexpected columns |
| none | Do not automatically evolve |
When you explicitly provide a schema, the available evolution behavior differs from pure inference-based ingestion.
Choose deliberately.
16. Why schema changes can stop a stream
Suppose the current schema is:
order_id
amount
and a new file introduces:
order_id
amount
currency
In supported evolution modes, Auto Loader can update schema state and stop with an unknown-field condition.
The next restart can resume using the updated schema.
This makes schema change visible instead of silently hiding it.
17. Automatic restart architecture
Production systems should combine Auto Loader with orchestration.
NEW COLUMN
|
v
SCHEMA STATE UPDATED
|
v
STREAM STOPS
|
v
JOB RESTART
|
v
STREAM RESUMES
Ingestion configuration and restart strategy are one architecture.
18. Schema hints
Schema hints provide explicit type guidance.
Example:
.option(
"cloudFiles.schemaHints",
"headers map_string_string, statusCode SHORT"
)
Use hints when semi-structured fields are ambiguous, vendor schemas are inconsistent, inferred types are unacceptable or downstream types are contractual.
19. Rescued data
Unexpected fields can be preserved rather than discarded.
EXPECTED FIELDS
|
+------> NORMAL COLUMNS
|
UNEXPECTED FIELDS
|
v
RESCUED DATA
This is useful for vendor feeds, JSON, semi-structured APIs and gradual migrations.
Rescued data must be monitored. Otherwise it becomes a hidden data-quality backlog.
20. Rescue vs fail
Rescue
Use when source evolution is expected and preserving unexpected information is valuable.
Fail
Use when the schema is a strict contract and unexpected data makes the dataset unsafe.
Mental model:
STRICT CONTRACT
|
v
FAIL FAST
FLEXIBLE CONTRACT
|
v
RESCUE + OBSERVE
21. File formats
Auto Loader supports common file formats including:
- JSON;
- CSV;
- Parquet;
- Avro;
- XML in supported runtimes;
- text;
- binary files.
Schema inference behavior differs by format.
Parquet and Avro carry typed schema information, while JSON and CSV rely more heavily on inference and configuration.
22. Partition inference
Hive-style paths can provide partition columns.
Example:
/orders/
year=2026/
month=11/
day=08/
file.json
Partition inference is useful for physical organization.
Directory attributes are not a replacement for the actual business schema.
23. Multi-source ingestion
If two independent source locations feed the same target:
SOURCE A ---> TARGET
SOURCE B ---> TARGET
design ingestion state independently:
SOURCE A ---> CHECKPOINT A ---> TARGET
SOURCE B ---> CHECKPOINT B ---> TARGET
This keeps recovery, replay and ownership boundaries explicit.
24. Backfills
Production ingestion must handle missed and late files.
A robust pattern combines normal discovery with periodic backfill:
NORMAL DISCOVERY
+
PERIODIC BACKFILL
|
v
COMPLETE INGESTION
Backfills should be designed around the business completeness SLA.
They should not be emergency-only procedures.
25. File-arrival triggers
A file-arrival trigger answers:
When should a job start?
Auto Loader answers:
How should the streaming pipeline discover and ingest files?
They can be combined:
FILE ARRIVES
|
v
FILE-ARRIVAL TRIGGER
|
v
JOB
|
v
AUTO LOADER
|
v
DELTA
This separates orchestration from ingestion.
26. Triggered vs continuous
Triggered / available-now
Good for periodic processing, cost-controlled workloads and file-arrival orchestration.
Continuous
Good for sustained low-latency requirements.
Do not choose continuous simply because Auto Loader is a streaming source. Quantify the SLA first.
27. Performance architecture
Auto Loader performance is not only a Spark SQL problem.
Analyze:
DISCOVERY
|
+--> listing / file events
|
STATE
|
+--> checkpoint / file state
|
PROCESSING
|
+--> files per trigger
+--> transformation cost
|
SINK
|
+--> Delta write cost
The ingestion bottleneck may exist before Spark transformations begin.
28. Millions of files
At very large object counts, discovery becomes a first-class architecture decision.
Compare:
LIST MILLIONS OF OBJECTS
vs
CONSUME INCREMENTAL FILE EVENTS
For large production sources, managed file events can improve discovery scalability.
Model today's object count and the expected three-year growth.
29. Small-file problem
Auto Loader does not fix poor upstream file sizing.
Example:
10,000,000 files
x
5 KB
can create serious downstream overhead even if discovery is efficient.
Consider upstream batching, file sizing, compaction, ingestion frequency and downstream Delta layout.
30. File-state growth
Long-lived streams can accumulate file state.
State retention must be balanced against the replay window.
Ask:
How far back must the system recognize a previously processed file?
Do not expire file state simply because the checkpoint has become large.
31. Source retention
Define whether source files are:
- retained;
- archived;
- deleted after a defined period;
- retained for compliance.
Source retention and checkpoint retention are different.
Example:
SOURCE FILE
|
v
INGESTED
|
+--> ARCHIVE
|
+--> DELETE AFTER POLICY
Never delete source data before the replay strategy is understood.
32. Security architecture
Use governed identities instead of hard-coded cloud keys.
Preferred pattern:
WORKLOAD IDENTITY
|
v
UNITY CATALOG
|
v
EXTERNAL LOCATION
|
v
CLOUD STORAGE
Use least privilege.
33. Unity Catalog integration
A modern enterprise pattern is:
CLOUD STORAGE
|
v
EXTERNAL LOCATION
|
v
AUTO LOADER
|
v
CATALOG.SCHEMA.BRONZE_TABLE
This provides a governed boundary for both storage access and data assets.
34. Observability
Monitor at least:
- files discovered;
- files processed;
- input records;
- output records;
- processing latency;
- failed batches;
- schema changes;
- rescued data;
- checkpoint health;
- source freshness;
- ingestion lag.
A production dashboard should answer:
Are files arriving?
|
v
Are they discovered?
|
v
Are they processed?
|
v
Are records landing?
|
v
Is the target fresh?
35. Failure scenarios
| Failure | Impact | First response |
|---|---|---|
| Checkpoint deleted | Stream state lost | Stop and follow recovery plan |
| New column | Schema evolution event | Apply approved evolution/restart |
| File-event issue | Discovery behavior changes | Inspect discovery mode |
| Millions of tiny files | Discovery and processing overhead | Fix source/file strategy |
| Source deleted too early | Replay unavailable | Restore from archive/backup |
| Shared checkpoint | Recovery interference | Separate state boundaries |
36. Anti-patterns
1. Treating file order as business order
File discovery is not event sequencing.
2. Deleting checkpoints to restart
This destroys state.
3. Accepting every schema change
Schema evolution must follow a contract.
4. One checkpoint for unrelated streams
Recovery boundaries become ambiguous.
5. Listing huge directories forever
Use a scalable discovery model at scale.
6. Hard-coded credentials
Use governed workload identities.
7. Deleting source files immediately
Preserve replay capability according to policy.
8. Using Auto Loader as database CDC
Use database CDC and Lakeflow AUTO CDC where appropriate.
37. Production reference architecture
CLOUD STORAGE
|
+--------+--------+
| |
FILE EVENTS ARCHIVE
|
v
AUTO LOADER
|
+------+------+
| |
CHECKPOINT SCHEMA STATE
| |
+------+------+
|
v
BRONZE STREAMING
TABLE
|
v
QUALITY EXPECTATIONS
|
+------+------+
| |
SILVER QUARANTINE
|
v
GOLD / DATA
PRODUCTS
|
+---+---+
| |
BI ML
The architecture separates discovery, state, schema, quality, transformation and consumption.
38. Hands-on lab — Basic Auto Loader
Objective
Ingest JSON files into Bronze.
Step 1
Configure Auto Loader:
df = (
spark.readStream
.format("cloudFiles")
.option("cloudFiles.format", "json")
.load(source_path)
)
Step 2
Write to Delta using a durable checkpoint.
Step 3
Add new files.
Step 4
Restart the stream.
Step 5
Verify that previously processed files are not treated as new input.
39. Hands-on lab — Schema evolution
Start with:
order_id
amount
Then introduce:
currency
Observe:
- schema detection;
- stream behavior;
- schema state;
- restart behavior;
- target schema.
Then test an incompatible type change.
40. Hands-on lab — Managed file events
Build:
EXTERNAL LOCATION
|
v
MANAGED FILE EVENTS
|
v
AUTO LOADER
|
v
BRONZE
Compare discovery latency, discovery overhead, operational complexity and scalability.
41. Hands-on lab — Failure recovery
Simulate:
- stream interruption;
- schema change;
- late file;
- duplicate file;
- checkpoint loss in a controlled test environment.
For each case document:
- symptom;
- root cause;
- recovery action;
- data-loss risk;
- duplicate risk;
- monitoring signal.
42. Auto Loader vs COPY INTO
COPY INTO
Useful for controlled batch ingestion, SQL-centric loading and workloads without streaming requirements.
Auto Loader
Prefer when files continuously arrive, incremental discovery matters, object populations are large, schema evolution matters or streaming ingestion is required.
Choose based on operating semantics, not syntax preference.
43. Enterprise ingestion standards
A platform team can standardize:
Naming
bronze_domain_dataset
Checkpoints
One controlled checkpoint per ingestion workload.
Schema
Approved evolution policy.
Quality
Standard expectations and quarantine behavior.
Security
Unity Catalog external locations and governed identities.
Operations
Standard monitoring and alerting.
Recovery
Documented replay procedure.
This turns Auto Loader from a developer trick into an enterprise ingestion service pattern.
44. Architect decision framework
Before production, answer:
- How many files arrive per hour?
- How many objects exist in the source?
- What latency is required?
- Is managed file events available?
- What is source retention?
- What is the replay window?
- What schema changes are allowed?
- What happens to unexpected fields?
- Where is the checkpoint?
- Who owns the storage identity?
- How are failures restarted?
- What is the ingestion SLA?
- How are missed files recovered?
- What is the target grain?
- How is data quality enforced?
If these questions are unanswered, the ingestion architecture is incomplete.
45. Enterprise architect exercise
A global retailer receives:
- 5 million JSON files per day;
- 2 TB of Parquet files per day;
- occasional vendor schema changes;
- late-arriving files;
- a 15-minute freshness SLA.
Design the ingestion architecture.
Your answer should cover:
- discovery mode;
- external location;
- managed file events;
- checkpoint strategy;
- schema evolution;
- rescued data;
- backfills;
- source retention;
- Bronze design;
- quality expectations;
- monitoring;
- cost controls;
- replay;
- disaster recovery.
Then explain why a simple directory-listing implementation is not your preferred long-term architecture.
46. Production checklist
Source
- Path documented.
- Format documented.
- Arrival pattern documented.
- File-size distribution measured.
- Retention policy defined.
Discovery
- Discovery mode selected intentionally.
- Managed file events evaluated.
- Ordering assumptions removed.
- Backfill strategy defined.
State
- Durable checkpoint configured.
- Checkpoint lifecycle protected.
- File-state growth monitored.
- Recovery procedure documented.
Schema
- Schema inference decision made.
- Schema hints documented.
- Evolution mode selected.
- Rescue behavior defined.
- Incompatible type behavior defined.
Governance
- Unity Catalog used where appropriate.
- External location defined.
- Least-privilege identity used.
- Secrets are not hard-coded.
Operations
- Restart strategy defined.
- Monitoring configured.
- Freshness SLA defined.
- Missed-file recovery tested.
- Source retention aligned with replay requirements.
47. What you should be able to explain after this chapter
You should now be able to explain:
- why Auto Loader exists;
- cloudFiles;
- directory listing;
- managed file events;
- file notification concepts;
- checkpoints;
- exactly-once ingestion semantics;
- schema inference;
- schema evolution;
- schema hints;
- rescued data;
- partition inference;
- backfills;
- file-arrival triggers;
- triggered vs continuous ingestion;
- file-state growth;
- source retention;
- Unity Catalog integration;
- production security;
- observability;
- failure recovery;
- Auto Loader vs COPY INTO;
- enterprise ingestion standards.
48. The enterprise takeaway
Auto Loader is not simply:
"A better way to read JSON files."
It is an ingestion architecture built around:
SCALABLE DISCOVERY
+
PERSISTENT STATE
+
SCHEMA CONTROL
+
INCREMENTAL PROCESSING
+
RECOVERABILITY
The architect's job is to design all five together.
A production ingestion system should be able to answer:
How do we discover every file, process each file safely, survive schema change, recover from failure and prove that the target is complete?
That is the real Auto Loader problem.