Snowflake Data Clean Rooms: Identity and data provider connectors¶

Prerequisites¶

1. Before configuring the Acxiom connector, you must contact Acxiom for help installing their native app.

2. Before a clean room administrator configures the connector, the owner of the Acxiom native app must:

   1. Sign in to Snowsight.

   2. Assume the role that has ownership rights to the Acxiom native app. For example, if the acxiom_admin_role role is the owner of the Acxiom native app, execute:

      USE ROLE acxiom_admin_role;
      

      Copy

   3. Execute the following command to grant Snowflake Data Clean Rooms access to the Acxiom realid_app_role application role:

      GRANT APPLICATION ROLE <acxiom_app_database>.realid_app_role
        TO ROLE samooha_app_role;
      

      Copy

Configure the Acxiom Real ID connector¶

To configure the Acxiom Real ID connector:

1. Sign in to your clean room environment in the web app.

2. In the left navigation, select Connectors.

3. Select the Identity & Data Providers tab.

4. Expand Acxiom - Real ID.

5. In the Application Database field, enter the name of the application database that was installed by the Acxiom native app.

6. In the Warehouse drop-down list, select the warehouse size. We recommend DCR_WH_XLarge, but you can read Acxiom’s guidance on warehouse size and performance. For more information about creating a warehouse for use with Snowflake Data Clean Rooms, see Add warehouse options.

7. Select Save.

Prerequisites¶

1. You must have installed the Acxiom Real ID native app, as described previously.

2. You must install the Acxiom Real ID Transcoding application.

3. Contact your collaborators to get the client ID and client secret generated for them when they installed the Acxiom Real ID Transcoding native app.

4. Before a clean room administrator configures the connector, the owner of the Acxiom native app must:

   1. Sign in to Snowsight.

   2. Assume the role that has ownership rights to the Acxiom native app. For example, if the acxiom_admin_role role is the owner of the Acxiom native app, execute:

      USE ROLE acxiom_admin_role;
      

      Copy

   3. Execute the following command to grant Snowflake Data Clean Rooms access to the Acxiom realid_app_role application role:

      GRANT APPLICATION ROLE <acxiom_app_database>.realid_app_role TO ROLE samooha_app_role;
      

      Copy

Configure the Acxiom Real ID Transcoding connector¶

To configure the Acxiom Real ID Transcoding connector:

1. Sign in to your clean room environment in the web app.

2. In the left navigation, select Connectors.

3. Select the Identity & Data Providers tab.

4. Expand Acxiom Real ID Transcoding.

5. In the Application Database field, enter the name of the application database that was installed by the Acxiom native app.

6. In the Client ID field, enter the client ID provided by Acxiom when you installed the native app.

7. In the Client Secret field, enter the client secret provided by Acxiom when you installed the native app.

8. In the Warehouse drop-down list, select the warehouse size. We recommend DCR_WH_Medium, but you can read Acxiom’s guidance on warehouse size and performance. For more information about creating a warehouse for use with Snowflake Data Clean Rooms, see Add warehouse options.

9. In the Acxiom Collaborator section, select one or more collaborators along with the client ID and client secret that was generated for them when they installed the Acxiom Real ID Transcoding native app. If your collaborator does not appear in the list, you must add them to the clean room environment.

10. Select Save.

Step 1: Install the Merkury Identity Connector native app¶

1. Install the Merkury Identity Connector native app. You must contact Merkury for help installing the native application.

2. Grant privileges to the SAMOOHA_APP_ROLE role:

   1. Sign in to Snowsight.

   2. Assume the role that has ownership rights to the Merkury native app. For example, if the ACCOUNTADMIN role is the owner of the Merkury native app, execute USE ROLE ACCOUNTADMIN;

   3. Execute the following command to grant Snowflake Data Clean Rooms access to the Merkury DCR_DB_ROLE application role:

   GRANT APPLICATION ROLE <merkury_app_database>.DCR_DB_ROLE TO ROLE samooha_app_role;
   

   Copy

Step 2: Configure the Merkury Identity Connector native app¶

1. Sign in to your clean room in the web app.

2. In the left navigation, select Connectors.

3. Select the Identity & Data Providers tab.

4. Expand Merkury Identity Connector.

5. In the Application database field, enter the name of the application database that was installed by the Merkury Identity native app.

6. Authenticate yourself.

Prerequisites¶

The following must be completed before configuring the TransUnion TruAudience Identity connector in the clean room environment:

Step 1: Install the TransUnion native app

Use the Snowflake Marketplace to install the native app for TransUnion TruAudience Identity.

Step 2: Grant privileges to the clean rooms native app

After the TransUnion native app has been installed, but before a clean room administrator configures the connector, the owner of the TransUnion native app must follow these steps:

1. Sign in to Snowsight.

2. Assume a role that has ownership rights to the TransUnion native app. For example, if the tu_admin_role role is the owner of the TransUnion native app, execute:

   USE ROLE tu_admin_role;
   

   Copy

3. Grant Snowflake Data Clean Rooms access to the TransUnion application role and the TransUnion table installed in step 1:

   GRANT APPLICATION ROLE <transunion_app_database>.tru_app_public
      TO ROLE SAMOOHA_APP_ROLE;
   
   GRANT SELECT, INSERT
      ON TABLE SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.PUBLIC.SAMOOHA_INTERNAL_TRANSUNION_ID_GENERATION_RECORDS
      TO ROLE SAMOOHA_APP_ROLE;
   

   Copy

Step 3: Ensure the required stored procedure exists

The TransUnion connector relies on a stored procedure, which might not exist in some clean room environments. To ensure that the stored procedure exists, execute the following command as a user with the ACCOUNTADMIN role:

USE ROLE ACCOUNTADMIN;

DESCRIBE PROCEDURE SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.PUBLIC.GRANT_EXTERNAL_APP_ROLE;

Copy

If you receive an error that the procedure does not exist, you must use the following commands to define the procedure:

USE ROLE ACCOUNTADMIN;

CREATE OR REPLACE PROCEDURE SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.PUBLIC.GRANT_EXTERNAL_APP_ROLE(APP_ROLE string, APPLICATION string)
   RETURNS string
   LANGUAGE SQL
   EXECUTE AS OWNER
   AS
   $$
   GRANT APPLICATION ROLE IDENTIFIER(:APP_ROLE) TO APPLICATION IDENTIFIER(:APPLICATION);
   $$;

GRANT USAGE ON PROCEDURE SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.PUBLIC.GRANT_EXTERNAL_APP_ROLE(string, string) TO ROLE SAMOOHA_APP_ROLE;

Copy

Configuring the connector¶

To configure the TransUnion TruAudience Identity connector:

1. Sign in to your clean room environment in the web app.

2. In the left navigation, select Connectors.

3. Select the Identity & Data Providers tab.

4. Expand TransUnion - TruAudience Identity.

5. In the Application Database field, enter the name of the application database that was installed by the TransUnion native app

6. In the Collaboration Key field, enter the collaboration key received from TransUnion for authorization.

7. Select a warehouse that is used when clean room users integrate a table with TransUnion TruAudience Identity.

   If you want to complete the process of matching identities within an hour, use the following guidelines to help select the right warehouse size:

   Number of rows	Warehouse size
   < 100k	Large
   1 million	XLarge
   5-10 million with addresses	3X-Large
   > 10 million	3X-Large

8. Select Authenticate.

Cache for TransUnion TruAudience Identity¶

Snowflake maintains a cache that maps TransUnion collaborator IDs to values in the source table that uniquely identify records. For example, the cache might map each collaborator ID to a value in the user_id column of the source table. The cache is stored in the SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.PUBLIC.SAMOOHA_INTERNAL_TRANSUNION_ID_GENERATION_RECORDS table.

This table contains the following columns:

Column	Data type	Description
inputid	VARCHAR	Value from the column selected as the Unique Record Column during the integration.
collaborationid	VARCHAR	TransUnion collaboration ID generated based on the input ID and other integration parameters.
lastprocessed	TIMESTAMP_NTZ	Timestamp when TransUnion generated the collaboration ID.

You can perform the following actions on a cache:

Delete the cache

TRUNCATE SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.PUBLIC.SAMOOHA_INTERNAL_TRANSUNION_ID_GENERATION_RECORDS;

Copy

Delete specific records from the cache

You can delete specific records from the cache by specifying them as a comma-separated list of single-quoted values. For example, to delete the records with input IDs of 123456 and abcedf, execute:

DELETE FROM SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.PUBLIC.SAMOOHA_INTERNAL_TRANSUNION_ID_GENERATION_RECORDS
  WHERE inputid IN ('123456', 'abcedf');

Copy

Delete multiple records based on input IDs in a separate dataset

You can delete multiple records from the cache when the input IDs are present in a column of another table. For example, if the input IDs to be deleted are listed in the user_id column of the my_db.my_schema.ref_table table, execute:

DELETE FROM SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.PUBLIC.SAMOOHA_INTERNAL_TRANSUNION_ID_GENERATION_RECORDS
  WHERE INPUTID IN (
    SELECT user_id as INPUTID
    FROM my_db.my_schema.ref_table
  );

Copy

Add all records from a batch

You can add all of the records from a batch that is present in TransUnion’s view to the cache.

INSERT INTO SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.PUBLIC.SAMOOHA_INTERNAL_TRANSUNION_ID_GENERATION_RECORDS (
  INPUTID,
  COLLABORATIONID,
  LASTPROCESSED
SELECT
  INPUTID,
  COLLABORATIONID,
  LASTPROCESSED
FROM <TRANSUNION_APPLICATION_DATABASE>.SHARE_SCHEMA.REF_MATCHING_OUTPUT_VIEW
WHERE BATCHID = '<BATCH_ID>';

Copy

Merge all records from a batch

You can merge all of the records from a batch that is present in TransUnion’s view to the cache by overwriting existing input ID records with the corresponding new collaboration IDs and new last-processed timestamps.

MERGE INTO SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.PUBLIC.SAMOOHA_INTERNAL_TRANSUNION_ID_GENERATION_RECORDS CT
USING <TRANSUNION_APPLICATION_DATABASE>.SHARE_SCHEMA.REF_MATCHING_OUTPUT_VIEW OT
  ON
    CT.INPUTID = OT.INPUTID
    AND OT.BATCHID = '<BATCH_ID>'
WHEN MATCHED THEN
  UPDATE SET
    CT.COLLABORATIONID = OT.COLLABORATIONID,
    CT.LASTPROCESSED = OT.LASTPROCESSED
WHEN NOT MATCHED THEN
  INSERT (
    INPUTID,
    COLLABORATIONID,
    LASTPROCESSED
  ) VALUES (
      OT.INPUTID,
      OT.COLLABORATIONID,
      OT.LASTPROCESSED
  );

Copy

Add collaborator IDs for input ID records

You can add collaborator IDs for input ID records present as a column in a dataset and also present in a specific batch.

INSERT INTO SAMOOHA_BY_SNOWFLAKE_LOCAL_DB.PUBLIC.SAMOOHA_INTERNAL_TRANSUNION_ID_GENERATION_RECORDS (
  INPUTID,
  COLLABORATIONID,
  LASTPROCESSED
)
  SELECT
    INPUTID,
    COLLABORATIONID,
    LASTPROCESSED
  FROM <TRANSUNION_APPLICATION_DATABASE>.SHARE_SCHEMA.REF_MATCHING_OUTPUT_VIEW
  WHERE INPUTID IN (
    SELECT <column_name_containing_input_ids_to_be_added> as INPUTID
    FROM <dataset_fqtn_containing_input_ids_to_be_added>
    )
    AND BATCHID = '<BATCH_ID>';

Copy