CData Sync: In-Flight (ETL) Transformation Library

[jerodj-cdata]

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 

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]]) 

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.

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

TRIM(trimspec trimchar FROM string_value) 

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.

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 ) 

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.

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

UPPER ( character_expression ) 

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

• character_expression: The character expression.

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

LOWER ( character_expression ) 

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

• character_expression: The character expression.

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

Applying an in-flight transformation 

After creating a replication job, 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 

Visit our CData Sync page to read more information about CData Sync. To try out the transformation library, download a free 30-day trial! As always, our world-class Support Team is ready to answer any questions you may have.