Perform Known Identity Resolution in Snowflake (US Data)

Managing data in the known identity space does not require the same privacy protections as the pseudonymous RampID identity space and, as noted in prior sections, all attribute data included in the input table will pass through to the output table.

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.

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

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

This set of optional metadata provides flags that can indicate whether a particular postal address, phone number, or email address is the primary one for that individual. Best Contact flags are Boolean operators (TRUE or FALSE) that indicate if the address, email, or phone that was sent matches the one that the Known Identity Graph has determined is the best one for that user.

Best contact flag use cases include:

If a record is used that includes all of the best touchpoints, we will return "true" for all three flags. These flags will be returned in a _lr_metadata column.

The "clickVerifyDate" flag is a signal that is used in designating the best email address for a user that could have other uses in building data assets or scoring email address data. Because this signal relies on specific deterministic signals from a small subset of LiveRamp match data contributors, it appears on a subset of "Best Email" addresses and should not be used as the only filter for understanding active email addresses.

These flags are only available for Known ID person-based identifiers.

Best contact flags include the information shown in the table below:

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,
   'executionMode': 'resolution',
        'executionType': 'pii'
'inputTable': $customer_input_table_name,
'limit': 1,
‘outputIdentifiers’:[
	“knownId”, 
	“householdLink”, 
	“placeId”
],
‘outputMetadata’:[
	“matchLevel”, 
	“rank”, 
	“isBestEmailTouchpoint”
],
'outputTable': $output_table_name
“targetColumns”: {
      "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>",
    }
    }::variant as config;

-- 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,
      ‘executionMode': 'resolution',
        'executionType': 'pii'
'inputTable': $customer_input_table_name,
'limit': 1,
‘outputIdentifiers’:[
	“knownId”, 
	“householdLink”, 
	“placeID”
],
‘outputMetadata’:[
	“matchLevel”, 
	“rank”, 
	“isBestEmailTouchpoint”
],
'outputTable': $output_table_name
“targetColumns”: {
      "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>",
    }
    }::variant as config;

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,
    ‘executionMode': 'resolution',
        'executionType': 'email'
'inputTable': $customer_input_table_name,
'limit': 1,
‘outputIdentifiers’:[
	“knownId”, 
	“householdLink”, 
	“placeId”
],
‘outputMetadata’:[
	“matchLevel”, 
	“rank”, 
	“isBestEmailTouchpoint”
],
'outputTable': $output_table_name
    '<column to be resolved>' as target_column,
    1 as limit;

For known resolution, the identifiers and metadata returned is configurable, as outlined in the "Create the Metadata Table" section above. Depending on the values included, the output table will vary based on the following:

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

For PII resolution, additional configurability options (such as including additional identifiers and including additional metadata) could produce an output table that includes the following:

The email-only resolution process operates similarly to PII resolution. For known resolution, the identifiers and metadata returned is configurable, as outlined in the "Create the Metadata Table" section above. Depending on the values included, the output table will vary based on:

For email-only resolution, the output table includes the columns shown in the table below.