topic Re: ALTER TABLE DW in Data Warehouse

ALTER TABLE DW

smpa01 — Mon, 26 Aug 2024 15:33:00 GMT

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.

Re: ALTER TABLE DW

AndyDDC — Mon, 26 Aug 2024 17:06:48 GMT

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/

Re: ALTER TABLE DW

smpa01 — Mon, 26 Aug 2024 17:56:11 GMT

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?

Re: ALTER TABLE DW

AndyDDC — Mon, 26 Aug 2024 20:35:02 GMT

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.

Re: ALTER TABLE DW

frithjof_v — Tue, 27 Aug 2024 08:56:59 GMT

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

Re: ALTER TABLE DW

smpa01 — Tue, 27 Aug 2024 20:08:50 GMT

Voted

Re: ALTER TABLE DW

AndyDDC — Tue, 27 Aug 2024 20:48:26 GMT

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

Re: ALTER TABLE DW

frithjof_v — Sun, 01 Sep 2024 08:50:14 GMT

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.

Re: ALTER TABLE DW

cgonzalesrod — Wed, 13 May 2026 15:07:44 GMT

I found this to solve the problem. Please note that the column data type I was trying to change was 'day'.

-- 1. Add a new column with the correct data type

ALTER TABLE Dim_Dates ADD day_int INT;

-- 2. Copy data to the new column

UPDATE Dim_Dates SET day_int = CAST(day AS INT);

-- 3. Delete original column

ALTER TABLE Dim_Dates DROP COLUMN day ;

-- 4. Rename new column

EXEC sp_rename 'Dim_Dates .day_int', 'day', 'COLUMN';

Re: ALTER TABLE DW

Prince0011 — Sat, 11 Jul 2026 09:55:55 GMT

At the moment, this is a current limitation of Microsoft Fabric Data Warehouse.

ALTER TABLE ... ALTER COLUMN (such as changing a column's data type) is not supported in Fabric Warehouse. However, adding a new column using ALTER TABLE ... ADD is supported.

If you need to change a column's data type, the typical workaround is to:

Create a new table with the desired schema.
Copy the data from the existing table (using CAST/CONVERT where necessary).
Validate the data.
Rename or replace the original table, if appropriate.

While this isn't as convenient as a direct ALTER COLUMN, it's currently the recommended approach for schema changes that modify existing column definitions.

Microsoft has been steadily expanding the T-SQL surface area in Fabric Warehouse, so support for additional DDL operations may be added in future releases. It's worth keeping an eye on the release notes and Fabric roadmap for updates.

For more information:

Fabric Data Warehouse T-SQL surface area: https://learn.microsoft.com/fabric/data-warehouse/tsql-surface-area
Microsoft Fabric roadmap: https://roadmap.fabric.microsoft.com/

If this answers your question, please consider accepting it as the solution so it can help others facing the same limitation. If you found it helpful, a Kudos would also be appreciated!

Re: ALTER TABLE DW

Gautam_Kumar01 — Tue, 14 Jul 2026 04:47:19 GMT

Hi smpa01

I understand the frustration. This is a known limitation in Fabric Data Warehouse as of today.

Root Cause:

Fabric DW is built on Synapse Dedicated SQL Pool architecture. In this engine, `ALTER TABLE ALTER COLUMN` is not supported. That’s why you are getting:

`Msg 2401, Level 16, State 2, Line 21 - The specified ALTER TABLE statement is not supported`

What IS supported vs NOT supported:

| Operation | Status in Fabric DW |

| --- | --- |

| `ALTER TABLE ADD COLUMN` | Supported |

| `ALTER TABLE DROP COLUMN` | Supported |

| `ALTER TABLE ALTER COLUMN` | Not Supported |

| `ALTER TABLE RENAME` | Not Supported |

**Recommended Workaround - CTAS Pattern:**

Microsoft recommends using `CREATE TABLE AS SELECT` to change data types. Here is the full flow for your case:

```sql

Step 1: Create new table with updated schema

CREATE TABLE [dbo].[test_log_new]

WITH

(

DISTRIBUTION = ROUND_ROBIN,

CLUSTERED COLUMNSTORE INDEX

)

SELECT

CAST([row_sum] AS DECIMAL(38,6)) AS [row_sum], -- changed data type

[col2],

[col3],

CAST(NULL AS VARCHAR(50)) AS [ingestion_engine] -- new column added

FROM [dbo].[test_log];

Step 2: Validate data in new table

SELECT TOP 100 * FROM [dbo].[test_log_new];

Step 3: Swap tables

DROP TABLE [dbo].[test_log];

EXEC sp_rename 'dbo.test_log_new', 'test_log';