Forum Discussion

smpa01's avatar
smpa01
Community Champion
2 years ago

ALTER TABLE DW

I have been trying to use ALTER Table Alter Column on a data warehouse table but it does not sem to be supported.   I have a table that has a column a column [row_sum] [bigint] NULL I want to c...
data warehouse
Warehouse
AndyDDC's avatar
AndyDDC
Most Valuable Professional
2 years ago

As I've explained in this thread it's the "delta column mapping" feature, not individual functionality, that is missing.

frithjof_v's avatar
frithjof_v
Community Champion
2 years ago

I've also voted for this Idea:

 

Support Name Column Mapping Mode in SQL Endpoints for Lakehouse
https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=a93f4fa7-8d03-ee11-a81c-000d3ae52c8f

 

I hope many people will vote for this in order to highlight the need.

I hope it will be implemented both in SQL Analytics Endpoint and Data Warehouse.

 

 

An interesting blog article (the below is regarding Lakehouse, however I guess the same principles apply to Warehouse as well)

https://delta.io/blog/2022-08-29-delta-lake-drop-column/

 

I think the "Delta Lake drop column additional considerations" section of the article is especially interesting.

Word of caution regarding DROP column with delta column mapping enabled:

The data column doesn't get physically deleted. It just gets ignored in queries. So we still need to pay for storing that data. DROP column also would not satisfy regulatory requirements to physically delete the data.

 

The alternative is to overwrite the table with overwriteSchema and do vacuuming afterwards (or drop and recreate the table?). This is a more compute-costly alternative, but if the requirement is to physically delete the data then this seems to be the way to do it.