ALTER TABLE ADD COLUMN changes made to Synapse Delta tables in a Notebook do not reflect in Lake Database

Francesca Lightbown 0 Reputation points
2024-06-30T20:51:36.6366667+00:00

Running the following

spark.sql("ALTER TABLE example_table ADD COLUMN example_column STRING")

Successfully adds the column to the Delta table and this can be verified by running

%%sql

SELECT example_column FROM example_table

However when viewing the table from Lake Database example_column does not exist. I have tried running

%%sql

REFRESH TABLE example_table

AND

spark.catalog.refreshTable("example_table")

and it does not make a difference. Please could somebody offer a solution?

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,692 questions
{count} votes

1 answer

Sort by: Most helpful
  1. hossein jalilian 5,560 Reputation points
    2024-06-30T23:00:54.5066667+00:00

    Thanks for posting your question in the Microsoft Q&A forum.

    Here are a few steps you can try to ensure the schema is correctly updated in the Lake Database:

    • Verify the schema directly in Spark to ensure the column addition is recognized
        DESCRIBE TABLE example_table
        
      
    • Make sure that the table you are querying in the Lake Database is pointing to the correct Delta table location. Mismatched paths can cause schema inconsistencies.
    • Running vacuum and optimize commands can sometimes help in making sure the metadata is consistent.
        VACUUM example_table RETAIN 0 HOURS;
      

    OPTIMIZE example_table;

      
    - If the above steps don’t work, you might need to recreate the Lake Database table to ensure it picks up the latest schema changes. First, drop the table and then recreate it.
    
      ```sql
      DROP TABLE IF EXISTS lake_database.example_table;
    CREATE TABLE lake_database.example_table USING DELTA LOCATION 'path_to_your_table';
      
    

    Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful