Forum Discussion
Safest approach for handling schema changes
What is the safest approach for handling schema changes in source tables when downstream Fabric pipelines, Lakehouse tables, and Power BI semantic models depend on them?
For example, if a source system adds a new column, renames an existing column, changes a data type, or removes a column, what is the recommended way to manage these changes without breaking downstream pipelines and reports?
Would you recommend using schema validation, a staging layer, versioned schemas, or some other approach in Microsoft Fabric?
I would say that it will be a good approach to avoid allowing source schema changes to flow directly into the reporting layer.
A common pattern is:
Source → Bronze/Staging → Silver → Gold → Semantic Model
The Bronze layer can preserve the source structure, while the Silver/Gold layers provide a controlled schema for downstream consumers.
For potentially breaking changes such as column removal, renaming, or datatype changes, it is useful to:
- Validate the incoming schema before processing.
- Detect additions, removals and datatype changes.
- Keep the transformation layer independent of unnecessary source-specific changes.
- Test changes in a development/test workspace before production deployment.
- Review the impact on downstream pipelines, tables and semantic models before making breaking changes.
- Use deployment/versioning practices so that schema changes can be introduced in a controlled manner.
Adding a new nullable column is generally less disruptive than renaming/removing an existing column because downstream dependencies may reference the original column name.
For larger Fabric environments, I would also recommend maintaining a metadata/dependency inventory so that when a source column changes, you can identify which pipelines, tables, semantic models and reports could be affected before deploying the change.
The key principle is to detect and assess the schema change before it reaches the Gold/reporting layer, rather than discovering the problem after a production refresh fails.
4 Replies
Hi Selcii-16,
This depends on how your source system is changing. In general there's no safe way to handle removed columns. If a source removes a column that a report depends upon, then the report will break. I suppose you could always keep the column null on your side, but then the report may no longer be accurate as any visual that references that column will display nothing.
Adding columns is easy, you can set up your ETL to overwrite the schema in spark and that will add the new columns to your bronze/silver tables, and then I would recommend you leave them there until they are needed for something. Adding the columns to a semantic model is a manual process to my knowledge.
If columns are renamed, you'd need a way to identify what the old name was so you can remap the column.
I'd argue that any source that frequently renames or drops columns is a bad source.
Even though technically possible, you do not want Fabric to automatically pick up new schemas from sources. One way to implement is medallion architecture. Bronze could pick up new columns but Silver onwards continues to keep old columns so downstream does not break.
Using information schema you could easlily setup a process in place that compares columns between Bronze and Silver and sends you a notification / reports telling you about what has changed and you can react to it on a more organized way.
We have implemented this across all our Lakehouses because we pull data from lot of onprem systems that change schema without notice. Let me know if you need more inputs.Unfortunately, there is no clean and automatic way of handling schema changes. That's why schema evolution is an evergreen topic which gets people talking and disagreeing.
First thing to consider is if you really want to always copy every column from the source system. If you don't, you won't know when a new column is added to the source. If you do, you're always loading useless fluff into your data platform. With many ERP sources, you're rarely using more than 10% of the columns.
When you're not loading all columns
In this case, you're defining which columns you're fetching from the source. This option makes sure that new source columns won't break your pipeline. You're fetching and storing less data which is more efficient. The downside is when a source column is renamed or removed, your pipeline will fail before any data has even reached your platform. The only way to troubleshoot is to query the source to see what has changed.When you're loading all columns
This option let's you have more control on your platform side. However, without applying any additional logic on your ingestion process, any changes done on the source table schema will break your pipeline. In this case, you already have the raw data landed on your platform and most likely a helpful error message to get started with.What I would recommend if you want to keep loading all columns is to apply schema validation. There are many ways to get this done, and they all have their pros and cons.
I always have a raw data layer as files, and a delta table layer with metadata. That allows me to easily rerun the pipeline after applying fixes without burdening the source system again.
When you have loaded the raw data files on the platform, you can then compare the schema with the existing delta table. If there are no changes, you can just write the data as usual.
If a column has been added, you can either overwrite the schema by default. Or what I would rather do is set up a notification that let's you know a column has been added to the source system and investigate it yourself. You can then write all the other columns as usual.
If a column has been removed or renamed, you should also get notified. In these cases, you always need manual intervention. Even if you could technically write all the other columns as usual, I would strongly advice against it. If the column that has been dropped is crucial for your reports to work, you don't want the data to reach downstream. It's better to keep the last good version of data visible on the report than it is to show garbage.
TLDR: Compare new schema with existing one. Set up notifications. Investigate the change. Handle accordingly.
I would say that it will be a good approach to avoid allowing source schema changes to flow directly into the reporting layer.
A common pattern is:
Source → Bronze/Staging → Silver → Gold → Semantic Model
The Bronze layer can preserve the source structure, while the Silver/Gold layers provide a controlled schema for downstream consumers.
For potentially breaking changes such as column removal, renaming, or datatype changes, it is useful to:
- Validate the incoming schema before processing.
- Detect additions, removals and datatype changes.
- Keep the transformation layer independent of unnecessary source-specific changes.
- Test changes in a development/test workspace before production deployment.
- Review the impact on downstream pipelines, tables and semantic models before making breaking changes.
- Use deployment/versioning practices so that schema changes can be introduced in a controlled manner.
Adding a new nullable column is generally less disruptive than renaming/removing an existing column because downstream dependencies may reference the original column name.
For larger Fabric environments, I would also recommend maintaining a metadata/dependency inventory so that when a source column changes, you can identify which pipelines, tables, semantic models and reports could be affected before deploying the change.
The key principle is to detect and assess the schema change before it reaches the Gold/reporting layer, rather than discovering the problem after a production refresh fails.
