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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hello Anonymous ,
    We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet .
    In case if you have any resolution please do share that same with the community as it can be helpful to others .
    Otherwise, will respond back with the more details and we will try to help .

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Anonymous ,

      We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet .
      In case if you have any resolution please do share that same with the community as it can be helpful to others .
      Otherwise, will respond back with the more details and we will try to help .

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous 

    Using the OPTIMIZE command helped with one of the two tables in which I'm experiencing this problem. The other table shows the columns in the column list in the Lakehouse explorer, but as soon as I open the preview of the Delta Table, it does not show the new columns or new records of data.

     

    Maybe this has to do with the table size. The table for which OPTIMIZE did not help is quite a bit larger than the other one.

  • 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