Create a View in Snowflake using a Task

In this guide, we'll walk through how to create a Snowflake view within your Ascend Flow, using a Task Component. Task Components allow you to run generic SQL or Python code to carry out common tasks, such as creating views, creating or running stored procedures, or even making calls to APIs.

Prerequisites

• An existing Ascend project (see: Create a Project)
• An existing Ascend Workspace (see: Setting Up a Workspace)
• An existing Ascend Profile within your project that has a Snowflake dataplane (see: Setting Up a Profile)
• An existing Ascend Flow within your project that has at least one Read Component or Transform Component

Step 1: Navigate to your Flow folder.

1. From your Ascend Instance Homepage, click on your Workspace to open it.
2. From here, click on the Files icon on the left navigation bar. This will open the File Tree in the left panel.
3. In your File Tree, locate the Ascend project in which you would like to create the task component.
4. Within the project folder, navigate into the flows folder, and then into the folder for the flow where you would like to add the SQL task.
5. Right-click on the components folder and select New file.
6. Provide the file with a name and a .sql.jinja extension, such as my_task.sql.jinja.

Step 2: Build your Task Component

1. An empty file will open in the build panel. This is where you will define your SQL. Below is an example of a Snowflake View in Ascend.

my_project/flows/my_flow/components/my_task.sql.jinja

{{
  config(type="generic")
}}

CREATE OR REPLACE VIEW {{this}} AS
SELECT
    c.driver_id,
    YEAR(s.review_datetime) AS review_year,
    MONTH(s.review_datetime) AS review_month,
    COUNT(DISTINCT s.ride_id) AS num_rides,
    AVG(s.review_sentiment) AS avg_sentiment
FROM
    {{ ref('cab_rides') }} c
    INNER JOIN {{ ref('review_sentiment') }} s
    ON c.ride_id = s.ride_id
GROUP BY 1, 2, 3
ORDER BY avg_sentiment DESC

Let's dig into the above code:

1. We added a config block at the top of the file, and within this, we set the component type to generic. This tells Ascend that this is a generic SQL task and we should not expect to return any data from this component.
2. The SQL statement itself is a simple CREATE VIEW statement that joins two input components together, cab_rides and review_sentiment, and aggregates the data. These input components are referenced using the {{ref('component_name')}} syntax.
3. We also specify the view name to be {{this}}, which sets the view name to be the same as the component name. So if our task component is my_task.sql.jinja, our code will create a view called my_task. It should be noted that you do not need to specify {{this}}; you can simply enter the name of the view you would like to create.

You have now configured your task component to create a Snowflake view! Generic tasks are powerful tools that give you a wide range of capabilities within your Ascend Flow to meet all your ETL needs.

Next Steps:

1. Learn how to run your flow to see the view you have created.
2. Learn how to create a Python Task to run a Python script within your flow.
3. Learn how to create and run a stored procedure using a BigQuery SQL Task.