How should schema evolution be handled across silver and gold layers in a medallion architecture?

[temarych]

We run a medallion pipeline on Databricks:

• Bronze: AutoLoader ingests raw CSV files into Delta tables (append-only, all columns as STRING, schema evolution via addNewColumns)
• Silver: PySpark jobs clean and transform bronze data into Delta tables using batch writes and CDC merge
• Gold: dbt incremental models aggregate silver into fact and measure tables

AutoLoader handles bronze schema evolution automatically. The problem is silver and gold.

When a source adds or renames a column, we currently handle it manually - authoring ALTER TABLE statements for silver Delta tables and running dbt run --full-refresh for gold. This works but is ad-hoc and error-prone, especially for column renames.

Our most frequent case is new columns being added by the source. We also care about dropped columns - we don't want dead columns accumulating in silver and gold tables over time.

Questions:

1. Is there a recommended pattern for managing silver Delta table schema changes in a DAB-deployed pipeline - beyond manual ALTER TABLE?
2. Should we introduce a schema migration tool like Alembic/Liquibase/Flyway for Delta tables?

[balajij8]

You can skip using traditional schema migration tools like Liquibase, Flyway or Alembic into Delta Lake as they were generally designed for relational databases where state is tracked through sequential DDL scripts.

Silver Layer

• New Columns - You can let Spark handle column additions automatically during writes. For standard append or overwrite operations, configure the schema merge option on the Data Frame writer. You can check the cdc merge configuration & use it if feasible

• Column Renames -  You can treat renames as transformation logic rather than table migrations. Keep Bronze completely immutable. In your Silver transformations, read the incoming Bronze fields and alias them to the standardized Silver naming conventions. If you must physically rename a column on a Delta table without rewriting underlying data files, enable Delta Column Mapping on the Silver table properties which generally turns renames into instant metadata updates.

• Dropped Columns - To keep Silver from accumulating dead columns over time, you can avoid using SELECT * in Silver job definitions explicitly project the required column list instead. If you need to physically purge a dropped column from the table metadata without triggering a heavy data rewrite, use Delta Column Mapping allows you to execute column drops as metadata operations if feasible.

Gold Layer

• Automatic Column Sync - Configure the on_schema_change setting in your dbt incremental model config blocks or globally in your project file. Setting on_schema_change to sync_all_columns will generally add new columns to Gold and drop removed columns on the next incremental execution. Alternatively, append_new_columns will add incoming fields while preserving historical columns if an upstream field is dropped.

• Renames & Deprecations - Handle column renames within your dbt model CTEs by aliasing the updated Silver column back to the expected Gold schema. For dropped columns where downstream BI dashboards still uses, you can supply default NULL values in the dbt SELECT statement before removing the column entirely via sync_all_columns.

Deployment

You can add a schema validation task at the start of your workflow that compares bronze and silver metadata, firing an alert when schema drift occurs to catch unexpected source changes before they hit reports.