Forum Discussion

fabchinmay's avatar
fabchinmay
Icon for Microsoft Employee rankMicrosoft Employee
1 year ago
Solved

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

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 ...
one lake
  • v-csrikanth's avatar
    v-csrikanth
    1 year ago

    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.

  • burakkaragoz's avatar
    burakkaragoz
    1 year ago

    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's avatar
v-csrikanth
Icon for Community Support rankCommunity Support
1 year ago

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.