CODEWITHSUNDEEP
snowflake-cloud-data-platformcluster-analysisclustering-key
neverMind
neverMind

Reputation: 1784

Snowflake Automatic Clustering RESUMED accid. nows always turns on again even after SUSPEND

I have a base table for which I've built two MVs:

  1. to be filtered by LOCAL_TS (Epoch in milliseconds)
  2. other to be filtered for UTC_TS

I've clustered both initially by date(TS) and it was working fine, until ...I've accidentally run the command to RESUME CLUSTER on MV 1), which basically tells SF to do automatic clustering. This messed up by clustering info so badly, and even after I SUSPEND the automatic RECLUSTER, dropping the cluster key and adding again, the AUTOMATIC CLUSTER ON: true shows on SHOW MATERIALIZED VIEWS !

I don't get it: it seems like I can't turn on the AUT. recluster for that MV and that MV only? Can anyone please help me? I've been trying for hours, same query, but now MV does not appear to be really clustered by my key: it should have 30K partitions, and it's say 55K now.

Seems like the RESUME CLUSTER messed it all up, but it should be a way to revert this , right?

I'm open to suggestions.

Thank you!

Upvotes: 0

Views: 135

Answers (1)

Gokhan Atil
Gokhan Atil

Reputation: 10049

... and even after I SUSPEND the automatic RECLUSTER, dropping the cluster key and adding again, the AUTOMATIC CLUSTER ON: true

If this is the exact sequence, after adding the automatic cluster, its default status should be ON. So It's expected behaviour. If you want to suspend it, just run "SUSPEND RECLUSTER" on that MV again.

I've clustered both initially by date(TS) and it was working fine, until ...I've accidentally run the command to RESUME CLUSTER on MV 1)

Manual clustering is deprecated, are you still able to use "manual clustering" by any chance? If not, then there is no benefit to defining clustering keys unless you use Automatic Clustering. Just drop them :)

Upvotes: 0

Related Questions