topic Re: Lakehouse SQL endpoint tables "Sync Error" after adding a column to table in Data Engineering

Lakehouse SQL endpoint tables "Sync Error" after adding a column to table

rcufley-lrdist — Thu, 22 Jun 2023 23:25:29 GMT

Hi,

Having an issue with the Lakehouse SQL Endpoint tables report a "Sync Error" state after adding a column to the deltatable.

For example:

Initially created a DimDate table via:

df.write.format('delta').mode("overwrite").option("overwriteSchema", True).save('Tables/DimDate')

Table was accessable via SQL Endpoint

After adding a column for "MonthEndDate" to the dataframe and running the notebook again, SQL Endpoint of DimDate does not show the new column and reports this error:

MonthEnd is not available via the SQL Endpoint, however it is available within spark queries:

Am I doing something incorrect in the saving of the dataframe? Also got same result when merging schema instead of overwriting.

Any insight appreciated, thanks!

Re: Lakehouse SQL endpoint tables "Sync Error" after adding a column to table

rcufley-lrdist — Fri, 23 Jun 2023 13:30:55 GMT

Turns out the issue was foreign keys inhibit syncing of the schema.

Limitations and known issues - Microsoft Fabric | Microsoft Learn

Dropped the constraints and tables sync'd and issue resolved

Re: Lakehouse SQL endpoint tables "Sync Error" after adding a column to table

daxman — Tue, 27 Jun 2023 14:26:00 GMT

Interesting. I had some relationships defined in my Lakehouse SQL Endpoint, added columns to 2 tables in Spark, then ran into your same issue.

I removed all relationships from the model, and the SQL Endpoint is still having issues syncing. This is after waiting for 15 mins, trying to force a refresh from button in top left corner. Nothing seems to get it to sync again.

What exactly did you do after removing the relationships to get it to sync again?

Re: Lakehouse SQL endpoint tables "Sync Error" after adding a column to table

rcufley-lrdist — Tue, 27 Jun 2023 17:14:59 GMT

After removing the relationships from the model, I also deleted the foreign key constraints via a SQL endpoint query.

For example:

-- identifiy the foreign key constraint for each fact table
sp_fkeys 'FactSales'

-- then drop it

ALTER TABLE dbo.FactSales DROP CONSTRAINT FK_bd9f34c4_b835_4e87_956a_xxxxxxxxxxxx

Re: Lakehouse SQL endpoint tables "Sync Error" after adding a column to table

daxman — Tue, 27 Jun 2023 22:14:51 GMT

That resolved my issue, thank you so much!