When you edit your dataset's recipe, the Transformer page is opened, where you begin your wrangling tasks on a sample of the dataset. Through this interface, you build your transformation recipe and see the results in real-time as applied to the sample. When you are satisfied with what you see, you can execute a job against the entire dataset.
Your data transformation is complete when you have done the following:
Tip: Before you begin transforming, you should know the target schema that your transformed data must match. A schema is the set of columns and their data types, which define the constraints of your dataset.
Tip: If you want to match up against the target schema, you can import a dataset to serve as the target schema to which you are mapping. For more information on this advanced feature, see Overview of RapidTarget.
supports the following methods for building recipes. These methods are listed in order of ease of use:
Select something. When you select elements of data in the Transformer page, you are prompted with a set of suggestions for steps that you can take on the selection or patterns matching the selection. You can select columns or one or more values within columns.
Tip: The easiest method for building recipes is to select items in the application. Over time, the application learns from your selections and prompts you with suggestions based on your previous use. For more information, see Overview of Predictive Transformation.
Toolbar and column menus: In the Transformer page, you can access pre-configured transformations through the Transformer toolbar or through the column context menus.
Tip: Use the toolbar for global transformations across your dataset and the column menu for transformations on one or more selected columns.
Search and browse for transformations. Using the Search panel and the Transform Builder, you can rapidly assemble recipe steps through a simple, menu-driven interface. When you choose to add a step, you search for your preferred transformation in the Search panel. When one is selected, the transformation is pre-populated in the Transform Builder for you. See Search Panel.
Tip: Use the Transform Builder for performing modifications to the transformation you selected from the Search panel or a suggestion card. See Transform Builder.
Loading very large datasets in can overload your browser or otherwise impact performance, so the application is designed to work on a sample of data. After you have finished your recipe working on a sample, you execute the recipe across the entire dataset.
The default sample is the first set of rows of source data in the dataset, the number of which is determined by the platform. For smaller datasets, the entire dataset can be used as your sample. In the Transformer page, it's listed as Full Data in the upper-left corner.
In some cases, the default sample might be inadequate or of the wrong type. To generate a new sample, click the name of the sample in the upper-left corner.
NOTE: Collecting new samples requires system resources and storage. In some environments, collecting samples incurs monetary cost.
Tip: You should consider collecting a new sample if you have included a step to change the number of rows in your dataset or have otherwise permanently modified data (keep, delete, lookup, join, or pivot operations). If you subsequently remove the step that made the modification, the generated sample is no longer valid and is removed. This process limits unnecessary growth in data samples.
On the right side of the screen, you can launch a new sampling job on your dataset. For more information, see Samples Panel.
Data cleansing tasks address issues in data quality, which can be broadly categorized as follows:
15in the Temperature field of two different records should not mean Centigrade in one record and Fahrenheit in the other record.
When data is initially imported, it can contain multiple columns, rows, or specific values that you don't need for your final output. Specifically, this phase can involve the following basic activities:
First recipe steps:
When a dataset sample is first loaded into the Transformer page, attempts to split out the unstructured data to form regular, tabular data. If your data appears to contain a header row, it can be used for the titles of the columns.
In the above image, some initial parsing steps have been applied to structure the data into tabular format, but these steps are not added as formal parts of the recipe. They are hidden from view in the recipe.
The data resulting from these initial transforms is displayed in the data grid. See Data Grid Panel.
Use a row to create headers:
In most cases, the names of your columns are inferred from the first row of the data in the dataset. If you need to specify a different row, please complete the following:
Click the Search icon in the menu bar.
In the Search panel textbox, type:
On the left side of the data grid, you might notice a set of black dots. If you hover over one of these, the original row number from the source data is listed. Since the data transformation process can change the number of rows or their order, you might want to retain the original order of the rows.
Tip: Some operations, such as unions and joins, can invalidate source row number information. To capture this data into your dataset, it's best to add this transformation early in your recipe.
To retain the original row numbers in a column called,
rowId, please complete the following:
You can use a similar transformation to generate the full path and filename to file-based sources:
For more information, see Insert Metadata.
Delete unused columns:
Your data might contain columns that are not of use to you, so it's in your interest to remove them to simplify the dataset. To delete a column, click the caret next to the column's title and select Delete.
Tip: If you are unsure of whether to delete the column, you can use the same caret menu to hide the column for now. Hidden columns do appear in the output.
Tip: You can also delete ranges of columns, too. See Remove Data.
Check column data types:
When a dataset is imported, attempts to identify the data type of the column from the first set of rows in the column. At times, however, type inference can be incorrect.
Tip: Before you start performing transformations on your data based on mismatched values, you should check the data type for these columns to ensure that they are correct. For more information, see Supported Data Types.
For more information, see Change Column Data Type.
Display only columns of interest:
You can choose which columns you want to display in the data grid, which can be useful to narrow your focus to problematic areas.
In the Status bar at the bottom of the screen, click the Eye icon.
For more information, see Visible Columns Panel.
Review data quality:
After you have removed unused data, you can examine the quality of data within each column just below the column title.
Column header with data quality bar
The horizontal bar, known, as the data quality bar, identifies the quality of the data in the column by the following colors:
|green||These values are valid for the column data type.|
|red||These values do not match those of the column type.|
|black||There are no values for the column in these rows.|
Tip: When you select values in the data quality bar, those values are highlighted in the sample rows, and suggestions are displayed at the bottom of the screen in the suggestion cards to address the selected rows.
For more information, see Data Quality Bars.
Based on your selections and its knowledge of common data patterns, prompts you with suggested transformations. You can then select pre-configured transformations in the right panel of the Transformer page to quickly add steps.
Tip: Where possible, you should try to create your transforms by selecting data and then selecting the appropriate suggestion card. In some cases, you might need to modify the details of the recipe.
In the following example, the missing values in the
SUBSCRIBER_AGE column have been selected, and a set of suggestion cards is displayed.
Selecting missing values
Tip: When previewing a recipe step, you can use the checkboxes in the status bar to display only affected rows, columns, or both, which helps you to assess the effects of your step.
Depending on the nature of the data, you might want to keep, delete, or modify the values. Since the data is missing, the Delete card has been selected.
For more information, see Explore Suggestions.
For more background information, see Overview of Predictive Transformation.
Change data types:
If a column contains a high concentration of mismatched data (red), the column might have been identified as the wrong data type. For example, your dataset includes internal identifiers that are primarily numeric data (e.g.
10000022) but have occasional alphabetical characters in some values (e.g.
1000002A). The column for this data might be typed for integer values, when it should be treated as string values. For more information on the available types, see Supported Data Types.
Tip: Where possible, you should set the data type for each column to the appropriate type. does maintain statistical information and enable some transformation steps based upon data type. See Column Statistics Reference.
For more information, see Change Column Data Type.
Explore column details:
As needed, you can explore details about the column's data, including statistical information such as outliers.
Just below a column's data quality bar, you can review a histogram of the values found in the column. In the following example, the data histogram on the left applies to the
ZIP column, while the one on the right applies the
Column data histogram
When you mouse over the categories in the histogram, you can see the corresponding value, the count of instances in the sample's column, and the percentage of affected rows. In the left one, the bar with the greatest number of instances has been selected; the value
21202 occurs 506 times (21.28%) in the dataset. On the right, the darker shading indicates how rows with
ZIP=21202 map to values in the
Tip: Similar to the data quality bar, you can click values in a data histogram to highlight the affected rows and to trigger a set of suggestions. In this manner, you can use the same data quality tools to apply even more fine-grained changes to individual values in a column.
For a list of common tasks to cleanse your data, see Cleanse Tasks.
You can create data quality rules to apply to the specifics of your dataset. For example, if your dataset includes square footage for commercial rental properties, you can create a data quality rule that tests the
sqFt field for values that are less than 0. These values are flagged in red in a data quality bar for the rule for easy review and triage.
Tip: Data quality rules are not transformation steps. They can be used to assess the current state of the data and are helpful to reference as you build your transformation steps to clean up the data.
For more information, see Overview of Data Quality.
After you have performed initial cleansing of your data, you might need to perform modifications to the data to properly format it for the target system, specify the appropriate level of aggregation, or perform some other modification.
Tip: Modification steps are often specific to the downstream use-case for the data. If your source dataset needs to satisfy multiple downstream uses, you might need to make modifications to satisfy each use case, which are in conflict with each other. It might be easier to cleanse first, create a reference for the recipe object, and then import the reference dataset in each flow for further modification. For more information, see Flow View Page.
In the following example, the improperly capitalized word
BALTIMORE has been selected, so that you can change it to its propercase spelling (
Baltimore). Those rows are highlighted in the row data, and a set of suggestions for how to fix has been provided in the cards at the bottom of the screen. See Selection Details Panel.
Selecting values to modify
Depending on the nature of your data, you might want to keep or change the values, or you can remove the problematic rows altogether.
Tip: When you select one of the suggestion cards, the implied changes are previewed in the Transformer page, so you can see the effects of the change. This previewing capability enables you to review and tweak your changes before they are formally applied. You can always remove a transform step if it is incorrect or even re-run the recipe to generate a corrected set of results, since source data is unchanged. For more information, see Transform Preview.
In this case, select the Replace transformation. However, there are a couple of minor issues with the provided suggestion.
So, some aspects of this transform must be changed. Click Edit.
When you modify a transform step, you can make changes in the Transform Builder, which is a simple, menu-driven interface for modifying your transformations:
Modifying steps in the Transform Builder
In the Transform Builder, you can replace the pattern with the specific string to locate:
BALTIMORE. The new value, which is currently blank, can be populated with the replacement value:
Baltimore. Click Add.
The step is added to the recipe and automatically applied to the data sample displayed in the Transformer page. For more information, see Transform Builder.
See Cleanse Tasks.
Before you deliver your data to the target system, you might need to enhance or augment the dataset with new columns or values from other datasets.
You can append a dataset of identical structure to your currently loaded one to expand the data volume. For example, you can string together daily log data to build weeks of log information. See Append Datasets.
You can also join together two or more datasets based on a common set of values. For example, you are using raw sales data to build a sales commission dataset:
This commission dataset is created by performing an inner join between the sales transaction dataset and the employee dataset. In the Search panel, enter
join. See Join Data.
In some cases, you might need to include or replace values in your dataset with other columns from another dataset. For example, transactional data can reference product and customer by internal identifiers. You can create lookups into your master data set to retrieve user-friendly versions of customer and product IDs.
NOTE: The reference data that you are using for lookups must be loaded as a dataset into first.
To perform a lookup for a column of values, click the caret drop-down next to the column title and select Lookup.... See Lookup Wizard.
For a list of common workflows to enhance your dataset, see Enrichment Tasks.
The data that you see in the Transformer page is a sample of your entire dataset.
For larger datasets, you must learn how to generate new samples, which can provide different perspectives on your data and, in complex flows, enhance performance.
Tip: Sampling is an important concept in .
For more information, see Sampling Basics.
As part of the transformation process, you can generate and review visual profiles of individual columns and your entire dataset. These interactive profiles can be very helpful in identifying anomalies, outliers, and other issues with your data. For more information, see Profiling Basics.