Version 23.4.8843

• Differences between ETL and ELT
• Renaming Tables
• Mapping Columns
  • Renaming Columns
  • Applying SQL Transformations
  • Adding Columns
  • Removing Columns

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

Renaming 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 on the Task tab, 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 Columns tab provides control 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:

1. Select a task. Then select … > Edit.

2. Click the Columns tab. Then, click Edit Mapping.

3. Select the column row that you want to edit. Then select … > Edit Destination.

4. Edit the column names in the Edit Destination Column dialog box and click Done.

Applying SQL Transformations

To apply an SQL transformation:

1. Select the column row that you want and select … > Apply Transformation.

2. Click the Apply Transformation button to choose from a list of transformations or to apply a function of your choice. Then, click Done to save your selection.

Adding Columns

To add a column to your destination table:

1. Select a column row and click the edit icon at the end of the row.

2. Click the Columns tab. Then, click Edit Mapping.

3. Click the add icon (+) to open the Add Column dialog box

   

4. Add column names for your source and destination. Then click Add Column.

Removing Columns

To remove a column, clear the checkbox to the left of the row that you want to remove. That row becomes inactive, which indicates that the column is removed from your query and will not be replicated.