# Database Integration Set Up for DSR Automation (/docs/integrations/custom/database-integration-set-up-for-dsr-automation)

## Overview [#overview]

With the Transcend Database integration, you can fulfill DSRs directly against a Database by running SQL queries.

<Callout type="info" title="Throughput">
  Processing a high volume of DSRs? Turn on connection pooling on your Sombra and review the integration's rate limits. See [Database Integration Throughput](./database-integration-throughput.mdx) and [Data System Rate Limits](../../dsr-automation/connecting-data-systems/data-system-rate-limits.mdx). The integration's Connection tab shows a banner when it could benefit from pooling.
</Callout>

## Defining the SQL Queries for DSR Automation [#defining-the-sql-queries-for-dsr-automation]

In `Infrastructure / Integrations`, select the database of interest. On the `Manage Datapoints` page, you will find the tables in the database as <GlossaryTerm id="datapoint">datapoint</GlossaryTerm>. Datapoints can be discovered automatically by enabling the Datapoint Schema Discovery plugin for the integration or by manually creating datapoints in this interface.

![Manually create datapoint](/images/screenshot/2023-06-01-manually-create-datapoint.png)

## SQL Statement Variables [#sql-statement-variables]

When you write custom SQL — whether in the `Prepared Statement` field of a datapoint DSR action, in a preflight database enrichment SQL query, or in a consent workflow SQL trigger — Transcend lets you reference the data subject's identifier and any context from the inbound DSR by templating variables into your SQL. The same variable set is available in every SQL editor that targets a database integration. Variables are resolved per-request and bound through prepared-statement parameters when possible, so you never need to worry about quoting or escaping the values yourself.

### Placeholder syntax [#placeholder-syntax]

Three placeholder syntaxes are supported and produce identical results:

<table>
  <thead>
    <tr>
      <th>
        Syntax
      </th>

      <th>
        Meaning
      </th>

      <th>
        Example
      </th>
    </tr>
  </thead>

  <tbody>
    <tr>
      <td>
        `@variableName`
      </td>

      <td>
        Named-parameter form (identifier and request-context variables only)
      </td>

      <td>
        `WHERE email = @email`
      </td>
    </tr>

    <tr>
      <td>
        `{{variableName}}`
      </td>

      <td>
        Template-variable form
      </td>

      <td>
        `WHERE email = {{email}}`
      </td>
    </tr>

    <tr>
      <td>
        ?
      </td>

      <td>
        Positional placeholder — always binds the identifier value for the datapoint
      </td>

      <td>
        `WHERE email = ?`
      </td>
    </tr>
  </tbody>
</table>

You can mix and match all three in the same statement. Customer-supplied values flow through ODBC prepared-statement bind, never string interpolation, so SQL injection is not a concern.&#xA;

### The identifier variable [#the-identifier-variable]

The most common variable is the identifier value — the email/phone/userId/etc. that the DSR is looking up. The variable name is configurable per integration (see _Integrations → \<Integration> → DSR Automation → Identifiers_); All three of these are equivalent and resolve to the data subject's identifier:

```sql
-- Bare positional (legacy convention — every `?` is the identifier)
SELECT * FROM users WHERE email = ?;

-- Named (replace 'email' with whatever your data silo's identifier type is)
SELECT * FROM users WHERE email = @email;

-- Template form
SELECT * FROM users WHERE email = {{email}};
```

The same identifier value can appear multiple times in one statement — for example, when matching against several columns:

```sql
SELECT * FROM users WHERE email = @email OR alt_email = @email OR LOWER(login_email) = LOWER(@email);
```

### Request context variables [#request-context-variables]

Every DSR carries metadata about the request itself. These variables are available in every SQL statement:

<table>
  <tbody>
    <tr>
      <td>
        Variable
      </td>

      <td>
        Type
      </td>

      <td>
        Description
      </td>

      <td>
        Example Value
      </td>
    </tr>

    <tr>
      <td>
        `requestId`
      </td>

      <td>
        string
      </td>

      <td>
        The unique UUID of the privacy request
      </td>

      <td>
        `"1d50baa0-7409-43e8-baa2-7283d0fc4f7e"`
      </td>
    </tr>

    <tr>
      <td>
        `requestType`
      </td>

      <td>
        string
      </td>

      <td>
        The action type
      </td>

      <td>
        `"ACCESS"`, `"ERASURE"`, `"CONTACT_OPT_OUT"`, `"CUSTOM_OPT_IN"`, etc.
      </td>
    </tr>

    <tr>
      <td>
        `requestCreatedAt`
      </td>

      <td>
        string
      </td>

      <td>
        ISO 8601 timestamp of request submission
      </td>

      <td>
        `"2026-05-26T20:14:33.000Z"`
      </td>
    </tr>

    <tr>
      <td>
        `requestLocale`
      </td>

      <td>
        string
      </td>

      <td>
        Locale code submitted with the request
      </td>

      <td>
        `"en-US"`
      </td>
    </tr>

    <tr>
      <td>
        `requestDetails`
      </td>

      <td>
        string
      </td>

      <td>
        Free-text details supplied by the data subject
      </td>

      <td>
        `"Please remove all marketing data"`
      </td>
    </tr>

    <tr>
      <td>
        `requestIsTest`
      </td>

      <td>
        boolean
      </td>

      <td>
        `true` if the request was submitted in test mode
      </td>

      <td>
        `0, 1`
      </td>
    </tr>

    <tr>
      <td>
        `requestOrigin`
      </td>

      <td>
        string
      </td>

      <td>
        Origin of the request
      </td>

      <td>
        `"PRIVACY_CENTER"`, `"API"`, `"ADMIN_DASHBOARD"`, etc.
      </td>
    </tr>

    <tr>
      <td>
        `requestDataSubjectType`
      </td>

      <td>
        string
      </td>

      <td>
        The data subject type slug
      </td>

      <td>
        `"customer"`, `"employee"`
      </td>
    </tr>

    <tr>
      <td>
        `requestCountry`
      </td>

      <td>
        string | null
      </td>

      <td>
        ISO 3166-1 alpha-2 country code
      </td>

      <td>
        `"US"`
      </td>
    </tr>

    <tr>
      <td>
        `requestCountrySubDivision`
      </td>

      <td>
        string | null
      </td>

      <td>
        ISO 3166-2 subdivision code
      </td>

      <td>
        `"US-CA"`
      </td>
    </tr>

    <tr>
      <td>
        `requestPrimaryEmailValue`
      </td>

      <td>
        string | null
      </td>

      <td>
        The primary email associated with the request, when known
      </td>

      <td>
        `"alice@example.com"`
      </td>
    </tr>

    <tr>
      <td>
        `consentPurposeTriggerSlug`
      </td>

      <td>
        string | null
      </td>

      <td>
        For consent-workflow-triggered DSRs, the purpose slug that triggered the workflow
      </td>

      <td>
        `"Marketing"`
      </td>
    </tr>

    <tr>
      <td>
        `consentPurposeTriggerValue`
      </td>

      <td>
        boolean | null
      </td>

      <td>
        The new consent value that triggered the workflow
      </td>

      <td>
        `false`
      </td>
    </tr>

    <tr>
      <td>
        `partitionId`
      </td>

      <td>
        string | null
      </td>

      <td>
        The partition the request was filed under
      </td>

      <td>
        `"1bf15936-d4e1-43bc-8322-606c1afa34a4"`
      </td>
    </tr>

    <tr>
      <td>
        `partitionName`
      </td>

      <td>
        string | null
      </td>

      <td>
        Human-readable partition name
      </td>

      <td>
        `"EU Production"`
      </td>
    </tr>
  </tbody>
</table>

#### Examples [#examples]

Tag every audit-log row with the DSR ID so you can correlate later:

```sql
INSERT INTO dsr_audit (email, request_id, action, requested_at)
VALUES ({{email}}, {{requestId}}, {{requestType}}, {{requestCreatedAt}});
```

Preflight enricher — find every `user_id` associated with a customer's email and pass each one downstream as a new identifier:

```sql
-- Configured as an database preflight check with input identifier "email" and
-- output identifier "userId"
SELECT user_id AS "userId"
FROM customers
WHERE LOWER(email) = LOWER({{email}});
```

Skip the rest of an erasure if the request was submitted in test mode:

```sql
DELETE FROM users WHERE email = {{dsr_identifier_value}} AND NOT {{requestIsTest}};
```

Filter by region (useful for partitioned data warehouses):

```sql
SELECT * FROM customer_records WHERE customer_id = {{email}} AND region_code = {{requestCountry}};
```

### Custom field variables [#custom-field-variables]

Every request attribute / custom field defined on your Transcend organization automatically becomes a templatable variable named `customField_<camelCaseAttributeKey>`. The attribute key is [camelCased](https://appdevtools.com/case-converter) — e.g. an attribute named `Audit Reason` becomes `customField_auditReason`.

```sql
INSERT INTO dsr_audit (email, audit_note, requester_team)
VALUES (
{{email}},
{{customField_auditNote}},
{{customField_requesterTeam}}
);
```

If the attribute is multi-select, the value is bound as a JSON array string (e.g. `'["a","b"]'`) — use `PARSE_JSON()` (Snowflake), `'::JSONB'` (Postgres), or your driver's equivalent to access individual elements.

### Consent preference variables [#consent-preference-variables]

For DSRs triggered by consent workflows, every consent preference defined on the triggering purpose is exposed as `consentPreference_<preferenceSlug>`. Boolean preferences are inlined as SQL `TRUE`/`FALSE`; multi-select preferences are bound as JSON-array strings.

```sql
UPDATE marketing_subscriptionsSET email_opt_in = {{consentPreference_marketingEmail}}, sms_opt_in = {{consentPreference_marketingSms}} WHERE email = {{email}};
```

### NULL and boolean handling [#null-and-boolean-handling]

`null` and `boolean` variables are inlined as SQL literals rather than bound, because ODBC parameter binding is not portable across drivers for those types. Specifically:

- `null` → `NULL` (a real SQL NULL — comparisons like `IS NULL` work as expected).
- `true` → `TRUE`.
- `false` → `FALSE`.

Quoted placeholders are also handled: if you write `'{{requestLocale}}'` and `requestLocale` is null, the surrounding quotes are stripped and the result is the SQL `NULL` literal (not the string `'null'`).

#### Redshift gotcha: explicit type casts in `SELECT` [#redshift-gotcha-explicit-type-casts-in-select]

Amazon Redshift's query planner refuses to resolve untyped expressions in a `SELECT` projection (`Invalid type: 705 / Unreachable - tbl_trans.hpp` errors). When you project a templated variable directly as a column — especially `{{requestLocale}}`, `{{customField_*}}`, `{{requestId}}`, or anything that might resolve to `NULL` — wrap it in an explicit cast:

```sql
SELECT 
  email, 
  {{requestIsTest}}::BOOLEAN AS is_test, 
  {{customField_audit}}::VARCHAR AS audit_note, 
  {{requestLocale}}::VARCHAR AS locale, 
  {{requestId}}::VARCHAR AS rid 
FROM usersWHERE email = ?;
```

Snowflake, Postgres, MySQL, and the other supported drivers tolerate the untyped form, so the cast is only strictly required on Redshift — but it's portable across all of them and a good habit if you may run the same SQL against multiple silos.

### Database support [#database-support]

The following databases support all variables described above:

- Snowflake
- PostgreSQL
- Amazon Redshift
- MySQL
- MariaDB
- IBM DB2
- Oracle
- Azure Synapse
- Databricks Lakehouse
- Google Cloud Spanner

The following databases support only Identifier + `requestId`

- Microsoft SQL Server
- Google BigQuery
- MongoDB
- DynamoDB (PartiQL)
- Google Bigtable

The following databases only support identifier variables:

- Azure Cosmos DB (NoSQL)
- Elasticsearch

For drivers with partial support, you can still use the identifier (`?`, `@email, {{email}}`) and `{{requestId}}` in both preflight enricher and datapoint SQL, but referencing any other variable will leave the placeholder text in your query and cause a SQL parse error. If you need full variable support on one of those drivers for either preflight or datapoint queries, contact your Transcend account team.

### Sombra version requirements [#sombra-version-requirements]

Template variables are evaluated by your Sombra gateway, which sees both preflight enricher queries and per-datapoint DSR queries on the same `proxyDatabase` route. The minimum Sombra versions below apply to both flows — there is no separate version for enricher vs. datapoint use:

<table>
  <thead>
    <tr>
      <th>
        Variable family
      </th>

      <th>
        Minimum Sombra version
      </th>
    </tr>
  </thead>

  <tbody>
    <tr>
      <td>
        `?` and `{{requestId}}` (legacy)
      </td>

      <td>
        Available in all current Sombra versions
      </td>
    </tr>

    <tr>
      <td>
        `{{customField_*}}`, `{{consentPreference_*}}`, all request context variables (Databricks Lakehouse)
      </td>

      <td>
        `7.469.18`
      </td>
    </tr>

    <tr>
      <td>
        `{{customField_*}}`, `{{consentPreference_*}}`, all request context variables (Snowflake / Postgres / Redshift / MySQL / MariaDB / DB2 / Oracle / Azure Synapse)
      </td>

      <td>
        `7.510.0`
      </td>
    </tr>

    <tr>
      <td>
        `{{customField_*}}`, `{{consentPreference_*}}`, all request context variables (Google Cloud Spanner)
      </td>

      <td>
        `7.479.0`
      </td>
    </tr>
  </tbody>
</table>

### Common pitfalls [#common-pitfalls]

<table>
  <thead>
    <tr>
      <th>
        Pitfall
      </th>

      <th>
        What happens
      </th>

      <th>
        How to fix
      </th>
    </tr>
  </thead>

  <tbody>
    <tr>
      <td>
        Referencing a variable that doesn't exist
      </td>

      <td>
        Sombra returns a `Found named params with no provided values` error before the query runs
      </td>

      <td>
        Check spelling and casing — `customField_*` keys are [camelCase](https://appdevtools.com/case-converter); consent preferences use the preference slug exactly
      </td>
    </tr>

    <tr>
      <td>
        Wrapping a variable in quotes (`'{{requestId}}'`)
      </td>

      <td>
        Generally unnecessary — variables are bound as parameters, so quoting is handled by the driver
      </td>

      <td>
        Use `{{requestId}}` (no quotes); Transcend will quote string values for you
      </td>
    </tr>

    <tr>
      <td>
        Relying on `customField_*` for a custom field that isn't set on the request
      </td>

      <td>
        The variable resolves to an empty value and your query may behave unexpectedly
      </td>

      <td>
        Mark the custom field as required on the request form, or add a `COALESCE` / `CASE WHEN` to your SQL
      </td>
    </tr>

    <tr>
      <td>
        Using `{{var}}` syntax on Microsoft SQL Server or Google BigQuery for non-identifier variables
      </td>

      <td>
        The placeholder is left in the SQL and the driver returns a parse error
      </td>

      <td>
        Stick to `?` / `{{dsr_identifier_value}}` / `{{requestId}}` on those drivers until full support lands
      </td>
    </tr>

    <tr>
      <td>
        Erasure never reaches the database (no `DELETE` / `UPDATE` / `MERGE` in logs)
      </td>

      <td>
        Delete runs the datapoint ACCESS SQL first. Quoted placeholders like '\{\{email}}' return zero rows, so erasure can complete without running the mutation — especially when skip-secondary-if-no-files is enabled.
      </td>

      <td>
        Fix ACCESS first. Use `@email` or unquoted `{{email}}`. Confirm ACCESS returned files for this Delete request before debugging erasure SQL.
      </td>
    </tr>
  </tbody>
</table>

### Generating SQL Statements Based on Property Settings [#generating-sql-statements-based-on-property-settings]

The property settings column in the `Manage Datapoints` interface will allow you to view the columns of each datapoint table and assign each column a category of personal data. This process of assigning categories to columns will enable Transcend to generate access and erasure SQL statements for you automatically.

In this view the `Access Request Visibility` toggle determines whether a `SELECT *` statement or `SELECT <field>` statement.

![Access Toggle and SQL Statement](/images/screenshot/2023-06-01-prepared-access-statement.png)

The `Erasure Properties to Redact` toggle determines whether the erasure request is a `DELETE` or `UPDATE` statement.

![Erasure Toggle and SQL Statement](/images/screenshot/2023-06-01-prepared-deletion-statement.png)

### Generating SQL Statements based on Structured Discovery [#generating-sql-statements-based-on-structured-discovery]

Categories can also be set in the `Structured Discovery / Datapoints` interface by editing the `Data Categories` column. If a datapoint is declared as `Personal Identifier User ID`, `Contact Email`, or `Contact Phone`, then the same autogeneration of SQL statements can be applied for various DSR types.

### Generating SQL Statements Manually [#generating-sql-statements-manually]

You also have the option of manually setting SQL queries for each table in the `Prepared Statement` column text fields. This can be used to facilitate more complex queries e.g. joining two tables for an enrichment.

### Managing SQL Statements from Code [#managing-sql-statements-from-code]

SQL statements for facillitating DSR Automation can also be configured in code using [Transcend's CLI](https://github.com/transcend-io/tools/tree/main/packages/cli).

The workflow involves:

1\. Using the `transcend inventory pull` command to pull existing SQL statements down from your Transcend instance into a `trancend.yml` file.

```shellscript
    yarn transcend inventory pull --auth=$TRANSCEND_API_KEY --dataSiloIds=965b0eb2-b87d-464c-a849-571e31384001 --resources=dataSilos
```

1. Edit the SQL commands in `transcend.yml`. [See this example.](https://github.com/transcend-io/tools/blob/main/packages/cli/examples/sql.yml)
2. Commit and push your changes using the `transcend inventory push` command

To use this workflow, the required scopes for your Transcend API Key are:

- `Manage Data Inventory`
- `Manage Data Subject Request Settings`
- `Manage API Keys`

Once you have a workflow for updating and publishing SQL statements using the CLI, you may want to automate this process using CI. [Here is an example for how to accomplish this using a GitHub Action.](https://github.com/transcend-io/tools/tree/main/packages/cli#ci-integration)

## Testing DSR Automation against your Database [#testing-dsr-automation-against-your-database]

Instructions for manually submitting DSRs can be found in this [documentation](../../dsr-automation/processing-requests/submission.mdx).

After a test request has been made, select your database in the `Infrastructure / Integrations` view and view `Processed Requests` to see a log of requests that interacted with your database. Selecting these will allow you to drill down on details pertaining to the the request. Requests can also be restarted from this view.

![View processed requests](/images/screenshot/2023-06-01-processed-requests.png)