Snowflake

Snowflake is a cloud computing organization that offers various cloud services. You can host data-intensive applications in Snowflake without facing operational complexities. This document explains how you can add Snowflake as a Data Source in ADOC. Once you add Snowflake as a Data Source, ADOC can monitor your Snowflake account and display critical information and raise alerts, as configured.

Performance and Cost optimization on Snowflake

This section of the guide provides step by step instructions to set up Snowflake for performance monitoring and cost optimization using the ADOC. Follow the steps to ensure proper configuration and access to enable ADOC to effectively monitor your Snowflake account, display critical information and raise alerts as configured.

Prerequisites

• Ensure that you have the ACCOUNT ADMIN role in Snowflake to execute the necessary command.
• Remember to define variables at the start of the script as you will use them through out the configuration process.
• Following the instructions are crucial to fully utilize the ADOC capabilities in monitoring and optimizing your Snowflake account.

Step 1 : Define Variables

Begin by setting up essential variables for roles, warehouse, users, and databases that will be used throughout the script. These variables serves as identifiers in subsequent steps.

-- 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 = '<password>';

​

-- Database for monitoring tasks

SET acceldata_database = 'AD_MONITOR_DB';

Note Replace <password> with a secure password of your choice.

Step 2 : Create Role and Warehouse

Initialize the role and warehouse defined in Step 1.

1. Switch to ACCOUNTADMIN Role: Use the ACCOUNTADMIN role to create warehouses, grant permissions and create other roles.

-- Switch to ACCOUNTADMIN role

USE ROLE ACCOUNTADMIN;

2. Create Warehouse: Create a warehouse with size x-small for running lightweight queries to fetch metadata.

-- Create warehouse for compute tasks

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

3. Configure Auto-suspend: Update the auto-suspend time to ensure minimal credit usage.

-- Set auto-suspend to 30 seconds to reduce costs

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

4. Create Role: Create a role to be assigned to the user for fetching metadata.

-- Create role for compute reliability monitoring

CREATE OR REPLACE ROLE IDENTIFIER($acceldata_role);

Step 3 : Grant Necessary Privileges

Assign the required privileges for monitoring and accessing Snowflake resources.

1. Grant Imported Privileges: Allow the role to access shared tables or views imported from the SNOWFLAKE database. This is crucial to enable resource monitoring and metadata access.

-- Grant access to imported privileges from the SNOWFLAKE database

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

2. Grant MONITOR Privilege on Account: Enable the role to monitor account-level metrics for resource usage and performance analysis.

-- Grant MONITOR privilege on the account

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

3. Grant USAGE on Warehouse: Allow the role to use the warehouse for query execution and operations.

-- Grant USAGE privilege on the warehouse

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

Step 4 : Create User

Create a user that will execute the monitoring tasks.

1. Create User:

-- Create user with default warehouse, role, and namespace

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;

Note Setting default values ensures the user can pull metadata without additional configurations. If not set, the user might encounter issues fetching data.

2. Existing Users: If you have an existing user you wish to reuse, ensure all the default roles and settings are assigned.

-- Update default role, warehouse, and namespace for existing user

ALTER USER <username> SET DEFAULT_ROLE = <ROLE_NAME>;

ALTER USER <username> SET DEFAULT_WAREHOUSE = <WAREHOUSE_NAME>;

ALTER USER <username> SET DEFAULT_NAMESPACE = SNOWFLAKE.ACCOUNT_USAGE;

3. Grant Role to User : Assign the created role to the user.

-- Grant role to user

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

Note Granting the role to the user is essential for enabling permissions required to make JDBC connections and fetch metadata from Snowflake.

Step 5 : Database and Schema Permissions

Ensure the necessary database and schema permissions are assigned to the role.

1. Create Database (if none exists): Create the monitoring database to store grants and permissions for Snowflake warehouses and resource monitors.

-- Create the monitoring database if it doesn't exist

CREATE DATABASE IF NOT EXISTS IDENTIFIER($acceldata_database);

Note This database will be used to run the stored procedures for managing grants and permissions.

2. Grant Usage on Database: Allow the role to access the newly created database.

-- Grant USAGE privilege on the database to the role

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

3. Use the Database: Change the active database to the monitoring database for subsequent operations.

-- Switch to the monitoring database

USE DATABASE IDENTIFIER($acceldata_database);

4. Grant Schema Privileges: Assign necessary permissions on the PUBLIC schema of the database.

-- Grant necessary privileges on the PUBLIC schema

GRANT MONITOR, USAGE, CREATE FILE FORMAT, CREATE STAGE

  ON SCHEMA PUBLIC

  TO ROLE IDENTIFIER($acceldata_role);

Note These permissions allow the role to monitor, use, and manage objects in the schema.

Step 6 : Execute Stored Procedure

Grant monitor privileges on all warehouses and resource monitors.

1. Create Stored Procedure:

-- Create stored procedure to grant MONITOR privilege on all warehouses and resource monitors

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 privilege to role ' + ROLE_NAME + ' on all warehouses and resource monitors';

  $$;

2. Grant Usage on Procedure:

-- Grant USAGE on the procedure to the role

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

3. Call Stored Procedure:

-- Call the stored procedure to grant privileges

CALL GRANT_PRIVILEGES_TO_ALL_WAREHOUSES($acceldata_role);

Optional Steps

Optional Step 1: Grant Create Integration Privilege

If you need to create integrations (e.g., for external stages using storage integration):

-- Grant CREATE INTEGRATION privilege to the role

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

This privilege allows the role to create integrations for secure connections with external services like AWS S3.

Optional Step 2: Enable ORGADMIN Role

-- Switch to ORGADMIN role

USE ROLE ORGADMIN;

​

-- Enable ORGADMIN privileges for the account

ALTER ACCOUNT my_account1 SET IS_ORG_ADMIN = TRUE;

The ORGADMIN role is required to access organization-level usage data.

Optional Step 3: OAuth & Snowflake API Client

For OAuth integration and additional configurations, refer to: Snowflake Integration with ADOC.

Troubleshooting Steps

1. Adding Snowflake as a Data Source: For detailed procedure, see Adding Snowflake as a Data Source.
2. Post configuration steps: Ensure all steps have been completed successfully by following the verification checklist. Also see Snowflake Checklist.
3. Changing User Role After OAuth Configuration: f you need to change the role of an existing user -

-- Grant role to user

GRANT ROLE <ROLE_NAME> TO USER <USER_NAME>;

​

-- Show all roles

SHOW ROLES;

​

-- Grant USAGE on database to role

GRANT USAGE ON DATABASE <DATABASE_NAME> TO ROLE <ROLE_NAME>;

4. Stage Creation Failure: If stage creation fails:
   1. Log in using ACCOUNTADMIN.
   2. Select the corresponding database used during onboarding.
   3. Run:

-- Show existing stages

SHOW STAGES;

​

-- Grant ownership on the stage to the role

GRANT OWNERSHIP ON STAGE <STAGE_NAME> TO ROLE <ROLE_NAME>;

Data Reliability on Snowflake

Ensuring data reliability inside Snowflake systems is critical for enterprises to retain the integrity and availability of their data. ADOC enables enterprises to monitor, evaluate, and optimize data dependability in Snowflake, ensuring that data is accurate, consistent, and easily accessible.

Setting Up User and Role for Data Reliability

To leverage all functionalities of ADOC for Data Reliability operations on Snowflake, a specific user and role setup is required. This section outlines the minimal permissions necessary to achieve comprehensive data reliability monitoring and optimization with ADOC.

1. Create a Custom Role: Begin by creating a custom role that will be assigned the necessary permissions for data reliability tasks within Snowflake. Optionally, you can add a comment to describe the role's purpose.

CREATE ROLE <custom_role_name> COMMENT = '<Optional comment>';

2. Grant Usage on Warehouse: The custom role must have usage permissions on the warehouse that will be used for Data Reliability operations. Replace <warehouse_name> and <custom_role_name> with your specific details.

GRANT USAGE ON WAREHOUSE <warehouse_name> TO ROLE <custom_role_name>;

3. Grant Usage on Database: For each database that will be used for Data Reliability operations, grant usage permissions to the custom role. This step should be repeated for every relevant database.

GRANT USAGE ON DATABASE <database_name> TO ROLE <custom_role_name>;

4. Grant Usage on Schema: Within each database, schema-level usage permissions are required. Execute this command for each schema in the database involved in Data Reliability tasks.

GRANT USAGE ON SCHEMA <database>.<schema> TO ROLE <custom_role_name>;

5. Grant Select on All Tables Within the Schema : To perform data reliability checks, the custom role needs select permissions on all tables within the target schema.

GRANT SELECT ON ALL TABLES IN SCHEMA <database>.<schema> TO ROLE <custom_role_name>;

6. Create Users: Create a new user that will be associated with the custom role. This ensures that every session initiated by this user will automatically utilize the specified role.

CREATE USER aduser DEFAULT_ROLE = <custom_role_name>;

7. Set Password for the User: For authentication purposes, define a password for the newly created user. While this example uses simple username/password authentication, Snowflake supports various authentication methods, including Multi-Factor Authentication (MFA).

ALTER USER aduser SET PASSWORD = '<password>';

8. Grant the custom role to the User: Finally, assign the previously created custom role to the user to enable them to perform Data Reliability operations within Snowflake.

GRANT ROLE <custom_role_name> TO USER aduser;

Following these steps will prepare your Snowflake setup for integration, resulting in complete monitoring and data reliability optimization.

Adding Snowflake as a Data Source

To add Snowflake as a Data source:

1. Click Register from the left pane.
2. Click Add Data Source.
3. Select the Snowflake Data Source. The Snowflake Data Source basic Details page is displayed.

The following table describes parameters to be entered in the Enter Connection Details window:

4. Enter a name for the data source in the Data Source name field.
5. (Optional) Enter a description for the Data Source in the Description field.
6. (Optional) Enable the Compute capability by switching on the Compute toggle switch.
7. (Optional) Enable the Data Reliability capability by switching on the Data Reliability toggle switch.

8. Select a Data Plane from the Select Data Plane drop-down menu.

To create a new Data Plane, click Setup Dataplane.

9. Click Next. The Snowflake Connection Details page is displayed.

10. Specify the Snowflake URL which is used to locate the database schema in the Snowflake URL field.
11. (Optional) Enable Snowflake OAuth by toggling the Enable OAuth switch.

12. (Optional) Provide the following information to configure Snowflake OAuth:
    1. Authorization Endpoint: This is the URL where Snowflake redirects users to authenticate and authorize access to their data.
    2. Token Endpoint: This is the URL where Snowflake exchanges authorization credentials for an access token.
    3. Client ID: This is a unique identifier assigned to your application by Snowflake when you register it for OAuth.
    4. Client Secret: This is a secret key known only to the application and Snowflake, used to authenticate the identity of the application to the authorization server.
    5. Refresh Token Validity: This specifies the duration for which a refresh token issued by Snowflake remains valid before it expires and needs to be renewed.
    6. OAuth User Name: The user name used for authentication.

12.1. Once you have entered all the details, click the Authenticate button, and you'll be directed to the sign-in page for Snowflake.

12.2. Enter your Username and Password. Upon successful sign-in, the data source will be authenticated.

13. Specify the username with access to the Snowflake account in the Username field.

14. Specify the password to connect to the Snowflake database in the Password field.
15. Enter the user role in the Role field.
16. Select the Dataplane Engine, either Spark or Pushdown Data Engine, for profiling and data quality.
17. Click Test Connection to check if the connection created is working.

If the connection is successful, you can view a Connected message, as shown in the following image. If there is an error, you must verify the Snowflake details provided and enter the correct details.

Observability Set Up

The Set Up Observability page allows you to setup Compute and Data Reliability capabilities. These sections are active only if you enabled them on the Snowflake Data Source basic Details page.

This section has the following panels:

• Compute Observability: Enter the following details to set up Compute Capabilities:

  • Warehouse: Select the warehouses from the drop-down list for which you want to set up Compute Observability.

  • Database: Enter the name of the database whose public schema could be used to configure external storage. By default, the database name is AD_MONITOR_DB.

  • Cost per Credit: Enter the cost per credit for your Snowflake account.

  • Snowflake Fetch Past Data From: Select the historical time period for which you want to fetch the data from Snowflake. The available options are Last 15 Days, Last 30 Days, Last 60 Days, Last 90 Days, Last 180 Days, and Last 1 Year.

  • Snowflake Polling Schedule: Configure the polling schedule to determine when data is fetched from Snowflake. By default, the polling time is set to 12:00 AM (00:00) in the selected time zone (e.g., UTC). Additional timings can be added and customized as per your requirements.

  • (Optional) Configure external stage using storage integration: Enable this toggle button to configure an external stage that references a Snowflake storage integration. On clicking the Submit button, the following privileges on a role are checked by ADOC:

    • CREATE INTEGRATION privilege
    • CREATE FILE FORMAT privilege on given schema
    • CREATE STAGE privilege on given schema
    • USAGE privilege on given database

  • If Configure external stage using integration toggle is not enabled then the following privileges on a role are checked by ADOC:

    • CREATE FILE FORMAT privilege on given schema
    • CREATE STAGE privilege on given schema
    • USAGE privilege on given database

Set Up Observability Error on Submit

• Data Reliability: Enter the following details to set up Data Reliability:
  • Warehouse: Select the warehouses from the drop-down list for which you want to set up Data reliability.
  • Databases: Select the database(s) from the drop-down list for which you want to set up Data reliability.
  • Enable Query Analysis: Turn on this toggle switch to enable query analysis on your queries.
  • Enable Crawler Execution Schedule: Turn on this toggle switch to select a time tag and time zone to schedule the execution of crawlers for Data Reliability.
  • Click the Submit button.

Snowflake is now added as a Data Source. You can choose to crawl your Snowflake account now or later.

Important If you have enabled only the Compute capability, the Start Crawler & Go to Data Sources button is not displayed. You can only view the Go to Data Sources button. The Data Source crawl feature is not applicable for the Compute data sources.

You can navigate to the manage page to view the options available after adding the Data Source.

Optimizing Data Partitioning

Controlling the partition size of data being loaded is critical for improving performance and scalability in ADOC's Snowflake integration. The environment option SNOWFLAKE_PARTITION_SIZE_IN_MB allows you to fine-tune the partition size, which affects how the Snowflake connection generates data-loading tasks. This section explains how to set up this environment variable to increase parallelism and data processing efficiency.

The SNOWFLAKE_PARTITION_SIZE_IN_MB parameter can be adjusted to increase or reduce partition sizes based on the client's individual data volume and processing requirements. Lowering the number from the usual 2000 MB can result in more partitions being created, which is especially useful for huge datasets that require better parallelism.

Note Default Snowflake partition size: 100 MB. | ADOC Default partition size: 2000 MB.

Example: To change the partition size to 10 MB,

SNOWFLAKE_PARTITION_SIZE_IN_MB=10

Reproducing and Monitoring Changes

1. Initial Data Processing: Run a profile or data quality (DQ) job on a dataset size in GBs. Note the number of partitions and the parallelism level achieved.
2. Modify Partition Size: Adjust the SNOWFLAKE_PARTITION_SIZE_IN_MB to a value lower than 2000, such as 10 MB, to increase the number of partitions.
3. Observe Performance Improvement:
   1. Run the same profile/DQ job.
   2. Compare the number of partitions and parallelism level to the initial run. A significant increase in partition count and parallelism should be observed, improving job performance.

PrivateLink Setup for Snowflake Connection

This guide provides step-by-step instructions to set up AWS Private Link for secure connectivity between Snowflake and Acceldata platform in the USW2 region

Prerequisites

• AWS Account with necessary permissions.
• Snowflake account configured
• VPC setup in USW2 region

Step 1: Authorize PrivateLink Access

• Share AWS Account ID:
  • Provide your AWS account ID to the Acceldata support team to authorize PrivateLink from that account.

Step 2: Create VPC Endpoints

1. Navigate to VPC Console:

   • Open the Amazon VPC console.

2. Create Endpoints:

   • Create two VPC endpoints with the following details:

Note: Our assistance is restricted to the US - West-2 region.

1. ADOC Control Plane: com.amazonaws.vpce.us-west-2.vpce-svc-091c001843d33bbaa 2. Secure Relay: com.amazonaws.vpce.us-west-2.vpce-svc-02830f09899d40f01

Step 3: Configure Route53 DNS

1. Set Up DNS Routing:

   • Configure Route 53 with the following records:

     • ADOC Control Plane:

       • Record Type: A
       • Record Name: <tenant>.acceldata.app
       • Value: PrivateLink interface IP of ADOC Control Plane VPC Endpoint

     • Secure Relay:

       • Record Type: A
       • Record Name: dataplane.acceldata.app
       • Value: PrivateLink interface IP of Secure Relay VPC Endpoint

Security Considerations

Custom ARN: Utilize a custom ARN with least/restricted access.

IAM Roles and Policies: Ensure proper IAM roles and policies are in place for secure access.

Additional Help

Snowflake Checklist

For additional reference and help see here.

Take a look at this video which explains the process of adding Snowflake as a data source.

Try our demo here.

Try Demo

Type to search, ESC to discard

Type to search, ESC to discard

Type to search, ESC to discard