Forum Discussion
Exclude All Null Column
Just to confirm,
- Is this a Fabric Data Pipeline? (e.g. not a data factory pipeline)
- The first Copy Data task populates a list of available tables for import?
- The For Each contains the copy task to move the data from the source database to the azure sql database, and then from the Azure SQL db to the Lakehouse?
- Has the new column been added to the source database that you're loading from?
- The For Each is failing because the new column doesn't exist downstream in the Azure SQL database?
- Is there a reason why you're not writing directly to the Lakehouse from the source database (eg cut out the azure sql db)?
Just a thought, but if you use the Overwrite option when writing to a Lakehouse table (not Files as the schema could evolve anyway if you were just writing parquet files) then that will evolve the schema of the lakehouse table automatically when source columns get added to the source.
Not sure if that could help in your situation? Overwrite would replace the data downstream with the latest incoming so not sure if that's what you want
Hi AndyDDC , thanks for responding!
In answer to your bullet points:- The first Copy Data task populates a list of available tables for import?
Yes, it runs through and lists out all of the tables for import. The table in question shows up in this step. - The For Each contains the copy task to move the data from the source database to the azure sql database, and then from the Azure SQL db to the Lakehouse?
Yes, it sends a copy of the data to the Azure database and then from there to the Lakehouse as Parquet files. - Has the new column been added to the source database that you're loading from?
Yes, the new column was added to the source database last week, but it wasn't communicated that it was being added so we only found out about it through the pipeline breaking. - The For Each is failing because the new column doesn't exist downstream in the Azure SQL database?
I believe so, but I'm not sure how to go about fixing this step as there is no explicit mapping to the source to be able to define the column or data type. I think this is the step that I'm just not getting my head around. I'm still getting to grasps with it all as I'm an analyst by trade but having to fill in until we get a proper engineer so trying my best until then 😊 - Is there a reason why you're not writing directly to the Lakehouse from the source database (eg cut out the azure sql db)?
We're building to a medallion architecture so the parquet files will give us some flexibility when applying transformations for the 'Silver Layer'. Definitely more than happy to listen to any suggestions if there are better ways of doing it.
Thanks.
So this is just my opinion on this, but you could write from the source database directly to the Lakehouse Files section (your raw/bronze layer). That would mean that any new columns/altered columns in the source will just get automatically written to Parquet files in the lakehouse files section.
The issue as you've discovered is that there is no auto-update on the azure sql database table schema, you'd have to either:
- drop and recreate the table in the azure sql database using a pre-copy script in the copy data task and set the auto-create table option on (this will of course delete the data from the azure sql db)
- for each table, pass in a sql statement that only included the columns you want into the copy data task
You could go one step further and just write the source data to the lakehouse tables and use Overwrite (although it does "overwrite" the data in the destination table, although keeps history using delta versioning).
Hi heastham ,
We haven’t heard from you on the last response and was just checking back to see if your query got resolved. Please let us know if you have any further queries.
- The first Copy Data task populates a list of available tables for import?
