search

keboola / php-db-import-export

[READ-ONLY] - see https://github.com/keboola/storage-backend
MIT License
1 stars 0 forks source link

Snowflake use truncate not delete #128

Closed zajca closed 2 years ago

zajca commented 2 years ago

SLACK: https://keboola.slack.com/archives/C02BTQ5JADP/p1659525972397539

tomasfejfar commented 2 years ago

https://community.snowflake.com/s/question/0D50Z000073NqcKSAS/truncate-vs-delete

TRUNCATE deletes the file load history It allows external file or file in stage (without update in data) to be loaded into the table again. whereas the DELETE,

does not delete the file load history. If you delete rows loaded into the table you cannot load the data from same file again unless data is modified in the file and stage it again.

Lokesh is spot on. One other thing I want to point out is TRUNCATE does not spin up the warehouse whereas DELETE does.

Given that the underlying data structures for Snowflake (micro partitions) are immutable then Snowflake would need to move all not deleted rows into new micro partitions and mark the old partitions in metadata as history for time travel. In this case no rows will be moved as all are being deleted - but I suspect that, given in a previous response, a warehouse needs to be active for a DELETE action that Snowflake still is doing some work. In the case of TRUNCATE this is purely a metadata action (no warehouse).

So my unauthoritative short answer would be that TRUNCATE will be the fastest and least expensive (both resource and $$).