Forum Discussion

smpa01's avatar
smpa01
Community Champion
1 year 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 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.

 

data warehouse
Warehouse

10 Replies

    • AndyDDC's avatar
      AndyDDC
      Most Valuable Professional
      1 year 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
        1 year 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.

    • smpa01's avatar
      smpa01
      Community Champion
      1 year ago

      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's avatar
        AndyDDC
        Most Valuable Professional
        1 year ago

        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.

  • cgonzalesrod's avatar
    cgonzalesrod
    New Member
    3 months ago

    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';
  • Prince0011's avatar
    Prince0011
    Solution Sage
    1 month ago

    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:

    1. Create a new table with the desired schema.

    2. Copy the data from the existing table (using CAST/CONVERT where necessary).

    3. Validate the data.

    4. 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:

    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!

  • Gautam_Kumar01's avatar
    Gautam_Kumar01
    Post Partisan
    1 month ago

     

    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

    )

    AS

    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';