The Trade Desk connector for Snowflake: Optouts and deletions for merchants

If you're a merchant and want to update user optout status, or delete some user information, you must first pair with the advertiser that's associated with your merchant ID.

The merchant user optout feature allows you to process optouts and deletions for both first-party and third-party data in one action.

IMPORTANT: When you opt out or delete user IDs, The Trade Desk performs that action for all segments associated with your data. Always verify that the IDs in your requests are correct, because the action is permanent and the changes cannot be restored.
When sending subsequent data to The Trade Desk, make sure you do not include user IDs that have been opted out or deleted.

To use this feature, you must pair with an advertiser that is associated with your merchant ID. There is a 1:1 mapping between the merchant ID and the associated advertiser ID.

Data format

The data you upload to The Trade Desk connector native app must be in a format consumable by an endpoint from The Trade Desk.

Make sure your optout or deletion data is in the correct format for the POST /data/deletion-optout/merchant endpoint.

For specific data requirements, see SQL objects.

What you need to know

Here's what you need to know about optout and deletion of segment data:

There is no limit to how many optouts or deletions you can upload. The native app takes care of splitting and batching if needed.
For optout, we opt out the ID from your data segments by setting its TTL value to 0, thereby limiting its targeting in your campaigns.
The REQUEST_TYPE column allows you to change the setting between optout or deletion.
If you delete an ID, the deletion works on the current state of The Trade Desk platform, but it does not restrict the use of that identifier in the future. Do not include an ID that you've deleted in a future segment or conversion event, unless you want to resume using the ID.
ID deletion closely matches the implementation behavior of the underlying endpoint from The Trade Desk. For details, refer to the documentation for the endpoint. See Data format.

Snowflake marketplace listing

The following listing from The Trade Desk for processing audience data in Snowflake is available in the Snowflake marketplace:

TTD Connector: Audience & Conversion Data

Complete setup steps

Before processing the data, you must complete all the steps covered in The Trade Desk connector for Snowflake: Get started, including the following setup tasks:

Grant permissions.
Complete pairing steps.
Specify the main partner you want to work with.
For the specified partner, select one or more advertisers you want to work with.

For details, see Configure the native app.

Process data

Processing of optouts or deletions for merchant data starts in one of these ways:

When you call the stored procedure APP.SP_START_USER_OPTOUT_TASK_MERCHANT(), which detects changes in the data table and starts the Snowflake triggered task.
When you initiate processing via the Snowflake Native App UI or by running the APP.SP_PROCESS_USER_OPTOUTS_MERCHANT(BATCH_SIZE INT, STOP_AFTER_SECONDS INT) stored procedure.

TIP: When you're processing data for the first time, we recommend using a small amount of data, such as 100 records. On a smaller dataset it's easier to make sure that processing goes smoothly, and to troubleshoot if needed. When you've confirmed that everything is working smoothly for this small amount, you can process large volumes of data.

To process merchant deletions or optouts in the Snowflake Native App UI, follow these steps:

Copy data from your own database into the input table, transforming the data as needed to meet the formatting requirements. For details, see SQL objects.
On the User Optouts Merchant page, click Refresh to refresh the UI page. Ensure your input data is displayed.
Click Process Immediately. This triggers execution.

Verify data processing

There are several ways you can verify that your data was processed correctly:

In the native app, refresh the page and check for success and errors.
In Snowflake, check the APP.VW_USER_OPTOUT_RESULT_MERCHANT view. If there are errors, see Error messages.
Confirm with your Technical Account Manager, who verifies that the ID deletions and optouts were successfully ingested and validates the data within The Trade Desk platform.

For important usage notes, see Stored procedures and SQL objects.

Reference information

The following sections provide reference information for using this feature, such as supported ID types, tables and views, stored procedures, and example commands.

User optouts merchant page

The User Optouts Merchant page shows dimensions of your optout and deletion information, along with some simple controls to manage execution. The table schema was created during configuration (see Configure the native app).

The following information is on this page:

Merchant User Optouts Summary: This table summarizes count and percentage for the following statuses: Unprocessed, Successful, Failed. It also shows totals.
Most Recent Failed IDs (by ERROR_CODE): Includes error codes and a count for each.
Latest 10 Successful User Optout Records: Includes RECORD_ID and other values for each record.
Latest 10 Unprocessed User Optout Records: Includes MERCHANT_ID and other values for each record.

The following table lists the buttons on the User Optouts Merchant page and what they do.

Button	Function
Process immediately	Checks the input table for data that hasn't been processed yet, and immediately submits it to the authorized URL for processing, per the External Integration Binding permission (see Native app permissions).
Start task	Starts a task that checks the input table for changes and submits new changes regularly to the authorized URL for processing, per the External Integration Binding permission (see Native app permissions).
Stop task (Only present when a task is running)	Stops the current process from running. You could use this, for example, if you're getting errors and you want to stop and try to fix the problem.
Refresh	Refreshes the page in The Trade Desk connector native app. This button refreshes only the UI; it does not affect the processing of data. It refreshes values displayed, such as the number of ID deletions and optouts processed.

Supported ID types

For ID optouts and deletions for merchants, the uploaded data must be in a format consumable by the POST /data/deletion-optout/merchant endpoint. The Trade Desk connector native app supports a subset of the ID types supported by this endpoint, as well as additional ID types to support the processing of UID2s.

The following table lists all supported ID types.

IMPORTANT: Only the ID types listed in this table are supported. The following ID types require UID2 signed paperwork and the associated additional permissions: UID2, UID2Token, email, phone, email_hash, and phone_hash.

ID Type	Data Type	Description
`TDID`	GUID	The Trade Desk 36-character GUID (including dashes) for this user.
`DAID`	GUID	The raw device ID for this user, sent in 36-character GUID format (including dashes). Use the iOS ID for advertisers (IDFA) or Android advertising ID (AAID).
`UID2`	String	The raw UID2 value, also known as UID2. This value is case-sensitive. Raw UID2s are generated and managed using UID2 APIs. See also Unified IDs.
`UID2Token`	String	The encrypted UID2 token, also known as an advertising token. This token is case-sensitive. Tokens are generated and managed using UID2 APIs. For details about UID2 APIs, see UID2 Endpoints: Summary. For details about UID2 tokens, see Raw IDs vs. tokens.
`RampID`	String	The 49-character or 70-character RampID (previously known as IdentityLink). IMPORTANT: This must be a RampID from LiveRamp that is mapped specifically for The Trade Desk. For details about mapping a RampID, see LiveRamp documentation.
`ID5`	String	The ID5 for the user. Data providers holding these IDs should engage with ID5 directly to obtain the necessary decryption keys. For details about ID5, see the ID5 site. IMPORTANT: The ID5 ID is supported only in Europe and must be in decrypted form.
`netID`	String	The user's netID as a 70-character Base64-encoded string. For details about netID, see the netID Developer Zone. IMPORTANT: This ID is supported only in Europe.
`FirstId`	String	The user's First-id, a first-party cookie typically set by publishers in France. For details about First-id, see the First-id site. IMPORTANT: This ID is supported only in Europe.
`email`	String	A raw email address for conversion to a UID2. The native app normalizes and hashes the email address before sending the data to The Trade Desk. It never sends raw email addresses outside your Snowflake account.
`phone`	String	A raw phone number for conversion to a UID2. The native app normalizes and hashes the phone number before sending the data to The Trade Desk. It never sends raw phone numbers outside your Snowflake account.
`email_hash`	String	A hashed email address for conversion to a UID2. For requirements, see Email address hashing and encoding.
`phone_hash`	String	A hashed phone number for conversion to a UID2. For requirements, see Phone number hashing and encoding.

SQL objects

The Trade Desk connector native app creates a data table, INPUT.USER_OPTOUT_MERCHANT, for the user ID deletion and optout data.

The following table lists the column names, in sequence.

Column Name	SQL Data Type	Notes
`RECORD_ID`	INT	A unique ID assigned to the record. Each time a new row is added, the value increases.
`ID_TYPE`	VARCHAR	Supports any one of the user ID types listed in Supported ID types.
`ID_VALUE`	VARCHAR	Supports the value for any one of the user ID types listed in Supported ID types: for example, `ID_TYPE` of `email` and `ID_VALUE` of `user123@example.com`. For an example with multiple IDs, see Insert merchant user optout records.
`MERCHANT_ID`	VARCHAR	Your merchant ID, assigned to you by The Trade Desk.
`REQUEST_TYPE`	INT	The type of request. For details, see Request object properties in the `POST /data/deletion-optout/thirdparty` endpoint documentation.

Tables and views

For merchant optouts, The Trade Desk connector native app exposes the database objects shown in the following table.

Table/View	Name	Notes
Table	`INPUT.USER_OPTOUT_MERCHANT`	Populate this table with merchant user optouts that you want to submit for processing.
View	`APP.VW_USER_OPTOUT_RESULT_MERCHANT`	Shows the processing result for each record in the `INPUT.USER_OPTOUT_MERCHANT` table.

When processing merchant user optout records, the Native App performs these steps:

Performs authorization and validation checks.
Uploads the records from the INPUT.USER_OPTOUT_MERCHANT table to The Trade Desk.
Adds a result that appears in the APP.VW_USER_OPTOUT_RESULT_MERCHANT view with the same RECORD_ID as the INPUT.USER_OPTOUT_MERCHANT record.

Results view

The following table shows sample results in the APP.VW_USER_OPTOUT_RESULT_MERCHANT view.

NOTE: The table includes the following columns: RECORD_ID, IS_SUCCESS, ERROR_CODE, ERROR_MESSAGE, PROCESSED_ON_UTC. Because of the width, depending on your screen resolution, the table might wrap.

+-----------+------------+------------+---------------+-------------------------+
| RECORD_ID | IS_SUCCESS | ERROR_CODE | ERROR_MESSAGE | PROCESSED_ON_UTC        | 
+-----------+------------+------------+---------------+-------------------------+
| 54817     | TRUE       |            |               | 2025-11-03 19:38:40.299 |
| 40438     | TRUE       |            |               | 2025-11-03 19:38:40.299 |
| 38149     | TRUE       |            |               | 2025-11-03 19:38:40.299 |
| 47101     | TRUE       |            |               | 2025-11-03 19:38:40.299 |
| 21783     | TRUE       |            |               | 2025-11-03 19:38:40.299 |
| 14502     | TRUE       |            |               | 2025-11-03 19:38:40.299 |
| 63876     | TRUE       |            |               | 2025-11-03 19:38:40.299 |
| 86838     | TRUE       |            |               | 2025-11-03 19:38:40.299 |
| 29681     | TRUE       |            |               | 2025-11-03 19:38:40.299 |
| 20467     | TRUE       |            |               | 2025-11-03 19:38:40.299 |
| 42601     | TRUE       |            |               | 2025-11-03 19:38:40.299 |
| 68455     | TRUE       |            |               | 2025-11-03 19:38:40.299 |
| 82517     | TRUE       |            |               | 2025-11-03 19:38:40.299 |
| 73125     | TRUE       |            |               | 2025-11-03 19:38:40.299 |
| 83127     | TRUE       |            |               | 2025-11-03 19:38:40.299 |
| 84326     | TRUE       |            |               | 2025-11-03 19:38:40.299 |
| 83132     | TRUE       |            |               | 2025-11-03 19:38:40.299 |
| 69606     | TRUE       |            |               | 2025-11-03 19:38:40.299 |
| 48168     | TRUE       |            |               | 2025-11-03 19:38:40.299 |
| 59515     | TRUE       |            |               | 2025-11-03 19:38:40.299 |
| 83694     | TRUE       |            |               | 2025-11-03 19:38:40.299 |
| 91868     | TRUE       |            |               | 2025-11-03 19:38:40.299 |
| 95025     | TRUE       |            |               | 2025-11-03 19:38:40.299 |
| 64237     | TRUE       |            |               | 2025-11-03 19:38:40.299 |
+-----------+------------+------------+---------------+-------------------------+

Stored procedures

For processing optouts in merchant data, the native app includes the stored procedures shown in the following table. You can also trigger most of these through the UI.

Procedure	UI Equivalent	Description
`APP.SP_PROCESS_USER_OPTOUTS_MERCHANT(BATCH_SIZE INT, STOP_AFTER_SECONDS INT)`	Select Process Immediately.	Processes `BATCH_SIZE` records in the `INPUT.USER_OPTOUT_MERCHANT` table immediately. After processing of each `BATCH_SIZE` batch of records is complete, the procedure checks whether the `STOP_AFTER_SECONDS` value has elapsed, and does one of the following: If `STOP_AFTER_SECONDS` has not elapsed, the procedure loops and processes another `BATCH_SIZE`. If `STOP_AFTER_SECONDS` seconds has elapsed, the procedure completes. There might be unprocessed rows at that point: if so, `APP.SP_PROCESS_USER_OPTOUTS_MERCHANT` should be called again until all rows are processed.
`APP.SP_START_USER_OPTOUT_TASK_MERCHANT()`	Select Start Task.	Starts a task that runs `APP.SP_PROCESS_USER_OPTOUTS_MERCHANT` whenever there are changes to the input table. The task is executed by the warehouse specified in the app configuration, Permissions step.
`APP.SP_STOP_USER_OPTOUT_TASK_MERCHANT()`	Select Stop Task.	Stops the task that runs `APP.SP_PROCESS_USER_OPTOUTS_MERCHANT`.
`APP.SP_GET_USER_OPTOUT_TASK_STATE_MERCHANT()`	View Task State.	Determines whether the user optout task is running.
`APP.SP_CLEAN_USER_OPTOUT_RESULTS_MERCHANT()`	None	Deletes all records from the `APP.VW_USER_OPTOUT_RESULT_MERCHANT` view where the `RECORD_ID` value no longer exists in the `INPUT.USER_OPTOUT_MERCHANT` table.
`APP.SP_FORCE_RESET_USER_OPTOUT_MERCHANT_PIPELINE()`	None	Forces a reset, clearing the ingestion pipeline state. For details, see Using the force reset procedure. IMPORTANT: Use only as a last resort.

Using the force reset procedure

The APP.SP_FORCE_RESET_USER_OPTOUT_MERCHANT_PIPELINE() stored procedure is a last-resort fix that clears out the ingestion pipeline state.

IMPORTANT: Use this only in scenarios where the pipeline is stuck and cannot make progress.

Here are some sample scenarios:

Invalid or malformed data was inserted into the input table, causing the ingestion pipeline to fail repeatedly or throw exceptions.
No new records are being ingested, and no new entries are appearing in the results table, even though data is incoming.

If you absolutely have to use this stored procedure, follow these steps.

IMPORTANT: Any rows that were present before executing this stored procedure, in the input table or the pipeline, will not be picked up for processing afterwards. After running the stored procedure, only newly inserted rows are picked up for processing.

If there's any data in the input table that hasn't yet been processed, or is invalid or problematic, remove it.
Run the APP.SP_FORCE_RESET_USER_OPTOUT_MERCHANT_PIPELINE() stored procedure.
Insert new data into the INPUT.USER_OPTOUT_MERCHANT table for processing, or re-send data if needed, after verifying that it is not invalid or malformed.

Error messages

If you're a merchant processing user ID deletion and optout data, you might see one or more of the error messages shown in the following table.

Error Code	Error Message
`EmailOrPhoneNotAllowed`	The selected partner is not allowed to push email and phone data
`UID2NotAllowed`	The selected partner is not allowed to push UID2 data
`EUIDNotAllowed`	The selected partner is not allowed to push EUID data
`Uid2Error`	Invalid identifier
`InvalidMerchantId`	Unable to map the provided MerchantId `MERCHANT_ID` to a MerchantAdvertiserId
`MerchantAdvertiserNotAllowed`	You do not have permission to the merchant advertiser id associated with the merchant: `MERCHANT_ID`
`InvalidUserId`	One of the following: Invalid TDID Invalid DAID Invalid RampID Invalid NetID Invalid FirstID Invalid ID5 Invalid UID2 Failed to decrypt UID2 Token. Invalid email address: bad email Action: Resolve the issue with the email and try again. For normalization details, see Email address normalization. Phone number is not normalized: bad phone Action: Resolve the issue with the phone number and try again. For normalization details, see Phone number normalization.
`HttpError`	HTTP error message

Example SQL commands

The following sample SQL commands show how to complete specific activities using this Native App.

Insert merchant user optout records

The following sample shows the SQL command syntax for inserting merchant user ID deletion and optout records.

INSERT INTO INPUT.USER_OPTOUT_MERCHANT (ID_TYPE, ID_VALUE, MERCHANT_ID, REQUEST_TYPE) VALUES
    ('TDID', '47d4fc00-9eab-4880-93b2-8bf473ecf729', MERCHANT_ID_PLACEHOLDER, 0),
    ('UID2', 'tMmiiTI7IaAcPpQPFQ65uMVCWH8av9jw4cwf/F5HVRQ=', MERCHANT_ID_PLACEHOLDER, 1),
    ('DAID', '47d4fc00-9eab-4880-93b2-8bf473ecf729', MERCHANT_ID_PLACEHOLDER, 0),
    ('RampID', 'Xi1005p_iYcKP7ZlvFwwK9EwR8GKl_VJqIWUhEaAFmHLAjNOQ9b6OQzSkA43XiVFcTYQ9X', MERCHANT_ID_PLACEHOLDER, 1),
    ('ID5', 'ID5-c62drGF0EC6wsCZVFDbTbZwi33eB0uZTIC8FxJpzsQ', MERCHANT_ID_PLACEHOLDER, 0),
    ('netID', 'vp3hsuQk0I8gUEh-Y4gm3R6e8Wm7z8vkWrp_kjlXtKgbIYraTgAmKfm5kbo660gMo2lR4w', MERCHANT_ID_PLACEHOLDER, 1),
    ('FirstId', '8934d279bba4c7d652a02f624dc334e3', MERCHANT_ID_PLACEHOLDER, 0),
    ('email', 'user4554@example.com', MERCHANT_ID_PLACEHOLDER, 1),
    ('email_hash', 'tMmiiTI7IaAcPpQPFQ65uMVCWH8av9jw4cwf/F5HVRQ=', MERCHANT_ID_PLACEHOLDER, 0),
    ('phone', '+1234567890', MERCHANT_ID_PLACEHOLDER, 1),
    ('phone_hash', 'EObwtHBUqDNZR33LNSMdtt5cafsYFuGmuY4ZLenlue4=', MERCHANT_ID_PLACEHOLDER, 0);

Replace the placeholder MERCHANT_ID_PLACEHOLDER with your merchant ID.

The REQUEST_TYPE values are:

0: ID optout (default value). Opts out the ID from your data segments by setting its TTL value to 0, limiting its targeting in your campaigns.
1: ID deletion. Deletes the ID from your data segments.

Process unprocessed merchant user optout records

The following sample shows the SQL command syntax for processing unprocessed merchant user ID deletion and optout records. This example processes 1000 rows at a time in a loop, and stops looping if 180 seconds or greater have elapsed.

CALL APP.SP_PROCESS_USER_OPTOUTS_MERCHANT(1000, 180);

Query the results: Merchant user optouts

In the results, RECORD_ID in the APP.VW_USER_OPTOUT_RESULT_MERCHANT view corresponds to RECORD_ID in INPUT.USER_OPTOUT_MERCHANT.

If IS_SUCCESS is TRUE, upload of the user optout request was successful.

If IS_SUCCESS is FALSE, examine the ERROR_CODE and ERROR_MESSAGE fields to determine the failure reason.

The Native App UI shows a summary of errors, including a count of ERROR_CODE values and a recent example of each error.

SELECT * FROM APP.VW_USER_OPTOUT_RESULT_MERCHANT;

Delete processed merchant user optout records

IMPORTANT: Review any errors, and make sure that all the records have been processed successfully before deleting them.

Deleting processed user optout records regularly helps improve performance and reduce storage costs.

DELETE FROM INPUT.USER_OPTOUT_MERCHANT o WHERE EXISTS (SELECT 1 FROM APP.VW_USER_OPTOUT_RESULT_MERCHANT r WHERE r.RECORD_ID = o.RECORD_ID);

Delete results for processed merchant user optout records

When you delete processed merchant user optout records from the INPUT.USER_OPTOUT_MERCHANT table, use the following to delete results corresponding to those records.

CALL APP.SP_CLEAN_USER_OPTOUT_RESULTS_MERCHANT();

FAQs

The following is a list of commonly asked questions about the Snowflake Native App and processing optouts or deletions.

How should I handle HTTP 429 responses?

A 429 response error code indicates a temporary overload to the service. If you see this status code, wait for a short period before retrying. If any records failed to process, insert them into the input table again, and then retry processing the data.

Why can't I see all available procedures?

If you find that you cannot complete a key basic procedure, it's likely that you haven't been assigned the application role necessary to complete the task or procedure.

For example, if you don't have the APP_ADMIN application role, you might see the following error on trying to run the SP_START_SEGMENT_TASK task:

SQL compilation error: Unknown user-defined function APP.SP_START_SEGMENT_TASK.

If this happens, ask someone with the app owner's role (the role that installs the app) to assign the necessary application role to you.

For details, see Application roles.

What happens when I opt out or delete a user ID?

When you opt out or delete a user ID, The Trade Desk permanently removes that ID from all segments that contain your first‑party data. This action cannot be undone.

Can deleted or opted‑out user IDs be restored?

No. Your action of deleting or opting out user IDs cannot be reversed.

What happens if I accidentally include a deleted or opted‑out user ID in a future upload?

You should never submit deleted or opted‑out IDs in future data uploads or API requests. Re‑submitting might lead to data quality issues or compliance inconsistencies.

Does opting out behave the same as deleting an ID?

No. Here's the difference between opt-out and delete:

When a user ID is opted out, we opt out the ID from your data segments by setting its TTL value to 0. The data still exists in your dataset but is not used in your campaigns.
When a user ID is deleted, the data is permanently removed.

When I delete data, are REDS feeds affected?

Yes. When you delete an ID, the following things occur with regard to REDS feeds:

The ID is removed from the REDS feeds from the current date forward for a period of 30 days, and will not reappear in the REDS feeds unless you upload the ID again.
The ID is also removed from REDS data for approximately the previous three months.