Table: snowflake_database - Query Snowflake Databases using SQL

Snowflake is a cloud-based data warehousing platform that provides comprehensive solutions for data storage, processing, and analysis. It supports a wide range of data types, including structured and semi-structured data, and allows for seamless scaling of resources to meet the demands of any size of data workload. Snowflake databases are a key resource within this platform, housing the data that is processed and analyzed.

Table Usage Guide

The snowflake_database table provides insights into databases within the Snowflake data warehousing platform. As a data engineer or analyst, explore database-specific details through this table, including ownership, creation time, and associated metadata. Utilize it to uncover information about databases, such as their size, the number of tables they contain, and their overall usage statistics.

Examples

Basic info

Explore the creation dates, current status, origin, owner, and retention time of your databases in Snowflake to understand their management and maintenance history. This is useful to assess the operational aspects of your databases and for future planning.

select
  name,
  created_on,
  is_current,
  origin,
  owner,
  retention_time
from
  snowflake_database;

select
  name,
  created_on,
  is_current,
  origin,
  owner,
  retention_time
from
  snowflake_database;

List databases with retention time greater than 1 day

Explore which databases have a retention time greater than a day. This is useful for understanding the longevity of your data and managing storage resources effectively.

select
  name,
  created_on,
  is_current,
  origin,
  owner,
  retention_time
from
  snowflake_database
where
  retention_time > 1;

select
  name,
  created_on,
  is_current,
  origin,
  owner,
  retention_time
from
  snowflake_database
where
  retention_time > 1;

Schema for snowflake_database

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
account	text	=, !=, ~~, ~~, !~~, !~~	The Snowflake account ID.
comment	text		Comment for this database.
created_on	timestamp with time zone		Creation time of the database.
is_current	text		Name of the current database for authenticating user.
is_default	text		Name of the default database for authenticating user.
name	text		Name of the database.
options	text
origin	text		Name of the origin database.
owner	text		Name of the role that owns 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.
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_database