Common Table Expressions (CTEs) in Snowflake - Step-by-Step Execution

Process Flow - Common Table Expressions (CTEs)

Start Query

↓

Define CTE with AS

↓

CTE Executes and Stores Result

↓

Main Query Uses CTE Result

↓

Return Final Result

↓

End Query

The query starts by defining a CTE, which runs first and stores its result. Then the main query uses this stored result to produce the final output.

Execution Sample

Snowflake

WITH recent_orders AS (
  SELECT order_id, customer_id, order_date
  FROM orders
  WHERE order_date > '2024-01-01'
)
SELECT * FROM recent_orders;

This query defines a CTE named recent_orders that selects orders after January 1, 2024, then selects all rows from this CTE.

Process Table

Step	Action	Query Part	Result Description
1	Start Query	WITH clause	Begin processing the query with CTE definition
2	Execute CTE	recent_orders CTE	Select orders where order_date > '2024-01-01' from orders table
3	Store CTE Result	recent_orders	Temporary result set with filtered orders stored
4	Execute Main Query	SELECT * FROM recent_orders	Retrieve all rows from the stored CTE result
5	Return Result	Final output	Output all recent orders matching the condition
6	End Query	Query complete	Query execution finished

💡 Query ends after main SELECT uses the CTE result

Status Tracker

Variable	Start	After Step 2	After Step 3	Final
recent_orders	undefined	CTE query result set	Stored temporary table with filtered orders	Used in main query to output rows

Key Moments - 3 Insights

Why does the CTE run before the main query?

The CTE executes first to prepare a temporary result set that the main query can use, as shown in execution_table step 2 and 3.

Can the main query modify the CTE data?

No, the CTE result is read-only for the main query. It acts like a temporary snapshot, as seen in step 4 where data is only selected.

What happens if the CTE returns no rows?

The main query will return no rows because it selects from the empty CTE result, as implied in step 4.

Visual Quiz - 3 Questions

Look at the execution_table, what happens at step 3?

A The main query starts execution

B The query ends

C The CTE result is stored temporarily

D The orders table is updated

💡 Hint

Refer to execution_table row with Step 3 describing storing the CTE result

At which step does the main query use the CTE result?

A Step 2

B Step 4

C Step 1

D Step 6

💡 Hint

Check execution_table row Step 4 where main query selects from recent_orders

If the CTE query returned no rows, what would the main query output be?

A No rows

B An error

C All rows from orders table

D Only one row

💡 Hint

See key_moments explanation about empty CTE results affecting main query output

Concept Snapshot

Common Table Expressions (CTEs) in Snowflake:
- Use WITH clause to define a CTE
- CTE runs first and stores a temporary result
- Main query uses this result as a table
- Helps organize complex queries
- CTE result is read-only and temporary

Full Transcript

This visual execution shows how Common Table Expressions (CTEs) work in Snowflake. The query starts by defining a CTE using the WITH clause. The CTE runs first, selecting filtered data from the orders table. This result is stored temporarily. Then the main query selects all rows from this stored CTE result. The query ends after returning the final output. Key points include that the CTE executes before the main query, the main query cannot modify the CTE data, and if the CTE returns no rows, the main query returns no rows as well.

Practice

1. What is the main purpose of using a WITH clause in Snowflake SQL?

easy

A. To set session variables

B. To permanently store data in a new table

C. To create a user-defined function

D. To define a temporary named result set for use in a query

Solution

1. Step 1: Understand the role of the WITH clause

   The WITH clause defines a Common Table Expression (CTE), which is a temporary named result set used within a query.

2. Step 2: Differentiate from other SQL features

   Unlike permanent tables or functions, CTEs exist only during query execution and help organize complex queries.

3. Final Answer:

   To define a temporary named result set for use in a query -> Option D

4. Quick Check:

   WITH clause = temporary named result set [OK]

Hint: WITH means temporary named query part [OK]

Common Mistakes:

• Thinking WITH creates permanent tables
• Confusing WITH with functions or variables
• Assuming WITH changes session settings

2. Which of the following is the correct syntax to start a CTE in Snowflake?

easy

A. CREATE CTE cte_name AS (SELECT * FROM table);

B. DEFINE cte_name SELECT * FROM table;

C. WITH cte_name AS (SELECT * FROM table)

D. BEGIN CTE cte_name SELECT * FROM table;

Solution

1. Step 1: Recall the standard CTE syntax

   In Snowflake, a CTE starts with the keyword WITH followed by the CTE name and AS, then the query in parentheses.

2. Step 2: Compare options to syntax

   WITH cte_name AS (SELECT * FROM table) matches this pattern exactly: WITH cte_name AS (SELECT * FROM table)

3. Final Answer:

   WITH cte_name AS (SELECT * FROM table) -> Option C

4. Quick Check:

   CTE syntax starts with WITH [OK]

Hint: CTE always starts with WITH keyword [OK]

Common Mistakes:

• Using CREATE instead of WITH
• Omitting AS keyword
• Using BEGIN or DEFINE which are invalid here

3. Given the query:

WITH cte AS (SELECT 1 AS num UNION ALL SELECT 2) SELECT SUM(num) FROM cte;

What is the output of this query?

medium

A. 3

B. 2

C. 1

D. Error

Solution

1. Step 1: Understand the CTE content

   The CTE named 'cte' selects two rows with values 1 and 2 under the column 'num'.

2. Step 2: Calculate the SUM of 'num'

   The main query sums the values 1 and 2, resulting in 3.

3. Final Answer:

   3 -> Option A

4. Quick Check:

   1 + 2 = 3 [OK]

Hint: Sum values inside CTE then add [OK]

Common Mistakes:

• Thinking UNION ALL removes duplicates
• Confusing SUM with COUNT
• Expecting syntax error due to CTE

4. Identify the error in the following Snowflake query using a CTE:

WITH cte AS SELECT * FROM employees SELECT * FROM cte;

medium

A. Missing parentheses around the CTE query

B. CTE name cannot be 'cte'

C. CTE cannot be used in the same query

D. Missing AS keyword before SELECT in CTE definition

Solution

1. Step 1: Review CTE syntax requirements

   A CTE query must be enclosed in parentheses after the AS keyword.

2. Step 2: Check the given query

   The query lacks parentheses around the SELECT statement in the CTE definition.

3. Final Answer:

   Missing parentheses around the CTE query -> Option A

4. Quick Check:

   CTE query must be in parentheses [OK]

Hint: CTE query always inside parentheses [OK]

Common Mistakes:

• Omitting parentheses around CTE query
• Thinking CTE names are restricted
• Believing CTEs can't be reused in same query

5. You want to calculate the average salary per department using a CTE named dept_salaries that selects employee salaries and departments. Which query correctly uses the CTE to get the average salary per department?

hard

A. WITH dept_salaries AS SELECT department, salary FROM employees SELECT department, AVG(salary) FROM dept_salaries GROUP BY department;

B. WITH dept_salaries AS (SELECT department, salary FROM employees) SELECT department, AVG(salary) FROM dept_salaries GROUP BY department;

C. WITH dept_salaries (department, salary) AS (SELECT department, salary FROM employees) SELECT AVG(salary) FROM dept_salaries;

D. WITH dept_salaries AS (SELECT department, salary FROM employees) SELECT AVG(salary) FROM employees GROUP BY department;

Solution

1. Step 1: Verify correct CTE syntax and usage

   WITH dept_salaries AS (SELECT department, salary FROM employees) SELECT department, AVG(salary) FROM dept_salaries GROUP BY department; correctly defines the CTE with parentheses and AS, then uses it to select department and average salary grouped by department.

2. Step 2: Check aggregation and grouping

   WITH dept_salaries AS (SELECT department, salary FROM employees) SELECT department, AVG(salary) FROM dept_salaries GROUP BY department; groups by department and calculates AVG(salary) from the CTE, which is the intended calculation.

3. Final Answer:

   WITH dept_salaries AS (SELECT department, salary FROM employees) SELECT department, AVG(salary) FROM dept_salaries GROUP BY department; -> Option B

4. Quick Check:

   CTE with correct syntax and grouping = WITH dept_salaries AS (SELECT department, salary FROM employees) SELECT department, AVG(salary) FROM dept_salaries GROUP BY department; [OK]

Hint: Use WITH ... AS (...) then GROUP BY correctly [OK]

Common Mistakes:

• Omitting parentheses in CTE definition
• Not grouping by department when aggregating
• Using employees table instead of CTE in main query