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	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
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.)
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