Recently my friend asked very basic but important difference between Truncate and Delete in snowflake. With respect to Oracle, Truncate reset the high water mark of a table back to “zero” and release allocated space back to dba_free_space. Delete physically deletes data row by row and High-water marks are not reset. The freed space is not available for use and stay exactly where they are. So, in short, delete never reset or lower the high-water mark. In terms of Snowflake, Truncate and Delete does not make any difference on the micro partitions and no impact on Storage. Both commands mark the data for deletion on Storage Space.
How do DML Operations work in micro-partitions?
Any changes to the files (Micro-Partitions) performed via DML operations, can only be made by replacing it with a different file that includes the changes. No updates happen to the existing files as Amazon S3 and Azure Blob are immutable file systems. File additions and removals gets track in the metadata.
Still, we have following difference between TRUNCATE and DELETE.
- The truncate command deletes the load metadata history which prevents COPY command to load the same file to the table. In other words file present in stage can be load again into table even there is no change in data. It does not delete the load history stored in the information_schema.load_history.
Say we have CUSTOMER_PIPE table in our account.
We have used the COPY command and it loads the data into Table.
Now Try to run the same command again:
Though we can override this behavior by using FORCE = TRUE , but here we are discussing about TRUNCATE property. Truncate the table now and run the COPY command again. We have not done any changes to the file.
truncate table CUSTOMER_PIPE;
Now verify the LOAD_HISTORY table and will see load history is available for both runs. Even Truncate has not purge this entry.
- Also Truncate does not involve the Warehouse to be available, it is taken care by Cloud Service Layer. While delete requires the Warehouse to be up and running.
Note: Truncating the Table, does not delete the snowpipe metadata history and it prevents to load the load in to the table if same file copied again to bucket.