Create & Manage Transformations using SQL

Click on the Transformations link in the Sidebar as shown below:

transformations 1

Adding a new Transformation

Pre-requisites for creating Data Transformation

  • The Data Warehouse where you intend to execute the transformation should be added to your account, Click here to learn how.

  • Keep the SQL Script you wish to execute as part of the transformation ready. Please test the script thoroughly to ensure it works as required.

  • Ensure the user configured as part of the Data Warehouse connection has the requisite rights to run the script on the destination.

Step by Step Process

Step 1

Go to the first pane which is the SQL Transformations Tab. You will be able to view the configured SQL transformations list.

sql transformations 1
Step 2

Click on the Create New button to create a new transformation for applying to your data in the data warehouse.

sql transformations 2
Step 3

Enter a name for the transformation. Please choose a unique name and ensure the name has no spaces and special characters except for an Underscore (_).

transformations add 1
Step 4

Choose the Data Warehouse where you would want the transformation script to run.

transformations add 2
Step 5

Type in the SQL query or script you would want to execute. In case of multiple SQL statements in the script ensure each statement is terminated by a semi-colon(;)

DataChannel does not parse the SQL query for logical or semantic correctness and just executes the provided SQL script on the schedule. Please check the SQL scripts carefully before configuring the transformations.
transformations add 3
Step 6

Choose dependencies if you would want your transformation to run only when a data pipeline has successfully executed.

transformations add 4
Step 7

Select a schedule for the pipeline if needed and click on Save button to save this transformation.

Viewing/Editing your SQL Transformation after configuration

  1. Once you have finished configuring your SQL Transformation, you will be redirected back to the main transformation screen where you can view a list of configured SQL transformations. On the screen, you will notice an actions column wherein you can see an Edit icon.

    sql transformations 3

  2. Clicking on the Edit icon will redirect you to the following ‘Edit Transformation’ screen. You will notice that the screen is divided into three side-by-side tabs namely, ‘Setup’, ‘Last Run Details’ and ‘Change History’.

  3. The ‘Setup’ Tab allows you to change your warehouse, edit the transformation and the scheduling for the transformation:

    sql transformations 4

  4. The ‘Last Run Details’ Tab displays the execution details of the configured SQL Transformation in reverse chronological order in a tabular format. You can view the status of run from the ‘Status’ column.

    sql transformations 5

  5. The ‘Change History’ Tab displays the details of changes made to the configuration/scheduling of your SQL Transformation in reverse chronological order.

sql transformations 6

Still have Questions?

We’ll be happy to help you with any questions you might have! Send us an email at info@datachannel.co.

Subscribe to our Newsletter for latest updates at DataChannel.