Configure Snowflake access for quality operations - Precisely Data Integrity Suite

Set up dedicated Snowflake roles and permissions for two scenarios: read-only access for rules and profiling through a catalog datasource, and read/write access for quality pipeline execution.

1. Set up roles and permissions for a catalog datasource.

   This configuration grants a custom role the minimum permissions required to evaluate quality rules and run profiling against a Snowflake datasource. Key permissions, including CREATE STAGE, CREATE FILE FORMAT, and CREATE FUNCTION, support temporary processing objects that are removed automatically after each run.

   1. Create a custom role.

      create role PRECISELY_DIS_INSIGHTS;

   2. Create an empty staging schema.

      create schema EXAMPLE_DB.STAGING_SCHEMA;

      If the staging schema already exists, drop the existing stage before you continue:

      drop stage if exists EXAMPLE_DB.STAGING_SCHEMA.PRECISELY_DQ_STAGE;

   3. Grant staging permissions to the role.

      grant CREATE STAGE, CREATE FILE FORMAT, CREATE FUNCTION on schema EXAMPLE_DB.STAGING_SCHEMA to role PRECISELY_DIS_INSIGHTS;

   4. Grant warehouse usage permission to the role.
      Note: Grant the role permission to use the desired warehouse for processing.

      grant usage on warehouse EXAMPLE_WH to role PRECISELY_DIS_INSIGHTS;

   5. Grant database and schema access to the role.
      Note: Provide the role permission to use the database and desired schemas, and to read data from the tables.

      • grant usage on database EXAMPLE_DB to role PRECISELY_DIS_INSIGHTS;
      • grant usage on all schemas in database EXAMPLE_DB to role PRECISELY_DIS_INSIGHTS;
      • grant usage on future schemas in database EXAMPLE_DB to role PRECISELY_DIS_INSIGHTS;
      • grant select on all tables in database EXAMPLE_DB to role PRECISELY_DIS_INSIGHTS;
      • grant select on future tables in database EXAMPLE_DB to role PRECISELY_DIS_INSIGHTS;
   6. Assign the role to the user who will perform cataloging.

      grant role PRECISELY_DIS_INSIGHTS to user PRECISELY_DIS_USER;

   7. Grant the user permission to use internal stages.

      alter user PRECISELY_DIS_USER set PREVENT_UNLOAD_TO_INTERNAL_STAGES=FALSE;

      Note: This user-level Snowflake setting is currently required for any operation that uses a pipeline engine. It allows the pipeline to write temporary processing data to the staging schema while executing UDF and UTF operations. These temporary files are used only during pipeline execution and are automatically deleted after processing completes.
   8. Create a Snowflake connection using the following credentials.
      • User: PRECISELY_DIS_USER
      • Role: PRECISELY_DIS_INSIGHTS
      • Warehouse: EXAMPLE_WH
   9. Create a pipeline engine and set its schema to the staging schema.

      Create a pipeline engine with schema STAGING_SCHEMA.

2. Grant roles and permissions for a quality pipeline.

   This configuration grants a dedicated role full access to compute resources, schemas, tables, and staging areas required during pipeline execution. Unlike the catalog datasource setup, this flow also grants write permissions so the pipeline can store results and manage intermediate tables.

   Required permissions are:

   • CREATE STAGE: Allows the pipeline to create temporary storage locations for intermediate execution files.
   • CREATE FILE FORMAT: Enables the pipeline to define formats required for reading and writing staged data.
   • CREATE FUNCTION: Allows creation of user-defined functions used during rule evaluation or transformations.
   • SELECT: Provides read access to source tables required for rule evaluation.
   • INSERT, UPDATE, DELETE, TRUNCATE: Allows the pipeline to write results and manage intermediate tables.
   • CREATE TABLE: Enables creation of result tables or temporary processing tables.
   • READ and WRITE on Stage: Allows upload and retrieving intermediate files used during execution.

   Note:

   • The pipeline may create temporary stages, file formats, or functions during execution.
   • Intermediate data may be written to the internal stage while rules are evaluated.
   • Temporary files and objects are used only during pipeline execution.
   • Cleanup for most temporary artifacts is automated after the pipeline run completes.
   • The staging schema and stage object remain available for reuse across pipeline runs.
   1. Create a dedicated role for pipeline execution.

      CREATE ROLE IF NOT EXISTS PRECISELY_DQ_OPERATOR;

   2. Grant warehouse usage so the role can run pipeline workloads.

      GRANT USAGE ON WAREHOUSE DQ_AUTO_DEV TO ROLE PRECISELY_DQ_OPERATOR;

   3. Grant database access.

      GRANT USAGE ON DATABASE DQ TO ROLE PRECISELY_DQ_OPERATOR;

   4. Grant schema access to the schema where the pipeline reads and writes data.

      GRANT USAGE ON SCHEMA DQ.AUTOMATION_DO_NOT_TOUCH TO ROLE PRECISELY_DQ_OPERATOR;

   5. Grant object-creation permissions for stages, file formats, and functions.

      GRANT CREATE STAGE, CREATE FILE FORMAT, CREATE FUNCTION ON SCHEMA DQ.AUTOMATION_DO_NOT_TOUCH TO ROLE PRECISELY_DQ_OPERATOR;

   6. Grant read access to existing and future tables.

      GRANT SELECT ON ALL TABLES IN SCHEMA DQ.AUTOMATION_DO_NOT_TOUCH TO ROLE PRECISELY_DQ_OPERATOR;
      GRANT SELECT ON FUTURE TABLES IN SCHEMA DQ.AUTOMATION_DO_NOT_TOUCH TO ROLE PRECISELY_DQ_OPERATOR;

   7. Grant write access to existing tables.

      GRANT INSERT, UPDATE, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA DQ.AUTOMATION_DO_NOT_TOUCH TO ROLE PRECISELY_DQ_OPERATOR;

   8. Grant write access to future tables.

      GRANT INSERT, UPDATE, DELETE ON FUTURE TABLES IN SCHEMA DQ.AUTOMATION_DO_NOT_TOUCH TO ROLE PRECISELY_DQ_OPERATOR;

   9. Grant table creation permission.

      GRANT CREATE TABLE ON SCHEMA DQ.AUTOMATION_DO_NOT_TOUCH TO ROLE PRECISELY_DQ_OPERATOR;

   10. Create an internal stage for temporary pipeline files.

       CREATE STAGE IF NOT EXISTS DQ.AUTOMATION_DO_NOT_TOUCH.PRECISELY_DQ_STAGE;

   11. Grant stage read and write permissions to the role.

       GRANT READ, WRITE ON STAGE DQ.AUTOMATION_DO_NOT_TOUCH.PRECISELY_DQ_STAGE TO ROLE PRECISELY_DQ_OPERATOR;

   12. Create a dedicated Snowflake user for pipeline execution.

       CREATE USER IF NOT EXISTS PRECISELY_DQ_USER PASSWORD = 'your_strong_password' DEFAULT_ROLE = PRECISELY_DQ_OPERATOR DEFAULT_WAREHOUSE = DQ_AUTO_DEV DEFAULT_NAMESPACE = DQ.AUTOMATION_DO_NOT_TOUCH MUST_CHANGE_PASSWORD = FALSE DISABLED = FALSE;

   13. Assign the role to the pipeline user.

       GRANT ROLE PRECISELY_DQ_OPERATOR TO USER PRECISELY_DQ_USER;

   14. Allow the pipeline to unload intermediate results to internal stages.

       ALTER USER PRECISELY_DQ_USER SET PREVENT_UNLOAD_TO_INTERNAL_STAGES = FALSE;