CODEWITHSUNDEEP
snowflake-cloud-data-platform
Eric Mamet
Eric Mamet

Reputation: 3671

Can I find the number of micro-partitions used in Snowflake by a specific table?

Is there a way to find out how many micro-partitions are being used for a specific table (or database if table not possible)? Ideally showing how many partitions are being retained due to time travel...

Upvotes: 2

Views: 3249

Answers (2)

Seeling Cheung
Seeling Cheung

Reputation: 157

To add to the above responses: 1) Using Query Profile Execute a query against the table to generate the query profile, but try to keep the query more efficient such as adding the LIMIT clause to limit the number of rows returned and by avoiding SELECT STAR (because Snowflake is a columnar store and in general it matters for performance to retrieve as few columns as needed).

2) Using SYSTEM$CLUSTERING_INFORMATION Every table in Snowflake is clustered. It is a matter of 'natural clustering' (done by the system automatically ) or 'explicit clustering' (user specifies clustering key).
For natural clustering (where the records will be stored based on ingestion order), user can still use the function. Example using the Sample shared database's TPCH datasets (which are naturally clustered): SELECT SYSTEM$CLUSTERING_INFORMATION('snowflake_sample_data.tpch_sf1.orders', '(o_orderpriority)');

-- Query result shows the orders table has 10 micro-partitions.
{ "cluster_by_keys" : "LINEAR(O_ORDERPRIORITY)", "total_partition_count" : 10, "total_constant_partition_count" : 0, "average_overlaps" : 9.0, "average_depth" : 10.0, "partition_depth_histogram" : { "00000" : 0, "00001" : 0, "00002" : 0, "00003" : 0, "00004" : 0, "00005" : 0, "00006" : 0, "00007" : 0, "00008" : 0, "00009" : 0, "00010" : 10, "00011" : 0, "00012" : 0, "00013" : 0, "00014" : 0, "00015" : 0, "00016" : 0 } }

Upvotes: 2

Balaji-Snowflake
Balaji-Snowflake

Reputation: 191

When you query the data from the table , say select * from or any other query on that table , in the Profile Page , you will see the information about Pruning.

Example :

Pruning

Partitions scanned 1,505

Partitions total . 50,262

If the table has been clustered , then you can run the below command to which gives the information about the partitions.

select SYSTEM$CLUSTERING_INFORMATION( '' , '( [ , ... ] )' )

Upvotes: 4

Related Questions