Snowflake extension

  • Latest Dynatrace
  • Extension

Improve health and performance monitoring of your Snowflake via metrics and Dynatrace Intelligence.

Get started

Overview

Snowflake monitoring is based on a remote monitoring approach implemented as a Dynatrace ActiveGate extension. The extension queries the Account Usage and Information Schema for key performance and health metrics. It extends your visibility by allowing Dynatrace Intelligence to provide anomaly detection and root cause analysis.

Use cases

Use this extension to:

  • Monitor the current state of your Snowflake account.
  • Improve visibility into billing and credits.
  • Monitor storage consumption trends.
  • Monitor how much time each warehouse consumes.
  • Monitor the availability of the Snowflake account.

Requirements

Ensure you have the following prerequisites covered.

  • Designate an ActiveGate group or groups that will remotely connect to your Snowflake Database server to pull data. All ActiveGates in each group must connect to your Snowflake database.
  • Enable log monitoring.
  • Create a dedicated database user in your database instance with necessary permissions. Dynatrace uses this user to run monitoring queries against your Snowflake database.

    • A user or a role with the access to the following schemas:

      • INFORMATION_SCHEMA
      • ACCOUNT_USAGE

      The user must have an ACCOUNTADMIN role or a role granted by the ACCOUNTADMIN user.

      • Configure a user with permissions

      In Snowflake, run the following to create a custom role:

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

      use role SYSADMIN;

Activation and setup

Follow these steps to configure your device for Snowflake database monitoring.

Add DB instance

  1. Go to Dynatrace Hub Hub.
  2. Select and install Snowflake extension. This enables the extension in your monitoring environment.

    • Required permission: Change monitoring settings

      `
  3. Select Add DB Instance in the Databases Databases. This opens the Add DB Instance wizard.
  4. Select Snowflake section in the wizard.

Select hosting type

Select a hosting type from the options. This choice determines which script generates the necessary database objects later in the process.

  1. From the Add DB Instance wizard, select the host type that matches your requirement.
  2. Select Next.

Select ActiveGate group

  1. Select the ActiveGate group to determine which ActiveGates will run the extension.
  2. Select Next step.

Create a connection

Set up the connection to your database instance. Provide the required credentials directly in the wizard or use secure alternatives:

  1. Name the connection, so you can identify it later.
  2. Add the details in the Configure connection section.
    1. Select connection. Use Select from existing hosts or Enter manually to add connection details.
    2. Add Database name, Schema, and Warehouse.
  3. Provide the Authenticate credentials for the dynatrace monitoring user you have created directly or use secure alternatives.
    • Basic credentials: Authentication details passed to Dynatrace when activating monitoring configuration are masked to prevent them from being retrieved.
    • Credential vault: Use vault credentials to securely store and retrieve database credentials.
  4. Select Next.

Install instance

  1. Add manual configurations based on the monitoring requirements.

    • Add configurations to monitor the instance

      • Specify full Snowflake host for connection (for example, zzyxxx-tc12345.snowflakecomputing.com)
      • Specify database name to connect to and warehouse which will execute the monitoring queries
      Warehouse consumption

      • Data in the Queries feature set is retrieved every hour.

      • Data in the Default feature set is retrieved every 720 minutes.

        The execution of the data retrieval queries requires the warehouse to be active for both feature sets.

      snowflake.account.availability metric is an exception. It gets retrieved every minute and doesn't require a warehouse.

  2. Select Create DB instance monitoring.

Details

Licensing and cost

The Snowflake extension queries for metrics every minute. In both Classic and DPS Licensing models, license consumption is based on metric datapoints. To estimate the amount of metric datapoints produced by an extension configuration per minute, use the following formula:

default: 9 x Snowflake Warehouse + 7 x Snowflake Account

The formula estimates several metrics per feature set and a multiplier depending on which entity the metric is split by.

For the DPS license, you can calculate the approximate yearly datapoints consumption by using the following formula:

<metric datapoints per minute> x 60 minutes x 24 hours x 365 days

In the classic licensing model, metric ingestion will consume Davis Data Units (DDUs) at the rate of .001 DDUs per metric data point.

Multiply the above formula for annual data points by .001 to estimate annual DDU usage.

This extension can also ingest logs if the queries feature set is selected. For more information, see Log ingest and process feature overview or DDU consumption model for Log Management and Analytics, depending on your license model.

For more information about licensing costs, see Davis Data Units or DPS metrics, depending on your license model.

DQL and Logs

Queries feature set must be enabled to run DQL queries.

  • Example of a DQL query that lists the longest-running queries:

    fetch logs
    | filter matchesValue(dt.extension.name, "com.dynatrace.extension.sql-snowflake")
    | filter matchesValue(event.group, "longest_queries")
    | sort asDouble(execution_status) desc

  • Example of a DQL query that lists failed queries:

    fetch logs
    | filter matchesValue(dt.extension.name, "com.dynatrace.extension.sql-snowflake")
    | filter matchesValue(event.group, "failed_queries")
    | sort timestamp desc

Feature sets

When activating your extension using monitoring configuration, you can limit monitoring to one of the feature sets. To work properly, the extension has to collect at least one metric after the activation.

In highly segmented networks, feature sets can reflect the segments of your environment. Then, when you create a monitoring configuration, you can select a feature set and a corresponding ActiveGate group that can connect to this particular segment.

All metrics that aren't categorized into any feature set are considered to be the default and are always reported.

A metric inherits the feature set of a subgroup, which in turn inherits the feature set of a group. Also, the feature set defined on the metric level overrides the feature set defined on the subgroup level, which in turn overrides the feature set defined on the group level.

default
Metric nameMetric keyDescription
Snowflake availabilitysnowflake.account.availabilityWhether Snowflake responds to queries or not
Warehouse compute credits usedsnowflake.account.warehouse.credits.computeNumber of credits used for the warehouse.
Warehouse cloud services credits usedsnowflake.account.warehouse.credits.cloudServicesNumber of credits used for cloud services for the warehouse.
Warehouse credits usedsnowflake.account.warehouse.credits.totalNumber of credits billed for the warehouse.
Account table storage bytessnowflake.account.storage.tableNumber of bytes of table storage used, including bytes for data currently in Time Travel.
Account stage storage bytessnowflake.account.storage.stageNumber of bytes of stage storage used by files in all internal stages (named, table, and user).
Account fail-safe storage bytessnowflake.account.storage.failsafeNumber of bytes of data in Fail-safe.
Warehouse compilation timesnowflake.account.warehouse.time.compilationQuery compillation time per warehouse.
Warehouse execution timesnowflake.account.warehouse.time.executionQuery execution time per warehouse.
Warehouse elapsed timesnowflake.account.warehouse.time.elapsedQuery total elapsed time per warehouse.
Warehouse queued provisioning timesnowflake.account.warehouse.time.queued.provisioningQuery queued provisioning time per warehouse.
Warehouse queued overload timesnowflake.account.warehouse.time.queued.overloadQuery queued overload time per warehouse.
Warehouse blocked timesnowflake.account.warehouse.time.blockedQuery blocked time per warehouse.
Account compute creditssnowflake.account.credits.computeNumber of credits used by warehouses and serverless compute resources.
Account cloud services creditssnowflake.account.credits.cloudServicesNumber of credits used for cloud services.
Account total credits usedsnowflake.account.credits.totalTotal number of credits used by the account.
Queries
Metric nameMetric keyDescription
Hub

Explore in Dynatrace Hub

Expand visibility to improve health and performance monitoring of your Snowflake database.

Related tags
DatabaseSQLSQLSnowflake Inc.Application Observability