What is the best approach in Snowflake for delete operations on a really large table?

user_9687534 0
Viewed 2577

We're thinking of moving our ODS from S3 into Snowflake but have some concerns on performance. Deleting 39 million rows from a 1.5 Billion (this would be on the smaller side) row table took 15 minutes on an x-small warehouse, 8 on a small, and 5 on a medium. We could throw money at larger instances, but really don't want to do that until all other measures were explored.

We were also thinking about implementing a manual partition system, to chunk up the table, but there would be a dev cost to create the supporting functionality.

Does Snowflake have a partitioning system that I'm not aware about that's equivalent to SQL Server? I know that's a reach, but swapping partitions was great.

Thanks for any feedback!

snowflake-cloud-data-platform
2 Answers

Snowflake doesn't have partitions like SQL Server as such, but the storage method of data in Snowflake is micro-partitions, which are similar...sort of. You can use Snowflake's automatic re-clustering service to align those micro-partitions on one or multiple fields, which would then make deleting on those keys a much faster operation. Leveraging the fields that you are deleting off of to cluster your tables should help quite a bit.

user_121789800

The approach of doing explicit clustering on a table requires thoughtful planning to consider various access patterns and workloads (ingestions, queries etc) touching that same table and cost considerations, so I am not sure if doing explicit clustering for the delete case is good enough reason to change the table layout.
What if instead of deleting from large able, use CTAS to create another table on the surviving rows and the drop original table?

user_91758750
Related