Perform Identity Resolution in Snowflake
LiveRamp's Identity Resolution capability in Snowflake allows for the translation of various identifiers to RampIDs. This allows you to resolve personally-identifiable information (PII) or device identifiers to a persistent pseudonymous identifier for persons and households. You can also input an individual-based RampID and get back any household-based RampID that might be associated with that individual.
LiveRamp's Identity Resolution capability in Snowflake allows for the translation of various identifiers to RampIDs. This allows you to resolve personally-identifiable information (PII) or device identifiers to a persistent pseudonymous identifier for persons and households. You can also input an individual-based RampID and get back any household-based RampID that might be associated with that individual.
Note
For more information about RampID, see "RampID Methodology".
Once you've translated your data to RampIDs, you can then share that data to your LiveRamp account for activation. For more information, see "Share Data from Snowflake to Your LiveRamp Account".
The following identifiers can be resolved:
Names
Postal addresses
Email addresses
Phone numbers
Cookies
MAIDs (mobile device IDs)
CTV IDs (Connected TV Device IDs)
CIDs (custom identifiers)
Person-based, maintained RampIDs (for resolution to household RampIDs)
Based on the type of identifier you’re resolving, you might receive one RampID per identifier or multiple RampIDs per identifier:
For PII touchpoints (in PII or email resolution), you can choose to receive from 1 to 10 RampIDs (if available).
For PII touchpoints, you can also choose a deconfliction configuration that will return RampIDs that are ranked most relevant based on how active the linkages are in the digital ecosystem and other factors. This can be particularly useful for large data sets where overconnected RampIDs can introduce noise into analytics. For more information, see the “Deconfliction Options” section below.
Typically for cookie and mobile device ID resolution, one RampID is returned (but shared touchpoints with more than one RampID can exist).
For CTV identifiers it is common to receive multiple individual RampIDs per identifier.
For CIDs, the number of RampIDs will vary.
When resolving individual RampIDs to household RampIDs, only one household RampID is returned.
These capabilities are available within Snowflake through a native app, which creates a share to your account, opening up a view to query the reference data set from within your own Snowflake environment. See "LiveRamp Embedded Identity in Snowflake" for more information.
Performing identity resolution with the LiveRamp Identity native app requires the creation of two tables:
A metadata table, indicated in the sample SQL as
customer_meta_table_name
.An input table, indicated in the sample SQL as
customer_input_table_name
.
Deconfliction Options
Deconfliction is needed for analytics use cases and works best against universe datasets, meaning the full customer view across CRM-based data, subscriber data, or transaction data. These are typically large datasets, and because of the number of different touchpoints included (address, email, phone) and the sharing of touchpoints among consumers, one record in your dataset might match to some RampIDs in the LiveRamp Identity Graph that are also connected to other consumers (such as family members or relatives). This creates identity conflicts because some of these RampIDs might match to other records in your systems. These conflicts are the inevitable result of the sharing of devices, the use of multiple email addresses (and the sharing of email addresses), and other similar consumer behavior.
For certain use cases, you might want to include as many RampIDs as possible to maximize match and reach, and for these, deconfliction is not recommended. However, for other use cases, such as measurement, you might want to minimize noise across first-party datasets and the deconfliction configuration enables tuning of the outputs to maximize accuracy and fit the LiveRamp graph to your universe. This can be particularly useful for large datasets where overconnected RampIDs can introduce noise into analytics. Some use cases that benefit from this configuration are closed loop measurement, reach and frequency, or mutually exclusive segments as examples.
Using deconfliction on large universe datasets can help match your records to the RampIDs that are determined to be the most relevant for each record by looking at the strength of the connection of each RampID to each touchpoint. It includes references to how active the linkage is in the digital ecosystem.
To utilize deconfliction, you’ll need to provide a CID (custom ID) for each record, and for the configuration to drive the highest value, the full universe is required to deconflict across the table. The output you receive will include the best fidelity RampIDs for each CID.
There are two deconfliction options you can choose from:
Standard: This configuration returns the RampIDs that are determined to be most relevant and removes CIDs that are determined to be duplicates (based on linking to the same RampID). This is the default option and covers most advertiser use cases.
Maximized first-party fidelity: This configuration returns the RampIDs that are determined to be most relevant but preserves additional CIDs even if there are RampIDs that indicate LiveRamp could consolidate them. This option is ideal for publishers and other data owners.
Overall Steps
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:
Create the input table for the appropriate identity resolution operation.
Note
An input table needs to be prepared for each identity resolution operation and can only contain one type of identifier.
Specify the variables to be used in the calls.
Create the metadata table for the appropriate identity resolution operation.
Note
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 MAIDs and hashed emails, you’ll need a different metadata table for each operation.
Set up permissions for the tables to be used for identity resolution.
Perform the appropriate identity resolution process, depending on the identifiers being resolved.
See the sections below for information on performing these tasks.
Note
The LiveRamp Identity native app is parameterized and relies on variables set by the user with the sample SQL in the Execution worksheet. When executing an identity resolution operation, those variables must be set during each active session.
Create the Input Table for Identity Resolution
An input table needs to be prepared for each identity resolution operation.
When creating tables, keep the following guidelines in mind:
The column names for the input table can be whatever you want to use, as long as the names match the values specified in the metadata table.
Do not use any column names that are the same as the column names returned in the output table for the identity resolution operation you're going to run.
Every column name must be unique in a table.
Try not to use additional columns in the input tables required for the identity resolution operation as having extra columns slows down processing.
Per Snowflake guidelines, table names cannot begin with a number.
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.
Input Table Columns for PII Resolution (Without Deconfliction)
The standard PII resolution process (without deconfliction configured) 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.
Note
This offering includes running our Identity Graph on demand. Our testing indicates that a warehouse size of 2XL that is not being used for any other operations performs best for average workloads, but this is dependent on your individual setup. Contact your LiveRamp representative to understand the performance implications of your setup.
When resolving email data only, using the email-only resolution operation can provide higher throughput compared to full PII resolution. This is dependent on warehouse and setup, so talk with your LiveRamp team to determine the best approach for the use case.
Utilizing hashed attributes requires a LiveRamp Data Ethics review and an attestation. We will also work with your team to confirm separation of known and pseudonymous data prior to enabling permissions.
These column names cannot be used in the input table for PII resolution:
RampID
__lr_rank
__lr_filter_ name
See the table below for a list of the suggested input table columns and descriptions for PII resolution.
Suggested Column Name | Example | Description |
---|---|---|
| John | You can include separate First Name and Last Name columns or you can combine first name and last name in one column (such as “Name”). |
| Doe | You can include separate First Name and Last Name columns or you can combine first name and last name in one column (such as “Name”). |
| 123 Main St | |
| Apt 1 | You can include separate Address 1 and Address 2 columns or you can combine all street address information in one column (such as “Address”). |
| Smalltown | When matching on address, City is optional. |
| CA |
|
| 12345 |
|
| john@email.com |
|
| 555-123-4567 |
|
|
|
Input Table Columns for PII Resolution (With Deconfliction)
This process allows for resolution of touchpoints with a deconfliction step, minimizing conflicts across the entire dataset. The output from this process is a deconflicted CID to RampID mapping.
See the table below for a list of the suggested input table columns and descriptions for PII resolution with deconfliction.
Note
You can include columns with attribute data, but these columns will not be returned in the output table.
Suggested Column Name | Example | Description |
---|---|---|
| g221lariab la8;blNj10gtQjQ3QUEwMTNEMTcaktboEc0g9022cxoiaklr91054 |
|
| John | You can include separate First Name and Last Name columns or you can combine first name and last name in one column (such as “Name”). |
| Doe | You can include separate First Name and Last Name columns or you can combine first name and last name in one column (such as “Name”). |
| 123 Main St | |
| Apt 1 | You can include separate Address 1 and Address 2 columns or you can combine all street address information in one column (such as “Address”). |
| Smalltown | When matching on address, City is optional. |
| CA |
|
| 12345 |
|
| john@email.com |
|
| 555-123-4567 |
|
Input Table Columns for Email-Only Resolution (Without Deconfliction)
The standard email-only resolution process (without deconfliction configured) 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.
Note
When resolving email data only, using email-only resolution can provide higher throughput compared to full PII resolution. Talk with your LiveRamp team to determine the best approach for the use case.
To perform identity resolution across additional PII touchpoints, see the “View the PII Resolution Output Table (Without Deconfliction)” section above.
See the table below for a list of the suggested input table columns and descriptions for email-only resolution.
Suggested Column Name | Example | Description |
---|---|---|
| 8c9775a5999b5f0088008c0b26d7fe8549d5c80b0047784996a26946abac0cef |
|
| Male | For email address resolution, you can include columns with attribute data. These columns will be returned in the output table (for more information, see the "View the Email-Only Resolution Output Table (Without Deconfliction)" section below). |
Input Table Columns for Hashed Email-Only Resolution (With Deconfliction)
This process allows for resolution of touchpoints with a deconfliction step, minimizing conflicts across the entire dataset. The output from this process is a deconflicted CID to RampID mapping.
See the table below for a list of the suggested input table columns and descriptions for hashed email-only resolution with deconfliction.
Note
You can include columns with attribute data, but these columns will not be returned in the output table.
Suggested Column Name | Example | Description |
---|---|---|
| g221lariab la8;blNj10gtQjQ3QUEwMTNEMTcaktboEc0g9022cxoiaklr91054 |
|
| 8c9775a5999b5f0088008c0b26d7fe8549d5c80b0047784996a26946abac0cef |
|
Input Table Columns for Device ID Resolution
The device ID resolution operation can be used for the following purposes:
To translate device identifiers (cookies, MAIDs, and CTV IDs) into individual RampIDs
To translate individual RampIDs into their associated household RampIDs
See the tables below for a list of the suggested input table columns and descriptions for these device ID resolution options.
Note
Each device ID resolution input table should contain only one identifier column (either a device identifier or a maintained RampID).
You can include columns with attribute data, but these columns will not be returned in the output table.
See the table below for the suggested input table column and description for translating device identifiers.
Suggested Column Name | Example | Description |
---|---|---|
| 1f4d256c-1f08-41f6-a108-bbe511de9497 | Can be one of the following 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.
Suggested Column Name | Example | Description |
---|---|---|
| XYT999wXyWPB1SgpMUKlpzA013UaLEz2lg0wFAr1PWK7FMhsd |
|
Input Table Columns for CID Matching
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.
Note
You can include columns with attribute data, but these columns will not be returned in the output table.
Suggested Column Name | Example | Description |
---|---|---|
| b916clarib la1;blNj10gtQjQ3QUEwMTNEMTcaktboEc0g9022cxoiaklr20185 | The CID for translation to a RampID |
Specify the Variables
To specify the variables to be used for the operation:
Open the Execution Steps worksheet with the sample SQL for execution.
Update the following variables in the sample SQL that is shown below and then run the SQL:
DATABASE
: The name of your database.PUBLIC
: The name of the schema that holds the tables.INPUT_TABLE
: The name of the input table to use for the operation.META_TABLE
: The name of the metadata table to use for the operation.OUTPUT_TABLE
: The name of the output table that will be created after the operation has been run.IDENTITY_RESOLUTION_AND_TRANSCODING
: The name of the database the native app is loaded to.
--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'); 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';
Create the Metadata Table
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 MAIDs and hashed emails, you’ll need a different metadata table for each operation.
See the sections below for instructions on creating the metadata table for each identity resolution type.
Create the Metadata Table for PII Resolution (Without Deconfliction)
To create the metadata table for PII resolution without deconfliction:
Update the following variables in the sample SQL from the Execution worksheet shown below and then run the SQL:
<client_id>
: Enter either an existing client ID or a new one provided in implementation.<client_secret>
: Enter the password/secret for the client ID.<up to 4 name column names>
: Enter the names of the column(s) in the input table to be used for the “name” element. Each input table column name should be enclosed in double quotes. Enter a maximum of 4 name columns. If entering multiple column names, separate the column names with commas.<up to 7 address column names>
: Enter the names of the column(s) in the input table to be used for the “address” element. Each input table column name should be enclosed in double quotes. Enter a maximum of 7 address columns. If entering multiple column names, separate the column names with commas.<city column>
: Enter the name of the column to be used for the “city” element.<state column>
: Enter the name of the column to be used for the “state” element.<zipcode column>
: Enter the name of the column to be used for the “zipcode” element.<phone column>
: Enter the name of the column to be used for the “phone” element.<email column>
: Enter the name of the column to be used for the “email” element.up to 10 hashed attribute column names:
Enter the names of the attribute column(s) in the input table that should be passed through to the output in hashed format. Each input table column name should be enclosed in double quotes. Enter a maximum of 10 hashed attribute columns. If entering multiple column names, separate the column names with commas.
Note
You can specify the number of RampIDs to be returned for each set of PII, to a maximum of 10 RampIDs per PII set. By default, 1 RampID will be returned per PII set. To change this, replace the “1” in the “as limit_val” line at the bottom of the SQL sample shown below with your desired number.
-- 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;
The populated SQL with the suggested input column names might look like the example shown below:
-- 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;
Create the Metadata Table for PII Resolution (With Deconfliction)
To create the metadata table for PII resolution with deconfliction:
Update the following variables in the sample SQL from the Execution worksheet shown below and then run the SQL:
<client_id>
: Enter either an existing client ID or a new one provided in implementation.<client_secret>
: Enter the password/secret for the client ID.cid
: Enter the name of the column to be used for the "cid" element.<up to 4 name column names>
: Enter the names of the column(s) in the input table to be used for the “name” element. Each input table column name should be enclosed in double quotes. Enter a maximum of 4 name columns. If entering multiple column names, separate the column names with commas.<up to 7 address column names>
: Enter the names of the column(s) in the input table to be used for the “address” element. Each input table column name should be enclosed in double quotes. Enter a maximum of 7 address columns. If entering multiple column names, separate the column names with commas.<city column>
: Enter the name of the column to be used for the “city” element.<state column>
: Enter the name of the column to be used for the “state” element.<zipcode column>
: Enter the name of the column to be used for the “zipcode” element.<phone column>
: Enter the name of the column to be used for the “phone” element.<email column>
: Enter the name of the column to be used for the “email” element.deconfliction_config
: Enter the configuration for the type of deconfliction to execute (for more information, see the “Deconfliction Options” section above). Accepted values include:standard
: This configuration returns the RampIDs that are determined to be most relevant and removes CIDs that are determined to be duplicates (based on linking to the same RampID). This is the default option and covers most advertiser use cases.maximized_1P_fidelity
: This configuration returns the RampIDs that are determined to be most relevant but preserves additional CIDs even if there are RampIDs that indicate LiveRamp could consolidate them. This option is ideal for publishers and other data owners.
Note
When deconfliction is configured, the limit parameter is not needed. All RampIDs will be used to deconflict across the entire data set returning an optimized CID to RampID table for the output.
-- 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 -- Hashed emails cannot be mixed with other pii types. This would cause job failure 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, 'cid_mapping' as execution_type, parse_json($$ { "cid":"cid", "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, {'deconflictionConfig':'standard'} as config, ;
Create the Metadata Table for Email Resolution (Without Deconfliction)
To create the metadata table for email resolution without deconfliction:
Update the following variables in the sample SQL from the Execution worksheet shown below and then run the SQL:
<client_id>
: Enter either an existing client ID or a new one provided in implementation.<client_secret>
: Enter the password/secret for the client ID.<column to be resolved>
: Enter the name of the column containing the email addresses to be resolved.
Note
You can specify the number of RampIDs to be returned for each email, to a maximum of 10 RampIDs per email. By default, 1 RampID will be returned per email. To change this, replace the “1” in the “as limit_val” line at the bottom of the SQL sample shown below with your desired number.
-- 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;
Create the Metadata Table for Hashed Email Resolution (With Deconfliction)
To create the metadata table for hashed email resolution with deconfliction:
Update the following variables in the sample SQL from the Execution worksheet shown below and then run the SQL:
<client_id>
: Enter either an existing client ID or a new one provided in implementation.<client_secret>
: Enter the password/secret for the client ID.cid
: Enter the name of the column to be used for the "cid" element.<column to be resolved>
: Enter the name of the column containing the email addresses to be resolved.deconfliction_config
: Enter the configuration for the type of deconfliction to execute (for more information, see the “Deconfliction Options” section above). Accepted values include:standard
: This configuration returns the RampIDs that are determined to be most relevant and removes CIDs that are determined to be duplicates (based on linking to the same RampID). This is the default option and covers most advertiser use cases.maximized_1P_fidelity
: This configuration returns the RampIDs that are determined to be most relevant but preserves additional CIDs even if there are RampIDs that indicate LiveRamp could consolidate them. This option is ideal for publishers and other data owners.
Note
When deconfliction is configured, the limit parameter is not needed. All RampIDs will be used to deconflict across the entire data set returning an optimized CID to RampID table for the output.
-- Only hashed_email and cid are supported for hashed email deconfliction -- Additional entries will cause a job failure, 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, 'cid_mapping' as execution_type, parse_json($$ { "cid": "<cid column>", "hashed_email":"<hashed email column>" } as target_columns, {'deconflictionConfig':'standard'} as config, ;
Create the Metadata Table for Device ID Resolution
To create the metadata table for device ID resolution:
Update the following variables in the sample SQL from the Execution worksheet shown below and then run the SQL:
<client_id>
: Enter either an existing client ID or a new one provided in implementation.<client_secret>
: Enter the password/secret for the client ID.<execution_type>
: Enter the execution type:Enter
Cookies
to resolve cookiesEnter
MAID
to resolve mobile device IDsEnter
CTV
to resolve CTV IDsEnter
HHLink
to resolve individual RampIDs into household RampIDs
<column to be resolved>
: Enter the name of the column containing the device identifiers (cookies, MAIDs, or CTV IDs) or RampIDs to be resolved.
--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;
Create the Metadata Table for CID Matching
To create the metadata table for CID matching to an existing sync:
Update the following variables in the sample SQL from the Execution worksheet shown below and then run the SQL:
<client_id>
: Enter either an existing client ID or a new one provided in implementation.<client_secret>
: Enter the password/secret for the client ID.<execution_type>
: Enter the CID name provided to you by LiveRamp and specific to the mapping created.<column to be resolved>
: Enter the name of the column containing the CIDs to be mapped.
--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;
Set Up Permissions
To set up the permissions for the tables used for translation, run the SQL in the Execution Steps worksheet shown below:
Note
This SQL utilizes the variables that were set up in the “Specify the Variables” section above.
--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); grant select on table identifier ($customer_meta_table_name) to application identifier($application_name); use database identifier ($application_name); use schema lr_app_schema;
Perform the Identity Resolution Operation
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, which includes checking that the output has succeeded. 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:
Locate the
lr_resolution_and_trancoding
procedure shown below and run that SQL.call lr_resolution_and_transcoding( $customer_input_table_name, $customer_meta_table_name, $output_table_name, );
The operation runs to completion.
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.
View the Output Table
The identity resolution results end up in the output table in the same database you specified previously.
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>' );
View the PII Resolution Output Table (Without Deconfliction)
The PII resolution process (without deconfliction) 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.
Column | Example | Description |
---|---|---|
| XYT999wXyWPB1SgpMUKlpzA013UaLEz2lg0wFAr1PWK7FMhsd | Returns the resolved RampID in your domain encoding. |
| Male | Any attribute columns passed through the service are returned. |
| 63889cfb9d3cbe05d1bd2be5cc9953fd | Any hashed attribute columns passed through the service are returned with their values MD5 hashed. |
| 1 | Provides insight on the match cascade level associated with the identifiers. If no maintained RampID is found, this value will be "null". |
| name_phone | Returns the filter name where the match occurred, which will be one of the following options:
If no maintained RampID is found, this value will be "null". |
View the PII Resolution Output Table (With Deconfliction)
For PII resolution with deconfliction, the output table includes the fields shown in the table below.
Column | Example | Description |
---|---|---|
| 93abc799-a0a5-40b5-80dd-d2ab61d4d072 | The CID passed into the process will be returned as an MD5-hashed CID. |
| XYT999wXyWPB1SgpMUKlpzA013UaLEz2lg0wFAr1PWK7FMhsd | Returns the resolved and deconflicted RampID in your domain encoding. |
View the Email-Only Resolution Output Table (Without Deconfliction)
The email-only resolution process (without deconfliction) 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.
Column | Example | Description |
---|---|---|
| XYT999wXyWPB1SgpMUKlpzA013UaLEz2lg0wFAr1PWK7FMhsd | Returns the resolved RampID in your domain encoding. |
| Male | Any attribute columns passed through the service are returned. |
View the Email-Only Resolution Output Table (With Deconfliction)
For email-only resolution with deconfliction, the output table includes the fields shown in the table below.
Column | Example | Description |
---|---|---|
| 93abc799-a0a5-40b5-80dd-d2ab61d4d072 | The CID passed into the process will be returned as an MD5-hashed CID. |
| XYT999wXyWPB1SgpMUKlpzA013UaLEz2lg0wFAr1PWK7FMhsd | Returns the resolved and deconflicted RampID in your domain encoding. |
View the Device Identifier Resolution Output Table
For device identifier resolution, the output table includes the fields shown in the table below:
Column | Example | Description |
---|---|---|
| 93abc799-a0a5-40b5-80dd-d2ab61d4d072 | |
| XYT999wXyWPB1SgpMUKlpzA013UaLEz2lg0wFAr1PWK7FMhsd |
View the CID Matching Output Table
For CID matching against an existing CID sync with LiveRamp, the output table includes the fields shown in the table below:
Column | Sample | Description |
---|---|---|
| 93abc799-a0a5-40b5-80dd-d2ab61d4d072 | Original identifier passed in. |
| XYT999wXyWPB1SgpMUKlpzA013UaLEz2lg0wFAr1PWK7FMhsd | Returns the resolved RampID in your domain encoding. |
Privacy Filter
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):
Column Values: The process evaluates the combination of all the column values on a per row basis for unique values. If a particular combination of column values occurs 3 or fewer times, the rows containing those column values will not be matchable and will not be returned in the output table.
Note
This check does not apply to hashed attributes.
>5% of the table unmatchable: If, based on column value uniqueness, >5% of the file rows are unmatchable, the job will fail.
Number of Unique RampIDs: If fewer than 100 unique RampIDs would be returned, the job will fail.
Reswizzle full table: Upon completion, the full table will be reswizzled to return the rows
RampID | attribute_1 | attribute_2 | attribute_n
in a different order than what was submitted in the input table.
Note
When using the deconfliction configuration, attributes are not preserved so the privacy filter is not applied.