Snowflake Configuration (AWS)

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

Steps

1. Create a KMS Key

1. Open the AWS console using the Qrvey deployment’s AWS account.
2. Create a new key:

• Select Asymmetric.
• Choose Sign and verify.
• Set Key spec to RSA_2048.
• Select Next.
• Set Alias to QRVEY_DEPLOYMENT-snowflake-kms (replace QRVEY_DEPLOYMENT with your deployment name).
• Select Next.
• Assign one or more AWS users as Key administrators.
• Select Finish.

3. Open the key details:

• Go to the Public key tab.
• Copy the KMS public key (exclude opening/closing lines and newlines).
• Note the KMS key’s ARN.

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

3. Create a Role for Snowflake Access

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

• Choose Another AWS account.
• Enter the Account ID and set Require external ID to 0000.
• Select Next.
• Attach the policy created above.
• Set Role Name and Description, then create the role.
• Note the role’s ARN.

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:

• Run:

  DESC INTEGRATION QrveyExportToS3_QRVEY_DEPLOYMENT;

• Note STORAGE_AWS_IAM_USER_ARN and STORAGE_AWS_EXTERNAL_ID.

• 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'

5. Create a Secret for the Snowflake Cluster

1. In AWS Secrets Manager, create a new secret:

• Select Other type of secret and Plaintext.

• Add key/value pairs:

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

• Name the secret (for example, SnowflakeClusterSecret).

• Store the secret and note its ARN.

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}"
    ]
   }
  ]
}

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.