Simply Snowflake Shorts — Part 2
METADATA OBJECTS:
In this blog we would discuss about some of the classic snowflake metadata objects that helps us derive many key metrics like ‘credit usage over time’, ‘table metadata’, ‘warehouse usage’, etc.. All these metrics are available from SNOWFLAKE shared database object namely ‘SNOWFLAKE’ as shown below:
3 most commonly used schema names from which folks derive all the key metrics are as follows:
- Account Usage(account_usage): This has details about all the objects which has been created under various databases, warehouse level details, copy history, etc. as shown below:
One of they key differentiator of this object is it also captures details about dropped objects. Example below:
-- To check when was the last time the same table has been dropped & again recreated with same name.
select table_id, table_name, deleted from "SNOWFLAKE"."ACCOUNT_USAGE"."TABLES" where table_name='T1_TEST';
If we see over here the “DELETED” column indicates when the table was dropped last time & “TABLE_ID” allocates unique ids to ensure we maintain 2 versions of the same table so over here as mentioned above ID=2050 is for the deleted table & ID=3074 is for the active table.
2. Information Schema(information_schema): This is also a data dictionary intended to provide the metadata information about the objects and is more real time as compared to account_usage schema. One very important thing to note over here is whenever we create a new database then this schema is created by default. This meaning we have 2 versions of Information_Schema available one at a datadase level and another one coming from Snowflake share DB as shown below.
3. Organization Usage (organization_usage): This schema provides historical usage of all accounts within the organization. This is available on Snowflake shared DB. We need to take a note that we need to have “accoundadmin role” OR “grant the access to the same object to the role orgadmin using the accoundadmin”
A quick & short comparision between the three::
THANK YOU !!
Please keep reading my blogs it is only going to encourage me in posting more such content. You can find me on LinkedIn by clicking here and on Medium here.
Happy Learning :)
Disclaimer: The views expressed here are mine alone and do not necessarily reflect the view of my current, former, or future employers.
