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.