Table: snowflake_warehouse - Query Snowflake Warehouses using SQL

A Snowflake Warehouse is a virtual warehouse in Snowflake, a cloud-based data warehousing platform. A warehouse is the computational resource that executes all data processing tasks, including loading data, executing transformations, and running queries. Each virtual warehouse is an independent compute resource that does not share compute resources with other virtual warehouses.

Table Usage Guide

The snowflake_warehouse table provides insights into Snowflake Warehouses. As a data engineer or analyst, you can explore details about each warehouse, including its size, state, and usage statistics. Use this table to understand the performance of your warehouses and to identify potential areas for optimization.

Examples

Basic info

Analyze the settings to understand the status and characteristics of your Snowflake warehouses. This can be useful for capacity planning and resource allocation.

select
  name,
  size,
  type,
  state
from
  snowflake_warehouse;

select
  name,
  size,
  type,
  state
from
  snowflake_warehouse;

List active warehouses

Explore which warehouses are currently active in your Snowflake environment. This can help in managing resources efficiently and ensuring optimal performance.

select
  name,
  size,
  type,
  state
from
  snowflake_warehouse
where
  state = 'STARTED';

select
  name,
  size,
  type,
  state
from
  snowflake_warehouse
where
  state = 'STARTED';

Get a count of warehouses grouped by size

Determine the distribution of warehouse sizes within your infrastructure to better manage resources and planning. This query is useful for understanding the scale of your operations.

select
  count(*),
  size
from
  snowflake_warehouse
group by
  size;

select
  count(*),
  size
from
  snowflake_warehouse
group by
  size;

List warehouses with auto-resume disabled

Determine the areas in which warehouses have the auto-resume feature disabled to assess potential inefficiencies in warehouse management.

select
  name,
  type,
  size,
  auto_resume
from
  snowflake_warehouse
where
  not auto_resume;

select
  name,
  type,
  size,
  auto_resume
from
  snowflake_warehouse
where
  auto_resume = 0;

Schema for snowflake_warehouse

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
account	text	=, !=, ~~, ~~, !~~, !~~	The Snowflake account ID.
auto_resume	boolean		Specifies whether to automatically resume a warehouse when a SQL statement (e.g. query) is submitted to it.
auto_suspend	bigint		Specifies the number of seconds of inactivity after which a warehouse is automatically suspended.
available	text		Percentage of the warehouse compute resources that are provisioned and available.
comment	text		Comment for the warehouse.
created_on	timestamp with time zone		Date and time when the warehouse was created.
is_current	text		Whether the warehouse is in use for the session.
is_default	text		Whether the warehouse is the default for the current user.
max_cluster_count	bigint		Maximum number of warehouses for the (multi-cluster) warehouse (always 1 for single warehouses).
min_cluster_count	bigint		Minimum number of warehouses for the (multi-cluster) warehouse (always 1 for single warehouses).
name	text		Name for warehouse.
other	text		Percentage of the warehouse compute resources that are in a state other than available, provisioning, or quiescing.
owner	text		Role that owns the warehouse.
provisioning	text		Percentage of the warehouse compute resources that are in the process of provisioning.
queued	bigint		Number of SQL statements that are queued for the warehouse.
quiescing	text		Percentage of the warehouse compute resources that are executing SQL statements, but will be shut down once the queries complete.
region	text		The Snowflake region in which the account is located.
resource_monitor	text		ID of resource monitor explicitly assigned to the warehouse; controls the monthly credit usage for the warehouse.
resumed_on	timestamp with time zone		Date and time when the warehouse was last started or restarted.
running	bigint		Number of SQL statements that are being executed by the warehouse.
scaling_policy	text		Policy that determines when additional warehouses (in a multi-cluster warehouse) are automatically started and shut down.
size	text		Size of the warehouse (X-Small, Small, Medium, Large, X-Large, etc.)
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
started_clusters	bigint		Number of warehouses currently started.
state	text		Whether the warehouse is active/running (STARTED), inactive (SUSPENDED), or resizing (RESIZING).
type	text		Warehouse type; STANDARD is the only currently supported type.
updated_on	timestamp with time zone		Date and time when the warehouse was last updated, which includes changing any of the properties of the warehouse or changing the state (STARTED, SUSPENDED, RESIZING) of the warehouse.

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_warehouse