Snowflake Configuration (AWS)

Follow these steps to configure access to Snowflake clusters for loading data into Qrvey and exporting data to S3.

Step 1: Create a KMS Key

1. Open the AWS console using the Qrvey deployment’s AWS account.

2. Create a new key:

   a. Select Asymmetric.

   b. Select Sign and verify.

   c. Set Key spec to RSA_2048.

   d. Select Next.

   e. Set Alias to QRVEY_DEPLOYMENT-snowflake-kms (replace QRVEY_DEPLOYMENT with your deployment name).

   f. Select Next.

   g. Assign one or more AWS users as Key administrators.

   h. Select Finish.

3. Open the key details:

   a. Go to the Public key tab.

   b. Copy the KMS public key (exclude opening/closing lines and newlines).

   c. Record the KMS key’s ARN.

Step 2: Create a Policy for Snowflake Permissions

1. In the Qrvey AWS account, open IAM > Policies > Create Policy.

2. Use the following JSON for permissions:

   {
     "Version": "2012-10-17",
     "Statement": [
     {
       "Effect": "Allow",
       "Action": [
         "s3:PutObject",
         "s3:GetObject",
         "s3:GetObjectVersion",
         "s3:DeleteObject",
         "s3:DeleteObjectVersion"
       ],
       "Resource": "arn:aws:s3:::QRVEY_DEPLOYMENT-dataload-drdatacommons/sql_export/*"
     },
     {
       "Effect": "Allow",
       "Action": [
         "s3:ListBucket",
         "s3:GetBucketLocation"
       ],
       "Resource": "arn:aws:s3:::QRVEY_DEPLOYMENT-dataload-drdatacommons",
       "Condition": {
         "StringLike": {
         "s3:prefix": [
           "sql_export/*"
         ]
         }
       }
     }
     ]
   }

3. Select Next: Tags, then Next: Review.

4. Name the policy (for example, SnowflakeDataAccessPolicy).

Step 3: Create a Role for Snowflake Access

1. In IAM, go to Roles > Create Role.

2. Select Another AWS account.

3. Enter the Account ID and set Require external ID to 0000.

4. Select Next.

5. Attach the policy created above.

6. Set the Role Name and Description, then create the role.

7. Record the role’s ARN.

Step 4: Configure Snowflake

1. In the Snowflake console, create a user and assign a role with read permissions.

2. Create a Storage Integration:

   CREATE STORAGE INTEGRATION QrveyExportToS3_QRVEY_DEPLOYMENT
     TYPE = EXTERNAL_STAGE
     STORAGE_PROVIDER = S3
     ENABLED = TRUE
     STORAGE_AWS_ROLE_ARN = 'EXPORT_TO_S3_ARN'
     STORAGE_ALLOWED_LOCATIONS = ('s3://QRVEY_DEPLOYMENT-dataload-drdatacommons/sql_export/')

3. Grant usage to the Snowflake user role:

   GRANT USAGE ON integration QrveyExportToS3_QRVEY_DEPLOYMENT TO ROLE SNOWFLAKE_USER_ROLE;

4. Set the trust relationship:

   a. Run:

   DESC INTEGRATION QrveyExportToS3_QRVEY_DEPLOYMENT;

   b. Record the STORAGE_AWS_IAM_USER_ARN and STORAGE_AWS_EXTERNAL_ID values.

   c. In IAM, edit the role’s trust relationship:

   {
    "Version": "2012-10-17",
    "Statement": [
     {
       "Effect": "Allow",
       "Principal": {
        "AWS": "STORAGE_AWS_IAM_USER_ARN"
       },
       "Action": "sts:AssumeRole",
       "Condition": {
        "StringEquals": {
         "sts:ExternalId": "STORAGE_AWS_EXTERNAL_ID"
        }
       }
     }
    ]
   }

5. Update the Snowflake user with the KMS public key:

   ALTER USER SNOWFLAKE_USER SET rsa_public_key='KMS_PUBLIC_KEY'

Step 5: Create a Secret for the Snowflake Cluster

In AWS Secrets Manager, create a new secret:

1. Select Other type of secret and Plaintext.

2. Add key-value pairs:

   {
   "user": "XXXX",
   "password": "****",
   "account": "accountid.region",
   "kmsARNId": "KMS_KEY_ARN",
   "storageIntegration": "QrveyExportToS3_QRVEY_DEPLOYMENT"
   }

3. Name the secret (for example, SnowflakeClusterSecret).

4. Store the secret and record its ARN.

Step 6: Attach Policy to Pod Identity Role

1. In EKS, find your cluster and open the role in Pod Identity associations.

2. Attach the following policy:

   {
     "Version": "2012-10-17",
     "Statement": [
     {
       "Sid": "VisualEditor0",
       "Effect": "Allow",
       "Action": "secretsmanager:GetSecretValue",
       "Resource": "${SECRET_ARN}"
     },
     {
       "Sid": "getPublicKey",
       "Effect": "Allow",
       "Action": [
         "kms:GetPublicKey",
         "kms:Sign"
       ],
       "Resource": [
         "${ARN_KMS}"
       ]
     }
     ]
   }

Step 7: Connect Qrvey to Snowflake

1. In Qrvey, go to Datasets > New Connections.
2. Create a new Snowflake connection.
3. Enter the Secret ARN.
4. Test the connection.