Data Type Modification in Fabric Lakehouse Table – Limitations & Supported Conversions

[fabchinmay]

Hi Team,

I am trying to modify the data type of a table column that contains data using a notebook (Spark SQL) and SQL Endpoint queries.

However, neither of these approaches seem to be working. Is this a product limitation, or am I missing something?

Additionally, could you provide a list of all possible data type conversions in a Fabric Lakehouse table and the steps to achieve them?

%%sql

create table dbo.table1

(id int,

name string,

sal DECIMAL(5,3),

dob date,

modifieddatetime bigint)

using delta

%%sql

insert into dbo.table1

VALUES

(1, 'xyz', 10, '2025-02-10', 12378684542677823),

(2, 'abc',     10, '2025-02-10', 12378684542677823),

(3, 'pqr',   10, '2025-02-10', 12378684542677823),

(4, 'mno',    10, '2025-02-10', 12378684542677823)

[v-csrikanth · Accepted solution]

Hi @fabchinmay 

Sorry fo the late response.
Thank you for being part of the Microsoft Fabric Community.
You're running into a known limitation in Microsoft Fabric Lakehouse — you cannot directly alter column data types on an existing Delta Lake table using ALTER TABLE in Spark SQL or SQL Endpoint once the table has data. This is a Delta Lake constraint.

ALTER TABLE dbo.table1 ALTER COLUMN modifieddatetime TYPE STRING;
This will fail silently or throw an error, depending on the engine used (Spark vs SQL endpoint).
For a work around you will need to create a new table with the desired schema, then insert the data from the old table with the appropriate casting.
For reference please refer the documenet posted by @lbendlin 

 Data types - Microsoft Fabric | Microsoft Learn

If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @fabchinmay 

We haven't heard from you since last response and just wanted to check whether the solution provided has worked for you. If yes, please Accept as Solution to help others benefit in the community.
Thank you.

If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @fabchinmay 

It's been a while since I heard back from you and I wanted to follow up. Have you had a chance to try the solutions that have been offered?
If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!

Best Regards,
Community Support Team _ C Srikanth.

[burakkaragoz · Accepted solution]

Hi @fabchinmay ,

yeah, this is a known limitation with Fabric Lakehouse tables (Delta format). You can’t directly change the data type of a column if the table already has data – neither via Spark SQL nor SQL Endpoint.

Here’s what we usually do:

---

Option 1 – Create a new table with correct types

CREATE TABLE dbo.new_table (
  id INT,
  name STRING,
  sal DOUBLE,
  dob DATE,
  modifieddatetime BIGINT
) USING DELTA;

INSERT INTO dbo.new_table
SELECT CAST(id AS INT), name, CAST(sal AS DOUBLE), dob, modifieddatetime
FROM dbo.old_table;

Then drop the old table and rename the new one if needed.

---

Option 2 – Use Data Wrangler (GUI)
If you’re using notebooks, you can load the table into a DataFrame and use Data Wrangler to change column types visually. It’s not perfect, but works for small tables.

---

Note: Even if you manage to change the type via Spark, it might break the SQL Endpoint metadata sync – so better to go with a clean table.

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.

[v-csrikanth · Accepted solution]

Hi @fabchinmay 

Sorry fo the late response.
Thank you for being part of the Microsoft Fabric Community.
You're running into a known limitation in Microsoft Fabric Lakehouse — you cannot directly alter column data types on an existing Delta Lake table using ALTER TABLE in Spark SQL or SQL Endpoint once the table has data. This is a Delta Lake constraint.

ALTER TABLE dbo.table1 ALTER COLUMN modifieddatetime TYPE STRING;
This will fail silently or throw an error, depending on the engine used (Spark vs SQL endpoint).
For a work around you will need to create a new table with the desired schema, then insert the data from the old table with the appropriate casting.
For reference please refer the documenet posted by @lbendlin 

 Data types - Microsoft Fabric | Microsoft Learn

If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @fabchinmay 
It's been a while since I heard back from you and I wanted to follow up. 
If the issue has been clarfied on the data types.
If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!

[fabchinmay]

Hi , The issue is still persists. I am unable to modify the data type of a table column that contains data using a notebook (Spark SQL) and SQL Endpoint queries

[lbendlin]

This may come as a little bit of a shock to you...

Data types - Microsoft Fabric | Microsoft Learn