Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Lakehouse - Delta table columns not updating

Hi all,   I am using a Python Notebook that's being triggered by a data factory to update Delta tables each night. In short, the process is as follows: 1. A data factory orchestrates all processes...
data engineering
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Anonymous ,

Thanks for using Fabric Community.

As I understand the issue you're facing is that new columns and records added to a Delta Lake table through notebooks triggered by a Data Factory pipeline are not appearing when queried via the Lakehouse SQL endpoint, but they are visible in PySpark notebooks.

The Delta Lake table's internal metadata may not be fully updated, can you please execute optimization commands like OPTIMIZE or VACUUM?

Can you please try and let me know.

sgunasekhar's avatar
sgunasekhar
Regular Visitor
2 years ago

I am encountering the same issue where renaming an existing column in a Delta table reflects in the PySpark Notebook but does not update in the SQL Endpoint. I have tried using VACUUM and OPTIMIZE commands, but they did not resolve the problem.

 

I have attempted to resolve this issue by dropping the table, deleting associated folders, and recreating the Delta table, but the problem persists—column renames and new columns still do not reflect in the SQL Endpoint

 

This seems to be a critical limitation, as basic operations like adding, renaming, or dropping columns in Delta tables are not syncing correctly with the SQL endpoint. This issue is severely impacting our project timeline, and we urgently need a workaround or a permanent solution.

 

fabricator1 Anonymous Anonymous AndyDDC Anonymous

  • frithjof_v's avatar
    frithjof_v
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Are you using the column name mapping feature? That may be a reason why the sync to SQL Analytics Endpoint fails.

     

    Something like 

     

    ALTER TABLE table_name SET TBLPROPERTIES (

       'delta.columnMapping.mode' = 'name',

       'delta.minReaderVersion' = '2',

       'delta.minWriterVersion' = '5')

     

    In that case, I think the sync to the SQL Analytics Endpoint breaks somehow if you enable the column name mapping.

     

    Or are you experiencing really slow updates in the SQL Analytics Endpoint?

    You can try to create another Lakehouse in another Workspace, and use a shortcut to bring your original table into the new lakehouse. For some users, that has made the SQL Analytics Endpoint perform better. 

    One reason for slow SQL Analytics Endpoint can be many lakehouses in the same workspace.

     

    Perhaps there is something to be gained from these docs:

    https://learn.microsoft.com/en-us/fabric/data-warehouse/sql-analytics-endpoint-performance