Hello Databricks Community,

We are working with Airflow DAGs to trigger Databricks jobs that use Delta tables for performing upsert operations via a MERGE statement. The job was initially designed to perform a merge upsert with predefined Delta tables. However, we recently introduced a new column to the schema, and this has caused issues as the previous Delta tables don't have this new column, resulting in upsert failures.

While one solution we considered is to perform a schema overwrite just before the job run to add this new column with a NULL default, we are concerned that this approach would introduce unnecessary schema changes for every future job runs—even when the new column is already present. This would potentially lead to performance overhead and also lack clarity on when schema changes were actually necessary. Another approach would be, to change the code after the first job run to remove the modification since the metadata is established in delta with first run and the future job runs would be fine but we are missing the lineage of changes which might have happened.

Given this scenario, we believe this issue is akin to handling migrations in software engineering and managing table creation or modification in a robust way. We are looking for best practices or recommendations for:

1. Tracking Schema Changes: How can we manage schema evolution and ensure metadata lineage tracking for Delta tables in a consistent way?

2. Efficient Schema Updates: What are the best practices for modifying the schema without unnecessarily repeating operations? How can we avoid repeated schema additions if they are not needed anymore?

3. Table Creation and Management: Should we treat this as part of a database migration effort and ensure proper versioning of the Delta table schema? If so, what tools or strategies are recommended to track these changes effectively?

Any advice, strategies, or examples from the community would be greatly appreciated!

Thank you!