# Databricks Auto Loader explained: cloudFiles, schema evolution and rescued data

Auto Loader is Databricks' source for loading new files as they land in cloud storage or a volume, read with spark.readStream.format("cloudFiles"). It records every file it finds in the checkpoint, so each is loaded exactly once, infers the schema and keeps it at cloudFiles.schemaLocation, and decides what to do with a new column by cloudFiles.schemaEvolutionMode: add it after a restart, keep it in _rescued_data, stop, or ignore it. For data in apps and databases, Lakeflow Connect's managed connectors do the same job without files.

- Page: https://tecyfy.com/data-engineering/course/getting-data-in/auto-loader
- Course: Data engineering course (https://tecyfy.com/data-engineering/course), Module 6: Getting data in, Auto Loader and Lakeflow Connect
- Parts of this module: Load new files once, with a checkpoint (https://tecyfy.com/data-engineering/course/getting-data-in); Auto Loader and Lakeflow Connect (https://tecyfy.com/data-engineering/course/getting-data-in/auto-loader); Change data capture with MERGE (https://tecyfy.com/data-engineering/course/getting-data-in/change-data-capture); Build it: ingest every file once (https://tecyfy.com/data-engineering/course/getting-data-in/build-it)

At the end of this part you can:

- Load new files with Auto Loader into a Unity Catalog table, from a volume
- Choose a schema evolution mode for what should happen when a file brings a new column
- Say when to use Lakeflow Connect instead of files

[Part 1](/data-engineering/course/getting-data-in) loaded the shop's order files into a bronze table with plain PySpark: a streaming read of `landing/`, a checkpoint that remembers each file, and a trigger that stops when the new files are loaded. On Databricks, the same job is usually done by Auto Loader, a source built for files in cloud storage. This part is Databricks only, so its code follows Databricks' docs.

## Auto Loader in a notebook

Auto Loader is a streaming source called `cloudFiles`. It records every file it finds in the checkpoint, and loads each one exactly once, as part 1's stream did. What it adds, by Databricks' account, is scale and the schema: it finds new files among billions without listing every folder, and it can infer the schema of the files and change it when they change.

In a notebook on Databricks, with the shop's schemas from [module 5](/data-engineering/course/unity-catalog):

```python
from pyspark.sql import functions as F

catalog = spark.catalog.currentCatalog()
spark.sql("CREATE VOLUME IF NOT EXISTS bronze.landing")
spark.sql("CREATE VOLUME IF NOT EXISTS bronze.checkpoints")
landing = f"/Volumes/{catalog}/bronze/landing"
checkpoint = f"/Volumes/{catalog}/bronze/checkpoints/orders_raw"

query = (
    spark.readStream.format("cloudFiles")
    .option("cloudFiles.format", "csv")
    .option("cloudFiles.schemaLocation", checkpoint)
    .option("cloudFiles.schemaHints", "order_id BIGINT, amount DECIMAL(10,2), updated_at TIMESTAMP")
    .load(landing)
    .select("*", F.col("_metadata.file_name").alias("source_file"))
    .writeStream
    .option("checkpointLocation", checkpoint)
    .option("mergeSchema", "true")
    .trigger(availableNow=True)
    .toTable("bronze.orders_raw")
)
query.awaitTermination()
```

Upload part 1's 2 files to the `landing` volume in **Catalog**, run the cell, and `bronze.orders_raw` holds their 5 rows. Run it again and nothing is added. To see which files the stream has taken, query its checkpoint:

```sql
SELECT * FROM cloud_files_state('/Volumes/<catalog>/bronze/checkpoints/orders_raw')
```

## The schema it keeps

There is no `.schema(...)` in that code. Auto Loader samples the first files, up to 50 GB or 1,000 files, and writes the schema it found into `cloudFiles.schemaLocation`, here the same folder as the checkpoint, as Databricks' examples do. For CSV, JSON and XML, which do not record types, it infers every column as a string, so a later file with an odd value never breaks the stream. `cloudFiles.inferColumnTypes` set to `true` infers types from the sample instead, and `cloudFiles.schemaHints`, used above, names the types you already know.

A value that does not fit goes into a column Auto Loader adds itself, `_rescued_data`, as JSON with the path of its file. Order 1003's amount `abc` cannot be a `DECIMAL(10,2)`, so its amount is null and the `abc` is kept in `_rescued_data`. That is part 1's `_bad` column, kept for you.

## When a file brings a new column

Say the shop's system starts sending a `coupon` column. What Auto Loader does is set by `cloudFiles.schemaEvolutionMode`, and each mode is a different answer to the question "who decides about a new column?":

(An interactive demo goes here on the page: https://tecyfy.com/data-engineering/course/getting-data-in/auto-loader)

The default when Auto Loader infers the schema is `addNewColumns`. It stops the stream once, on purpose, after recording the new column, and the next start loads the file with it. Databricks recommends running the stream as a job that restarts on its own, and the `mergeSchema` option on the write lets the Delta table take the new column too.

Give Auto Loader a full schema with `.schema(...)` instead, and the default becomes `none`, which ignores new columns. That is the trap in the table: a new column is silently dropped. For a schema you know, Databricks' pattern is `rescue` mode, which keeps anything unexpected in `_rescued_data`.

## Finding the files

Auto Loader finds new files in 1 of 2 ways. **Directory listing**, the default, lists the folder on each run. **File notification** has the cloud storage announce each new file, and Databricks recommends it, with file events, for most workloads. Auto Loader does not promise to load files in the order they arrived, which matters for changes to the same order, as [part 3](/data-engineering/course/getting-data-in/change-data-capture) shows.

## Apps and databases: Lakeflow Connect

Not every source drops files. The shop's payments might live in Salesforce, its stock in PostgreSQL. Lakeflow Connect has managed connectors that load these into Unity Catalog tables with no files in between. There are connectors for SaaS apps such as Salesforce, Workday, HubSpot and Jira, and for databases such as MySQL, PostgreSQL and SQL Server. There are also file stores such as SharePoint and Google Drive, and message buses.

Each connector builds an ingestion pipeline on serverless compute, governed by Unity Catalog. Its first run loads all the data you selected, and later runs load only what changed, where the source can say what changed. The database connectors read the database's own record of changes, through an ingestion gateway, which is the subject of [part 3](/data-engineering/course/getting-data-in/change-data-capture). Free Edition limits outbound internet access to a set of trusted domains, so a connector to an outside system may not reach it there; files in a volume always work.

## Check yourself

1. You give Auto Loader the full schema of the order files with .schema(ORDERS) and set no evolution mode. A file arrives with a new column, coupon. What happens to coupon?

   A. The stream stops, adds coupon to the schema, and loads it after a restart
   B. It is kept in _rescued_data
   C. It is ignored, because none is the default mode when you give a schema, and nothing is rescued unless rescuedDataColumn is set
   D. The stream stops until you add coupon to the schema

2. Auto Loader infers the schema of the shop's CSV files and you set no options for types. What type does amount get?

   A. DECIMAL, from the values in the sample
   B. STRING, because Auto Loader infers every CSV column as a string unless cloudFiles.inferColumnTypes is true
   C. DOUBLE, Spark's default for numbers
   D. None, because CSV needs a schema

### Answers

1. C. addNewColumns is the default only when Auto Loader infers the schema. With a schema given, the default is none, which neither stops nor keeps the new column. Set cloudFiles.schemaEvolutionMode to rescue, or give the schema as schemaHints with a schemaLocation, if new columns matter.
2. B. For CSV, JSON and XML, which do not record types, Auto Loader infers strings, so a type change in a later file never breaks the stream. Set cloudFiles.inferColumnTypes to true to infer types from the sample, or name the types you know with cloudFiles.schemaHints.

## Questions

### What is the difference between Auto Loader and spark.readStream on files?

Both load new files once each with a checkpoint. Auto Loader, the cloudFiles source, adds what the plain file source lacks on Databricks, by Databricks' account. It finds files efficiently among billions, at a cost that grows with the files rather than the folders. It also infers and evolves the schema, and rescues data that does not fit instead of losing it. Databricks recommends it for loading files from cloud storage with Structured Streaming.

### What is _rescued_data in Auto Loader?

A column Auto Loader adds when it infers a schema. It holds, as JSON with the file's path, every value that did not fit the schema, such as a column the schema does not have or a value of the wrong type. In rescue mode, every new column goes there instead of changing the schema.

## Sources

- [What is Auto Loader?](https://docs.databricks.com/aws/en/ingestion/cloud-object-storage/auto-loader/)
- [Configure schema inference and evolution in Auto Loader](https://docs.databricks.com/aws/en/ingestion/cloud-object-storage/auto-loader/schema)
- [Automatic type widening with Auto Loader](https://docs.databricks.com/aws/en/ingestion/cloud-object-storage/auto-loader/type-widening)
- [Common data loading patterns](https://docs.databricks.com/aws/en/ingestion/cloud-object-storage/auto-loader/patterns)
- [Using Auto Loader with Unity Catalog](https://docs.databricks.com/aws/en/ingestion/cloud-object-storage/auto-loader/unity-catalog)
- [Configure Auto Loader for production workloads](https://docs.databricks.com/aws/en/ingestion/cloud-object-storage/auto-loader/production)
- [CREATE VOLUME](https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-syntax-ddl-create-volume)
- [Lakeflow Connect connector concepts](https://docs.databricks.com/aws/en/ingestion/lakeflow-connect/)
- [Databricks Free Edition limitations](https://docs.databricks.com/aws/en/getting-started/free-edition-limitations)