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

Question

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!

Answer 1

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.