# CData Sync: In-Flight (ETL) Transformation Library

**URL:** <https://community.cdata.com/t/cdata-sync-in-flight-etl-transformation-library/354>\
**Category:** Getting started\
**Tags:** article, cdata-sync\
**Created:** [August 23, 2023, 3:54pm UTC](https://community.cdata.com/t/cdata-sync-in-flight-etl-transformation-library/354 "2023-08-23T15:54:12Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![jerodj-cdata](https://sea3.discourse-cdn.com/cdata/user_avatar/community.cdata.com/jerodj-cdata/32/7_2.png) [@jerodj-cdata](https://community.cdata.com/u/jerodj-cdata)\
**Post date:** [August 23, 2023, 3:54pm UTC](https://community.cdata.com/t/cdata-sync-in-flight-etl-transformation-library/354/1 "2023-08-23T15:54:13Z")

</div>

CData Sync supports both extract-transform-load (ETL) and extract-load-transform (ELT) transformations for data pipelines. A built-in transformation library allows organizations to easily mask personally identifiable information, handle NULL values, and more. In this article, we describe the pre-defined transformations available and explain how to apply an in-flight transformation to a column.

## Pre-built SQL transformations&nbsp;

CData Sync includes a library pre-defined SQL transformations to make in-flight transformations of your data easier than ever.

### MASK(expression, mask character [, start\_index [,end\_index]])&nbsp;

Replaces the characters between start\_index and end\_index with the mask\_character within the string.

- **string\_expression** : The string expression to be searched.
- **mask\_character** : The character to mask with.
- **start\_index** : The optional number of characters to leave unmasked at beginning of string. Defaults to 0.
- **end\_index** : The optional number of characters to leave unmasked at end of string. Defaults to 0.

```auto
SELECT MASK('1234567890','*',); -- Result: '**********' SELECT MASK('1234567890','*', 4); -- Result: '1234******' SELECT MASK('1234567890','*', 4, 2); -- Result: '1234 **** 90' 

```

### TRIM(trimspec trimchar FROM string\_value)&nbsp;

Returns the character expression with leading and/or trailing blanks removed.

- **trimspec** : Optional. If included must be one of the keywords BOTH, LEADING or TRAILING.
- **trimchar** : Optional. If included should be a one-character string value.
- **string\_value** : The string value to trim.

```auto
SELECT TRIM(' trimmed '); -- Result: 'trimmed' SELECT TRIM(LEADING FROM ' trimmed '); -- Result: 'trimmed ' SELECT TRIM('-' FROM '-----trimmed-----'); -- Result: 'trimmed' SELECT TRIM(BOTH '-' FROM '-----trimmed-----'); -- Result: 'trimmed' SELECT TRIM(TRAILING '-' FROM '-----trimmed-----'); -- Result: '-----trimmed'

```

### ISNULL ( check\_expression , replacement\_value )&nbsp;

Replaces null with the specified replacement value.

- **check\_expression** : The expression to be checked for null.
- **replacement\_value** : The expression to be returned if check\_expression is null.

```auto
SELECT ISNULL(42, 'Was NULL'); -- Result: 42  SELECT ISNULL(NULL, 'Was NULL'); -- Result: 'Was NULL' 

```

### UPPER ( character\_expression )&nbsp;

Returns the character expression with lowercase character data converted to uppercase.

- **character\_expression** : The character expression.

```auto
SELECT UPPER('MIXED case'); -- Result: 'MIXED CASE' 

```

### LOWER ( character\_expression )&nbsp;

Returns the character expression with the uppercase character data converted to lowercase.

- **character\_expression** : The character expression.

```auto
SELECT LOWER('MIXED case'); -- Result: 'mixed case' 

```

## Applying an in-flight transformation&nbsp;

After [creating a replication job](https://www.cdata.com/kb/articles/sync-getting-started.rst), you can apply transformations, including renaming the destination table, destination columns, and modifying the values before they are loaded into the destination.

To apply a transformation:

1. Click on a job from the **Jobs** page.

2. On the job detail page, navigate to the **Task** tab and click on a table.

3. On the table detail page, navigate to the **Columns** tab and click **Edit Mapping**.

4. Click the Options menu (**…) for a column and select Apply Transformation**.

5. In the **Transform Source** window, edit the column directly or apply any of the pre-defined SQL transformations. The example below masks the first 12 characters of the Receipt\_ID with an asterisk ( **\*** ).

6. Click **Done**.

### Free trial & more information&nbsp;

Visit our [CData Sync page](https://www.cdata.com/sync) to read more information about CData Sync. To try out the transformation library, [download a free 30-day trial](https://www.cdata.com/sync/download/)! As always, our world-class [Support Team](https://www.cdata.com/support/submit.aspx) is ready to answer any questions you may have.