Create & Manage Transformations using SQL
Click on the Transformations link in the Sidebar as shown below:
Pre-requisites for creating Data Transformation
- Step 1
Go to the first pane which is the SQL Transformations Tab. You will be able to view the configured SQL transformations list.
- Step 2
Click on the Create New button to create a new transformation for applying to your data in the data warehouse.
- 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 (_).
- Step 4
Choose the Data Warehouse where you would want the transformation script to run.
- 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.
- Step 6
Choose dependencies if you would want your transformation to run only when a data pipeline has successfully executed.
- Step 7
Select a schedule for the pipeline if needed and click on Save button to save this transformation.
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.
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’.
The ‘Setup’ Tab allows you to change your warehouse, edit the transformation and the scheduling for the transformation:
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.
The ‘Change History’ Tab displays the details of changes made to the configuration/scheduling of your SQL Transformation in reverse chronological order.
We’ll be happy to help you with any questions you might have! Send us an email at firstname.lastname@example.org.
Subscribe to our Newsletter for latest updates at DataChannel.