Right workaround to solve the unsupported Truncate Table

[pmscorca]

Hi,

during some tests to feed some warehouse tables I've noticed that the TRUNCATE TABLE is unsupported. This is a serious lack for Fabric!

The statement is supported by SQL Server and Synapse Analytics without any issues.

I'd like know because this feature isn't supported.

For now, I've solved using a DELETE statement, but is it the right workaround to apply? A lakehouse/warehouse is based on delta tables: could using a DELETE statement be a bad practice against delta tables?

If possible, I would avoid to use a DROP TABLE and a CREATE TABLE statement.

Thanks

[v-cboorla-msft · Accepted solution]

Hi @pmscorca 

Thanks for using Microsoft Fabric Community.

I understand your concern about the lack of support for the TRUNCATE TABLE statement in Microsoft Fabric. The reason for this limitation is due to the architecture of Fabric, which is designed to work with delta tables and lakehouse storage. TRUNCATE TABLE is a SQL Server-specific statement that is optimized for relational databases, and it doesn't translate well to the distributed, columnar storage of Fabric. In Fabric, tables are composed of multiple files, and TRUNCATE TABLE would require rewriting the entire table, which could be inefficient and lead to data inconsistencies.

Using a DELETE statement is a viable workaround, it's important to consider its limitations. DELETE statements can be slower and more resource-intensive than TRUNCATE TABLE, especially for large tables. Additionally, DELETE statements can leave behind empty files, which can lead to storage inefficiencies.

A better approach might be to use the VACUUM statement, which is specifically designed for delta tables in Fabric. VACUUM removes all data from a table, including files and metadata, making it a more efficient and storage-friendly option.
If you need to completely remove a table, using a DROP TABLE statement followed by a CREATE TABLE statement is a valid approach. While it may seem cumbersome, it ensures that the table is properly removed and recreated, which can be important for data consistency and integrity.

In summary, while TRUNCATE TABLE is not supported in Fabric, using DELETE or VACUUM statements can be viable workarounds, depending on your specific use case. If you need to completely remove a table, using DROP TABLE and CREATE TABLE statements is a safe and reliable approach.

I hope this information helps.

Thank you.

[AndyDDC]

Hi @pmscorca TRUNCATE table is now supported in Fabric Warehouses

TRUNCATE TABLE (Transact-SQL) - SQL Server | Microsoft Learn

[FrancescoPwc]

Hi @AndyDDC is it available also on lakehouse tables? (from lakehouse SQL Endpoint and Spark sql from notebooks?)

[v-cboorla-msft · Accepted solution]

Hi @pmscorca 

Thanks for using Microsoft Fabric Community.

I understand your concern about the lack of support for the TRUNCATE TABLE statement in Microsoft Fabric. The reason for this limitation is due to the architecture of Fabric, which is designed to work with delta tables and lakehouse storage. TRUNCATE TABLE is a SQL Server-specific statement that is optimized for relational databases, and it doesn't translate well to the distributed, columnar storage of Fabric. In Fabric, tables are composed of multiple files, and TRUNCATE TABLE would require rewriting the entire table, which could be inefficient and lead to data inconsistencies.

Using a DELETE statement is a viable workaround, it's important to consider its limitations. DELETE statements can be slower and more resource-intensive than TRUNCATE TABLE, especially for large tables. Additionally, DELETE statements can leave behind empty files, which can lead to storage inefficiencies.

A better approach might be to use the VACUUM statement, which is specifically designed for delta tables in Fabric. VACUUM removes all data from a table, including files and metadata, making it a more efficient and storage-friendly option.
If you need to completely remove a table, using a DROP TABLE statement followed by a CREATE TABLE statement is a valid approach. While it may seem cumbersome, it ensures that the table is properly removed and recreated, which can be important for data consistency and integrity.

In summary, while TRUNCATE TABLE is not supported in Fabric, using DELETE or VACUUM statements can be viable workarounds, depending on your specific use case. If you need to completely remove a table, using DROP TABLE and CREATE TABLE statements is a safe and reliable approach.

I hope this information helps.

Thank you.

[FrancescoPwc]

this is bad, I am finding fabric much less versatile than databricks. TRUNCATE is a property of delta tables, it should be allowed!

There is also no support for SHOW CREATE TABLE!

VACUUM is not a viavble substitute and DROP AND CREATE is too time consuming

[pmscorca]

Hi,

I've voted this idea: Support TRUNCATE TABLE in Fabric Warehouse 

[pmscorca]

Hi, I need to delete all data as the TRUNCATE TABLE statement does.

In order to use the VACUUM (T-SQL) statement, where could I find the official documentation to delete all data of a warehouse table?

I need to use the VACUUM statement as a pre-copy script in a copy activity.

Thanks

[TwinkleCyril · Accepted solution]

TRUNCATE Table is now generally available is Microsoft Fabric DW. Review TRUNCATE TABLE (Transact-SQL) - SQL Server | Microsoft Learn for more details.

[pmscorca]

Hi, this is a good new but the Fabric Updates blog doesn't seem updated. Thanks

[TwinkleCyril]

It will be available in Aug blog.

[v-cboorla-msft]

Hi @pmscorca 

Please refer to the similar thread which might help you.

Thread link : Solved: Warehouse - VACUUM or RESTORE? - Microsoft Fabric Community

Thank you.

[pmscorca]

Hi, ok but I don't think to VACUUM as a statement for maintenance purpose, but for cleaning a staging table before a new data loading.

Thanks

[AndyDDC]

Unfortunately VACUUM is not a statement that can be executed on a Fabric Warehouse table.