turbot/snowflake
steampipe plugin install snowflake

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;

Schema for snowflake_schemata

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form, e.g. connection_name.
accounttextThe Snowflake account ID.
catalog_idtextID of the database that the schema belongs to.
catalog_nametextDatabase that the schema belongs to.
commenttextComment for this schema.
createdtimestamp with time zoneCreation time of the schema.
deletedtimestamp with time zoneDeletion time of the schema.
is_managed_accesstextWhether the schema is a managed access schema.
is_transienttextWhether this is a transient schema.
last_alteredtimestamp with time zoneLast altered time of the schema.
regiontextThe Snowflake region in which the account is located.
retention_timebigintNumber of days that historical data is retained for Time Travel.
schema_idtextID of the schema.
schema_nametextName of the schema.
schema_ownertextName of the role that owns the schema.

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