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...

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
  }
}