Version 22.0.8483

• Creating a Transformation
• Understanding the Transformations Detail Page
  • Task Tab
  • Triggers Tab
  • Notifications Tab
  • Logging & History Tab
• Sample Query for All Databases

An SQL transformation processes SQL queries midstream (ETL) in a pipeline. This type of transformation processes external SQL scripts or SQL queries that you create in an SQL editor. You can use these queries to insert, delete, update, and retrieve rows from a database. The SQL transformation processes the queries, returning rows and database errors.

Creating a Transformation

To add a new transformation:

1. Click Create Transformation. This action opens the Create New Transformation modal.

2. Enter a transformation name and choose an associated connection.

   

3. Click Create to continue.

Understanding the Transformations Detail Page

The Transformations detail page consists of two sections:

• Connection: Displays the connection, which cannot be modified.

• Job Settings: Consists of four tabs, which are outlined in the following sections.

Task Tab

The Task tab enables you to manage the queries that execute as part of your transformations. Click Configure to enter a transformation. You can enter multiple statements, each separated with a semicolon (;). When you enter multiple statements, the statements are parsed into separate tasks that run sequentially. Logs are written for each statement that is executed.

Triggers Tab

The Triggers tab enables you set transformations to run on a schedule or after a specific job completes. When you select the After Job setting, choose which job should trigger the transformation and (optionally) choose a list of tasks within that job. If you do not select any tasks, the transformation runs after the job completes. If you select one or more tasks, the transformation runs after the job completes, only if any new data is replicated to the destination.

Notifications Tab

The Notifications tab enables you to configure Sync to configure transformations to send email notifications on completion of a job. For more information on setting up Email notifications and configuring your mail server, see Notifications.

Logging & History Tab

The Logging & History tab enables you to monitor current transformation runs and to review the previous runs. See Logging and History for more information about what information each level of verbosity contains and how to retrieve your log files.

Sample Query for All Databases

The following SQL query deduplicates records:

CREATE OR REPLACE TABLE dbo.Accounts_Deduplicated as (
  SELECT *, row_number() over (partition by id order by modified desc) as duplicate_rank 
  FROM dbo.Accounts
);	
DELETE FROM dbo.Accounts_Deduplicated	WHERE duplicate_rank > 1;