Snowflake Compute

Connecting Snowflake to ADOC helps you monitor and optimize the performance and cost of your Snowflake compute resources. Once connected, ADOC can:

• Track credit consumption to improve cost efficiency.
• Enable automated alerts and diagnostics for query performance and compute utilization.

Prerequisites

Before connecting Snowflake to ADOC, you need to set up Snowflake so ADOC can securely monitor your data for quality, performance, and cost. This setup ensures the right users and roles exist, and that they have the permissions needed to access only what’s necessary.

Users and Roles Overview

Before we start, here’s a quick overview of the users and roles that will be created:

To set up your Snowflake data source for compute monitoring, you must do the following:

Sql

-- Role for compute reliability monitoring

SET acceldata_role = 'AD_COMPUTE_MONITOR';

-- Warehouse for compute tasks

SET acceldata_warehouse = 'AD_COMPUTE_WH';

-- User for compute reliability tasks

SET acceldata_user = 'AD_USER';

-- Password for the user (replace '<password>' with a strong password)

SET acceldata_password = '<your_secure_password>';

-- Database for monitoring tasks

SET acceldata_database = 'AD_MONITOR_DB';

USE ROLE ACCOUNTADMIN;

CREATE OR REPLACE WAREHOUSE IDENTIFIER($acceldata_warehouse) WAREHOUSE_SIZE = 'X-Small';

ALTER WAREHOUSE IDENTIFIER($acceldata_warehouse) SET AUTO_SUSPEND = 30;

CREATE OR REPLACE ROLE IDENTIFIER($acceldata_role);

GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO ROLE IDENTIFIER($acceldata_role);

GRANT MONITOR ON ACCOUNT TO ROLE IDENTIFIER($acceldata_role);

GRANT USAGE ON WAREHOUSE IDENTIFIER($acceldata_warehouse) TO ROLE IDENTIFIER($acceldata_role);

CREATE OR REPLACE USER IDENTIFIER($acceldata_user)

LOGIN_NAME = $acceldata_user

PASSWORD = $acceldata_password

DEFAULT_WAREHOUSE = $acceldata_warehouse

DEFAULT_ROLE = $acceldata_role

DEFAULT_NAMESPACE = SNOWFLAKE.ACCOUNT_USAGE;

ALTER USER <existing_username>

SET DEFAULT_ROLE = <your_role>,

DEFAULT_WAREHOUSE = <your_wh>,

DEFAULT_NAMESPACE = SNOWFLAKE.ACCOUNT_USAGE;

GRANT ROLE IDENTIFIER($acceldata_role) TO USER IDENTIFIER($acceldata_user);

CREATE DATABASE IF NOT EXISTS IDENTIFIER($acceldata_database);

GRANT USAGE ON DATABASE IDENTIFIER($acceldata_database) TO ROLE IDENTIFIER($acceldata_role);

USE DATABASE IDENTIFIER($acceldata_database);

GRANT MONITOR, USAGE, CREATE FILE FORMAT, CREATE STAGE

ON SCHEMA PUBLIC

TO ROLE IDENTIFIER($acceldata_role);

CREATE OR REPLACE PROCEDURE grant_privileges_to_all_warehouses(ROLE_NAME VARCHAR)

RETURNS VARCHAR

LANGUAGE JAVASCRIPT

AS

$$

var warehouses = snowflake.createStatement({sqlText: "SHOW WAREHOUSES"}).execute();

while (warehouses.next()) {

var warehouse_name = warehouses.getColumnValue(1);

snowflake.createStatement({sqlText: "GRANT MONITOR ON WAREHOUSE " + warehouse_name + " TO ROLE " + ROLE_NAME}).execute();

}

var resource_monitors = snowflake.createStatement({sqlText: "SHOW RESOURCE MONITORS IN ACCOUNT"}).execute();

while (resource_monitors.next()) {

var monitor_name = resource_monitors.getColumnValue(1);

snowflake.createStatement({sqlText: "GRANT MONITOR ON RESOURCE MONITOR " + monitor_name + " TO ROLE " + ROLE_NAME}).execute();

}

return 'Granted monitor privileges';

$$;

GRANT USAGE ON PROCEDURE grant_privileges_to_all_warehouses(VARCHAR) TO ROLE IDENTIFIER($acceldata_role);

CALL GRANT_PRIVILEGES_TO_ALL_WAREHOUSES($acceldata_role);

GRANT CREATE INTEGRATION ON ACCOUNT TO ROLE IDENTIFIER($acceldata_role);

USE ROLE ORGADMIN;

ALTER ACCOUNT my_account1 SET IS_ORG_ADMIN = TRUE;

SHOW STAGES;

GRANT OWNERSHIP ON STAGE <stage_name> TO ROLE <role_name>;

GRANT ROLE <role> TO USER <user>;

ALTER USER <user> SET DEFAULT_ROLE = <role>;

1. Define Variables

Start by defining variables for reuse across the setup steps. This makes the script portable and easier to manage or rerun later.

2. Create Role and Warehouse

Ensure you’re using the ACCOUNTADMIN role for full privileges:

1. Create a dedicated warehouse: This ensures low-cost, temporary compute for ADOC to run metadata queries. This warehouse is used for lightweight metadata queries by ADOC. It isolates observability compute from your production workloads, helping you manage cost and reduce risk.
2. Create a custom monitoring role: This role is scoped specifically to monitor performance and usage. It ensures least-privileged access and easier audit tracking.

3. Grant Required Priveleges

1. Grant access to Snowflake’s Account Usage schema: Required to access views like QUERY_HISTORY, and __WAREHOUSE_METERING_HISTORY, etc.
2. Enable monitoring of Snowflake resources: Allows access to credit usage, performance stats, query behavior, and warehouse efficiency.
3. Allow role to use the monitoring warehouse

4. Create User for Monitoring

Create a new user that ADOC will use to query Snowflake metadata. This ensures the user automatically starts with the right role and namespace. If reusing an existing user, update defaults.

1. Assign role to user

5. Create Monitoring Database and Grant Schema Access

1. Create the monitoring database
2. Grant usage on database
3. Switch to the new database
4. Grant schema-level privileges: These permissions are needed to manage internal stages, formats, and collect metadata.

6. Use Store Procedure to Grant Monitro Priveleges on All Warehouses

This script ensures the monitoring role has access to all current and future warehouses and resource monitors.

1. Create the procedure
2. Grant usage on the procedure
3. Run the procedure

Optional Steps

Optional 1: Allow storage integrations

Required if ADOC will use external stages (like S3 or Azure Blob) through secure integrations.

Optional 2: Enable ORGADMIN Role

Allows access to ORGANIZATION_USAGE schema for consolidated org-wide monitoring.

Optional 3: Troubleshooting & FAQ

Stage Creation Failure

OAuth reauthentication: If default roles/namespaces aren’t set, the user may need to re-authenticate via OAuth.

Changing role for existing users

Add Snowflake as a Data Source

Step 1: Start Setup

1. Navigate to the left main navigation menu, click Control Center -> Integrations.
2. From the Integrations page, click Add Data Source and select Snowflake from the list of data sources.
3. On the Basic Details page:
   1. Enter a name for this data source.
   2. (Optional) Add a description.
   3. Choose your Data Plane or click Setup Data Plane to create one.
   4. You must enable at least one or both of the following to continue:
      • Compute Observability
      • Data Reliability Monitoring
4. Click Next.

Step 2: Add Connection Details

1. Enter the Snowflake URL (e.g., https://<account>.snowflakecomputing.com)
2. Provide the following Snowflake credentials:
   1. Username
   2. Password
   3. Role (e.g., AD_COMPUTE_MONITOR)
3. Select your Data Plane Engine:
   • Spark (for external compute)
   • Pushdown (for in-Snowflake processing)

Step 3: Set Up Observability

If you enabled Compute Observability, fill in the Compute Observability section:

Why this matters: These settings allow ADOC to compute credit usage and query-level metrics from Snowflake, giving you visibility into workload costs and efficiency.

If you enabled Data Reliability, fill in the Data Reliability section:

Why this matters: These options allow ADOC to scan your Snowflake datasets for freshness, schema drift, null values, and other data quality issues.

Click Submit to complete the setup.

You’ll see a new Snowflake card on the Data Sources page with connection and crawler details.

Optimizing Data Partitioning

To tune performance for large datasets, you can adjust Snowflake's default parallelism using this environment variable in your ADOC Data Plane configuration:

Bash

SNOWFLAKE_PARTITION_SIZE_IN_MB=10

Setting Up PrivateLink (Optional)

You can connect Snowflake to ADOC securely over AWS PrivateLink for improved network isolation.

Prerequisites:

• AWS account with necessary permissions
• VPC in us-west-2
• Snowflake account ready

Step 1: Authorize PrivateLink Access

Why Use PrivateLink?

AWS PrivateLink allows your Snowflake data to connect with ADOC services over a secure, private network path—without traversing the public internet. This enhances data security, reduces latency, and improves performance.

Share your AWS account ID with the Acceldata support team. Acceldata will use this ID to authorize your account for PrivateLink connectivity.

Step 2: Create VPC Endpoints

In the AWS Management Console:

1. Navigate to VPC.
2. In the navigation pane, choose Endpoints.
3. Select Create Endpoint.
4. Create the following two endpoints:

Step 3: Configure DNS Using Route 53

In Amazon Route 53:

1. Navigate to the Hosted Zones section for your domain.
2. Add the following A records:

Troubleshooting

What’s Next

After you’ve connected Snowflake Compute in ADOC:

• Visit the Compute tab to view warehouse performance and query execution metrics.
• Track credit usage trends to identify cost-saving opportunities.
• Set up alerts for spikes in compute usage, query slowdowns, or unusual activity.
• Explore usage patterns to optimize warehouse sizing and scheduling.