page133
116
Despite the latency inherent within the Account Usage Store views, you prefer to
reference the Account Usage Store views as all account object information is available
centrally. Alternatively, you would need to identify each database and access each
information schema individually. Of course, your role would need to be entitled for each
database, which may prove challenging in a multi-tenant or highly segregated Snowflake
environment.
Using the Account Usage Store
Some table metadata is available from the Account Usage store as this next query
illustrates:
SELECT *
FROM snowflake.account_usage.tables
WHERE table_name LIKE '%_BASELINE%'
AND deleted IS NULL
LIMIT 10;
Account Usage Store views experience data latency of between 45 minutes and 3
hours; therefore, you may not immediately see the expected results.
The previous query output contains useful information, and you will return to this
content later; however, there is no mention of the number of micro-partitions.
Under what circumstances would it be useful to know the number of micro-
partitions? And if you have a valid use case, how can you identify the required
information?
One use case is replicating data between accounts where costs vary according to the
number of micro-partitions transferred between primary and secondary accounts. While
there are techniques explained later in this book to reduce the number of micro-
partitions transferred, here you have a reason to know how many micro-partitions
belong to each replicated object as each replica incurs storage cost.
With your use case defined and knowing each micro-partition contains compressed
data, you can readily identify that using row counts as a proxy to derive micro-partition
counts is not a valid approach.
ChaPTer 4
MiCro-ParTiTionS