The Information Schema is a tool full of potential that allows us to keep track of numerous aspects of our account and the database in which it exists.
Further proof of this is the other name by which this schema is called : Data Dictionary.
It is precisely the amount of things that this object allows us to see that made it one of the most featured topics among the SnowPro Core certification questions and one of the topics that I struggled with the most.
Let us therefore learn about it.

Introduction

When creating a database Snowflake automatically creates two schemas within it: the PUBLIC and the INFORMATION_SCHEMA. The former is an empty object that can be used as a container for the elements that the designer wants to create; the latter, on the other hand, is a special schema that provides tables and views that have access to the metadata of both objects in the database and objects that are at the account level (e.g., roles). The views defined by the system (there are more than 20!) are divisible precisely a these two families: database-level relative views and account-level views.

Account Views

The main account views are:

• APPLICABLE ROLES : shows one row for each role granted.
• DATABASES : shows all databases defined in the account.
• ENABLED ROLES : shows all the roles currently active within the account
• LOAD_HISTORY : shows all files uploaded using the COPY INTO command. The history of uploads has a duration of 14 days, it does not include data uploaded via Snowpipe
• INFORMATION_SCHEMA_CATALOG_NAME which provides the name of the Databse in which the INFORMATION_SCHEMA is allocated
• REPLICATION_DATABASES provides a record for each primary and secondary database within our organization.

I report an example of the use of LOAD_HISTORY.

I find it particularly interesting that the outcome of the upload is also present, so you can also see the failed uploads, why the process did not complete, and the column that caused the error!

Database Views

The views, on the other hand, that give information at the Database level are indeed many ( I refer you to the documentation to read them all) and provide information about all the objects in the database the names of the views are quite telling and are as follows:
COLUMNS, EXTERNAL_TABLES, FILE_FORMATS, FUNCTIONS, PIPES, PROCEDURES, SEQUENCES, SCHEMATA, STAGES, VIEWS
a row is produced for each corresponding object.

Tables

Regarding tables we have the generic TABLE view which displays one row for each table in the database me we have three other views which are :

• TABLE_CONSTRAINTS displays one row for each referential integrity constraint defined for tables in the specified (or current) database.
  Of the specified (or current) database.
• TABLE_PRIVILEGES displays one row for each privilege on each table in the database.
• TABLE_STORAGE_METRICS provides table-level storage information. The result shows the metadata for the table. the number of storage types billed for each table is present. The rows are maintained in
  this view until the corresponding tables are no longer billed for any storage type,
  regardless of the various states the data in the tables may be in (e.g., active, Time Travel, fail-safe, or saved).
  travel, fail-safe, or preserved for clones).

I report an example of using the TABLE_STORAGE_METRICS view.

In addition to the presence of in-depth information related to the name, allocation, and different timestamps related to the life of the table, I find it very interesting that there is also storage data related to time-travel and fail-safe.

Access to Metadata

In Snowflake it is possible to access metadata in more than one way, here we see two.

Analyzing the results of the queries we see that the metadata provided by the INFORMATION_SCHEMA is more complete and in greater numbers than that provided by the SHOW function.

Conclusion

Clearly the INFORMATION_SCHEMA offers possibilities for accessing a lot of information, it has tremendous descriptive power, and so it is a good idea to be aware of what it offers and how to use it. In addition to the things we have seen there are additional uses of this schema that I will go into in a future article.