topic Re: Right workaround to solve the unsupported Truncate Table in Data Warehouse

Right workaround to solve the unsupported Truncate Table

pmscorca — Fri, 21 Jun 2024 07:25:10 GMT

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

Re: Right workaround to solve the unsupported Truncate Table

v-cboorla-msft — Fri, 21 Jun 2024 09:51:16 GMT

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.

Re: Right workaround to solve the unsupported Truncate Table

pmscorca — Fri, 21 Jun 2024 10:29:05 GMT

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

Re: Right workaround to solve the unsupported Truncate Table

v-cboorla-msft — Fri, 21 Jun 2024 17:08:49 GMT

Hi pmscorca

Please refer to the similar thread which might help you.

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

Thank you.

Re: Right workaround to solve the unsupported Truncate Table

pmscorca — Fri, 21 Jun 2024 20:02:23 GMT

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

Re: Right workaround to solve the unsupported Truncate Table

AndyDDC — Fri, 28 Jun 2024 14:05:20 GMT

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

Re: Right workaround to solve the unsupported Truncate Table

FrancescoPwc — Fri, 05 Jul 2024 14:10:46 GMT

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

Re: Right workaround to solve the unsupported Truncate Table

pmscorca — Mon, 08 Jul 2024 11:41:39 GMT

Hi,

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

Re: Right workaround to solve the unsupported Truncate Table

TwinkleCyril — Sat, 10 Aug 2024 05:13:12 GMT

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

Re: Right workaround to solve the unsupported Truncate Table

pmscorca — Tue, 13 Aug 2024 05:36:00 GMT

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

Re: Right workaround to solve the unsupported Truncate Table

TwinkleCyril — Tue, 13 Aug 2024 21:45:34 GMT

It will be available in Aug blog.

Re: Right workaround to solve the unsupported Truncate Table

AndyDDC — Thu, 29 Aug 2024 09:00:49 GMT

Hi pmscorca TRUNCATE table is now supported in Fabric Warehouses

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

Re: Right workaround to solve the unsupported Truncate Table

FrancescoPwc — Thu, 29 Aug 2024 09:07:52 GMT

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