Perform Identity Resolution in Snowflake

The standard PII resolution process passes the data through a privacy filter which removes the PII and reswizzles the table. Because of this, any attributes you need to keep associated with the identifier need to be included in the input table. For more information, see the "Privacy Filter" section below.

These column names cannot be used in the input table for PII resolution:

See the table below for a list of the suggested input table columns and descriptions for PII resolution.

The standard email-only resolution process operates similarly to PII resolution. Any attributes you need to keep associated with the identifier need to be included in the input table. For more information, see the "Privacy Filter" section below.

See the table below for a list of the suggested input table columns and descriptions for email-only resolution.

The device ID resolution operation can be used for the following purposes:

See the tables below for a list of the suggested input table columns and descriptions for these device ID resolution options.

See the table below for the suggested input table column and description for translating device identifiers.

See the table below for a list of the suggested input table columns and descriptions for translating individual RampIDs into their associated household RampIDs.

See the table below for the suggested input table column and description for CID matching. This process enables the retrieval of an existing CID to RampID mapping, hosted by LiveRamp.

This process allows for resolution of a universe dataset with a deconfliction configuration, minimizing conflicts across the entire dataset. The output from this process is a deconflicted hashed CID to RampID mapping.

When resolving a universe dataset, you can use one input table or multiple input tables. Each input table can include one identifier type or multiple identifier types (including PII, hashed email, or MAIDs). The examples below can be used for the specific situations listed but you can create an input table that uses any combination of these identifiers.

The requirements for each identifier type (listed in the relevant sections above) apply here. For example, any PII-based records must include zip in order to match on address and hashed emails must be lowercased and UTF-8 encoded prior to hashing.

.See the table below for a list of the suggested input table columns for a job that will contain both hashed emails and MAIDs with deconfliction.

See the table below for a list of the suggested input table columns for a job that will contain plaintext PII.

The following code snippet shows an example of hashing emails in Snowflake:

-- SHA-1
SELECT SHA1(TRIM(LOWER('  LiveRamp@example.com  '))); 
-- Expected result: 91ac4ee2ca1782581f12d865a6779eb179f8b22a

-- MD5
SELECT MD5(TRIM(LOWER('  LiveRamp@example.com  ')));
-- Expected result: 39c324aa0c7a3ee896884fe0cf086f0c

-- SHA256
SELECT SHA2(TRIM(LOWER('  LiveRamp@example.com  '))); 
-- Expected result: 28324c709525ec8eda8aac51dfb36730262bc3051402250131c4c81fa453df8c

To create the metadata table for PII resolution:

-- FORFor PII Update the parameters here for the metadata table
-- Not all PII types need to be present, remove unused entries from the target_columns JSON
create or replace table identifier($customer_meta_table_name) as
select
    TO_VARCHAR(DECRYPT(ENCRYPT('<client_id>', 'HideFromLogs'), 'HideFromLogs'), 'utf-8') as client_id,
    TO_VARCHAR(DECRYPT(ENCRYPT('<client_secret>', 'HideFromLogs'), 'HideFromLogs'), 'utf-8') as client_secret,
    'resolution' as execution_mode,
    'pii' as execution_type,
    parse_json($$
    {
      "name": ["<up to 4 name column names>"],
      "streetAddress": ["<up to 7 address column names>"],
      "city": "<city column>",
      "state": "<state column>",
      "zipCode": "<zipcode column>",
      "phone": "<phone column>",
      "email": "<email column>",
      "hashedAttributes": ["<up to 10 hashed attribute column names>"]
    }
    $$) as target_columns,
    1 as limit;

-- FOR PII Update the parameters here for the metadata table
-- Not all PII types need to be present, remove unused entries from the target_columns JSON
create or replace table identifier($customer_meta_table_name) as
select
    TO_VARCHAR(DECRYPT(ENCRYPT('liveramp_client', 'HideFromLogs'), 'HideFromLogs'), 'utf-8') as client_id,
    TO_VARCHAR(DECRYPT(ENCRYPT('84159be2-ab93-4bf8-24c9-2g123ef08815', 'HideFromLogs'), 'HideFromLogs'), 'utf-8') as client_secret,
    'resolution' as execution_mode,
    'pii' as execution_type,
    parse_json($$
    {
      "name": ["first_name",”"last_name”"],
      "streetAddress": ["address_1",”"address_2"],
      "city": "city",
      "state": "state",
      "zipCode": "zip",
      "phone": "phone",
      "email": "email",
      "hashedAttributes": ["cid"]
    }
    $$) as target_columns,
    1 as limit;

To create the metadata table for email resolution:

-- email
-- FOR EMAIL Update the parameters here for the metadata table
create or replace table identifier($customer_meta_table_name) as
select
    TO_VARCHAR(DECRYPT(ENCRYPT('<client_id>', 'HideFromLogs'), 'HideFromLogs'), 'utf-8') as client_id,
    TO_VARCHAR(DECRYPT(ENCRYPT('<client_secret>', 'HideFromLogs'), 'HideFromLogs'), 'utf-8') as client_secret,
    'resolution' as execution_mode,
    'email' as execution_type,
    '<column to be resolved>' as target_column,
    1 as limit;

To create the metadata table for device ID resolution:

--FOR DEVICE OR CID RESOLUTION Update the parameters here for the metadata table
create or replace table identifier($customer_meta_table_name) as
select
    TO_VARCHAR(DECRYPT(ENCRYPT('<client_id>', 'HideFromLogs'), 'HideFromLogs'), 'utf-8') as client_id,
    TO_VARCHAR(DECRYPT(ENCRYPT('<client_secret>', 'HideFromLogs'), 'HideFromLogs'), 'utf-8') as client_secret,
    'resolution' as execution_mode,
    '<execution_type>' as execution_type,
    '<column to be resolved>' as target_column;

To create the metadata table for CID matching to an existing CID sync with LiveRamp:

--FOR DEVICE OR CID RESOLUTION Update the parameters here for the metadata table
create or replace table identifier($customer_meta_table_name) as
select
    TO_VARCHAR(DECRYPT(ENCRYPT('<client_id>', 'HideFromLogs'), 'HideFromLogs'), 'utf-8') as client_id,
    TO_VARCHAR(DECRYPT(ENCRYPT('<client_secret>', 'HideFromLogs'), 'HideFromLogs'), 'utf-8') as client_secret,
    'resolution' as execution_mode,
    '<execution_type>' as execution_type,
    '<column to be resolved>' as target_column;

To create the metadata table for universe dataset resolution with deconfliction, including the ability to use one or multiple input tables:

CREATE OR REPLACE TABLE identifier($customer_meta_table_name) as
SELECT {
       'clientId': TO_VARCHAR(DECRYPT(ENCRYPT('<client_id>', 'HideFromLogs'), 'HideFromLogs'), 'utf-8'),
       'clientSecret': TO_VARCHAR(DECRYPT(ENCRYPT('<client_secret>', 'HideFromLogs'), 'HideFromLogs'), 'utf-8'),
       'executionMode': 'resolution',
       'executionType': 'cid_mapping',
       'inputs': [
           {
               'inputTable': $customer_input_table_name,
               'targetColumns': {
                   -- For PII deconfliction  
                   'name': ['<up to 4 name column names>'],
                   'streetAddress': ['<up to 7 address column names>'],
                   'city': '<city column>',
                   'state': '<state column>',
                   'zipCode': '<zipcode column>',
                   'phone': '<phone column>',
                   'email': '<email column>',
                   -- CID column
                   'cid': 'cid_col'
               }
           },
           {
               'inputTable': $customer_input_table_name_2,
               'targetColumns': {
                   -- For Hashed Email deconfliction
                   'hashed_email': '<hashed email column>',
                   -- For Maid deconfliction
                   'maid': '<maid column>',
                   -- CID column
                   'cid': 'cid_col'
               }
           }
       ],
       'config': {'deconflictionConfig': 'STANDARD'},
       'outputTable': $output_table_name
   }::variant as config;

The PII resolution process passes the input table through a privacy filter which removes the PII and reswizzles the table (in addition to other operations). Because of this, any attributes you need to keep associated with the identifier need to be included in the input table. For more information, see the "Privacy Filter" section below.

Identity resolution of PII provides supplemental match metadata for additional insight into customer data that can provide powerful signals for making decisions based on RampIDs.

For PII resolution, the output table includes the fields shown in the table below.

The email-only resolution process operates similarly to PII resolution. Any attributes you need to keep associated with the identifier need to be included in the input table. For more information, see the "Privacy Filter" section below.

For email-only resolution without deconfliction, the output table includes the fields shown in the table below.

For device identifier resolution, the output table includes the fields shown in the table below:

For CID matching against an existing CID sync with LiveRamp, the output table includes the fields shown in the table below:

For universe dataset resolution with deconfliction, the output table includes the fields shown in the table below.