Snowflake Configuration (AWS)

Before you can load data into Qrvey for export to S3, you must configure access to Snowflake clusters.

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, then select Next.

   d. Set Alias to QRVEY_DEPLOYMENT-snowflake-kms (replace QRVEY_DEPLOYMENT with your deployment name), then select Next.

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

   f. Select Finish.

3. Open the key details.

   a. Go to the Public key tab.

   b. Copy the KMS public key (excluding header and footer lines and line breaks).

   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. Add permissions using the following JSON array:

   {
     "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 in Create a Policy for Snowflake Permissions.

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 object.

   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 the 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 a Policy to the 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: Create a Snowflake Connection

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