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 to work with the 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