Perform Identity Resolution in Snowflake (Non-US Data)

After you've set up the LiveRamp Identity native app in Snowflake (see "Set Up the LiveRamp Native App in Snowflake" for instructions), perform the following steps to perform identity resolution:

See the sections below for information on performing these tasks.

An input table needs to be prepared for each identity resolution operation.

When creating tables, keep the following guidelines in mind:

See the sections below for suggested input table columns and descriptions for each resolution type.

The output table is created by the operation that you run. For an example, see the sections in "View the Output Table" below.

-- 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 specify the variables to be used for the operation:

--Update this section with the appropriate variables
set customer_db_name = 'DATABASE';
set customer_schema_name = concat($customer_db_name, '.', 'PUBLIC');
set customer_input_table_name = concat($customer_schema_name, '.', 'INPUT_TABLE');
-- If there are multiple input tables add additional variables
set customer_input_table_name_2 = concat($customer_schema_name, '.', 'INPUT_TABLE_2');
set customer_meta_table_name = concat($customer_schema_name, '.', 'META_TABLE');
set output_table_name = 'OUTPUT_TABLE';

-- Name of the installed application
set application_name = 'IDENTITY_RESOLUTION_AND_TRANSCODING';

A metadata table can be reused for multiple operations, but a separate metadata table must be prepared for each different identity resolution operation you want to perform. For example, if you’re going to perform identity resolution on both PII and on hashed emails, you’ll need a different metadata table for each operation.

See the sections below for instructions on creating the metadata table.

-- 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>"],
      "zipCode": "<zip column>",
      "phone": "<phone column>",
      "email": "<email column>",
      "hashedAttributes": ["<up to 10 hashed attribute column names>"]
    }
    $$) as target_columns,
    1 as limit;

     'config': {'derived':};

-- 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”"],
      "zipCode": "zip",
      "phone": "phone",
      "email": "email",
      "hashedAttributes": ["cid"]
    }
    $$) as target_columns,
    1 as limit;

     'config': {'derived':};

-- 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;

    'config': {'derived':};

To set up the permissions for the tables used for translation, run the SQL in the Execution Steps worksheet shown below:

--The remainder of the commands should be run for ALL JOB TYPES, please switch to the Native App database and schema and execute the procedure.  Once completed, please run check_for_output for the output table to be written in the appropriate Job Schema

grant usage on database identifier ($customer_db_name) to application identifier($application_name);
grant usage on schema identifier ($customer_schema_name) to application identifier($application_name);
grant select on table identifier ($customer_input_table_name) to application identifier($application_name);
--If there are multiple input tables grant permission for all of them here
grant select on table identifier ($customer_input_table_name_2) to application identifier($application_name)
grant select on table identifier ($customer_meta_table_name) to application identifier($application_name);


use database identifier ($application_name);
use schema lr_app_schema;

Once you’ve completed the previous steps, you’re ready to perform the identity resolution operation.

You perform an identity resolution operation by running the identifier resolution procedure shown below. You can then view the output table to check the results.

The output tables vary somewhat, depending on the type of identifiers being resolved.

To perform the identity resolution operation:

Once the app returns a success message, the output should be displayed in the native app database under lr_app_schema.

If Snowflake returns a status message of Error, check the error message for any information to help you fix the issue and then try running the operation again. For some issues, the error message will direct you to contact LiveRamp Support. For more information, see "Snowflake Operation Error Codes".

The results end up in the output table in the same database, with the fields shown in the appropriate section below.

The identity resolution results end up in the output table in the application database under the schema “lr_job_schema”.

Once you've confirmed that the output table has been generated, see the appropriate section below for information on the output table format for the type of identity resolution operation that was run.

If for any reason you need to drop the output table, update the parameters in the following command and run:

call DROP_OUTPUT_TABLE(
    '<table_name>'
);

To minimize the risk of re-identification (the ability to tie PII directly to a RampID), the service includes the following processes when resolving PII identifiers (PII resolution or email-only resolution):