Overview

Target Audience

What You’ll Learn

Prerequisites

What You'll Need

Additional Information

Setup Identity Resolution Application

If not yet done, request for the LiveRamp Identity Resolution and Transcoding Native Application

**Consider This: ** This Quickstart will focus on Personally Identifiable Information (PII) setup and resolution. Other identifiers are available and require slightly different configurations that are discussed here

Setup the Input Table

Setup 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,
    '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;

Validate the Permissions on all tables

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);
grant select on table identifier ($customer_meta_table_name) to application identifier($application_name);


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

Executing the Identity Resolution Application

This process will involve two separate steps for completing the application execution

**Consider This: ** When executing PII it is recommended starting with a 2XL size warehouse

call lr_resolution_and_transcoding(
    <customer_input_table_name>,
    <customer_meta_table_name>,
    <output_table_name>
);

Execution Note This process will execute inside your warehouse, so do not kill this process

call check_for_output(
	$output_table_name
);

Audit the Identity Resolution Process

Sample Audits

SET final_output = '$output_table_name';

select __LR_RANK, count(__LR_RANK) as Count1 from identifier($final_output)
group by (__LR_RANK)
order by Count1 desc;

select __LR_FILTER_NAME, count(__LR_FILTER_NAME) as Count1 from identifier($final_output)
group by (__LR_FILTER_NAME)
order by Count1 desc;

--select FILTER_NAME, count(FILTER_NAME) as count1 from identifier($final_output);
--group by FILTER_NAME
--ORDER BY COUNT1 DESC;
select FILTER_NAME, count(FILTER_NAME) as Count1 from  LR_APP_SHARE_RESOLUTION_DEMO.LR_JOB_SCHEMA.DS_TEST_1A_POST_V5
group by (FILTER_NAME)
order by Count1 desc;

SELECT 
    SUBSTR(RAMPID, 1, 2) AS first_two_chars,
    COUNT(*) AS frequency
FROM 
    identifier($final_output)
GROUP BY 
    SUBSTR(RAMPID, 1, 2)
ORDER BY 
    frequency DESC;

SELECT COUNT(RAMPID) FROM   identifier($final_output);

Setup Identity Translation Application

If not yet done, request for the LiveRamp Identity Resolution and Transcoding Native Application

**Consider This: ** This Quickstart will focus on Translation from one RampID to another RampID domain. Please reach out to LiveRamp for the correct domain for your translation use case.

Setup the Input Table

CREATE OR REPLACE TABLE <Translation Input Table>
AS SELECT RAMPID , 'ZZZZ' AS TARGET_DOMAIN , 'RampID' AS TARGET_TYPE FROM <Table with a valid RampID Column>;

Setup 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,
    'transcoding' as execution_mode,
    'transcoding' as execution_type,
    '<column to be translated>' as target_column,
    '<column containing target domain>' as target_domain_column,
    '<column containing target type>' as target_type_column;

Validate the Permissions on all tables

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);
grant select on table identifier ($customer_meta_table_name) to application identifier($application_name);


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

Executing the Identity Translation Application

This process will involve two separate steps for completing the application execution

**Consider This: ** When executing Translation it is recommended starting with a Large size warehouse

call lr_resolution_and_transcoding(
    <customer_input_table_name>,
    <customer_meta_table_name>,
    <output_table_name>
);

Execution Note This process will execute inside your warehouse, so do not kill this process

call check_for_output(
	$output_table_name
);

Auditing Translation

-- setting the input table for the rest of the script
set audit_table = 'your Transcoding Output';
set audit_table_output = 'Extr Audit Table';

-- Counts of where the input and output are different.
SELECT COUNT(*) AS count_of_different_lengths
FROM  identifier($audit_table)
WHERE LENGTH(rampid) <> LENGTH(TRANSCODED_IDENTIFIER);

-- create a table of the rows that are different for audit review   
CREATE or REPLACE TABLE identifier($audit_table_output) AS
SELECT *,
       CASE 
           WHEN  TRANSCODED_IDENTIFIER LIKE '%Error, target key missing for transcoding%' THEN 'Target Access Mismatch'
           WHEN  TRANSCODED_IDENTIFIER LIKE '%Error, Invalid RampID Format. Overall.%' THEN 'Data Input Mismatch'
           WHEN LENGTH(rampid) <> LENGTH(TRANSCODED_IDENTIFIER) 
                AND LEFT(rampid, 2) <> LEFT(TRANSCODED_IDENTIFIER, 2) THEN 'Size and Maintained Mismatch'
           WHEN LENGTH(rampid) <> LENGTH(TRANSCODED_IDENTIFIER) THEN 'Size Mismatch'
           WHEN LEFT(rampid, 2) <> LEFT(TRANSCODED_IDENTIFIER, 2) THEN 'Maintained Mismatch'
       END AS mismatch_type
FROM identifier($audit_table)
WHERE LENGTH(rampid) <> LENGTH(TRANSCODED_IDENTIFIER)
   OR LEFT(rampid, 2) <> LEFT(TRANSCODED_IDENTIFIER, 2)
   OR TRANSCODED_IDENTIFIER LIKE '%Error, target key missing for transcoding%'
   OR TRANSCODED_IDENTIFIER LIKE '%Error, Invalid RampID Format. Overall%';

-- quick check of output table
select * from identifier($audit_table_output)
limit 10;

Collaborating using the Translation Output

Build a Table to share

CREATE TABLE NewTable AS
SELECT 
    t1.* EXCLUDE (RampID),
    t2.Transcoded_Identifier
FROM 
    <Your RampID and Attribute Table> t1
JOIN 
    <Your Output from Translation> t2
ON 
    t1.RampID = t2.RampID;

select * from NewTable limit 100;

Conclusion And Resources

What you learned

Additional Capabilities not discussed

Related Resources