Create Transformations

How to create Transformations

====================================

Click here to watch

1. Click "Transformations"

Access the Transformations feature.

Click 'Transformations'

2. Create a New Model

Models are a meant to represent a grouping of logical transformation schema

Create a New Model

3. Enter a unique name to identify your model and click "Create"

Finalize the creation by clicking "Create".

Enter a unique name to identify your model and click 'Create'

4. You should now be able to see the newly created model

Click the model to see all the transformations within it

You should now be able to see the newly created model. Click the model to see all the transformations within it

5. Once clicked, you should see all the transformations within the model

In this situation, we haven't created any transformations so it will be empty

Once clicked, you should see all the transformations within the model

6. Click "New Transformation" to create a new transformation within the model

Click "New Transformation" to create a new transformation within the model

Click 'New Transformation' to create a new transformation within the model

7. You should see a list of all eligible source connections and transformations on the left hand side of the page

At the moment, only PostgreSQL and MySQL are supported but we will add support for other sources soon

You should see a list of all eligible source connections and transformations on the left hand side of the page

8. Enter a unique name for the transformation

In this example, we will name the transformation as "payments".

Enter a unique name for the transformation

9. Type your SQL statement as a CTE

Type your SQL statement as a CTE. In order to reference a source connection, you MUST use the source designation e.g. {{ source('jaffleshop', 'payments') }}

Type your SQL statement as a CTE

10. Click "Compile" to run the query

Compile the transformation.

Click 'Compile' to run the query

11. Check the format of your results

If your SQL compilation is successful, you should see a preview of your results. Note: Context Data will only return 5 results to the view

Check the format of your results

12. You can add Description to explain what your transformation does (optional)

Provide a description for the transformation.

You can add Description to explain what your transformation does (optional)

13. Click "Save Transformation"

Save the transformation.

Click 'Save Transformation'

14. You should be redirected back to the transformations page.

Click your model and should see the newly created model

You should be redirected back to the transformations page. Click your model and should see the newly created model

15. You can create multiple transformations within your model

In this example, we will create another transformation

You can create multiple transformations within your model

16. We're going to create a new transformation called "orders"

Create a new transformation

We're going to create a new transformation called 'orders'

17. Enter the SQL query

Just the first transformation, we're going to enter the SQL query to create the transformation

Enter the SQL query

18. Click "Compile"

Compile the transformation.

Click 'Compile'

19. Click "Save Transformation"

If your SQL compilation is successful, you should now be able to save the transformation.

Click 'Save Transformation'

20. If we click our model this time, we should see 2 transformations which includes the newly created "orders" transformation

View all transformations within the model

If we click our model this time, we should see 2 transformations which includes the newly created 'orders' transformation

21. Let's create a dependent (downstream) transformation

In the previous examples, we created SQL transformations from source connection data. Now let's use these transformations to create another transformation

Let's create a dependent (downstream) transformation

22. Enter a transformation name. In this case, we will name this transformation as "customerpayments"

Name the transformation as "customerpayments".

Enter a transformation name. In this case, we will name this transformation as 'customerpayments'

23. Enter the SQL statement for the downstream transformation

In order for us to reference a previously created transformation, make sure to use the ref designation e.g. {{ ref('jaffleshop', 'payments') }}. In this example, you can see that we are referencing both the previously created "payments" and "orders" transformation

Enter the SQL statement for the downstream transformation

24. Click "Compile"

Compile the transformation.

Click 'Compile'

Compile the transformations.

Click 'Compile'

25. You should see that Context data correctly compiled the new SQL statement and correctly referenced the previously created transformations.

View results

You should see that Context data correctly compiled the new SQL statement and correctly referenced the previously created transformations.

26. We're going to add a description to this transformation

Describe the transformation.

We're going to add a description to this transformation

27. Click "Save Transformation"

Save the transformation.

Click 'Save Transformation'

28. If we click our model once more, we should see all transformations including the newly created transformation

View all created trasformations

If we click our model once more, we should see all transformations including the newly created transformation