Why Common Table Expressions (CTEs) in Snowflake? - Purpose & Use Cases

The Big Idea

What if you could write complex queries step-by-step without repeating yourself or getting lost in messy code?

The Scenario

Imagine you need to analyze sales data by first filtering recent orders, then calculating totals, and finally joining with customer info. Doing this step-by-step means writing long, repeated queries that are hard to read and fix.

The Problem

Manually repeating the same subqueries makes your SQL messy and slow to update. If you make a mistake, you must fix it in many places. It's like copying the same recipe multiple times and risking typos everywhere.

The Solution

Common Table Expressions let you name and save intermediate query results. You write each step once, then build on it clearly. This makes your SQL easier to read, maintain, and debug--like organizing your recipe into clear, reusable parts.

Before vs After

✗ Before

SELECT * FROM (SELECT * FROM orders WHERE date > '2024-01-01') o JOIN customers c ON o.customer_id = c.id

✓ After

WITH recent_orders AS (SELECT * FROM orders WHERE date > '2024-01-01') SELECT * FROM recent_orders o JOIN customers c ON o.customer_id = c.id

What It Enables

CTEs unlock clear, stepwise query building that saves time and reduces errors in complex data tasks.

Real Life Example

A data analyst quickly finds monthly sales trends by defining filtered sales data once, then reusing it to calculate totals and join with product info--all in one clean query.

Key Takeaways

CTEs let you break complex queries into named, reusable parts.

This makes SQL easier to read, write, and fix.

They help avoid repeating code and reduce mistakes.