How to Perform Common Table Expressions (CTEs) in the Snowflake Data Plane:

Introduction

If you need to recursively select data from a table in a database to infer dependencies, you can use a Common Table Expression (CTE). In the Snowflake Data Plane, you can perform a CTE by following these step-by-step instructions. We've provided sample code and data.

Step 1: Create a Table in Snowflake

To create a table, you can use the "create or replace table" command. In this step, you will create a table named "tmp_employees" with three columns: title, employee_id, and manager_id.

create or replace table tmp_employees (title varchar, employee_id integer, manager_id integer);

Step 2: Insert Data into Snowflake Table

To insert data into the "tmp_employees" table, you can use the "insert into" command. In this step, you will insert data for six employees.

insert into tmp_employees (title, employee_id, manager_id) 
values('President', 1, null), -- The President has no manager.
('Vice President Engineering', 10, 1),
('Programmer', 100, 10),
('QA Engineer', 101, 10),
('Vice President HR', 20, 1),
('Health Insurance Analyst', 200, 20);

Step 3: Create a Read Connector in Ascend with the Table Data

To create a read connector, you need to first create a warehouse, a database, and a schema. Then, in the Ascend UI, click on "Connections" and select "Snowflake" as your data source. Enter your credentials and select the warehouse, database, and schema you created. Finally, select the "tmp_employees" table as your data source.

Step 4: Create a Snowflake Transform Component in Ascend

To create a transform component, select the read connector you just created and click on "Add Transform". Choose "Snowflake" as your transform type and enter your Snowflake credentials again. Then, write the SQL query for your CTE in the "Query" field.

Step 5: Write the CTE Query

To write your CTE query, you can use the "with recursive" syntax. In this step, you will use a CTE to find all employees who report to the "Vice President Engineering".

Step 6: Save and Run the Transform

Once you have written your CTE query, click on "Save" to save the transform component. Then, click on "Run" to execute the query and view the results.

Conclusion

Performing a Common Table Expression (CTE) in Snowflake Data Plane involves creating a table, inserting data into the table, creating a read connector with the table data, creating a Snowflake transform component, and writing a CTE query using the "with recursive" syntax. With these steps, you can recursively select data from a table to infer dependencies in your data.