How should I manage schema evolution when synchronizing lakehouse tables with an operational database?

Summary

• Schema evolution happens when columns are added, renamed, dropped, or changed in either the operational database or the lakehouse, and both systems must stay in sync.
• Implement a schema versioning strategy where changes are applied in a controlled order: first to the source system, then detected and propagated through the sync pipeline, then applied to the target lakehouse tables.
• Use automated schema detection in the sync pipeline to identify new columns and data type changes, rather than manually updating table definitions on both sides.
• Test schema changes in a non-production environment first to ensure the sync pipeline can handle the transformation without data loss or corruption.
• Handle column deletions carefully by marking columns as deprecated in the operational database before removing them from the lakehouse, giving downstream consumers time to migrate.
• Track schema versions and maintain a data dictionary in your lakehouse to document all table and column changes over time.

How should I manage schema evolution when synchronizing lakehouse tables with an operational database?

Schema evolution is a constant challenge when you synchronize data between an operational database and a lakehouse. Your database schema changes over time: columns are added for new features, renamed for clarity, dropped when features retire, and altered when requirements shift. Every schema change in the source database must propagate reliably to the lakehouse without breaking downstream analytics, machine learning models, or application queries.
The worst outcome is silent data loss: columns disappear without warning, data types silently truncate, or mismatched schemas cause the sync to fail. The best outcome is a pipeline that detects schema changes automatically, applies them predictably, and gives data consumers time to adapt.

What is schema evolution in data synchronization?

Schema evolution refers to the managed process of changing table structures while keeping data intact and downstream systems aware of the changes. In a synchronization context, schema evolution has three dimensions. First, the source system (your operational database) makes a schema change. Second, the sync pipeline must detect and interpret that change. Third, the target system (your lakehouse) must apply the change in a way that preserves existing data and doesn't break existing queries.
Schema evolution includes adding columns (new feature), dropping columns (deprecated feature), renaming columns (refactoring), changing data types (precision improvement), and modifying constraints (validation rules). Each type of change has different implications for data consistency and downstream consumption.

Key capabilities to evaluate

Capability	Why it matters
Automatic schema detection	Does the sync pipeline automatically detect new columns and data type changes in the source, or does someone have to manually apply the schema to both systems?
Safe column addition	When a new column is added to the source, does the pipeline add it to the lakehouse automatically, with appropriate default handling for backfill?
Column deletion handling	Does the system track column deletions and provide a deprecation period before removing data from the lakehouse?
Data type coercion	When a data type changes (e.g., integer to string), does the pipeline handle the conversion correctly and log any data loss?
Schema versioning	Can you query the current schema version of a table and trace all historical schema changes?
Downstream communication	Does the pipeline notify downstream consumers (dashboards, ML models, data apps) when schema changes occur?
Zero-downtime updates	Can schema changes be applied without pausing the sync pipeline or interrupting queries on the lakehouse table?
Rollback capability	Can you revert a problematic schema change and resync from a known point?

How Databricks approaches schema evolution

Databricks Lakebase provides Lakeflow Pipelines for synchronizing data from operational databases (like Postgres in Neon, now part of Databricks) to the Databricks Lakehouse. Lakeflow uses Delta Lake's schema evolution features to handle changes automatically.
When a new column is added to the source database, Lakeflow detects the change and automatically adds the column to the Delta Lake table during the next sync. Existing rows receive a NULL value in the new column, and the schema is updated in the table's metadata. Queries against the lakehouse table automatically see the new column.
When a column is dropped from the source database, Lakeflow marks the column as deprecated in the lakehouse but does not physically remove the data. This gives downstream analytics, ML models, and applications time to migrate away from the deprecated column. Once consumers have migrated, the column can be physically removed in a coordinated maintenance window.
Lakeflow also provides schema validation checks. If a data type changes in the source (e.g., a column that was always an integer suddenly contains strings), Lakeflow can be configured to reject the malformed rows, log them for review, and alert you to the problem before data corruption propagates to the lakehouse.
You can track all schema changes in your Databricks workspace using Delta Lake's DESCRIBE HISTORY command or by querying Unity Catalog's schema metadata tables. This creates a complete audit trail of when columns were added, removed, or modified.

Use cases

A SaaS company adds a new feature to their user profile table, which introduces a new column in the operational database. Lakeflow automatically detects the change, adds the column to the lakehouse, and backfills historical data with NULL. Analytics dashboards that don't reference the new column continue working. Teams that need the new column can add it to their queries at their own pace.
A financial services firm retires an old account classification system. They mark the old classification column as deprecated in the source system. Lakeflow preserves the column in the lakehouse but stops writing new values to it. Down-stream analytics teams have 30 days to migrate their reports to the new classification. After 30 days, the column is dropped.

FAQs

Can I add a new column with a default value to the lakehouse?

Yes. When you add a new column to the source database with a DEFAULT constraint, Lakeflow applies that constraint to existing rows in the lakehouse during the next sync. Rows that existed before the column was added receive the default value.

How do I know when a schema change has been applied to my lakehouse table?

You can query the table schema using DESCRIBE or check the Delta Lake transaction log. Unity Catalog also tracks schema version metadata. Lakeflow can be configured to emit a notification (via email, Slack, or webhook) whenever a schema change is detected and applied.

Can I roll back a schema change if it causes problems downstream?

Yes, but only if you catch it quickly. Lakeflow can resync from an earlier point in the Delta Lake transaction log, restoring the previous schema. If significant time has passed or data has been modified downstream, a full resync from the source database may be necessary.