Redshift

Automate the fulfillment of data subject access, deletion, and identifier retrieval requests in Redshift.

Configuration

Create a Connection object with a Redshift 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":"Metrics DWH",
    "uuid":"e887952b-4bde-4344-bd1d-c9a46805ebed",
    "capabilities":["privacy/access","privacy/delete", "privacy/optout", "privacy/identifiers"],
    "mode":"live",
    "connector_type":"Redshift",
    "queries":{
        "identifiers": {
            "phone_number": [
                "call get_phone_number(%(email)s)"
            ]
        },
        "access": ["call dsr('access', %(email)s)"],
        "delete": ["call dsr('delete', %(email)s)"],
        "optout": ["call dsr('optout', %(email)s)"]
    },
"credentials_location":"arn:aws:secretsmanager:Region:AccountId:secret:datagrail.redshift"
}

Credential Creation

The Redshift connection authenticates with your server using username and password. Create a new secret in you credentials manager with the following key/value pairs:

{
    "user": "<DB username>",
    "password": "<DB password>",
    "host": "<server domain name or IP address>",
    "port": "<port, e.g. 5432>",
    "database": "<DB, e.g. BikeStores>"
}

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 Redshift without needing to modify the Agent configuration, and for the query lists to be easily readable, especially in the case of complex joins.