7.2 Auto Loader Schema Inference, Evolution, & Rescued Data Column

7.2 Auto Loader Schema Inference, Evolution, & Rescued Data Column

DP-750 Exam Focus: Master Auto Loader schema management. Understand how cloudFiles.schemaLocation persists inferred schemas, evaluate the four cloudFiles.schemaEvolutionMode settings (addNewColumns, failOnNewColumns, rescue, none), enforce specific data types with cloudFiles.schemaHints, and explain the behavior and utility of the _rescued_data column.

---

1. Schema Inference & cloudFiles.schemaLocation

In raw Bronze ingestion, source systems frequently emit files with dynamic, unannounced schema variations—such as newly added attributes, omitted fields, or slight data type variations.

Standard Spark structured streaming file readers require a rigidly predefined static schema (.schema(my_schema)). If a file arrives with an unexpected schema change, standard Spark readers either fail immediately or parse new columns as null, causing silent data loss.

Auto Loader eliminates manual schema definitions through automatic schema inference and persistent schema storage.

+-----------------------------------------------------------------------------------+
|                         SCHEMA INFERENCE & EVOLUTION FLOW                         |
+-----------------------------------------------------------------------------------+
|                                                                                   |
|  [ Ingest Files (JSON / CSV / Parquet) ]                                          |
|         |                                                                         |
|         v                                                                         |
|  [ Auto Loader Schema Engine ] <====> [ cloudFiles.schemaLocation ]               |
|  - Samples initial files               (Persists schema contract as JSON)         |
|  - Applies schemaHints overrides       /_schemas/0/schema.json                    |
|         |                                                                         |
|         v                                                                         |
|  [ Schema Drift Detection ]                                                       |
|         |                                                                         |
|         +---------------------------------------+                                 |
|         | (New column detected)                 | (Type mismatch / corrupt data)  |
|         v                                       v                                 |
|  [ schemaEvolutionMode ]                [ _rescued_data Column ]                  |
|  - addNewColumns (Default)              - Stores invalid record payload           |
|  - failOnNewColumns                     - Captures unmapped columns               |
|  - rescue                               - Prevents silent data corruption         |
|  - none                                         |                                 |
|         |                                       |                                 |
|         +-------------------+-------------------+                                 |
|                             |                                                     |
|                             v                                                     |
|                [ Target Bronze Delta Table ]                                      |
|                (Auto-evolved schema with rescued records)                         |
+-----------------------------------------------------------------------------------+

The cloudFiles.schemaLocation Option

To enable schema inference and evolution, data engineers must provide cloudFiles.schemaLocation. This directory stores the learned schema contract in cloud storage (ADLS Gen2):

# Auto Loader with Schema Inference
df_inferred = (spark.readStream
    .format("cloudFiles")
    .option("cloudFiles.format", "json")
    .option("cloudFiles.schemaLocation", "abfss://checkpoints@myadls.dfs.core.windows.net/iot_schema")
    .option("cloudFiles.inferColumnTypes", "true") # Informs Auto Loader to infer types beyond STRING for JSON/CSV
    .load("abfss://raw@myadls.dfs.core.windows.net/iot-telemetry/"))

Key Operational Behaviors

1. Initial Sampling: When the stream launches for the first time, Auto Loader samples the first 1,000 files (or up to 50 GB of data by default) to establish the initial schema definition.
2. Schema Persistence: The inferred schema is written to cloudFiles.schemaLocation as an immutable JSON schema file. On subsequent stream restarts, Auto Loader reads the schema directly from this location, skipping redundant sampling.
3. cloudFiles.inferColumnTypes: By default for JSON and CSV, Auto Loader infers all columns as StringType to avoid premature type casting errors. Setting cloudFiles.inferColumnTypes to true instructs Auto Loader to infer numeric, boolean, timestamp, and complex nested types.

---

2. Schema Evolution Modes

When a newly ingested file contains columns that are not present in the persisted schema contract, Auto Loader's behavior is dictated by cloudFiles.schemaEvolutionMode.

Mode Comparison & Production Scenarios

1. addNewColumns (Default)

Ideal for Bronze lakehouse ingestion where all landing data must be preserved. When a new column (e.g., battery_temp) appears, Auto Loader updates the schema file in schemaLocation and restarts the stream with the updated schema.

# Ingesting with addNewColumns and Delta schema merging
(spark.readStream
    .format("cloudFiles")
    .option("cloudFiles.format", "json")
    .option("cloudFiles.schemaLocation", "abfss://checkpoints@myadls/schema_sales")
    .option("cloudFiles.schemaEvolutionMode", "addNewColumns")
    .load("abfss://raw@myadls/sales_json/")
    .writeStream
    .format("delta")
    .option("checkpointLocation", "abfss://checkpoints@myadls/cp_sales")
    .option("mergeSchema", "true") # Required for target Delta table to accept evolved columns
    .toTable("bronze.sales.raw_transactions"))

2. failOnNewColumns

Used in strict governance environments where upstream source schema changes require manual approval or schema registry validation before entering the lakehouse.

3. rescue

Recommended when the downstream Delta table schema must remain strictly static, but unmapped attributes must be preserved for forensic analysis in _rescued_data rather than discarded.

---

3. The _rescued_data Column: Deep Dive

To prevent data loss and pipeline downtime from schema mismatches or corrupt records, Auto Loader automatically provisions a hidden column named _rescued_data by default.

                        RESCUED DATA INGESTION MATRIX

  Source Record in ADLS Gen2:
  {"device_id": 101, "reading": "INVALID_FLOAT", "new_sensor_val": 42.8}

  Target Schema:
  - device_id: BIGINT
  - reading: DOUBLE

  +-----------------------------------------------------------------------------------+
  |                               INGESTED DELTA ROW                                  |
  +-----------+---------+-------------------------------------------------------------+
  | device_id | reading | _rescued_data                                               |
  +-----------+---------+-------------------------------------------------------------+
  | 101       | null    | {"reading":"INVALID_FLOAT", "new_sensor_val":42.8,          |
  |           |         |  "_file_path":"abfss://raw@storage/data_01.json"}            |
  +-----------+---------+-------------------------------------------------------------+

What Is Captured in _rescued_data?

1. Data Type Mismatches: If a column is defined as DOUBLE (e.g., reading), but a source record contains a non-numeric string ("INVALID_FLOAT"), the column value in the main table is set to null, and the raw unparsed value is preserved inside _rescued_data.
2. Undeclared Columns: Under schemaEvolutionMode = 'rescue' (or if schema evolution is disabled), any column in the source file not declared in the schema is routed into _rescued_data.
3. Malformed Records: Unparseable JSON or CSV lines that violate standard formatting syntax.
4. Case Sensitivity Clashes: When case sensitivity is enabled, differences in column casing (e.g., CustomerId vs customerId) are preserved in _rescued_data to prevent accidental overwrites.

Querying Rescued Data with Databricks SQL

Data engineers can easily query, inspect, and extract rescued fields using standard SQL JSON extraction functions:

-- Query: Identify rows with data rescue events and extract original values
SELECT 
    device_id,
    reading,
    _rescued_data,
    get_json_object(_rescued_data, '$.reading') AS original_reading_str,
    get_json_object(_rescued_data, '$._file_path') AS source_file
FROM bronze.telemetry.raw_events
WHERE _rescued_data IS NOT NULL;

Exam Tip: To disable the rescued data column, set .option("cloudFiles.schemaEvolutionMode", "none") and .option("cloudFiles.schemaHints", "") or set .option("cloudFiles.rescuedDataColumn", "") (empty string). In production, leaving _rescued_data enabled is the recommended practice for Bronze ingestion.

---

4. Schema Hints & Explicit Overrides

While Auto Loader infers schemas effectively, automated inference may choose overly permissive or generic data types (for example, inferring an ISO timestamp string as StringType or an integer ID as LongType).

Schema Hints (cloudFiles.schemaHints) allow data engineers to explicitly enforce exact data types for specific columns while allowing Auto Loader to automatically infer the rest of the schema.

# Applying Schema Hints for precise datatype enforcement
df_hints = (spark.readStream
    .format("cloudFiles")
    .option("cloudFiles.format", "csv")
    .option("cloudFiles.schemaLocation", "abfss://checkpoints@myadls/csv_schema")
    .option("header", "true")
    .option("cloudFiles.inferColumnTypes", "true")
    .option("cloudFiles.schemaHints", """
        transaction_id BIGINT,
        amount DECIMAL(10, 2),
        transaction_timestamp TIMESTAMP,
        is_fraudulent BOOLEAN
    """)
    .load("abfss://raw@myadls/finance_csv/"))

Rules Governing Schema Hints

• Precedence: Explicit definitions in cloudFiles.schemaHints override both inferred types and types loaded from cloudFiles.schemaLocation.
• Partial Specification: Engineers only need to specify the subset of columns requiring strict typing; unmentioned columns are dynamically inferred.
• Type Casting Resilience: If incoming data cannot be cast to the type specified in schemaHints (e.g., "N/A" in a DECIMAL column), the column receives null and the raw value is safely captured in _rescued_data.