Forum Discussion

rcufley-lrdist's avatar
rcufley-lrdist
Frequent Visitor
3 years ago

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

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!



 

 

 

data engineering
Fundamentals

4 Replies

    • daxman's avatar
      daxman
      Advocate II
      3 years ago

      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's avatar
        rcufley-lrdist
        Frequent Visitor
        3 years ago

        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