This video provides a brief overview of what Wrangler is and what you can do with it.

We highly recommend you follow this quick tutorial before you start exploring on your own.

The video will walk you through building your first Wrangler job and explain the core principles in detail. You can then read additional details in the rest of this guide.

CloverDX Wrangler uses concepts of data sources and data targets to define where the data comes from and where it is written at the end of the transformation. To learn more about targets and their configuration, see target file configuration.

A data source is the location from where your data is loaded. All sources that you can work with are listed in the Data Catalog and in My Sources section.

Data Catalog provides an overview of the sources published by your organization - this is where you can find your source systems like CRM, data warehouse, and anything else your organization published.

My Sources section allows you to see all sources that you’ve created and configured in the past.

Data sources can be created in multiple ways:

See data sources for more information.

Each job must have one main source and it can also optionally include any number of additional sources which are used in lookups.

To add ("join") additional lookup sources to your job, follow these steps:

If you need to add multiple lookups each with a different lookup data source, repeat the above process as many times as necessary.

Instead of manipulating data directly and potentially doing something you might regret in the future, Wrangler records the steps you’re taking and then plays them back on the source data when the job is executed. Source data is never modified in any way.

You can always go back to change, remove, disable, or insert steps in your job to revert or modify the actions you’ve taken in the past.

Example: We initially deleted 'Profit margin' but later realized we might be needing it.

We can remove Step 2 (Delete column 'Profit margin') to bring the column back to our dataset as if we never deleted it in the first place. All subsequent actions will still be applied.

Tip: Steps are your safeguard against making mistakes. Any decision you’ve made in the past can be reverted or modified.

Disable a step if you don’t want to remove it just yet. Disabled steps won’t have any effect on the data and will stay in the job until you delete them.

See Using Steps sidebar for more information.

Before using the Add column step, note that there are steps that add a new column automatically:

In both scenarios, you don’t need to explicitly add a new column beforehand - both the Lookup and Calculate formula steps add columns for you.

All columns in CloverDX Wrangler have a type which defines what kind of data can be stored in the column - whether it is a number, date or text. Types are either automatically detected (for example when reading from a CSV file) or they are provided by the data source (when reading from a data source connector from the Data Catalog).

The data type for each column is indicated by an icon in the column header with additional information available in the tooltip shown when hovering over a column header. The following table shows how different types are visualized in the data preview.

For more information about data types, refer here.

Since having the correct data type is essential when working with your data, it is important to review the loaded data. If the type is not the type you would expect, you can convert column to a different type by using one of the conversion steps:

Formulas can be entered in the following steps:

Formulas are also used in step and group conditions. For more information, see Step and group conditions.

In your formulas, you will need to specify the column(s) that you want to work with. A column is referenced by its technical column name - a name that is automatically created by Wrangler by stripping various special characters from the column label (e.g., a column with the label Invoice amount (USD) will have the technical name $Invoice_amount_USD). Technical column names always include the $ sign to make it easy to distinguish from other names that can appear in formulas.

To find and easily copy the technical column name, hover over a column header.

Technical column names are case-sensitive and need to be entered in the exact form as displayed. If you mistype a technical column name, you will get an error like this:

Error: Field '$invoice_amount_USD' does not exist in record 'invoices_csv'

You can also use the autocomplete functionality in Formula Editor, which automatically displays a list of all existing technical column names after you type the $ character. Alternatively, you can display the list of all technical column names and all available functions by using the Ctrl + Space shortcut.

In many transformations you’ll need to test your data and use different values in your formula depending on the result of the test. Wrangler formulas offer two ways of doing this - either with if function or with ternary operator.

String is one of the most common types of data. Wrangler supports quite a few steps that work with string data - see more details in Text manipulation steps.

When working with string values in formulas, you have two options of how to write them - either with double quotes or with single quotes. The following represent the same strings in your formulas:

String columns can have two different types of empty values (null values shown as No value and an empty string). See No value: Working with null and empty values for more information.

When working with strings, string comparisons are sensitive to white-spaces and letter case. So "TREE" is not the same as "tree" and "blue car" is not the same as "blue    car" (multiple spaces between words).

For a list of string functions refer here.

Currency is represented as a decimal column with corresponding formatting on a column.

To see the values in their unformatted form, set the display format to No specific format.

Things to bear in mind when working with decimals:

Wrangler defines a single date type which stores both date and time with millisecond precision. If you wish to write date value in a formula, you can use syntax yyyy-MM-dd for dates or yyyy-MM-dd HH:mm:ss.SSS for full date and time.

For example, these are all valid date values:

You can use this syntax when writing date and time values in your formulas or in steps that require you to enter a date value (e.g., Replace empty value step).

When working with dates, you can take advantage of one of the following steps:

You can also extract the whole part of a date using one of the following functions in the Calculate formula step step:

Errors (or invalid values) occur when the underlying data doesn’t match the column data type, or when you use validation steps to find values that do not match your criteria.

To correct errors:

For more information on error handling refer to the Error handling in Wrangler section.

Depending on your target configuration, all rows where a data error is found are either rejected and output to a reject file when you run the job, or the job fails on the first error.

If you don’t see the data you expect, it might be caused by the limited size of the preview data. This is not an error, and you can continue working, only with limited visibility into the preview data.

Run the job to process and see all the data in the result.

How to work around the preview size limitation:

The No value label (in italics) indicates that a cell is empty (will show as empty in the target, depending on how it handles empty values). In formulas, No value is represented with the null keyword (case sensitive).

Note that for string columns there are two different empty values - a null value, which comes up as No value in fields, and an empty string (i.e., a string with zero length), which comes up as an empty field in Wrangler. They do not represent the same data and when writing your formulas you’ll need to make sure you handle both cases if needed. To work with empty strings, use two straight (double or single) quotes: "" or ''

In formulas you can use function isBlank which can determine if a string is empty - it will return true for strings that are null, empty or contain only whitespaces.

Example to remove null values:

Example to remove empty string values:

In many cases you may want to replace empty value of given column with a default value. This can be done via Replace empty values step. This step will allow you to use single value instead of all empty values in selected column.

Note that this step will correctly handle string data - both null and empty string will be replaced with the value you provide.

If you wish to dynamically compute the new value, you will have to use Calculate formula step. The best way to do this is to use if function which has the following syntax:

The function returns value_if_true if the condition is satisfied (i.e., if the condition evaluates to true for given row). This is what you can effectively use to replace empty values. For example, to use USD as a default currency, you can do this:

isBlank function is a CTL function that returns true if a string value is null, empty string or contains just spaces. If the condition is not met, original value remains in the column.

if function also has a second variant with one extra parameter:

See below for more details on how to use if function.

To filter your data, use the Filter rows based on formula step.

To get the complete results from your job, you first have to run the job via the Run button either from the job editor or from My Jobs page. Running the job will process all the data from the data source rather than just a sample.

Then depending on the data target, you will either be able to download the resulting file or the data will be written directly into the target. You can see the difference on the screenshot below - the first job called CompanyA payments to Excel uses Excel data target and therefore produces a file you can download via Download result button.

The second job uses a data target from Data Catalog. This target does not produce a file but rather writes the data into a Snowflake database (a target from Data Catalog can write to any system). As such, there is no file to download and you will need to use external tools to check the results in your Snowflake instance.

Note that in both cases you can download a reject file. This can downloaded from a status dialog shown when you click on the orange "rejected rows" link. See more details about job status and the status dialog here.

Data target defines the location where the data is written to. When a new job is created, a CSV target file is automatically generated and assigned as the target. The assigned data target can be changed as needed; for more information on how to update the target in an existing job, refer here.

A target can be:

To change your data target settings, click on the Target button in the overview diagram of your job at the top of the transformation editor screen or click on the icon which appears when you hover over the Target step.

Your currently used target will be automatically selected and its details displayed on the right side. To change the configuration of your target, click on the Edit button next to Configuration.

To select a different target, select the desired target from the list, click on the Select ⟶ button, and confirm your changes.

CSV and Excel target files can be downloaded from the My Jobs page once a job has finished successfully. Data target connectors do not generate an output file and write data directly into the configured target within the connector (e.g., a database or a third-party interface like Hubspot or Xero).

See Data targets for more information on how to add targets to My Targets or how to change your target configuration.

Targets created from the Data Catalog may require specific data formats. These targets may have required and optional columns that you can map into when building your data transformation. Jobs that use such targets will show a special Mapping step at the end of the step list in job editor.

This mapping step can be configured in a special Mapping view. This view offers functionalities to map either constant values or columns from your data preview to the target system. By creating a "link" between these elements, you essentially define how your data will be written into the corresponding target columns.

To learn more about how to work with your data in the Mapping mode, refer to Target mapping section.