Snowflake

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.

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.

Note: The Snowflake check is not available in Datadog Agent v6 using Python 2. To use Snowflake on Agent v6 see Use Python 3 with Datadog Agent v6 or upgrade to Agent v7.

datadog-agent integration install datadog-snowflake==2.0.1

Configuration

1. Create a Datadog specific role and user to monitor Snowflake. In Snowflake, run the following to create a custom role with access to the ACCOUNT_USAGE schema.

   Note: By default, this integration monitors the SNOWFLAKE database and ACCOUNT_USAGE schema. See “Collecting Organization Data” for information on how to monitor the ORGANIZATION_USAGE schema. This database is available by default and only viewable by users in the ACCOUNTADMIN role or any role granted by the ACCOUNTADMIN.

   use role ACCOUNTADMIN;
   grant imported privileges on database snowflake to role SYSADMIN;
   
   use role SYSADMIN;
   

   Alternatively, you can create a DATADOG custom role with access to ACCOUNT_USAGE.

   -- Create a new role intended to monitor Snowflake usage.
   create role DATADOG;
   
   -- Grant privileges on the SNOWFLAKE database to the new role.
   grant imported privileges on database SNOWFLAKE to role DATADOG;
   
   -- Create a user, skip this step if you are using an existing user.
   create user DATADOG_USER
   LOGIN_NAME = DATADOG_USER
   password = '<PASSWORD>'
   default_warehouse = <WAREHOUSE>
   default_role = DATADOG
   default_namespace = SNOWFLAKE.ACCOUNT_USAGE;
   
   -- Grant the monitor role to the user.
   grant role DATADOG to user <USER>;
   

2. 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.

       ## @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: <ORG_NAME>-<ACCOUNT_NAME>
   
       ## @param user - string - required
       ## Login name for the user.
       #
       user: <USER>
   
       ## @param password - string - required
       ## Password for the user
       #
       password: <PASSWORD>
   
       ## @param role - string - required
       ## Name of the role to use.
       ##
       ## By default, the SNOWFLAKE database is only accessible by the ACCOUNTADMIN role. Snowflake recommends
       ## configuring a role specific for monitoring:
       ## https://docs.snowflake.com/en/sql-reference/account-usage.html#enabling-account-usage-for-other-roles
       #
       role: <ROLE>
   
       ## @param min_collection_interval - number - optional - default: 15
       ## 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, set the `min_collection_interval` to 1 hour.
       #
       min_collection_interval: 3600
   
       # @param disable_generic_tags - boolean - optional - default: false
       # Generic tags such as `cluster` will be replaced by <integration_name>_cluster to avoid
       # getting mixed with other integration tags.
       # disable_generic_tags: true
   

   In the default `conf.yaml`, 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.

3. Restart the Agent.

Collecting Organization Data

By default, this integration monitors the ACCOUNT_USAGE schema, but it can be set to monitor organization-level metrics instead.

To collect organization metrics, change the schema field to ORGANIZATION_USAGE and increase the min_collection_interval to 43200 in the integration’s configuration. This reduces the number of queries to Snowflake, as most organization queries have a latency of up to 24 hours.

Note: To monitor organization metrics, your user must have the ORGADMIN role.

    - schema: ORGANIZATION_USAGE
      min_collection_interval: 43200

Only some organization metrics are enabled by default. To collect all available organization metrics, utilize the metric_groups configuration option:

    metric_groups:
      - snowflake.organization.warehouse
      - snowflake.organization.currency
      - snowflake.organization.credit
      - snowflake.organization.storage
      - snowflake.organization.contracts
      - snowflake.organization.balance
      - snowflake.organization.rate
      - snowflake.organization.data_transfer

Additionally, you can monitor both account and organization metrics at the same time:

    instances:
    - account: example-inc
      username: DATADOG_ORG_ADMIN
      password: '<PASSWORD>'
      role: SYSADMIN
      schema: ORGANIZATION_USAGE
      database: SNOWFLAKE
      min_collection_interval: 43200

    - account: example-inc
      username: DATADOG_ACCOUNT_ADMIN
      password: '<PASSWORD>'
      role: DATADOG_ADMIN
      schema: ACCOUNT_USAGE
      database: SNOWFLAKE
      min_collection_interval: 3600

Collecting data for multiple environments

If you want to collect data for multiple Snowflake environments, add each environment as an instance in your snowflake.d/conf.yaml file. For example, if you needed to collect data for two users named DATADOG_SYSADMIN and DATADOG_USER:

instances:
  - account: example-inc
    username: DATADOG_SYSADMIN
    password: '<PASSWORD>'
    role: SYSADMIN
    database: EXAMPLE-INC

  - account: example-inc
    username: DATADOG_USER
    password: '<PASSWORD>'
    role: DATADOG_USER
    database: EXAMPLE-INC

Proxy configuration

Snowflake recommends setting environment variables for proxy configuration.

You can also set the proxy_host, proxy_port, proxy_user, and proxy_password under init_config in the snowflake.d/conf.yaml.

NOTE: Snowflake automatically formats the proxy configurations and sets standard proxy environment variables. These variables also impact every requests from integrations, including orchestrators like Docker, ECS, and Kubernetes.

Private connectivity to Snowflake configuration

If private connectivity (such as AWS PrivateLink) is enabled in Snowflake, you can configure the Snowflake integration by updating the account configuration option to the following format:

      - account: <ACCOUNT>.<REGION_ID>.privatelink

Snowflake custom queries

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

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:

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 counts 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 looks 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:

Validation

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

Data Collected

Metrics

Events

Snowflake does not include any events.

Service Checks

snowflake.can_connect
Returns CRITICAL if the check cannot authenticate Snowflake credentials. Returns OK otherwise.
Statuses: ok, critical

Troubleshooting

Need help? Contact Datadog support.

Further Reading

Additional helpful documentation, links, and articles:

• Monitor Snowflake with Datadog