CODEWITHSUNDEEP
snowflake-cloud-data-platform
Rogier Werschkull
Rogier Werschkull

Reputation: 744

How can the NUMBER_OF_ROWS_RECLUSTERED per table in snowflake be greater than the table number of rows

I have just altered the clustering key of a large event table in Snowflake. As I am watching the automatic reclustering happening using the following query:

select *
  from table(information_schema.automatic_clustering_history(
    date_range_start=>dateadd(h, -2, current_timestamp)))
    order by start_time desc

...something odd happens: The NUM_ROWS_RECLUSTERED is much higher than the number of rows the table contains. Which I am guessing also has an effect on credit consumption.

As in: Table nr of rows is 1.32 billion, NUM_ROWS_RECLUSTERED is almost at 2.4 billion!

WHY is this happening? It does not 'feel' right...

What is even stranger is these numbers going down now as I am typing this post...

Upvotes: 1

Views: 135

Answers (1)

Mike Walton
Mike Walton

Reputation: 7339

The data in that view is displayed in hourly chunks, so the numbers can certainly change within the hour. It is also not uncommon for the service to recluster data over several passes, which means some micropartitions could be rebuilt several times. It sounds like your table is either new, had the cluster key added recently, or is updated very frequently. If you need more detail about what is happening, I'd suggest reaching out to support with the specific table.

Upvotes: 3

Related Questions