Snowflake
Incident Management is now generally available! Incident Management is now generally available!

Snowflake

Agent Check Agent Check

Supported OS: Linux Mac OS Windows

Overview

This check monitors Snowflake through the Datadog Agent. Snowflake is a SaaS-analytic data warehouse and runs completely on cloud infrastructure. This integration monitors credit usage, billing, storage, query metrics, and more.

NOTE: Metrics are collected via queries to Snowflake. Queries made by the Datadog integration are billable by Snowflake.

Setup

Follow the instructions below to install and configure this check for an Agent running on a host.

Installation

The Snowflake check is included in the Datadog Agent package. No additional installation is needed on your server.

Note: Snowflake check is currently not available for MacOS in Datadog Agent 6 using Python 2.

For users configuring the integration with Agent v7.23.0, upgrade the integration version to 2.0.1 to take advantage of latest features. You can upgrade the integration with the following command:
datadog-agent integration install datadog-snowflake==2.0.1

Configuration

  1. Edit the snowflake.d/conf.yaml file, in the conf.d/ folder at the root of your Agent’s configuration directory to start collecting your snowflake performance data. See the sample snowflake.d/conf.yaml for all available configuration options.

    Note: By default, this integration monitors the SNOWFLAKE database and ACCOUNT_USAGE schema. This database is available by default and only viewable by users in the ACCOUNTADMIN role or any role granted by the ACCOUNTADMIN.

        ## @param account - string - required
        ## Name of your account (provided by Snowflake), including the platform and region if applicable.
        ## For more information on Snowflake account names,
        ## see https://docs.snowflake.com/en/user-guide/connecting.html#your-snowflake-account-name
        #
      - account: <ACCOUNT>
       
        ## @param user - string - required
        ## Login name for the user.
        #
        user: <USER>
       
        ## @param password - string - required
        ## Password for the user
        #
        password: <PASSWORD>
      
        ## @param min_collection_interval - number - optional - default: 3600
        ## This changes the collection interval of the check. For more information, see:
        ## https://docs.datadoghq.com/developers/write_agent_check/#collection-interval
        ##
        ## NOTE: Most Snowflake ACCOUNT_USAGE views are populated on an hourly basis,
        ## so to minimize unnecessary queries the `min_collection_interval` defaults to 1 hour.
        #
        # min_collection_interval: 3600
    
    By default, the min_collection_interval is 1 hour. Snowflake metrics are aggregated by day, you can increase the interval to reduce the number of queries.
    Note: Snowflake ACCOUNT_USAGE views have a known latency of 45 minutes to 3 hours.
  2. Restart the Agent.

Snowflake Custom Queries

The Snowflake integration supports custom queries. By default, the integration connects to the shared SNOWFLAKE database and ACCOUNT_USAGE schema.

If you want to run custom queries in a different schema or database, add another instance to the sample snowflake.d/conf.yaml and specify the database and schema options. Ensure the user and role has access to the specified database or schema.

Configuration options

The custom_queries option has the following options:

OptionRequiredDescription
queryYesThis is the SQL to execute. It can be a simple statement or a multi-line script. All of the rows of the results are evaluated. Use the pipe if you require a multi-line script.
columnsYesThis is a list representing each column ordered sequentially from left to right.

There are 2 required pieces of data:
- name: This is the suffix to append to the metric_prefix to form the full metric name. If the type is specified as tag, the column is instead applied as a tag to every metric collected by this query.
- type: This is the submission method (gauge, count, rate, etc.). This can also be set to tag to tag each metric in the row with the name and value (<name>:<row_value>) of the item in this column.
tagsNoA list of static tags to apply to each metric.
Notes
  • At least one item in defined columns should be a metric type (gauge, count, rate, etc).
  • The number of items in columns must equal the number of columns returned in the query.
  • The order in which the items in columns are defined must be in the same order returned in the query
custom_queries:
  - query: select F3, F2, F1 from Table;
    columns:
      - name: f3_metric_alias
        type: gauge
      - name: f2_tagkey
        type: tag
      - name: f1_metric_alias
        type: count
    tags:
      - test:snowflake

Example

The following example is a query that will count all queries in the QUERY_HISTORY view tagged by database, schema, and warehouse names.

select count(*), DATABASE_NAME, SCHEMA_NAME, WAREHOUSE_NAME from QUERY_HISTORY group by 2, 3, 4;
Configuration

The custom query configuration in instances will look like the following:

custom_queries:
  - query: select count(*), DATABASE_NAME, SCHEMA_NAME, WAREHOUSE_NAME from QUERY_HISTORY group by 2, 3, 4;
    columns:
      - name: query.total
        type: gauge
      - name: database_name
        type: tag
      - name: schema_name
        type: tag
      - name: warehouse_name
        type: tag
    tags:
      - test:snowflake
Validation

To verify the result, search for the metrics using Metrics Summary:

Snowflake Metric Summary

Validation

Run the Agent’s status subcommand and look for snowflake under the Checks section.

Data Collected

Metrics

snowflake.storage.storage_bytes.total
(gauge)
The average number of bytes of table storage used including bytes for data currently in Time Travel.
Shown as byte
snowflake.storage.stage_bytes.total
(gauge)
The average number of bytes of stage storage used by files in all internal stages.
Shown as byte
snowflake.storage.failsafe_bytes.total
(gauge)
The average number of bytes of data in Fail-safe.
Shown as byte
snowflake.storage.database.storage_bytes
(gauge)
The average number of bytes of database storage used.
Shown as byte
snowflake.storage.database.failsafe_bytes
(gauge)
The average number of bytes of Fail-safe storage used.
Shown as byte
snowflake.billing.virtual_warehouse.avg
(gauge)
The average overall credits billed per hour for virtual warehouses.
Shown as unit
snowflake.billing.virtual_warehouse.sum
(gauge)
The sum of overall credits billed for virtual warehouses.
Shown as unit
snowflake.billing.cloud_service.avg
(gauge)
The average overall credits billed per hour for cloud services.
Shown as unit
snowflake.billing.cloud_service.sum
(gauge)
The total overall credits billed for cloud services.
Shown as unit
snowflake.billing.total_credit.avg
(gauge)
The average overall of credits used per hour for the account. This is the sum of snowflake.billing.cloud_service and snowflake.billing.virtual_warehouses.
Shown as unit
snowflake.billing.total_credit.sum
(gauge)
The total overall of credits used for the account. This is the sum of snowflake.billing.cloud_service and snowflake.billing.virtual_warehouses.
Shown as unit
snowflake.billing.warehouse.cloud_service.avg
(gauge)
The average credits used per hour for cloud services by warehouse.
Shown as unit
snowflake.billing.warehouse.cloud_service.sum
(gauge)
The total credits used for cloud services by warehouse.
Shown as unit
snowflake.billing.warehouse.virtual_warehouse.avg
(gauge)
The average credits used per hour by warehouse.
Shown as unit
snowflake.billing.warehouse.virtual_warehouse.sum
(gauge)
The total credits used by warehouse.
Shown as unit
snowflake.billing.warehouse.total_credit.avg
(gauge)
The average total number of credits used per hour for the warehouse. NOTE: This value does not take into account adjustments which may be higher than your actual credit consumption.
Shown as unit
snowflake.billing.warehouse.total_credit.sum
(gauge)
The total number of credits used for the warehouse. NOTE: This value does not take into account adjustments which may be higher than your actual credit consumption.
Shown as unit
snowflake.logins.fail.count
(count)
Total failed login attempts.
Shown as error
snowflake.logins.success.count
(count)
Total successful login attempts.
Shown as success
snowflake.logins.total
(count)
Total number of login attempts.
Shown as attempt
snowflake.query.executed
(gauge)
The average number of queries executed.
Shown as query
snowflake.query.queued_overload
(gauge)
Average number of queries queued because the warehouse was overloaded.
Shown as query
snowflake.query.queued_provision
(gauge)
Average number of queries queued because the warehouse was being provisioned.
Shown as query
snowflake.query.blocked
(gauge)
Average number of queries blocked by a transaction lock.
Shown as query
snowflake.query.execution_time
(gauge)
Average query execution time.
Shown as millisecond
snowflake.query.compilation_time
(gauge)
Average query compilation time.
Shown as millisecond
snowflake.query.bytes_scanned
(gauge)
Average query bytes scanned.
Shown as byte
snowflake.query.bytes_written
(gauge)
Average query bytes written.
Shown as byte
snowflake.query.bytes_deleted
(gauge)
Average query bytes deleted.
Shown as byte
snowflake.data_transfer.bytes.avg
(gauge)
Average bytes transferred.
Shown as byte
snowflake.data_transfer.bytes.sum
(gauge)
Total bytes transferred.
Shown as byte
snowflake.auto_recluster.credits_used.avg
(gauge)
Average credits billed for automatic reclustering.
Shown as unit
snowflake.auto_recluster.credits_used.sum
(gauge)
Total credits billed for automatic reclustering.
Shown as unit
snowflake.auto_recluster.bytes_reclustered.avg
(gauge)
Average bytes reclustered.
Shown as byte
snowflake.auto_recluster.bytes_reclustered.sum
(gauge)
Total bytes reclustered.
Shown as byte
snowflake.auto_recluster.rows_reclustered.avg
(gauge)
Average rows reclustered.
Shown as row
snowflake.auto_recluster.rows_reclustered.sum
(gauge)
Total rows reclustered.
Shown as row
snowflake.storage.table.active_bytes.avg
(gauge)
Average bytes owned by (and billed to) this table that are in the active state.
Shown as byte
snowflake.storage.table.time_travel_bytes.avg
(gauge)
Average bytes owned by (and billed to) this table that are in the Time Travel state.
Shown as byte
snowflake.storage.table.failsafe_bytes.avg
(gauge)
Average bytes owned by (and billed to) this table that are in the Fail-safe state.
Shown as byte
snowflake.storage.table.retained_bytes.avg
(gauge)
Average bytes owned by (and billed to) this table that are retained after deletion because they are referenced by one or more clones of this table.
Shown as byte
snowflake.pipe.credits_used.avg
(gauge)
Average number of credits billed for Snowpipe data loads.
Shown as unit
snowflake.pipe.credits_used.sum
(gauge)
Total number of credits billed for Snowpipe data loads.
Shown as unit
snowflake.pipe.bytes_inserted.avg
(gauge)
Average number of bytes loaded from Snowpipe.
Shown as byte
snowflake.pipe.bytes_inserted.sum
(gauge)
Total number of bytes loaded from Snowpipe.
Shown as byte
snowflake.pipe.files_inserted.avg
(gauge)
Average number of files loaded from Snowpipe.
Shown as file
snowflake.pipe.files_inserted.sum
(gauge)
Total number of files loaded from Snowpipe.
Shown as file
snowflake.replication.credits_used.avg
(gauge)
Average number of credits used for database replication.
Shown as unit
snowflake.replication.credits_used.sum
(gauge)
Total number of credits used for database replication.
Shown as unit
snowflake.replication.bytes_transferred.avg
(gauge)
Average number of bytes transferred for database replication.
Shown as byte
snowflake.replication.bytes_transferred.sum
(gauge)
Total number of bytes transferred for database replication.
Shown as byte
snowflake.query.bytes_spilled.local
(gauge)
Avg volume of data spilled to local disk.
Shown as byte
snowflake.query.bytes_spilled.remote
(gauge)
Avg volume of data spilled to remote disk.
Shown as byte

Service Checks

snowflake.can_connect:
Returns CRITICAL if the Agent cannot authenticate and connect to Snowflake, OK otherwise.

Events

Snowflake does not include any events.

Troubleshooting

Need help? Contact Datadog support.