Table: snowflake_schemata - Query Snowflake Schemata using SQL

Snowflake Schemata is a feature in Snowflake, a cloud-based data warehousing platform, that allows users to organize and manage data in logical groups. It provides a structure for storing, managing, and retrieving data, enabling efficient data operations. Snowflake Schemata helps users maintain control over their data, ensuring it is organized and easily accessible.

Table Usage Guide

The snowflake_schemata table provides insights into the organization and structure of data within Snowflake. As a Data Engineer or Data Analyst, explore schema-specific details through this table, including the database name, schema name, and associated metadata. Utilize it to uncover information about schemas, such as their organization, the data they contain, and how they are used in data operations.

Examples

Basic info

Analyze the settings to understand the ownership and access details of various databases within your Snowflake account. This can help in determining which databases have managed access, are transient, and identify their respective owners, aiding in better data management and security.

select
  schema_name,
  catalog_name as database_name,
  is_managed_access,
  is_transient,
  schema_owner
from
  snowflake_schemata;

select
  schema_name,
  catalog_name as database_name,
  is_managed_access,
  is_transient,
  schema_owner
from
  snowflake_schemata;

List schemas that allow managed access

Explore which schemas in your Snowflake database are configured to allow managed access. This can help you maintain security and manageability of your data by identifying which schemas are under controlled access.

select
  schema_name,
  catalog_name as database_name,
  is_managed_access,
  is_transient,
  schema_owner
from
  snowflake_schemata
where
  is_managed_access = 'YES';

select
  schema_name,
  catalog_name as database_name,
  is_managed_access,
  is_transient,
  schema_owner
from
  snowflake_schemata
where
  is_managed_access = 'YES';

List transient schemas

Explore the Snowflake databases that are managed access to identify those that are transient. This can help in understanding the database structures that are temporary and managed by Snowflake, which is beneficial for maintaining data integrity and efficient resource usage.

select
  schema_name,
  catalog_name as database_name,
  is_managed_access,
  is_transient,
  schema_owner
from
  snowflake_schemata
where
  is_managed_access = 'YES';

select
  schema_name,
  catalog_name as database_name,
  is_managed_access,
  is_transient,
  schema_owner
from
  snowflake_schemata
where
  is_managed_access = 'YES';

List schemas with a retention time greater than 15 days

Explore which schemas in your database have a retention time exceeding 15 days. This is useful for understanding and managing data storage and lifecycle within your system.

select
  schema_name,
  catalog_name as database_name,
  is_managed_access,
  retention_time,
  schema_owner
from
  snowflake_schemata
where
  retention_time > 15;

select
  schema_name,
  catalog_name as database_name,
  is_managed_access,
  retention_time,
  schema_owner
from
  snowflake_schemata
where
  retention_time > 15;

Control examples

Snowflake Security Overview and Best Practices > Identity and Access Management > Use managed access schemas to centralize grant management

Schema for snowflake_schemata

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
account	text	=, !=, ~~, ~~, !~~, !~~	The Snowflake account ID.
catalog_id	text		ID of the database that the schema belongs to.
catalog_name	text		Database that the schema belongs to.
comment	text		Comment for this schema.
created	timestamp with time zone		Creation time of the schema.
deleted	timestamp with time zone		Deletion time of the schema.
is_managed_access	text		Whether the schema is a managed access schema.
is_transient	text		Whether this is a transient schema.
last_altered	timestamp with time zone		Last altered time of the schema.
region	text		The Snowflake region in which the account is located.
retention_time	bigint		Number of days that historical data is retained for Time Travel.
schema_id	text		ID of the schema.
schema_name	text		Name of the schema.
schema_owner	text		Name of the role that owns the schema.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- snowflake

You can pass the configuration to the command with the --config argument:

steampipe_export_snowflake --config '<your_config>' snowflake_schemata