How to Alter a Single Column’s Datatype in Fabric Warehouse (or Workaround)

[Mahimaa29]

I want to modify the datatype of a single column in a Microsoft Fabric Warehouse table. However, it seems that the ALTER COLUMN command is not currently supported.

Could someone confirm whether it’s possible to alter a column’s datatype directly in Fabric Warehouse? If not, what is the recommended workaround

[v-tejrama]

Hi @Mahimaa29 ,

Thank you @tayloramy  and @AsgerLB  for the response provided!

Has your issue been resolved? If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Thank you.

[v-tejrama]

Hi @Mahimaa29 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[tayloramy]

Hi @Mahimaa29, 

Right now, ALTER COLUMN is not supported. See T-SQL Surface Area in Fabric Data Warehouse - Microsoft Fabric | Microsoft Learn

You would need to make a new table to change the data type. I recommend using a CTAS statement. 

1. Create a new table with the corrected type via CTAS, casting the target column.

2. Recreate any constraints

3. Swap names using sp_rename on the tables, then drop the old table.

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.

[AsgerLB · Accepted solution]

Hi Mahimaa29,

Since direct column alteration (ALTER COLUMN) is unsupported in Fabric DW, you have two primary options: a multi-step workaround to permanently change the base table, or using a view to change the data type presentation without modifying the table itself.

For changing the base table, you can add a new col, populate the new col with the old col's data in a cast and then delete the old col.

ALTER TABLE DimCustomer
ADD YearlyIncomeText VARCHAR(100);


UPDATE DimCustomer
SET YearlyIncomeText = CAST(YearlyIncome AS VARCHAR(100));

ALTER TABLE DimCustomer
DROP COLUMN YearlyIncome;

This will however, require you to update any mappings nessecary.

For changing the presentation layer you can create a simple view on top of the data.

CREATE VIEW V_DimCustomer_Formatted
AS
SELECT
    CityName,
    CAST(YearlyIncome AS VARCHAR(100)) AS YearlyIncomeText, -- The change happens here
    RegionCountryName,
    Phone
FROM
    DimCustomer;

  Both approaches works in Fabric 🙂 So it depends on where in your medallion architecture you are trying to make the fix.

Br
Asger