Skip to main content

Snowflake Connection

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

Setup Instructions​

In the setup process below, you will create and configure a Connection object with a Snowflake ConnectorType that is used to instantiate a connection. The agent configuration will be updated by placing the connection object into the DataGrailAgentConfig connections array.

Example Connection Object
{
  "name": "Contacts DB",
  "uuid": "c6dc661e-c917-4291-9702-e20d40266289",
  "capabilities": ["privacy/access", "privacy/deletion", "privacy/optout", "privacy/identifiers"],
  "mode": "live",
  "connector_type": "Snowflake",
  "queries": {
    "access": ["CALL sp_dsr_access(%(email)s);"],
    "delete": ["CALL sp_dsr_delete(%(email)s);"],
    "optout": ["CALL sp_dsr_optout(%(email)s);"],
    "identifiers": {
      "phone_number": ["CALL sp_dsr_get_phone(%(email)s);"]
    }
  },
  "credentials_location": "arn:aws:secretsmanager:Region:AccountId:secret:datagrail-rm-agent-snowflake"
}
Prerequisites

Before performing the steps below, ensure the following:

  • Request Manager Agent is deployed and connected in DataGrail.
  • Network is configured to allow the Agent to connect with the Snowflake instance.

Create Request Logic​

In this step, create the request logic to be executed in your system for a specific request type.

When a Data Subject requests to access the personal information that your company maintains on them, they will submit an Access request. Your request logic should return all applicable information found in this system.

Example Access Query
SELECT * FROM contacts WHERE email = 'subject@domain.com';
Using Stored Procedures

For ease of maintainability and readability, using stored procedures is recommended instead of individual queries. When you need to modify the underlying queries, there will be no need to modify the Agent configuration nor restart the Agent.

Example Stored Procedure
CALL sp_dsr_access('subject@domain.com');

Create and Store Credentials​

The Snowflake connection supports the following authentication methods:

Scoping Permissions

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.

Key-Pair Authentication​

To utilize key-pair authentication, a public and private key must be generated using the following steps. Additional information can be found in the Snowflake documentation.

  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 the public key to a Snowflake user:

    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": "<agent user>",
      "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>"
    }

Username/Password Authentication​

  1. In Snowflake, create a new user for the agent. Only grant the minimum necessary permissions for executing the request.

  2. Configure the following JSON key-value pairs:

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

  3. Store the JSON value in your vault with an entry name like datagrail-rm-agent-snowflake.

  4. Ensure that the agent is configured to retrieve the value of this vault entry.

Create Connection Object​

To define the interactions between the agent and the internal system, configure the Connection object.

  1. Using the empty connection object below, enter a connection name and uuid (v4).

    {
      "name": "",
      "uuid": "",
      "capabilities": [],
      "mode": "",
      "connector_type": "Snowflake",
      "queries": {
        "access": [],
        "delete": [],
        "optout": [],
        "identifiers": {}
      },
      "credentials_location": ""
    }

  2. Set the capabilities that correspond to your request logic.

  3. Under queries, add your request logic as a string to the corresponding request type.

  4. 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. Replace any static identifier in the request logic using pyformat parameter formatting (e.g., ...WHERE email=%(email)s).

    Parameter Binding Example
    -- BEFORE
    CALL sp_dsr_access('subject@domain.com');

    -- AFTER
    CALL sp_dsr_access(%(email)s);

  5. Set the credentials_location value to the location of the secret created above (e.g., AWS ARN).

Example Connection Objects

After following the steps above, the resulting connection object should look similar to these examples.

Example Connection Object with Access Capability
{
  "name": "Contacts DB",
  "uuid": "c6dc661e-c917-4291-9702-e20d40266289",
  "capabilities": ["privacy/access"],
  "mode": "live",
  "connector_type": "Snowflake",
  "queries": {
    "access": ["CALL sp_dsr_access(%(email)s);"],
    "delete": [],
    "optout": [],
    "identifiers": {}
  },
  "credentials_location": "arn:aws:secretsmanager:Region:AccountId:secret:datagrail-rm-agent-snowflake"
}

Update and Restart Agent​

In this section, you will integrate the internal system with DataGrail by updating the agent environment variable and restarting the agent to apply the changes.

Already Processing Requests?

If your DataGrail account is already processing Data Subject Requests, new agent connections may be automatically processed with all new requests. If you are not sure, check with your DataGrail admin or dedicated Account Manager before starting this section.

  1. In the DATAGRAIL_AGENT_CONFIG environment variable, add the new connection object to the connections array.
  2. Save the environment variable, and restart the agent service to apply the changes.
  3. After the restart, confirm that the agent service is healthy, and the logs contain no errors.
  4. In DataGrail, update the integration by navigating to Integrations and select the agent integration.
  5. Select Edit Integration, enter the Client Secret, and then select Save Changes.
  6. To view the new connection, navigate back to Integrations and locate it in the list.
Processing Your First Request

Now that you've successfully added this connection to DataGrail, try it out by submitting and processing a request.

 

Need help?
If you have any questions, please reach out to your dedicated Account Manager or contact us at support@datagrail.io.

Disclaimer: The information contained in this message does not constitute as legal advice. We would advise seeking professional counsel before acting on or interpreting any material.