UNION fails with NUM_COLUMNS_MISMATCH when schema changes

[Mart-Kruisselbrink]

Hi,

We are using bc2adls with the provided notebook to load data into a Microsoft Fabric Lakehouse. This setup has worked perfect so far, but we encountered the following error:

AnalysisException
[NUM_COLUMNS_MISMATCH] UNION can only be performed on inputs with the same number of columns, but the first input has 214 columns and the second input has 205 columns.

This occurs when the schema of incoming data changes (e.g. new/missing columns in Business Central), while the existing table in the Lakehouse still has the old schema.

The error originates from the union between new and existing data:

df_combined = df_new_filtered.union(df_old)

Impact:

Pipelines fail when schema changes occur, requiring manual intervention. Resetting the export from Business Central to Fabric is a time-consuming workaround and requires significant manual effort.

[Bertverbeek4PS]

Hi @Mart-Kruisselbrink the error is because I think you have changed the schema.
So what you need to do is remove the table in your lakehouse then reset that table in Business Central (I think you already have done it).
Then you can run it again.

[Mart-Kruisselbrink]

Hi @Bertverbeek4PS ,

Thanks for your response. And sorry for the late reaction.

Resetting the table and performing a new full load does work, but it requires manual intervention every time the Business Central schema changes.

In our case, we handle data extraction for multiple customers and we almost never have direct access to their Business Central environment. This means that resetting the export from Business Central is not always something we can do ourselves, and it can become quite time-consuming to coordinate with the customer.

I was wondering whether the notebook could support this scenario by using unionByName instead of union:

df_new = df_new.unionByName(df_old, allowMissingColumns=True).repartition(no_Partition)

The current union matches columns by position and therefore fails when the number of columns differs. With unionByName, columns are matched by name and missing columns are filled with null values.

Would this be a valid improvement for the BC2ADLS notebook, or is there a specific reason why the notebook currently requires a table reset after schema changes?

Kind regards,
Mart

[Bertverbeek4PS]

hi @Mart-Kruisselbrink,
No problem.

Saddle when there is a schema change we have to do this. That is because BC cannot handle parquet files and only csv files.
But there are API's for it that you can trigger from any application if you got the app registration and everything is set up.
I would recommend that way.

For the union there is no special need why I used used that. So I indeed could do that.

But in practice I think open morring is the better solution currently. So no need to run a notebook etc.