Perform Dataset Transformations
In some situations, a question requires a dataset to be in a specific schema or format, which differs from the schema of the data connection. In other situations, you might need to create a placeholder field for a field used in queries that is not in your original dataset.
LiveRamp Clean Room offers the ability to apply transformations to your dataset in the clean room to be able to be used in such questions. Performing data transformations provides the ability to rename fields or to add additional fields when configuring your datasets by leveraging various functions, such as concatenation and lowercase.
This allows you to modify how a field is used in a query without having to amend the underlying dataset, saving significant time and effort in coordination with your database team.
See the sections below for more information on performing different dataset transformations.
Note
This feature is currently only supported for standard clean rooms and is not available for industry clean rooms such as Amazon Marketing Cloud, Google Ads Data Hub, or Facebook Advanced Analytics.
Create a New Dataset Field
To create a new dataset field (including a placeholder field):
Note
To create a new dataset field that transforms an existing timestamp field, see the “Create a New Dataset Field to Transform a Timestamp Field” section below.
Navigate to your desired clean room and then select Datasets from the navigation menu.
For the dataset that you would like to modify, click the More Options menu (the three dots) and then select either or (depending on whether that dataset has already been configured).
In the upper-right corner, click .
Enter the new field name.
Note
Field names must be alphanumeric only and start with a letter. Field names cannot contain spaces or special characters.
Enter your field definitions, depending on the transformation function you are applying, by referencing the available dataset fields and functions. You can see the various function syntax and explanations by using the Transform Function dropdown on the righthand side.
Note
For a placeholder field, we recommend using 'default' (in single quotes).
Click .
Note
Multiple functions can be used together in the same definition.
If a dataset field has the "Include" toggle turned off, then it cannot be used in transformations.
Do not include "=" when writing the definition.
Currently we support "concatenate" and "lower" functions, with more supported in the future.
Create a New Dataset Field to Transform a Timestamp Field
If a timestamp field in your dataset does not use the same timestamp format as your collaboration partner’s dataset, you might need to create a new dataset field to transform that timestamp field.
Navigate to your desired clean room and then select Datasets from the navigation menu.
For the dataset that you would like to modify, click the More Options menu (the three dots) and then select either or (depending on whether that dataset has already been configured).
In the upper-right corner, click .
To change a DATETIME (aka TIMESTAMP) to a UNIX/EPOCH timestamp, you might use one of the following methods in the Field Definition field:
General transformation: CAST(UNIX_TIMESTAMP(whatever_field_is_named) AS LONG)
If the original timestamp has milliseconds: CAST(unix_timestamp(whatever_the_field_is_named, 'yyyy-MM-dd HH:mm:ss.SSS') as LONG)
If the original timestamp does not have milliseconds and is originally in DATETIME format (not STRING): CAST(unix_timestamp(whatever_the_field_is_named) as LONG)
To change a CALENDAR DATE into a UNIX/EPOCH timestamp, see the table below for the methods you might use in the Field Definition field:
Date Sample
Intl, US, or Other
2- or 4-Digit Year
Zero Placeholder?
Transformation
MM/DD/YYYY
US
4
Yes
CAST(UNIX_TIMESTAMP(TO_TIMESTAMP(whatever_the_field_is_named, 'MM/DD/YYYY')) as LONG)
M/D/YY
US
2
No
CAST(UNIX_TIMESTAMP(TO_TIMESTAMP(whatever_the_field_is_named, 'M/D/YY')) as LONG)
DD/MM/YYYY
Intl
4
Yes
CAST(UNIX_TIMESTAMP(TO_TIMESTAMP(whatever_the_field_is_named, 'DD/MM/YYYY')) as LONG)
YYYY-MM-DD
Other
4
Yes
CAST(UNIX_TIMESTAMP(TO_TIMESTAMP(whatever_the_field_is_named, 'yyyy/MM/dd')) as LONG)
Note
This is not an exhaustive list. Edit the date format based on the way your dates appear in your underlying dataset.
Make sure to not include spaces in the field definition (for example, don’t enter “CONCAT (First_NAME, LAST_NAME” as this would not work - instead enter “CONCAT(First_NAME, LAST_NAME”.
Click .
Rename a Dataset Field
To rename a dataset field:
Navigate to your desired clean room and then select Datasets from the navigation menu.
For the dataset that you would like to modify, click the More Options menu (the three dots) and then select either or (depending on whether that dataset has already been configured).
Click into the "Rename" field for the field you want to rename and input the new name. In this example, we changed the field name from "Advertiser_ID" to "Identifier".
Click .