Version 22.0.8483

• Differences between ETL and ELT
• Mapping Tables
• Mapping Columns
  • Renaming Columns
  • Applying Functions
  • Adding Columns
  • Concatenating Columns
  • Removing Columns
  • Setting Filters

Extract, Transform, Load (ETL) is a method of data extraction in which data is moved from a source and transformed in-flight (midstream) before delivery to its destination.

Differences between ETL and ELT

Traditionally, ETL has been the most popular way to move and transform data. However, with the rise of cloud computing and the greatly expanded storage capacity of databases, ETL is being overtaken gradually by Extra, Load, Transform (ELT) as the preferred method of data delivery and transformation.

The primary difference between the ETL and ELT is the order in which the steps take place. Howver, ELT also has other benefits:

• Performance: The larger storage limits of modern databases enable ELT to move the entirety of updated data before implementing changes. Because the data does not need to be cleaned or modified ahead of time, the speed and efficiency of the transformation is greatly increased when compared to traditional ETL methods.

• Simplicity: In situations where you need to move a large amount of data, ELT streamlines and simplifies the transformation process.

There are still cases where you might want to modify data before pushing it to the destination. In such cases, CData Sync makes it easy to perform the transformations.

Mapping Tables

In moving data from the source, Sync automatically creates a table in the destination. Alternatively, you can map to an existing table by clicking the table name in Task Settings, selecting Map to Existing Table from the drop-down menu, and choosing the destination table. Sync attempts to map the columns automatically via same names. You can then navigate into the Column Mapping section and make any adjustments.

Mapping Columns

The Column Mapping tab gives you more agency over which data to retrieve from your source table and how you want that data to be represented in the target table. From here, you can remove or adjust column mappings, rename destination columns, apply a transformation on the source, and more, as explained in the following sections.

Renaming Columns

To rename a column, click the destination column name. Then, you can edit the destination column name as you see fit. To save your change and continue, click OK.

Applying Functions

To apply a function, click the drop-down menu and choose from the available source columns. Choosing the edit icon opens the Edit Column modal and enables you to apply SQL functions to your source column.

Adding Columns

1. Click Add Column to create a new column in your destination table.

2. In the new modal, assign a value to the source column, which can be either a literal, an existing source column, or a transformation of source columns.

3. Name the destination column and click OK to add the new column to your mapping.

Concatenating Columns

You can concatenate columns by applying the CONCAT function, as described in the section Apply a Function.

The following example shows how to map the concatenation of the FirstName and LastName source columns into the FullName target column:

Removing Columns

To remove a column, hover over the right-arrow icon of the row that you want to remove and click the X. The source column then displays Select…, which signals that the column has been removed from your query and will not be replicated.

Setting Filters

You can filter your transformation by setting various options, as explained below:

• Click Add rule to create a new condition. Choose the column, operation, and value to add that condition to the query.

• Click Add group to create a new group. A group of conditions is contained within parentheses when they are added to your query.

• Toggle AND/OR to flip the operators that are used to combine multiple conditions, groups, or both.

• Click Delete to remove a condition or group from your filter.