Skip to main content

Perform RampID Transcoding in Snowflake

Abstract

LiveRamp's Transcoding application 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 Transcoding application 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.

Note

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

Specifically, RampID transcoding 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.

Overall Steps

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

  1. Verify that you've performed all the native app setup tasks in the completion checklist.

  2. Prepare the input and metadata tables for transcoding.

  3. Perform transcoding.

See the sections below for information on performing these tasks.

Completion Checklist for Native App Setup

Before performing a transcoding operation for the first time, verify that you've completed the following tasks to set up a transcoding native app in Snowflake:

Note

For instructions on completing these tasks, see "Set Up a Native App".

  • Accepted the terms and conditions of the application.

  • Installed the appropriate LiveRamp Snowflake native application.

  • Updated the firewall configuration for authentication.

  • Created and granted permissions to a role that gives the App Share database access to the appropriate tables.

  • Created logging and metrics tables, added them to a share, and shared that share back to LiveRamp.

After these tasks have been completed, you are ready to prepare the tables and perform the operation. See the sections below for more information.

Prepare the Tables for Transcoding

Transcoding with the LiveRamp native app requires the preparation and deployment of two tables:

  • A metadata table, indicated in the code as <{{transcoding_meta_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.

  • An input table, indicated in the code as <{{transcoding_input_table}}>.

    Note

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

To create the input and metadata tables:

  1. Click in the Create input and metadata table procedure.

  2. Click Run.

  3. Make any necessary changes to the variables:

    <SNOWFLAKE_CONSUMER_INPUT_DATABASE>

    <SNOWFLAKE_CONSUMER_SCHEMA>

    <transcoding_input_table>

    <transcoding_meta_table>

You can create these tables inside Snowflake or import the tables into your database using Snowflake’s standard methods. The <...> variables may be substituted with your own values. Be sure to reference the names correctly in the metadata table, which has as its default name <transcoding_meta_table>, and make sure that the column names also match up correctly.

When creating tables, keep the following guidelines in mind (in addition to the guidelines listed in the sections below):

  • Every column name must be unique in a table.

  • Try not to use additional columns in the tables required for the transcode operation. Having extra columns slows down processing.

  • The transcode operation can process records containing blank fields.

Table Naming Guidelines

When naming tables, follow these guidelines:

  • Table names must use ASCII characters and not contain either spaces or special characters such as !@#$%.

  • You can use underscores “_” within the name, but not as the initial character.

  • Consider using the following elements in your table names: type of data or description, a date or timestamp, and an identity designation. For example, the table name Identity_TwoButtonSuitsCampaign_impressions_2022-06-01 contains all three element types.

Metadata Table Columns and Descriptions

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 transcode to, and the identifier type.

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.

Metadata column names must match those shown in the table below. The column names are not case sensitive, and should not be enclosed in single or double quotation marks.

Column

Description

CLIENT_ID

Enter either an existing CLIENT_ID or a new one provided in implementation.

CLIENT_SECRET

Password/secret for the CLIENT_ID.

EXECUTION_MODE

Transcoding.

EXECUTION_TYPE

Transcoding.

TARGET_COLUMN

Enter the column name of the input table which contains the RampIDs to be transcoded.

TARGET_DOMAIN_COLUMN

Enter the column name of the input table which contains the target domain for the encoding the RampIDs should be translated to.

TARGET_TYPE_COLUMN

Enter the column name of the input table which contains the target identifier type.

Table 1. Metadata table columns for transcoding.


Input Table Columns and Descriptions

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

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.

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.

Table 2. Input table columns for transcoding.


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

Note

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

Perform Transcoding

You perform a transcoding operation by running the transcoding procedure and then checking that the output has succeeded. You then open the output table to check the results.

To transcode the RampIDs in the input table:

  1. Select the input table in the left-hand Database Objects navigation pane.

  2. Click on the magnifying glass icon tab shown in the figure below.

    Results of the transcode operation.
    Figure 5. Results of the transcode operation.


    Run the transcoding operation specific for your database and schema to convert your input table ID values using the parameters in the metadata table for conversion to new RampIDs in the output table.

  3. Locate the lr_resolution_and_trancoding procedure shown below and click on that code block.

    call <native_app_database_name>.lr_app_schema.lr_resolution_and_transcoding
(
    ‘<consumer_database_name>.<consumer_schema_name>.<INPUT_TABLE>’,
    ‘<consumer_database_name>.<consumer_schema_name>.<META_TABLE>’,
    ‘<OUTPUT_TABLE>’,
    ‘<consumer_database_name>.<consumer_schema_name>.<LOGGING_TABLE>’,
    ‘<consumer_database_name>.<consumer_schema_name>.<METRICS_TABLE>’
);

  4. Ensure that you have correctly entered the names of the database objects you are using into the procedure above.

  5. Click Run.

    The transcode operation runs to completion.

  6. Open the LiveRamp app worksheet.

  7. Locate the Check for output procedure shown below and click in that block.

    // Check for output
/*
Check for output
*/
call <native_app_database_name>.lr_app_schema.check_for_output(	'<OUTPUT_TABLE>',
);
/*
END SECTION
*/

  8. Click Run.

  9. Wait until Snowflake returns a status message of success or error.

    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, the TRANSCODING_TEST_TABLE shown in the example above (see Step 2).

Column

Sample

Description

RampID (original encoding)

XYT999RkQ3MEY1RUYtNUIyMi00QjJGLUFDNjgtQjQ3QUEwMTNEMTA1CgMjVBMkNEMTktRD

Returns the original RampID included in the input table.

Transcoded_identifier (RampIDs in target encoding)

XYT001k0MS00MDc1LUI4NjEtMjlCOUI0MUY3MENBCgNjVGQjE0MTMtRkFBMC00QzlELUJF

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

Table 3. Output table columns for transcoding.


In this section:

In the context of LiveRamp, a maintained identifier, such as a maintained RampID or a maintained AbiliTec ID, represents a person that LiveRamp can fully recognize. A maintained identifier is the identifier associated with a given record when there is a maintained record in the AbiliTec Identity Graph (our PII-based offline identity graph) for the input data provided.

The identifier (such as a derived RampID) created for a given PII touchpoint (such as an email address or phone number) when there is no maintained record in the AbiliTec Identity Graph.

In the context of LiveRamp, a maintained identifier, such as a maintained RampID or a maintained AbiliTec ID, represents a person that LiveRamp can fully recognize. A maintained identifier is the identifier associated with a given record when there is a maintained record in the AbiliTec Identity Graph (our PII-based offline identity graph) for the input data provided.

The identifier (such as a derived RampID) created for a given PII touchpoint (such as an email address or phone number) when there is no maintained record in the AbiliTec Identity Graph.