SQL Transformations

​

Creating an SQL Transformation

1. Open and navigate to the Transformations page.
2. Select Add Transformations > Add New Transformation.
3. Fill out the fields in the Add Transformation dialog box, as follows:
   • Name: Enter a name for your transformation.
   • Type: Select SQL as the transformation type.
   • Destination: Select a destination on which to run the transformation.
4. Click Add Transformation.

​

Specifying SQL Transformation Settings

• Overview
• Queries
• Comments

​

Overview Tab

​

Settings Category

1. Select … > Edit at the end of the transformation row. This action opens the Edit Settings dialog box.
2. (Optional) Select Enabled if you want to retry the transformation if one or more tasks fail. Only failed tasks are re-executed.
3. Select a level for Logfile Verbosity. The default level is Info.
4. Enter a timeout value in the Command Timeout field.
5. Click Save.

​

Trigger Category

1. Click Configure in the Triggers category. This action opens the Trigger dialog box.
2. Specify the type of trigger that you want. You can select from the following types:
   • None—Turns off scheduling options, allowing you to start a transformation manually.
   • Scheduled—Enables you to set a specific schedule by which to run your transformation. This setting also activates the Intervals and Minutes Past The Hour fields. You can choose more granular settings for the Intervals setting:
     • Minute—Activates the Every option where you can specify increments in minutes (every 10, 15, 20, or 30 minutes) in which to run your transformation.
     • Hourly—Activates a Minutes Past The Hour field so you can set your transformation to run hourly at specific numbers of minutes past the hour (0-59). For example, you might want to run the transformation every hour at 15 minutes past the hour.
     • Daily—Activates a Time of Day field so you can set the specific time each day that you want your transformation to run.
     • Weekly—Activates two fields (Days and Time of Day) where you can specify a specific day of the week and time to run your transformation.
     • Monthly—Activates Day and Time fields. However, for Day, you choose a specific day of each month (values of 1 through 31 or Last Day) along with the time.
     • Cron—Activates a Cron Statement field where you can write a cron job to initiate a transformation run.
     The Scheduled type is useful when you run multiple jobs. You cannot use the After Job option for multiple jobs because you cannot specify after which job to run the transformation.
   • After Job—Activates a Job drop-down list that presents a list of all available jobs. You can choose the specific job after which you want the transformation to run. You can also specify various tasks to run on your data by clicking Select Specific Tasks under the Job field. Simply select the task or tasks that you want to run.
3. Click Save.

​

Labels Category

1. Click Add Labels in the Labels category. This action opens the TransformationName - Add Label dialog box. You can search and select a label currently available in the list or you can add a new label.
2. To add a new label, enter the label in the Search or Add Label text box and click Add. Then, the new label appears in the list of available labels, as shown below.
3. Click Save.

​

Notifications Category

1. Select … > Edit at the end of the transformation row for which you want to set alerts.
2. Click Configure in the Notifications category.
3. Set the following options in the Notifications dialog box:
   • To—The email to which to send alerts.
   • Subject—The subject for alert emails.
   • Errors Only—An option that you can select to specify that alert emails should be sent only when the job encounters an error. If you do not select this option, sends alerts after each job run.
4. Click Save.

​

Queries Tab

​

Adding a Query

1. Click Add Query to open the Add Query dialog box.
2. Enter your query in the text box. If you have multiple statements, make sure that you separate each statement with a semicolon.
3. Click Add Query. If the addition of the query is successful, displays a message saying it is successful at the top of the Queries page.

​

Editing a Query

1. Select … > Edit at the end of the query row.
2. Modify your query, as necessary, in the Edit Query dialog box.
3. Click Save.

​

Transformations History Tab

​

Comments Tab

This tab records and tracks notes that are related to your transformation. Each comment appears with a timestamp. You can change the sort order based on timestamp by using the drop-down menu in the top-right corner, with options for Newest First (default) or Oldest First. You can also edit a comment or delete it by clicking, respectively, the Edit Comment icon () or the Remove Comment icon () at the end of the row.

​

Sample Query for All Databases

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;