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

Question

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?

Answer

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

Share via

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

1 answer