How to Query Information Schema on Snowflake? Examples, Best Practices, and Tools

Updated August 01st, 2023

Share this article

Snowflake’s Information Schema is a set of system-defined views that provide metadata about your account, databases, schemas, tables, and other database objects. You can use SQL queries to access this metadata and gather insights about your data warehouse.

Unlock Your Data’s Potential With Atlan – Start Product Tour

Table of contents #

Steps to query Information Schema in Snowflake
Top tools to query Snowflake’s Information Schema without writing SQL queries
Leveraging Snowflake’s Information Schema for metadata management
The benefits of deploying a metadata management tool for your Snowflake data warehouse
Bringing it all together
How to Query Information Schema on Snowflake? Related reads

Querying Snowflake Information Schema: Examples to get started #

To query the Information Schema in Snowflake, you need to follow this general structure:

SELECT * FROM <database_name>.information_schema.<view_name>;

Replace <database_name> with the name of your database, and <view_name> with the specific view you want to query within the Information Schema.

Here are some examples of common queries to get you started:

List all databases
List all schemas within a specific database
List all tables within a specific schema
List all columns for a specific table within a specific schema
List all views within a specific schema

Now, let us learn how to create the queries:

1. List all databases #

SELECT * FROM snowflake.information_schema.databases;

2. List all schemas within a specific database #

SELECT * FROM <database_name>.information_schema.schemata;

3. List all tables within a specific schema #

SELECT * FROM <database_name>.information_schema.tables WHERE table_schema = '<schema_name>';

4. List all columns for a specific table within a specific schema #

SELECT * FROM <database_name>.information_schema.columns WHERE table_schema = '<schema_name>' AND table_name = '<table_name>';

5. List all views within a specific schema #

SELECT * FROM <database_name>.information_schema.views WHERE table_schema = '<schema_name>';

Remember to replace <database_name>, <schema_name>, and <table_name> with your actual database, schema, and table names.

These are just a few examples, but you can query many other views in the Information Schema to gather metadata about your Snowflake environment. The Snowflake documentation provides a comprehensive list of available views and their descriptions: https://docs.snowflake.com/en/sql-reference/info-schema.html

Top tools to query Snowflake’s Information Schema without writing SQL queries #

There are several tools available that allow you to query Snowflake’s Information Schema without having to write SQL queries manually. These tools provide graphical user interfaces (GUIs) that make it easier to explore your data warehouse and access metadata. Some popular options include:

Snowflake Web Interface
dbt (Data Build Tool)
Data visualization and BI tools
Database management tools

Let us look into each of the above tools in brief:

1. Snowflake Web Interface #

Snowflake provides its own web-based interface called Snowsight (formerly Snowflake Worksheet). It features an easy-to-use interface for managing and querying your data warehouse. While it still requires some SQL knowledge, Snowsight offers features like autocomplete, syntax highlighting, and query history to make the process easier.

You can access Snowsight by logging into your Snowflake account at https://<account_identifier>.snowflakecomputing.com/console/login.

2. dbt (Data Build Tool) #

dbt is an open-source data transformation tool that works with Snowflake. While it primarily focuses on managing data transformations, it also has a feature called “dbt docs” that generates a web-based interactive schema documentation from your dbt project. This can help you explore your Information Schema without writing SQL queries.

Learn more about dbt at https://www.getdbt.com/.

3. Data visualization and BI tools #

Many data visualization and business intelligence tools, such as Tableau, Looker, Power BI, and Sisense, offer connectors to Snowflake. These tools allow you to explore and visualize your data warehouse’s schema and tables through a graphical interface, without the need to write SQL queries. Keep in mind that some of these tools require a paid license.

4. Database management tools #

Some database management tools, like SQL Workbench/J, DBeaver, and DataGrip, provide support for Snowflake. These tools offer a GUI to browse your Snowflake data warehouse’s schema, tables, and other objects. They can help you view and manage your Information Schema with little or no SQL knowledge. While some of these tools are open-source and free, others require a paid license.

Each of these options has its own advantages and limitations, so you’ll need to choose the one that best fits your requirements and preferences.

Leveraging Snowflake’s Information Schema for metadata management #

Metadata management tools help organizations maintain, discover, and understand their data assets by providing a centralized system for managing metadata. They usually offer a graphical user interface (GUI) to explore, catalog, and document data assets, making it easier for users to find and understand the data they need.

Metadata management tools leverage the Information Schema in Snowflake by:

Extracting metadata
Data lineage
Data cataloging
Data profiling
Data governance
Integration with other tools

Let us look into each of the above metadata management tools in brief:

1. Extracting metadata #

These tools connect to Snowflake’s Information Schema and extract metadata about databases, schemas, tables, columns, views, and other database objects. This metadata typically includes object names, data types, table relationships, and other structural information.

2. Data lineage #

Metadata management tools can use the information from the Information Schema to trace data lineage, showing how data flows through your organization’s data pipeline. This can help you understand dependencies and the impact of changes on your data assets.

3. Data cataloging #

The tools create a searchable data catalog that allows users to easily find and understand their data assets. This catalog typically includes metadata extracted from the Information Schema, as well as user-generated documentation, tags, and annotations.

4. Data profiling #

Some metadata management tools offer data profiling features, which use the Information Schema metadata to provide statistics and insights about your data, such as column value distributions, data quality issues, and data anomalies.

5. Data governance #

Metadata management tools can enforce data governance policies and processes by leveraging the Information Schema. For example, they can monitor and control access to sensitive data or enforce naming conventions and data quality rules.

6. Integration with other tools #

Metadata management tools often integrate with other data management tools, such as ETL platforms, data visualization tools, and data quality tools. By leveraging the Information Schema in Snowflake, they can ensure consistency and accuracy across your entire data stack.

Popular metadata management tools that can work with Snowflake include Alation, Collibra, Informatica Enterprise Data Catalog, and Apache Atlas. These tools vary in features, pricing, and ease of use, so you should evaluate them based on your organization’s specific needs and requirements.

Benefits of deploying a metadata management tool for the Snowflake platform #

Deploying a metadata management tool can help you maximize the value drawn from Snowflake for several reasons:

Improved discoverability
A better understanding of data
Data governance
Increased collaboration
Data lineage and impact analysis
Reduced time to insight
Integration with other data tools

Now, let us better understand each of the above reasons:

1. Improved discoverability #

A metadata management tool creates a centralized, searchable data catalog that allows users to easily find the data assets they need. This saves time and effort for data consumers, who no longer need to manually search through databases, schemas, and tables.

2. A better understanding of data #

Metadata management tools provide comprehensive documentation and context for your data assets, including data lineage, relationships, and user-generated descriptions. This enables data consumers to understand data more quickly and accurately, reducing the risk of errors and misinterpretations.

3. Data governance #

By centralizing metadata, these tools can help enforce data governance policies and processes. They can monitor and control access to sensitive data, enforce naming conventions, and maintain data quality standards, ensuring a consistent and compliant data environment.

4. Increased collaboration #

Metadata management tools promote collaboration between data producers, consumers, and stewards. They facilitate communication through shared documentation, annotations, and tagging, which can lead to improved data quality, better decision-making, and more efficient use of data resources.

5. Data lineage and impact analysis #

Understanding data lineage helps you track how data moves through your organization, from its source to its final destination. Metadata management tools provide insights into data lineage, making it easier to perform impact analysis and assess the consequences of changes in your data pipeline.

6. Reduced time to insight #

By making data more accessible and understandable, metadata management tools can speed up the time it takes for users to draw insights from your Snowflake data warehouse. This can lead to faster, data-driven decision-making and a more agile organization.

7. Integration with other data tools #

Metadata management tools can integrate with other data management and analytics tools in your data stack, such as ETL platforms, data visualization tools, and data quality tools. This ensures consistency and accuracy across your entire data ecosystem, maximizing the value of your Snowflake data warehouse.

Overall, deploying a metadata management tool can help you create a more efficient, collaborative, and well-governed data environment, maximizing the value of your Snowflake data warehouse and enabling your organization to make better data-driven decisions.

Bringing it all together #

Snowflake’s Information Schema is a set of system-defined views that provide metadata about your account, databases, schemas, tables, and other database objects. In this blog, we discovered a few SQL queries to access this metadata and gather insights about your data warehouse. Besides, we also learnt a few tools to query Snowflake’s Information Schema without writing SQL queries.

By implementing an appropriate metadata management tool and leveraging the Information Schema in Snowflake, you can create a more efficient, collaborative, and well-governed data environment.

Atlan is an active data catalog for the modern data stack and the first cataloging and metadata management solution to be approved by Snowflake Ready Technology Validation Program.

Are you looking to implement a data discovery and data catalog solution for your organization — you might want to check out Atlan.