Overview

It can be difficult to effectively monitor and optimize Snowflake infrastructure and data retrieval. Issues arise which may lead to inefficient resource utilization, higher costs, and a degraded customer experience.

With Datadog’s Snowflake integration, you can uncover long-running queries to improve performance and reduce costs, identify real time security threats, and monitor your Snowpark workloads.

After parsing your Snowflake data, Datadog populates the out-of-the-box overview dashboard with insights across all your collected resources. It also offers recommended monitors to help you get started on alerting on failed Snowpark executions or an abnormal amount of login attempts.

Setup

Installation

No installation steps are required.

Configuration

Connect your Snowflake account

1. Find your Snowflake account URL.

2. Enable the resources you are interested in collecting:

Account and Organization usage metrics

The table below describes the types of metrics collected and their associated metric prefixes.

Logs

The table below describes the types of logs collected and which Snowflake tables are included.

Cloud Cost Management

Enable Cloud Cost Management to receive Snowflake cost metrics aggregated from the SNOWFLAKE.ORGANIZATION_USAGE.USAGE_IN_CURRENCY_DAILY table. You can use these metrics with Cloud Cost Management to gain additional insight into your cost and usage.

3. Create a Datadog-specific role and user to monitor Snowflake. Execute the series of commands below in your Snowflake environment to create a user accessible for Datadog.

-- Create a new role intended to monitor Snowflake usage. The name of the role is customizable.
create role DATADOG;

-- Grant privileges on the SNOWFLAKE database to the new role.
grant imported privileges on database SNOWFLAKE to role DATADOG;
                
-- Grant usage to your default warehouse to the role DATADOG.
grant usage on warehouse <WAREHOUSE> to role DATADOG;

-- Grant ORGANIZATION_BILLING_VIEWER to the new role. Do this if you wish to collect Snowflake cost data.
grant database role SNOWFLAKE.ORGANIZATION_BILLING_VIEWER to role DATADOG;

-- Grant usage on the database, schema, and table of the event table.
grant usage on database <EVENT_TABLE_DATABASE> to role DATADOG:
grant usage on schema <EVENT_TABLE_DATABASE>.<EVENT_TABLE_SCHEMA> to role DATADOG;
grant select on table <EVENT_TABLE_DATABASE>.<EVENT_TABLE_SCHEMA>.<EVENT_TABLE_NAME> to role DATADOG:
                        
-- Create a user.
create user <USERNAME>
LOGIN_NAME = <USERNAME>
password = <PASSWORD>
default_warehouse =<WAREHOUSE>
default_role = DATADOG

-- Grant the monitor role to the user.
grant role DATADOG to user <USERNAME>

4. Configure key-pair authentication. The public key is assigned to the user created earlier, and the private key is uploaded to Datadog, allowing Datadog to connect to your Snowflake account.
   a. Create and upload a private key following the Snowflake instructions.
   b. Create a public key following the Snowflake instructions.
   c. Assign the public key to the user created earlier following the Snowflake instructions.

Custom metrics

The Snowflake integration supports custom queries to collect custom metrics. Users can write custom SQL queries to extract specific data and view it as metrics and metric tags in Datadog.

By default, the integration connects to the shared SNOWFLAKE database and ACCOUNT_USAGE schema. If you are querying a table outside the ACCOUNT_USAGE schema, ensure your configured role has the appropriate permissions to access the table.

The table below describes the fields used to define custom metrics.

Notes:

• At least one item in the defined columns should be a metric type (gauge, count, rate, distribution).
• 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.

Example:

Validation

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

Data Collected

Metrics

Events

The Snowflake Web integration does not include any events.

Service Checks

The Snowflake Web integration does not include any service checks.

Troubleshooting

Need help? Contact Datadog support.

Agent check: Snowflake

Agent: 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.

Agent: Setup

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

Agent: 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.

Agent: 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;
   
   -- Grant usage to your default warehouse to the role DATADOG.
   grant usage on warehouse <WAREHOUSE> 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 username - string - required
       ## Login name for the user.
       #
       username: <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 have access to the specified database or schema.

Configuration options

The custom_queries option has the following options:

Notes:

• At least one item in the defined columns should be a metric type (for example, gauge, count, rate).

• The number of items in the 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;

Custom query 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

Agent: Validation

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

Agent: Data Collected

Agent: Metrics

See Metrics for a list of metrics provided by this check.

Agent: Events

Snowflake does not include any events.

Agent: Service Checks

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

Agent: Troubleshooting

Need help? Contact Datadog support.

Further Reading

Additional helpful documentation, links, and articles:

• Monitor Snowflake with Datadog
• Monitor Snowflake Snowpark with Datadog