Transformation Activity (2.13)
Introduction
A transformation activity transforms fields in a single staging table. For each field that is transformed, you can either overwrite the existing field or set the value of a new field.
Options
For each transformation within a transformation group, the following options apply:
-
Function Type —Specifies the function to use to transform the value. See Transformation Functions .
-
Transformation Name —Specifies a short name for this transformation.
There are additional options depending on Function Type; see Transformation Functions.
When one or more transformations are applied to a field, they are organized into a transformation group. A transformation group represents an ordered pipeline of transformations that produces a single output value.
Regardless of how many transformations are defined, a transformation group produces only one output field—either by overwriting the source field or writing to a single result field.
-
Result Field Name —Specifies the field that will store the final result of the transformation group (if Overwrite is not selected). The system automatically adds a field to the staging table; the actual name of the new field is %T_ newname where newname is the name you specified.
If Overwrite is selected, the Result Field Name is the same as the Field Name.
-
Overwrite —Specifies whether to replace the old value. In this case, the system will not generate a new field in the staging table.
Overwrite is off by default. The system writes the final result of all transformations in the group to the selected output field.
Transformation Groups
Transformations applied to a field are stored as a transformation group. Group-level settings, such as Overwrite, Result Field Name, and Result Field Type, apply to the final output of the group rather than to individual transformations.
Transformations within a group are executed in order. Because of this, the result field type is determined by the last transformation in the sequence.
If you are unable to publish a transformation activity, verify that none of your transformations have the error 
icon.
This error indicates that the transformations are not compatible in sequence. The output of each transformation becomes the input to the next transformation in the group, so the data type produced by one transformation must match the expected input type of the next.
If Overwrite is enabled, the data type of the final transformation must also be compatible with the data type of the original field.
To resolve these errors, use the drag handles to reorder the transformations, or modify the output type by selecting a compatible value. Once all errors are resolved, Save Draft and Publish become available.
Transformation Functions
The available transformation functions are as follows:
Available for numeric fields. This transformation groups values into ranges or categories. Also specify Buckets in square brackets ([]) delimited by commas. The bucket list will be used to classify this field value. For example, specifying [:–10],[-9:0],[1:10],[11:20],[21:30],[31:] groups values into those ranges. Any value greater than 31 is categorized into the [31:] bucket.
Available for string fields. This function transforms the data by using a code table, which provides a set of key/value pairs. If the value being converted matches a key in the given code table, that value is converted to the value associated with that key. Also specify:
-
Code Table Name —Specify the name of a table that contains key/value data.
-
Code Table Key Column —Specify the name of the field in that table that contains the keys.
-
Code Table Value Column —Specify the name of the field in that table that contains the associated values.
Available for string fields. This function transforms the data by using a SQL expression. Also specify SQL Expression as a SQL expression that returns a single value, such as an expression that uses SQL functions, operators, or conditional expressions. You must delimit any field name that is a SQL reserved word. For information about supported SQL syntax and functions, see SQL FunctionsOpens in a new tab.
Within the expression, you can reference any column in the staging table by using the alias staging.
For example, the following expression concatenates the values of the FirstName_ and LastName_ columns into a single string:
staging.FirstName_ || ' ' || staging.LastName_
Available for date fields. This function adds a random but consistent number of days (between –30 and 30) to the field. The shift is consistent for a given value, preserving relative intervals and seasonality while obscuring exact dates. After this transformation, original values cannot be recovered within Data Studio.
Available for string fields. This function applies a deterministic cryptographic hash function to a value. This transformation is irreversible unless a separate re-identification mapping is maintained.
Available for string, boolean, and numeric fields. Returns the specified number of characters (provided in Left Count) from the leftmost characters of the field value. If Left Count exceeds the length of the value, the full value is returned. Also specify:
-
Left Count — Specify an integer that will be used to determine the number of characters returned from the starting position of the value.
Available for string fields. This function converts the string to lowercase.
This transformation obscures parts of values for partial visibility. It is not join-safe, as it may create duplicates, and should not be used for primary join keys or identifiers when deterministic joins are required. Also specify:
-
Masking Type —Specify the type of masking you would like to be applied to your string. Select one of the following: Default, which masks all characters with a Masking Character, which you will specify after selecting this option, Random Numbers, which masks each number with a random number, Email, which only shows the first letters of the username and domain, or Partial, which shows first prefix characters followed by custom padding followed by last suffix characters, which you will specify after selecting this option.
Available for numeric fields. This function adds small random noise to numeric values.
Available for string fields. This function performs a substring replacement. Also specify:
-
Old Substring — Type the text to be replaced.
-
New Substring — Type the replacement text.
Available for string, boolean and numeric fields. Returns the specified number of characters (provided in Right Count) from the rightmost characters of the field value. If Right Count exceeds the length of the value, the full value is returned. Also specify:
-
Right Count — Specify an integer that will be used to determine the number of characters returned from the starting position of the value.
Available for numeric fields. This function rounds the number as specified. Also specify Scale, the number of decimal places.
Available for string fields. This function trims whitespace (leading, trailing, or both). Select Trim Leading Whitespace, Trim Trailing Whitespace, or both.
Available for string fields. This function trims a specified set of characters (leading, trailing, or both). Specify one or more characters for Characters To Trim. Then select Trim Leading Characters, Trim Trailing Characters, or both.
Available for string fields. This function converts the string to uppercase.
Deleting Transformations and Transformation Groups
You can delete individual transformations or entire transformation groups within a transformation activity. This is distinct from deleting a transformation activity.
Deleting a transformation removes that transformation from its group. If a transformation group contains no remaining transformations, the group is deleted automatically.
Deleting a transformation group removes all transformations defined for the field.
If a transformation group is configured to write to a result field, deleting the group also removes that field from the staging table.
