CTEs and subqueries

CTEs and Subqueries in Snowflake: A Complete Guide for SnowPro Core Certification

Introduction

Common Table Expressions (CTEs) and subqueries are fundamental SQL constructs that every Snowflake professional must master. Understanding these concepts is essential for the SnowPro Core certification exam and for writing efficient, maintainable queries in production environments.

What Are CTEs?

A Common Table Expression (CTE) is a temporary named result set that exists only within the scope of a single SQL statement. CTEs are defined using the WITH clause and can be referenced multiple times within the main query.

CTE Syntax:

WITH cte_name AS (
  SELECT column1, column2
  FROM table_name
  WHERE condition
)
SELECT * FROM cte_name;

What Are Subqueries?

A subquery is a query nested inside another query. Subqueries can appear in the SELECT, FROM, WHERE, or HAVING clauses. They are enclosed in parentheses and execute before the outer query.

Types of Subqueries:

1. Scalar Subqueries - Return a single value
2. Row Subqueries - Return a single row with multiple columns
3. Table Subqueries - Return multiple rows and columns
4. Correlated Subqueries - Reference columns from the outer query

Why Are CTEs and Subqueries Important?

• Code Readability: CTEs break complex queries into logical, named components
• Reusability: CTEs can be referenced multiple times in a single query
• Maintenance: Easier to debug and modify compared to deeply nested subqueries
• Performance: Snowflake's optimizer handles both constructs efficiently
• Recursion: CTEs support recursive queries for hierarchical data

How CTEs Work in Snowflake

When you execute a query with a CTE, Snowflake:
1. Parses the CTE definition
2. Evaluates the CTE as part of query optimization
3. May materialize the CTE results or inline them based on the optimizer's decision
4. Executes the main query using the CTE results

Multiple CTEs Example:

WITH sales_2023 AS (
  SELECT product_id, SUM(amount) as total_sales
  FROM sales
  WHERE year = 2023
  GROUP BY product_id
),top_products AS (
  SELECT product_id
  FROM sales_2023
  WHERE total_sales > 10000
)
SELECT p.name, s.total_sales
FROM products p
JOIN sales_2023 s ON p.id = s.product_id
WHERE p.id IN (SELECT product_id FROM top_products);

Recursive CTEs

Snowflake supports recursive CTEs for processing hierarchical or graph-like data structures.

WITH RECURSIVE hierarchy AS (
  SELECT employee_id, manager_id, name, 1 as level
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.manager_id, e.name, h.level + 1
  FROM employees e
  JOIN hierarchy h ON e.manager_id = h.employee_id
)
SELECT * FROM hierarchy;

CTEs vs Subqueries: Key Differences

| Aspect | CTEs | Subqueries |
| Readability | Higher | Lower for complex queries |
| Reusability | Can reference multiple times | Must repeat the query |
| Recursion | Supported | Not supported |
| Scope | Entire statement | Limited to placement location |

Performance Considerations

• Snowflake's query optimizer treats CTEs and subqueries similarly
• The optimizer may choose to materialize or inline CTEs based on cost analysis
• Neither approach is inherently faster; performance depends on the specific query
• Use EXPLAIN to analyze query execution plans

Exam Tips: Answering Questions on CTEs and Subqueries

1. Know the WITH Clause Syntax: Exam questions often test proper CTE syntax, including the placement of commas between multiple CTEs

2. Understand Recursive CTE Requirements: Remember that recursive CTEs require the RECURSIVE keyword and must have an anchor member and a recursive member joined by UNION ALL

3. Recognize Scope Limitations: CTEs are only valid within the statement where they are defined; they cannot be referenced in subsequent statements

4. Compare Readability Scenarios: When asked which approach is better for complex multi-step transformations, CTEs are typically the preferred answer due to improved readability

5. Watch for Correlated Subquery Questions: Understand that correlated subqueries execute once for each row in the outer query, which can impact performance

6. Remember CTE Naming Rules: CTE names follow standard Snowflake identifier rules and cannot duplicate names within the same WITH clause

7. Know Where Subqueries Can Appear: Be prepared to identify valid subquery placements in SELECT, FROM, WHERE, and HAVING clauses

8. Practice Converting Between Forms: Some questions may ask you to identify equivalent queries written as CTEs versus subqueries

9. Understand Materialization: Remember that Snowflake decides whether to materialize CTE results; this is not something you control with syntax

10. Focus on Use Cases: Recursive CTEs are specifically designed for hierarchical data traversal - this is a common exam topic

Common Exam Question Patterns

• Identifying syntax errors in CTE definitions
• Choosing between CTEs and subqueries for specific scenarios
• Understanding recursive CTE behavior and termination conditions
• Recognizing when a subquery returns a scalar value versus a table
• Determining the scope and lifetime of CTE definitions

Summary

Mastering CTEs and subqueries is crucial for both the SnowPro Core exam and practical Snowflake development. CTEs offer superior readability and support recursion, while subqueries provide flexibility in query construction. For the exam, focus on syntax, scope rules, recursive CTE requirements, and understanding when each approach is most appropriate.