Transformations

Version 24.2.8969


Transformations


CData Sync offers two types of transformations:

Both ELT and ETL involve the same three steps:

  • Extract: Raw data is extracted from the source system. In ETL transformations, the data is then moved to a staging area.

  • Transform: The data is cleaned and mapped.

  • Load: The data is copied into the target system.

While the steps remain essentially the same between the two processes (with some differences), the order in which those steps are performed differs.

Once you understand the basic concepts and tasks related to transformations, you can move on to more sophisticated features. You implement these features via settings from the Transformations page by selecting Edit from the options menu () that corresponds to a specific transformation. This option opens a Settings page with these tabs:

  • Overview

  • Queries (for SQL transformations only)

  • Transformations History

These tabs, described in the following sections, offer many robust features that enable you to customize and enhance the capabilities of your transformations.

Overview Tab

The Overview tab contains six settings categories:

  • Last Run: This category displays the date and time of the last transformation run, whether the run was successful, the amount of time the run took, and the number of rows that were affected.

  • Next Run: This category displays the date and time of the next run.

  • Settings: For SQL transformations, this category displays current settings for transformation type, logfile verbosity, and the command timeout for destination connections. For dbt Cloud transformations, this category displays settings for transformation type, the dbt job, threads, and the command timeout. For more details about settings, see “Settings Category” on the page that is appropriate for your transformation type: SQL Transformations or DBT Transformations.

  • Trigger: This category provides the Edit Trigger dialog box, which enables you to define various trigger settings. For more details and steps to configure these settings, see “Trigger Category” on the page that is appropriate for your transformation type: SQL Transformations or DBT Transformations.

  • Notifications: This category provides the Edit Notification dialog box, which enables you to request that Sync notify you via email after a transformation run is completed. For more details and steps to configure notifications, see “Notifications Category” on the page that is appropriate for your transformation type: SQL Transformations or DBT Transformations.

  • Labels: This category enables you to add or select one or more labels to attach to a transformation.

For more details about the Overview tab, see the page that is appropriate for your transformation type: SQL Transformations or DBT Transformations.

Queries Tab

This tab, which is available for SQL transformations only, enables you to add and manage queries that execute as part of your transformations. For more details about the Queries tab, see SQL Transformations.

Transformations History Tab

This tab provides details about your transformation job run, includng run data, status, records affected, and run time. From this tab, you can also filter your report and export the history. For more details about the Transformations History tab, see the page that is appropriate for your transformation type: SQL Transformations or DBT Transformations