Snowflake

Automate the fulfillment of data subject access, deletion, opt-out, and identifier retrieval requests in Snowflake.

Configuration

Create a Connection object with a Snowflake ConnectorType to instantiate a connection. Once the object has been created and the credentials have been stored, place the object in the RMAgentConfig connections array.

Example Configuration

{
    "name": "Accounts DB",
    "uuid": "f237cdae-e8d1-4799-be0a-8a79c25e33de",
    "capabilities": ["privacy/access", "privacy/delete", "privacy/optout", "privacy/identifiers"],
    "mode": "live",
    "connector_type": "Snowflake",
    "queries": {
        "identifiers": {
            "phone_number": [
                "CALL get_phone_number(%(email)s)"
            ]
        },
        "access": ["CALL dsr_operation('access', %(email)s)"],
        "delete": ["CALL dsr_operation('delete', %(email)s)"],
        "optout": ["CALL dsr_operation('optout', %(email)s)"]
    },
    "credentials_location": "arn:aws:secretsmanager:Region:AccountId:secret:datagrail.snowflake"
}

Credential Creation

The Snowflake connection supports both username/password authentication and key-pair authentication (recommended).

It is advised to create a DataGrail Agent-specific Snowflake User or Role to scope the permissions to the operations that it needs to perform.

Username/Password Authentication

To use password authentication, create a secret in your credentials manager with the following key/value pairs:

{
    "user": "<DB username>",
    "password": "<DB password>",
    "account": "<Snowflake Account, e.g. EXA*****>",
    "warehouse": "<Snowflake Warehouse, e.g. COMPUTE_WH>",
    "database": "<Snowflake DB, e.g. SNOWFLAKE_SAMPLE_DATA>"
}

Key-Pair Authentication

To utilize key-pair authentication, a public and private key must be generated using the following steps. Snowflake's documentation can be found here.

1. Generate the private key openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
2. Generate the public key openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
3. Assign public key to user within Snowflake ALTER USER {user} SET RSA_PUBLIC_KEY={public_key}
4. Base64 encode the private key openssl base64 -in rsa_key.p8 -out encoded_rsa_key.p8
5. Create a secret in your credentials manager with the following key/value pairs.

{
    "user": "<DB username>",
    "private_key": "<Base64 encoded private key>",
    "account": "<Snowflake Account, e.g. EXA*****>",
    "warehouse": "<Snowflake Warehouse, e.g. COMPUTE_WH>",
    "database": "<Snowflake DB, e.g. SNOWFLAKE_SAMPLE_DATA>"
}

Query Syntax and Parameter Binding

The Queries object allows query strings to be dynamically formatted with identifiers. Identifiers are passed individually to queries and are bound to the variables in the operation. Variables are specified using the pyformat parameter style, e.g. ...WHERE email=%(email)s.

Best Practices

For ease of maintainability and readability, it is recommended that the various queries be stored procedures. This allows for the underlying queries to be modified in Snowflake without needing to modify the Agent configuration, and for the query lists to be easily readable, especially in the case of complex joins.