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

[rcufley-lrdist]

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!

[rcufley-lrdist]

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

[daxman]

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?

[rcufley-lrdist]

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

[daxman]

That resolved my issue, thank you so much!