What is ETL?
ETL is a method of data extraction in which data is moved from a source and transformed “in-flight” before delivery to its destination. ETL gets its name from its three steps: Extract, Transform, and Load. Traditionally, ETL has been the most popular way to move and transform data. Limited database storage capacity effectively prevented data from being moved in its entirety before the transformation process. With greatly expanded storage capacity and the rise of cloud computing, ETL is gradually being overtaken by ELT (Extract, Load, Transform) as the preferred method of data delivery and transformation. There are still some cases where you may want to modify data before pushing it to the destination. CData Sync makes it easy to perform some of these transformations.
When moving data from the source, Sync will automatically create a table in the destination. Alternatively, you can map to an existing table by clicking the table name in the Task Settings, selecting “Map to Existing Table” from the dropdown menu, and choosing the destination table. Sync will attempt to auto-map the columns via same names. You can then navigate into the Column-Mapping section and make any adjustments.
The Column-Mapping tab gives you more agency over which data to retrieve from your source table and how you’d like that data to be represented in the target table. From here, you’re able to remove or adjust column mappings, rename destination columns and apply a transformation on the source.
Renaming a Column
To rename a column, click the the destination column name. You can then edit the destination column name as you see fit. To save your change, click OK to continue.
Apply a Function
To apply a function, select the dropdown menu and choose from the available source columns. Choosing the edit icon will open the Edit Column modal and allow you to apply SQL Functions to your Source Column.
Adding a Column
- Select the Add Column button to create a new column in your Destination Table.
- 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 Column(s).
- Name the Destination Column and Select OK to add the New Column to your mapping.
You can concatenate columns by applying the CONCAT function, as described above in the Apply a Function section.
The below example shows how to map the Concatenation of the FirstName and LastName Source Columns into the FullName Target Column:
Removing a Column
To remove a column, hover over the right-arrow icon of the row you’d like to remove and click the X. The Source Column will show “Select…”, signaling that the column has been removed from your query and will not be replicated.
- Select the Add rule button to create a new condition. Choose the Column, Operation, and Value to add that condition to the query.
- Select the Add group to create a new group. A group of conditions is contained within parentheses when added to your query.
- Toggle AND/OR to flip the operators used to combine multiple conditions and/or groups.
- Select Delete to remove a condition or group from our filter