What you will be able to do
- Explain why Auto Loader infers JSON fields, including nested ones, as strings, and how to change that
- Pick a cloudFiles.schemaEvolutionMode for JSON whose structure changes over time, and predict how the stream behaves
- Land whole JSON records in a VARIANT column with Auto Loader or COPY INTO, and know what that approach gives up
- Place checkpoint and schema files where Unity Catalog allows them
Key concept
Schema location (cloudFiles.schemaLocation) — This is the directory where Auto Loader saves the schema it inferred and every later change to it. If you set it, Auto Loader can create a Unity Catalog table from JSON without a declared schema and then evolve that table as new fields arrive.
1.How Auto Loader infers a schema for JSON
JSON is self-describing, but loosely: one file may hold a field as a number and the next file may hold it as text, and objects can be nested to any depth. Auto Loader is the cloudFiles Structured Streaming source. It can read JSON from S3, ADLS, GCS or a Unity Catalog volume and write it to a Unity Catalog Delta table without you declaring a schema first. Before it can do that, it has to decide what the columns are.
Auto Loader chooses strings on purpose. JSON, CSV and XML carry no type information, so a type guessed from early files could be contradicted by later ones. To work out the columns, Auto Loader samples the first 50 GB or 1000 files it discovers, whichever limit it reaches first. It writes the result to a _schemas directory under cloudFiles.schemaLocation. If you want the Spark DataFrameReader's behaviour, where types are inferred from the sample values, set cloudFiles.inferColumnTypes to true.
The example below lands JSON in a Unity Catalog managed table. It stores the schema in the same directory as the checkpoint, which the docs allow. availableNow=True processes the files that are currently available and then stops.
checkpoint_path = "s3://dev-bucket/_checkpoint/dev_table"
(spark.readStream
.format("cloudFiles")
.option("cloudFiles.format", "json")
.option("cloudFiles.schemaLocation", checkpoint_path)
.load("s3://autoloader-source/json-data")
.writeStream
.option("checkpointLocation", checkpoint_path)
.trigger(availableNow=True)
.toTable("dev_catalog.dev_database.dev_table"))Checkpoint 1 of 6· Check yourself
A team wants Auto Loader to infer JSON columns as numbers, booleans and so on from the data, the way spark.read does. Which option turns that on?
Type inference from sample data is off by default for JSON. cloudFiles.inferColumnTypes turns it on. Schema hints override individual columns, and the sample-size settings only change how much data is sampled.
“To enable this behavior with Auto Loader, set the option cloudFiles.inferColumnTypes to true.”Source: docs.databricks.com
Sources1
2.When new JSON fields appear: evolution modes and rescued data
Sooner or later a producer adds a field. Auto Loader detects new columns as it processes data. In the default mode, it merges the new columns onto the end of the schema in the schema location, then stops the stream with an UnknownFieldException. When the stream restarts, it picks up the updated schema. Because of that stop-and-restart pattern, Databricks recommends running Auto Loader streams under Lakeflow Jobs so they restart automatically. You choose the behaviour with cloudFiles.schemaEvolutionMode.
| Mode | Stream fails? | What happens to the new data |
|---|---|---|
| addNewColumns (default without a schema) | Yes, then resumes on restart | Added to the schema; existing column types do not change |
| addNewColumnsWithTypeWidening | Yes, like addNewColumns | Added, and supported types widen (for example int to long); unsupported changes go to the rescued data column |
| rescue | No | Schema never evolves; new columns go to the rescued data column |
| failOnNewColumns | Yes, and does not restart until fixed | Schema is not updated automatically |
| none (default with a provided schema) | No | Ignored, unless the rescuedDataColumn option is set |
The defaults depend on whether you supply a schema. If you do not, the default is addNewColumns. If you do, the default is none, and addNewColumns is not allowed at all. It does work, though, when you pass your schema as schema hints. The rescued data column is a JSON blob. It holds fields that were missing from the schema, had the wrong data type, or differed from a schema field name only in letter case, together with the path of the source file.
Checkpoint 2 of 6· Match them up
Match each schema evolution mode to how it behaves on a new column
Tap a term, then the definition that fits it.
rescue and none both keep the stream running without evolving the schema. The difference is that rescue always captures the new columns, while none drops them unless you ask for a rescued data column.
“Does not evolve the schema, new columns are ignored, and data is not rescued unless the rescuedDataColumn option is set.”Source: docs.databricks.com
Checkpoint 3 of 6· Exam question
A team continuously lands nested JSON event files in cloud object storage and streams them with Auto Loader into a Unity Catalog-governed Delta table. They want the stream to keep running without interruption when unexpected new fields appear in the JSON payloads, while still capturing those unexpected fields for later review instead of silently dropping them. Which `cloudFiles.schemaEvolutionMode` setting satisfies this requirement?
Correct answer: B — rescue
- A. This is the default evolution mode, and it deliberately stops the stream with an `UnknownFieldException` once new columns are detected so the schema can be updated before the stream resumes. That interruption is exactly what the team wants to avoid.
- B. This mode never stops the stream for new fields; unmatched or unexpected fields are automatically written into the `_rescued_data` column as a JSON blob, so processing continues and the extra data is preserved for later inspection.
- C. This mode halts processing permanently as soon as a new column appears and requires a manual schema update before the stream can resume, which is the opposite of uninterrupted processing.
- D. This mode ignores new columns entirely and does not preserve them unless rescued data handling is separately enabled, so the extra fields would be silently discarded rather than captured for review.
3.Keeping nested JSON whole with the VARIANT type
Turning every nested field into its own column is not always what you want. In Databricks Runtime 15.3 and above, Delta tables support a VARIANT column, which stores semi-structured data as it arrives. Auto Loader can load each whole record into a single VARIANT column using the singleVariantColumn option. VARIANT copes with schema and type changes and keeps case sensitivity and NULLs, so this pattern holds up in most ingestion scenarios.
Checkpoint 4 of 6· Fill the gap
Which option makes Auto Loader write each whole JSON record into one VARIANT column?
(spark.readStream
.format("cloudFiles")
.option("cloudFiles.format", "json")
.option(" ? ", "variant_column")
.load("/Volumes/catalog_name/schema_name/volume_name/path")
.writeStream
.option("checkpointLocation", checkpoint_path)
.toTable("table_name")
)singleVariantColumn names the VARIANT column that receives the entire record. This example assumes the target table already exists with that single VARIANT column.
Source: docs.databricks.comThe single-column pattern has costs. Malformed records cannot be encoded as VARIANT, and a VARIANT value can hold at most 16 MB. Records over that size are treated like corrupt records, which in the default PERMISSIVE mode means they go to corruptRecordColumn. Because the whole record sits in one column, no schema evolution happens and rescuedDataColumn is not supported. You can also go part of the way: a schema hint makes only one nested field a VARIANT, and the other fields are inferred as usual.
# Supply schema hints.
# Writes the `address` column as variant.
# Infers the schema for other fields using standard rules.
(spark.readStream.format("cloudFiles")
.option("cloudFiles.format", "json")
.option("cloudFiles.schemaHints", "address VARIANT")
.load("/Volumes/catalog_name/schema_name/volume_name/path")
.writeStream
.option("checkpointLocation", checkpoint_path)
.toTable("table_name")
)Pulling out hot fields matters for a second reason. VARIANT columns cannot be clustering keys, partition columns or Z-order keys, and they cannot be used in comparisons, grouping or ordering. Databricks recommends extracting frequently queried fields into ordinary columns. If JSON is already sitting in a string column, PARSE_JSON produces the VARIANT and the : path syntax extracts a field.
CREATE TABLE table_name AS
SELECT json_string:id AS id,
PARSE_JSON(json_string) variant_column
FROM source_dataCheckpoint 5 of 6· Exam question
During Auto Loader schema inference on a directory of nested JSON files, an engineer notices that every field, including numeric and boolean values, is inferred as a string type in the target Delta table. Which configuration change causes Auto Loader to apply standard Spark type inference (numbers, booleans, dates) to the JSON fields instead of defaulting everything to strings?
Correct answer: A — Set `cloudFiles.inferColumnTypes` to `true`
- A. This option explicitly enables type detection during schema inference, so JSON fields are typed as numbers, booleans, or dates using Spark's standard inference behavior instead of defaulting every field to a string.
- B. This setting controls how newly arriving columns are handled after the schema has already been established; it does not change how the initial data types of existing fields are inferred from the JSON payloads.
- C. This setting toggles the file discovery strategy used to detect new files arriving in the source directory and has no effect on how column data types are inferred from the file contents.
- D. This setting controls whether Auto Loader validates the options passed to the stream reader and has no bearing on the data type assigned to inferred JSON columns.
Sources3
4.COPY INTO for VARIANT, and where Unity Catalog lets the state live
If you work in SQL and do not need a stream, COPY INTO can also put the entire contents of a source into a single column, using the same singleVariantColumn setting as a format option. Even so, Databricks recommends Auto Loader over COPY INTO when it is available.
CREATE TABLE table_name (variant_column VARIANT);
COPY INTO table_name
FROM '/Volumes/catalog_name/schema_name/volume_name/path'
FILEFORMAT = JSON
FILES = ('file-name')
FORMAT_OPTIONS ('singleVariantColumn' = 'variant_column')Unity Catalog governs where the stream's own state is kept as well as the target table. Databricks recommends keeping checkpoint and schema evolution files in storage locations managed by Unity Catalog. Unity Catalog will not allow those files to be nested under the table directory. Lakeflow pipelines avoid the question altogether: when a CREATE STREAMING TABLE statement uses read_files, the pipeline manages the checkpoint and schema locations for you.
Checkpoint 6 of 6· Check yourself
An engineer sets cloudFiles.schemaLocation to a _schema subfolder inside the target Unity Catalog table's own storage directory. What happens?
Unity Catalog forbids putting checkpoint or schema inference files under the table directory. Keep them in a separate location that Unity Catalog manages.
“Unity Catalog does not allow you to nest checkpoint or schema inference and evolution files under the table directory.”Source: docs.databricks.com
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.addNewColumns is always Auto Loader's default, so supplying an explicit schema still lets new JSON fields be added automatically.Why is that wrong?
With a provided schema the default becomes none and addNewColumns is not allowed. To get evolution, supply the schema as schema hints instead.
Covered in When new JSON fields appear: evolution modes and rescued data
2.Loading records with singleVariantColumn still gives you schema evolution and a rescued data column as a safety net.Why is that wrong?
The whole record goes into one VARIANT column, so no schema evolution happens and rescuedDataColumn is not supported. Malformed and oversized records go to the corrupt record column instead.
3.A VARIANT column holding the raw event can be used as the table's clustering or partition key.Why is that wrong?
VARIANT columns cannot be clustering keys, partitions or Z-order keys. Extract the field you need into a regular column.
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“Auto Loader samples the first 50 GB or 1000 files that it discovers, whichever limit is crossed first.”
↩︎ How Auto Loader infers a schema for JSON“Auto Loader infers all columns as strings (including nested fields in JSON files)”
↩︎ How Auto Loader infers a schema for JSON“When Auto Loader detects a new column, the stream stops with an UnknownFieldException.”
↩︎ When new JSON fields appear: evolution modes and rescued data“Databricks recommends configuring Auto Loader streams with Lakeflow Jobs to restart automatically after such schema changes.”
↩︎ When new JSON fields appear: evolution modes and rescued data“Specifying a target directory for the cloudFiles.schemaLocation option enables schema inference and evolution.”
↩︎ Key concept“addNewColumns mode is the default when a schema is not provided, but none is the default when you provide a schema.”
↩︎ Exam trap 1“To enable this behavior with Auto Loader, set the option cloudFiles.inferColumnTypes to true.”
↩︎ Checkpoint“Does not evolve the schema, new columns are ignored, and data is not rescued unless the rescuedDataColumn option is set.”
↩︎ Checkpoint - 2.
“The rescued data column is returned as a JSON blob containing the rescued columns and the source file path of the record.”
↩︎ When new JSON fields appear: evolution modes and rescued data - 3.https://docs.databricks.com/aws/en/ingestion/variantOfficial docs
“VARIANT type can only hold records up to 16 MB in size.”
↩︎ Keeping nested JSON whole with the VARIANT type“Databricks recommends extracting frequently queried fields and storing them as non-variant columns to accelerate queries and optimize storage layout.”
↩︎ Keeping nested JSON whole with the VARIANT type“Databricks recommends using Auto Loader over COPY INTO when available.”
↩︎ COPY INTO for VARIANT, and where Unity Catalog lets the state live“no schema evolution occurs during ingestion and rescuedDataColumn is not supported”
↩︎ Exam trap 2“VARIANT columns cannot be used for clustering keys, partitions, or Z-order keys.”
↩︎ Exam trap 3 - 4.https://docs.databricks.com/aws/en/ingestion/cloud-object-storage/auto-loader/unity-catalogOfficial docs
“Databricks recommends always storing checkpoint and schema evolution information in storage locations managed by Unity Catalog.”
↩︎ COPY INTO for VARIANT, and where Unity Catalog lets the state live“When you use read_files in a CREATE STREAMING TABLE statement inside Lakeflow pipelines, checkpoint and schema locations are managed automatically.”
↩︎ COPY INTO for VARIANT, and where Unity Catalog lets the state live“Unity Catalog does not allow you to nest checkpoint or schema inference and evolution files under the table directory.”
↩︎ Checkpoint