Skip to main content

Perform RampID Translation in Snowflake

Abstract

LiveRamp's Translation capability in Snowflake allows for the translation of a RampID from one partner encoding to another using either maintained or derived RampIDs. This allows you to match persistent pseudonymous identifiers to one another and enables use of the data without sharing the sensitive underlying identifiers.

LiveRamp's Translation capability in Snowflake allows for the translation of a RampID from one partner encoding to another using either maintained or derived RampIDs. This allows you to match persistent pseudonymous identifiers to one another and enables use of the data without sharing the sensitive underlying identifiers.

Caution

This content is for customers utilizing the latest version of the LiveRamp Identity Resolution and Translation native app in Snowflake (starting October 2023). For customers utilizing previous versions of LiveRamp's native apps in Snowflake (the LiveRamp Identity Resolution native app or the LiveRamp Translation native app), see this documentation.

Note

For more information about RampIDs, see "RampID Methodology".

Specifically, RampID translation enables:

  • Person-based analytics

  • Increased match rates in data collaboration

  • Measurement enablement across device types

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.

Translation 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.

Overall Steps

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

  1. Create the input table for translation.

    Note

    An input table needs to be prepared for each translation operation.

  2. Specify the variables to be used in the calls.

  3. Create the metadata table.

    Note

    As long as the column names in the input table stay the same, the original metadata table can be reused for multiple operations. You only need to create a new metadata table if you change the column names in the input table.

  4. Set up permissions for the tables to be used for translation.

  5. Perform translation and view the output.

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 a translation operation, those variables must be set during each active session.

Create the Input Table for Translation

An input table needs containing the RampIDs to be translated to be prepared for each operation.

When creating input 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.

  • Every column name must be unique in a table.

  • Try not to use additional columns in the tables required for the translation operation. Having extra columns slows down processing and all attribute columns will be dropped during translation.

  • The translate operation can process records containing blank fields.

  • Per Snowflake guidelines, table names cannot begin with a number.

Column

Sample

Description

rampid

XYT999RkQ3MEY1RUYtNUIyMi00QjJGLUFDNjgtQjQ3QUEwMTNEMTA1CgMjVBMkNEMTktRD

RampID (maintained or derived) for translation.

target_domain_column

T001

Target domain:

  • Enter a partner’s domain when translating from your native encoding to that partner’s domain.

  • Enter your domain when translating from a partner’s encoding to your native encoding.

id_type

RampID

Target type. Currently only "RampID" is supported.

The output table is created by the operation that you run. For an example, see the "Perform Translation" section below.

Note

You can translate both maintained RampIDs and derived RampIDs in your table. For more on RampID types and versions, see “RampID”.

Specify the Variables

To specify the variables to be used for the operation:

  1. Open the Execution Steps worksheet with the sample SQL for execution.

  2. Update the following variables in the sample SQL 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.

    • METRICS_TABLE: The name of the metrics table to use for the operation.

    • LOGGING_TABLE: The name of the logging 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 customer_metrics_table_name = concat($customer_schema_name, '.', 'METRICS_TABLE');
set customer_logging_table_name = concat($customer_schema_name, '.', 'LOGGING_TABLE');
set output_table_name = 'OUTPUT_TABLE';

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

Create the Metadata Table

The metadata table passes the required credentials, specifies the type of operation, and specifies the column names in the input table to reference for the original RampIDs, the domain to translate to, and the identifier type.

Note

As long as the column names in the input table stay the same, the original metadata table can be reused for multiple operations. You only need to create a new metadata table if you change the column names in the input table.

To create the metadata table:

  • Update the following variables in the Execution Steps sample SQL 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 translated>: Enter the column name of the input table which contains the RampIDs to be translated.

    • <column containing target domain>: Enter the column name of the input table which contains the target domain for the encoding the RampIDs should be translated to.

    • <column containing target type>: Enter the column name of the input table which contains the target identifier type.

--FOR TRANSCODING 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,
    '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;

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 Translation

You perform a translation operation by running the translation procedure to translate the RampIDs in the input table and then checking that the output has succeeded. You then open the output table to check the results.

To perform translation

  1. 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,
            $customer_logging_table_name,
    	$customer_metrics_table_name
    );

    The translate operation runs to completion.

  2. Locate the Check for output procedure shown below and run that SQL.

    call check_for_output(
    	$output_table_name
    );

    If Snowflake returns a status message of error, run the procedure again until Snowflake returns a status message of "Success. Output Table: <OUTPUT_TABLENAME> is now available".

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

The results end up in the output table in the same database. See the table below for an example of an output table.

Column

Example

Description

RampID

XYT999RkQ3MEY1RUYtNUIyMi00QjJGLUFDNjgtQjQ3QUEwMTNEMTA1CgMjVBMkNEMTktRD

Returns the original RampID included in the input table.

transcoded_identifier

XYT001k0MS00MDc1LUI4NjEtMjlCOUI0MUY3MENBCgNjVGQjE0MTMtRkFBMC00QzlELUJF

Translated RampID or NULL (NULL due to unreadable native RampID or unauthorized domain, etc.).