Perform Identity Resolution in Snowflake

The 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 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 a list of the suggested input table columns and descriptions 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 a list of the suggested input table columns and descriptions for CID resolution.

To create the metadata table for PII resolution:

-- 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('<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>"
    }
    $$) 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"
    }
    $$) 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 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;

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, the results end up in the output table in the same database, with the following fields (as shown below):

For device identifier resolution, the results end up in the output table in the same database, with the following fields (as shown below):

For CID resolution, the results end up in the output table in the same database, with the following fields (as shown below):