Forum Discussion
Advocate ILakehosue table has an error: An internal error has occurred while applying table changes to SQL.
Hello,
I have a pipeline in where it reads data from my SQL Server and then write it in a lakehouse table.
I set a scheduled refresh for this table, but even though the refresh succeeds, the data is not being updated. I tried to do a manual refresh but it still did not work. I now realized that some of my tables (including the one I am testing) have a red cross in the lakehouse with the error message: An internal error has occurred while applying table changes to SQL.
I could not manage to find any information related to this error message, so now I am wondering if data is not updating because of this.
What does this error mean and what can I do to solve it?
ilseeb - I find it odd that the Table was working was fine and then after a few days of runs it started to not load.
Can you clone the Pipeline and write the data to a new table, for testing? Furthermore, can you modify the source to only include records from before the first day you ran the table and then see if the new table works. Then run it again for the full dataset or whatever you are running now and see if the table breaks.
For the overwrite we noticed that the default "Overwrite" option was not actually overwriting things. There were some records deleted in the source that were still present in the Lakehouse after multiple job runs. I found a post in the Community where the Overwrite option was not doing everything that it did before.
The workaround (temporary one) was to use a Dynamic value of Overwrite for the Destination Action:
That seemed to work for us with the Schema and Data changes.
Jeremy
13 Replies
jwinchell40Resolver III
Did this table ever work/load properly?
I've run into similiar issues moving data into Lakehouses using the different methods and sources available to us. A few things I ran into that might be worth checking.
- Can you validate that all of the data types in the Source/Destination (if you can browse columns) are what they should be?
- Check for any spaces in the column names coming from the source; this should be less of an issue with Pipelines but spaces in column names on the Lakehouse side can be a problem
- Check DateTime columns or columns that should get converted to date time; the format could be off
Another thing on the Pipeline side, if you are doing an Overwrite there had been an issue where the traditional "overwrite" in the UI was not really overwriting the table. We had found a solution on the community where you can parameterize the Append/Overwrite option and set it to "Overwrite". Once we did that the tablees rebuilt properly.
Hopefully, this provides a starting point but let me know the outcome of the above and we can brainstorm some more.
Jeremy
Hi, do you happen to have a link to that solution in the community?
- ilseeb
Hi Jeremy,
The table worked in the past, I checked it during the first few days after I created it and it was working fine.
I checked the source and destination types, everything is ok, there are no spaces in the column names of the lakehouse and I changed DataTime datatype to Date to make it simpler. It still did not work. I tried both a manual refresh and the automatic refresh but the table is still not udpating.
Could you please provide more details about the solution from the community regarding"Overwrite" functionality?
- jwinchell40
ilseeb - I find it odd that the Table was working was fine and then after a few days of runs it started to not load.
Can you clone the Pipeline and write the data to a new table, for testing? Furthermore, can you modify the source to only include records from before the first day you ran the table and then see if the new table works. Then run it again for the full dataset or whatever you are running now and see if the table breaks.
For the overwrite we noticed that the default "Overwrite" option was not actually overwriting things. There were some records deleted in the source that were still present in the Lakehouse after multiple job runs. I found a post in the Community where the Overwrite option was not doing everything that it did before.
The workaround (temporary one) was to use a Dynamic value of Overwrite for the Destination Action:
That seemed to work for us with the Schema and Data changes.
Jeremy
- ilseeb
I tried the Overwrite dynamic value and it looks like it's working now. I'll keep monitoring it today and tomorrow to see if the table is correctly updated after each run.
Why do you mention that it was a temporary fix?
