Snowflake

📘

Admin credentials required

To complete this guide, you will need permissions to use the ACCOUNTADMIN role on Snowflake.

This guide explains how to create a read-only service account for Monte Carlo on Snowflake.

To review all steps necessary to integrate a data warehouse with Monte Carlo, please see here.

Creating a service account

Please use the SQL snippet below to create a service account for Monte Carlo. Before using the snippet, you will need to populate the following:

  • <monte_carlo_password>: Strong password to be used by the service account user.
  • <your_database>: Snowflake database to be monitored by Monte Carlo.

Please note:

  • If you need to monitor multiple databases in your Snowflake account, please run the snippet multiple times, changing the value of <your_database> on each run.
  • Monte Carlo will use an XS warehouse by default. If you expect heavy monitoring workloads, you may consider a larger warehouse, or assigning Monte Carlo's service account to an existing larger warehouse in your account. This setting can be modified later if necessary.
  • We recommend using Snowflake's worksheet interface to run the snippet. Please copy the code, make any necessary modifications and use the All Queries option to run it in full.
Running the full snippet using Snowflake worksheetsRunning the full snippet using Snowflake worksheets

Running the full snippet using Snowflake worksheets

-- Configuration
set mc_username='MONTE_CARLO';
set mc_password='<monte_carlo_password>';
set mc_warehouse_size='XSMALL';
set mc_warehouse_name='MONTE_CARLO_WH';
set mc_role_name='MONTE_CARLO_ROLE';
set database_to_monitor='<your_database>';

-- Set role for grants
USE ROLE ACCOUNTADMIN;

-- Create warehouse for Monte Carlo's monitoring workload
CREATE WAREHOUSE IF NOT EXISTS identifier($mc_warehouse_name) WAREHOUSE_SIZE=$mc_warehouse_size INITIALLY_SUSPENDED=TRUE
AUTO_SUSPEND = 5 AUTO_RESUME = TRUE;

-- Create the role Monte Carlo will use
CREATE ROLE IF NOT EXISTS identifier($mc_role_name);

-- Create Monte Carlo's user and grant access to role
CREATE USER IF NOT EXISTS identifier($mc_username) PASSWORD=$mc_password DEFAULT_ROLE=$mc_role_name;
GRANT ROLE identifier($mc_role_name) TO USER identifier($mc_username);

-- Grant permissions to use the new warehouse
GRANT OPERATE, USAGE, MONITOR ON WAREHOUSE identifier($mc_warehouse_name) TO ROLE identifier($mc_role_name);

-- Grant privileges to allow access to query history
GRANT IMPORTED PRIVILEGES ON DATABASE "SNOWFLAKE" TO ROLE identifier($mc_role_name);

-- Grant metadata privileges to database to be monitored
GRANT USAGE,MONITOR ON DATABASE identifier($database_to_monitor) TO ROLE identifier($mc_role_name);
GRANT USAGE,MONITOR ON ALL SCHEMAS IN DATABASE identifier($database_to_monitor) TO ROLE identifier($mc_role_name);

USE DATABASE identifier($database_to_monitor);
CREATE OR REPLACE PROCEDURE GRANT_REFERENCES_TO_MONTE_CARLO()
  RETURNS VARCHAR
  LANGUAGE javascript
  EXECUTE AS CALLER
  AS
  $$
  snowflake.createStatement({sqlText: `use database identifier($database_to_monitor)`}).execute();
  var schemas_to_grant = snowflake.createStatement({ sqlText:`select * from information_schema.SCHEMATA where SCHEMA_NAME <> 'INFORMATION_SCHEMA'`}).execute();
  var granted_schemas = "";
  while(schemas_to_grant.next()) {
    table_schema = schemas_to_grant.getColumnValue("SCHEMA_NAME");
    
    snowflake.createStatement({ sqlText:`GRANT REFERENCES ON ALL TABLES IN SCHEMA ${table_schema} TO ROLE identifier($mc_role_name)`}).execute();
    snowflake.createStatement({ sqlText:`GRANT REFERENCES ON ALL VIEWS IN SCHEMA ${table_schema} TO ROLE identifier($mc_role_name)`}).execute();
    snowflake.createStatement({ sqlText:`GRANT REFERENCES ON ALL EXTERNAL TABLES IN SCHEMA ${table_schema} TO ROLE identifier($mc_role_name)`}).execute();

    snowflake.createStatement({ sqlText:`GRANT REFERENCES ON FUTURE TABLES IN SCHEMA ${table_schema} TO ROLE identifier($mc_role_name)`}).execute();
    snowflake.createStatement({ sqlText:`GRANT REFERENCES ON FUTURE VIEWS IN SCHEMA ${table_schema} TO ROLE identifier($mc_role_name)`}).execute();
    snowflake.createStatement({ sqlText:`GRANT REFERENCES ON FUTURE EXTERNAL TABLES IN SCHEMA ${table_schema} TO ROLE identifier($mc_role_name)`}).execute();
    granted_schemas += table_schema + "; "
  }
  return `Granted references for schemas ${granted_schemas}`;
  $$;
CALL GRANT_REFERENCES_TO_MONTE_CARLO();

-- Grant read-only privileges to database to be monitored
GRANT SELECT ON ALL TABLES IN DATABASE identifier($database_to_monitor) TO ROLE identifier($mc_role_name);
GRANT SELECT ON ALL EXTERNAL TABLES IN DATABASE identifier($database_to_monitor) TO ROLE identifier($mc_role_name);
GRANT SELECT ON ALL VIEWS IN DATABASE identifier($database_to_monitor) TO ROLE identifier($mc_role_name);
GRANT USAGE ON FUTURE SCHEMAS IN DATABASE identifier($database_to_monitor) TO ROLE identifier($mc_role_name);

USE DATABASE identifier($database_to_monitor);
CREATE OR REPLACE PROCEDURE GRANT_SELECT_FUTURES_TO_MONTE_CARLO()
  RETURNS VARCHAR
  LANGUAGE javascript
  EXECUTE AS CALLER
  AS
  $$
  snowflake.createStatement({sqlText: `use database identifier($database_to_monitor)`}).execute();
  var schemas_to_grant = snowflake.createStatement({ sqlText:`select SCHEMA_NAME from information_schema.SCHEMATA where SCHEMA_NAME <> 'INFORMATION_SCHEMA'`}).execute();
  var granted_schemas = "";
  while(schemas_to_grant.next()) {
    table_schema = schemas_to_grant.getColumnValue("SCHEMA_NAME");
    snowflake.createStatement({ sqlText:`GRANT SELECT ON FUTURE TABLES IN SCHEMA ${table_schema} TO ROLE identifier($mc_role_name)`}).execute();
    snowflake.createStatement({ sqlText:`GRANT SELECT ON FUTURE VIEWS IN SCHEMA ${table_schema} TO ROLE identifier($mc_role_name)`}).execute();
    snowflake.createStatement({ sqlText:`GRANT SELECT ON FUTURE EXTERNAL TABLES IN SCHEMA ${table_schema} TO ROLE identifier($mc_role_name)`}).execute();
    granted_schemas += table_schema + ";"
  }
  return `Granted future select for schemas ${granted_schemas}`;
  $$;
CALL GRANT_SELECT_FUTURES_TO_MONTE_CARLO();

Monitoring multiple databases with Monte Carlo

To monitor multiple databases, you will grant Monte Carlo's service account the necessary permissions to each of them. Monte Carlo will automatically monitor all databases to which it has access.

To grant permissions, please run the snippet in the previous section for each of the databases you would like to monitor. You will modify the database_to_monitor parameter in each run.

Providing service account credentials during onboarding

When using Monte Carlo's onboarding wizard, please provide the following details to validate and complete the integration:

  • User and Password: Please provide the credentials you chose while creating the service account.
  • Account: This would be the part that precedes snowflakecomputing.com in your Snowflake console URL. For example, if you use the URL https://xyz12345.us-east-1.snowflakecomputing.com/ to access Snowflake, your account name will be xyz12345.us-east-1.
  • Warehouse: Please provide the name of the warehouse you chose while creating the service account, MONTE_CARLO_WH by default.

Did this page help you?