CData Sync: In-Flight (ETL) Transformation Library

  • 23 August 2023
  • 0 replies

Userlevel 2

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' 


-- 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.  

-- Result: 42 


-- Result: 'Was NULL' 

UPPER ( character_expression ) 

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

  • character_expression: The character expression.  

-- Result: 'MIXED CASE' 

LOWER ( character_expression ) 

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

  • character_expression: The character expression.  

-- 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. 
  1. On the job detail page, navigate to the Task tab and click on a table. 
  1. On the table detail page, navigate to the Columns tab and click Edit Mapping
  1. Click the Options menu (…) for a column and select Apply Transformation.
  2. 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 (*). 
  3. 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.

0 replies

Be the first to reply!