Learning Snowflake SQL and Scripting

Chapter 13. Metadata

When you create schema items to store information about your customers, products, experiment results, health records, or whatever is of importance to your organization, Snowflake needs to keep track of all of your tables, columns, clusters, views, and other database objects. Not surprisingly, Snowflake stores this information in a database for internal use, but also makes this information, known as metadata, available for database users. This chapter explores the different types of available metadata, along with some examples of how it can be used.

information_schema

Snowflake makes its metadata available via a set of views in the information_schema schema. If you are using Snowsight, you can see information_schema listed under every database, including Snowflake’s sample databases, as shown in Figure 13-1.

Figure 13-1. Snowflake’s database listing

If you expand Views under information_schema, you will see a listing of 25 different views covering schema items as shown in Figure 13-2 (Databases, Tables, Views, Columns), security (Applicable_Roles, Enabled_Roles, Object_Privileges, Usage_Privileges), data movement (External_Tables, File_Formats, Load_History, Pipes, Replication_Databases), and programs (Functions, Stored Procedures).

Figure 13-2. Information_Schema ...