Best Practice for Handling Schema Evolution with Auto Loader in Production?

[Islam_hoti]

Hi Databricks Community,

I’d like to hear how other Data Engineers are handling schema evolution with Auto Loader in production environments.

Consider the following scenario:

We have a continuously running ingestion pipeline using Auto Loader that processes JSON files into a Bronze Delta table.

df = (

    spark.readStream

    .format("cloudFiles")

    .option("cloudFiles.format", "json")

    .option("cloudFiles.schemaLocation", schema_path)

    .option("cloudFiles.schemaEvolutionMode", "addNewColumns")

    .load(source_path)

)

Everything works as expected until the source system introduces a new column.

With addNewColumns, Auto Loader detects the new field and updates the schema, but the stream can stop with an UnknownFieldException before continuing after a restart.

For a production pipeline, I see a few possible approaches:

1. Keep addNewColumns and configure the Lakeflow Job to restart automatically.
2. Use rescue mode and capture unexpected fields in _rescued_data.
3. Use schema hints for fields that we expect could change.
4. Use addNewColumnsWithTypeWidening where supported when compatible data type changes are also expected.

My main question is:

What approach do you consider the best practice for production pipelines where upstream schemas can change frequently?

I’m particularly interested in how you balance:

• Pipeline availability
• Automatic schema evolution
• Data quality
• Avoiding silent schema changes
• Governance with Unity Catalog
• Operational maintenance

Would you allow schema evolution automatically in the Bronze layer and enforce a stricter schema in Silver, or would you enforce the schema from the ingestion layer?

Interested to hear how others are designing this in real-world Databricks architectures.

#Databricks #DataEngineering #AutoLoader #StructuredStreaming #Lakeflow #DeltaLake #UnityCatalog

[Khasim_1 · Accepted solution]

Hi @Islam_hoti,

This is a central design decision for any production-grade Lakehouse. In my experience architecting petabyte-scale environments, the goal is to minimize "Pipeline Friction" without sacrificing "Data Governance."

Here is the "Architectural Standard" I recommend for this scenario:

1. The Bronze Strategy: "Capture Everything"

In the Bronze layer, your primary goal is to ensure that no data is lost due to upstream changes.

• Mode: I recommend staying with addNewColumns but always combining it with rescuedDataColumn.
• Why: rescuedDataColumn acts as your "safety net." If a field changes in a way that addNewColumns can’t handle (like a data type conflict), the data isn't lost; it’s captured in a JSON blob for later recovery.
• The Restart: The UnknownFieldException and subsequent restart are actually a "feature," not a bug. It ensures the state/checkpoint is updated with the new metadata. In a Lakeflow Job, configuring an automatic retry makes this process transparent and self-healing.

1. The Silver Strategy: "Enforce and Cleanse"

This is where you solve the "Silent Schema Change" problem. While Bronze is flexible, Silver should be strict.

• We allow Bronze to evolve automatically to keep the lights on.
• In the Silver transformation, we use Schema Enforcement. If the new column in Bronze isn't explicitly handled in your Silver logic, it simply stays in Bronze until an engineer or architect decides how to map it. This prevents "junk" columns from polluting your business-ready tables.

1. The Modern Twist: Type Widening & Unity Catalog

If you are on a recent Databricks Runtime (13.3+), you should definitely evaluate Type Widening. It allows Auto Loader to evolve a column from an INT to a BIGINT without failing the stream or requiring a rewrite of the Delta table. Under Unity Catalog, this is managed seamlessly, ensuring that your governance and lineage remain intact even as the schema grows.

For a production environment, I prefer Option 1 (Auto-restart) + Option 2 (Rescue Mode). Enforcing the schema at the ingestion layer (Bronze) is a recipe for operational headaches—upstream teams will inevitably break your pipeline. By capturing the change in Bronze and enforcing the logic in Silver, you maintain high availability while keeping strict governance where it matters most.

[J_Chakraborty]

You can absolutely leverage Databricks Genie and the new Genie Ontology to handle downstream changes caused by addNewColumnsWithTypeWidening, and handle the changes using Genie ontology, but it requires understanding the division of labor between your Bronze data engineering layer and your Semantic/AI layer.

While your Bronze table updates automatically, this introduces a problem for downstream consumers. To let your downstream business queries know what these new columns mean, or how a data type widening impacts calculation logic, Genie Ontology comes into play.