ALTER TABLE DW

[smpa01]

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 change it to [row_sum] DECIMAL(38,6) NULL and add a new column

But I am not being able to do that

It is extremely frustrating, what does MS suggest the BI author should in such case, DELETE the whole table and start from scratch?

Also, is ALTER Table be allowed in DW in future?

Thank you in advance.

[frithjof_v]

I created some ideas, please vote to highlight the need:

Fabric Warehouse - drop column

https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=f895cdc6-5164-ef11-a4e5-000d3ae625cf

Fabric Warehouse - rename columns

https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=e0610603-5264-ef11-a4e5-000d3ae625cf

Fabric Warehouse - Change column type

https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=82e8ca31-5264-ef11-a4e5-000d3ae625cf

[AndyDDC]

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

[frithjof_v]

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.

[smpa01]

Voted

[AndyDDC]

Hi @smpa01 currently you can only add new columns to an existing table. This is because MS are using an older version of the Delta Lake read/write protocol.  There's no info yet when further support for ALTER TABLE will be supported.

I have more info in this blog https://www.serverlesssql.com/the-reality-of-alter-table-in-fabric-warehouses-2/

[smpa01]

This is simply not practical.

MS must inform this community when can we expect this to happen?

Till it happens what are the alternatives?

Theses gotchas keep on preventing any serious data-engineering in Fabric.

It is simply not possible to simply DELETE a table with valuable data to alter a single column type, imagine the nightmare of the fabric developer if this sort of req keeps coming frequently from business every now and then. How do I deal with it in absence of ALTER?

[AndyDDC]

At the moment you would need to write the new table out and delete the old table.  I do this by writing the new table and then using sp_rename to switch the names of the table.  As I said, MS are working from the OSS Delta specification and there is no word yet on when the delta "column mapping" feature will be implemented.  

If this feature is on the roadmap then it'll appear here https://learn.microsoft.com/en-gb/fabric/release-plan/data-warehouse

-------------------------------

If my answer has been useful please provide kudos and mark as the solution.