Snowflake SQL Tutorial 43 - WITH & CTEs in Snowflake - The Same Query, Read Top to Bottom Instead of Inside Out

Snowflake SQL · Episode 43

In this post we are going to cover the WITH clause in Snowflake, and the thing it creates, which is called a common table expression, or a CTE.

A CTE is a derived table with its name written at the top of the query instead of being buried in the middle of it. We write WITH, a name, AS, and then the subquery in brackets. After that the main query just refers to the name.

We will start by writing the same query both ways, so you can see that nothing about the answer changes. Then we will use several CTEs at once, make one read another to build a pipeline, check that pipeline a step at a time, and reference one CTE twice, which is the thing a derived table cannot do. At the end we will find out what happens when the statement finishes.

Every query below was run on a real Snowflake account, and every result you see is the output it gave back.

Episode 43 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

On this page

1. Let's create some sample data
2. Let's write the same query both ways
3. Now let's write more than one CTE
4. Now let's make one CTE read another
5. Let's check the pipeline one step at a time
6. Now let's reference the same CTE twice
7. Can we use a CTE in the next statement?
8. Let's name the columns up front
9. So what does WITH give us?

Let's create some sample data

The first two things we try use EMPLOYEES and DEPARTMENTS. There are eight employees across four departments.

After that we move to CUSTOMERS and ORDERS, the shared sample tables for this course, joined on CUSTOMER_ID.

Sample data

-- The tables this episode queries. EMPLOYEES comes from Episode 1 and
-- DEPARTMENTS from Episode 24; both are reproduced here so you can paste this
-- into a worksheet and follow along. CUSTOMERS and ORDERS, used from section 3
-- onwards, are the shared sample tables for this course.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

CREATE OR REPLACE TABLE DEPARTMENTS (
    DEPT_ID    INT,
    DEPT_NAME  VARCHAR,
    LOCATION   VARCHAR,
    BUDGET     NUMBER(12,2)
);

INSERT INTO DEPARTMENTS VALUES
    (10, 'Engineering', 'Charlotte', 2400000.00),
    (20, 'Finance',     'Charlotte', 1100000.00),
    (30, 'Marketing',   'Raleigh',    750000.00),
    (40, 'Operations',  'Raleigh',    640000.00);

CREATE OR REPLACE TABLE EMPLOYEES (
    EMP_ID          INT,
    FIRST_NAME      VARCHAR,
    LAST_NAME       VARCHAR,
    EMAIL           VARCHAR,
    DEPT_ID         INT,
    MANAGER_ID      INT,
    SALARY          NUMBER(10,2),
    COMMISSION_PCT  NUMBER(4,2),
    HIRE_DATE       DATE
);

-- Two rows to remember for later: Sofia, the lowest-paid person here, and
-- Grace, on the last line, whose DEPT_ID is NULL.
INSERT INTO EMPLOYEES VALUES
    (1, 'Aisha',  'Khan',      'aisha.khan@example.com',      10,    6, 118000.00, NULL,  '2019-03-11'),
    (2, 'Marcus', 'Reid',      'marcus.reid@example.com',     10,    6,  96500.00, NULL,  '2021-07-01'),
    (3, 'Lena',   'Fischer',   'lena.fischer@example.com',    20,    6, 104000.00, 0.05,  '2018-01-22'),
    (4, 'Tomas',  'Novak',     'tomas.novak@example.com',     20,    3,  87250.00, NULL,  '2022-09-05'),
    (5, 'Priya',  'Raman',     'priya.raman@example.com',     30,    6,  96500.00, 0.10,  '2020-11-17'),
    (6, 'Daniel', 'O''Connor', 'daniel.oconnor@example.com',  10, NULL, 131000.00, NULL,  '2016-05-30'),
    (7, 'Sofia',  'Marino',    'sofia.marino@example.com',    30,    5,  71400.00, 0.08,  '2023-02-13'),
    (8, 'Grace',  'Lin',       'grace.lin@example.com',     NULL,    6,  92000.00, NULL,  '2021-04-19');

Two rows in EMPLOYEES are worth noting now, because they explain an absence later on. Sofia is the lowest-paid person in the table. Grace has a NULL DEPT_ID.

Let's write the same query both ways

The last post ended on a derived table that was becoming awkward to read. Here it is again. We group the employees by department, count and sum, and join that to DEPARTMENTS for the name.

Derived table

SELECT D.DEPT_ID, D.DEPT_NAME, S.HEADCOUNT, S.PAYROLL
FROM DEPARTMENTS D
JOIN (SELECT DEPT_ID, COUNT(*) AS HEADCOUNT, SUM(SALARY) AS PAYROLL
      FROM EMPLOYEES GROUP BY DEPT_ID) S
  ON S.DEPT_ID = D.DEPT_ID
ORDER BY D.DEPT_ID;

Result3 rows · 4 columns

DEPT_ID	DEPT_NAME	HEADCOUNT	PAYROLL
10	Engineering	3	345500.00
20	Finance	2	191250.00
30	Marketing	2	167900.00

Three rows, and there is nothing wrong with the answer. What is awkward is the reading. We start at the top, hit an open bracket in the FROM clause, and have to hold the outer query in our head while we work out what the inner one produces.

Now let's write the same thing with a WITH clause. The word WITH, a name, AS, and the identical subquery in brackets. Then a main query that simply joins to the name.

The same thing as a CTE

WITH DEPT_STATS AS (
  SELECT DEPT_ID, COUNT(*) AS HEADCOUNT, SUM(SALARY) AS PAYROLL
  FROM EMPLOYEES
  GROUP BY DEPT_ID
)
SELECT D.DEPT_ID, D.DEPT_NAME, S.HEADCOUNT, S.PAYROLL
FROM DEPARTMENTS D
JOIN DEPT_STATS S ON S.DEPT_ID = D.DEPT_ID
ORDER BY D.DEPT_ID;

Result3 rows · 4 columns

DEPT_ID	DEPT_NAME	HEADCOUNT	PAYROLL
10	Engineering	3	345500.00
20	Finance	2	191250.00
30	Marketing	2	167900.00

The same three rows, the same values, the same column headers. Nothing about the answer changed. Only where we read the subquery.

Here are the two side by side.

	Derived table	CTE
Where the subquery is written	Inside the FROM clause	At the top, before the main query
Reading order	Inside out — find the brackets first	Top to bottom
Does it have a name?	Yes, an alias — required	Yes, and the name comes before the query, not after it
Can the main query reference it more than once?	No — you would write the subquery out again	Yes
Can a second one read the first?	Only by nesting it inside	Yes, by name

No performance claim

This page makes no claim about which of the two runs faster, because nothing here was timed. The argument for WITH on this page is entirely about reading and about the two capabilities in the last rows of that table.

Now let's write more than one CTE

A single WITH clause can define several CTEs. We write WITH once and separate the definitions with commas. There is no second WITH.

Here one CTE counts the orders per customer and the other totals them, and the main query joins CUSTOMERS to both.

Query

WITH ORDER_COUNTS AS (
  SELECT CUSTOMER_ID, COUNT(*) AS N_ORDERS FROM ORDERS GROUP BY CUSTOMER_ID
),
ORDER_TOTALS AS (
  SELECT CUSTOMER_ID, SUM(AMOUNT) AS TOTAL FROM ORDERS GROUP BY CUSTOMER_ID
)
SELECT C.CUSTOMER_NAME, N.N_ORDERS, T.TOTAL
FROM CUSTOMERS C
JOIN ORDER_COUNTS N ON N.CUSTOMER_ID = C.CUSTOMER_ID
JOIN ORDER_TOTALS T ON T.CUSTOMER_ID = C.CUSTOMER_ID
ORDER BY T.TOTAL DESC;

Result8 rows · 3 columns

CUSTOMER_NAME	N_ORDERS	TOTAL
Northwind Traders	9	61660.00
Meridian Health	5	56800.00
Alpine Logistics	5	36565.00
Vantage Media	3	15870.00
Kestrel Analytics	5	15635.25
Harbour Foods	4	9076.25
Blue Ridge Supply	6	6060.50
Delta Print Co	4	3760.00

Eight rows, one per customer. This is the same answer as the two derived tables in the last post. What has changed is where the two ideas live. They are at the top now, named, one per line, instead of sitting in the middle of the FROM clause.

The syntax people get wrong

The comma goes between definitions and never before the main query. A trailing comma after the last CTE makes Snowflake expect a third definition and find a SELECT instead.

Now let's make one CTE read another

This is the feature that changes how we write SQL. A CTE can refer to a CTE defined above it. That means a query stops being one expression and becomes a pipeline with named steps.

We are going to write three steps.

• SHIPPED — the orders whose STATUS is 'SHIPPED'.
• PER_CUSTOMER — reads SHIPPED and groups it by customer.
• RANKED — reads PER_CUSTOMER and joins CUSTOMERS for the name and the country.

Each step does one thing, is named for the thing it does, and reads the step before it. The main query then filters the last one.

Query

WITH SHIPPED AS (
  SELECT ORDER_ID, CUSTOMER_ID, ORDER_DATE, AMOUNT
  FROM ORDERS
  WHERE STATUS = 'SHIPPED'
),
PER_CUSTOMER AS (
  SELECT CUSTOMER_ID, COUNT(*) AS N_SHIPPED, SUM(AMOUNT) AS SHIPPED_TOTAL
  FROM SHIPPED
  GROUP BY CUSTOMER_ID
),
RANKED AS (
  SELECT P.*, C.CUSTOMER_NAME, C.COUNTRY
  FROM PER_CUSTOMER P
  JOIN CUSTOMERS C ON C.CUSTOMER_ID = P.CUSTOMER_ID
)
SELECT CUSTOMER_NAME, COUNTRY, N_SHIPPED, SHIPPED_TOTAL
FROM RANKED
WHERE SHIPPED_TOTAL > 5000
ORDER BY SHIPPED_TOTAL DESC;

Result6 rows · 4 columns

CUSTOMER_NAME	COUNTRY	N_SHIPPED	SHIPPED_TOTAL
Northwind Traders	USA	9	61660.00
Meridian Health	USA	5	56800.00
Alpine Logistics	NULL	4	29665.00
Vantage Media	UK	3	15870.00
Kestrel Analytics	Canada	4	14135.25
Harbour Foods	Canada	3	6826.25

Six rows. Every step of that is readable in order, and nothing is nested inside anything.

One customer, Alpine Logistics, has a NULL COUNTRY. The pipeline never touches that column, so the NULL travels through it untouched.

That last clause, WHERE SHIPPED_TOTAL > 5000, is doing something we should not have to take on trust. So let's check it.

Let's check the pipeline one step at a time

Each step of a chained WITH is an ordinary query, so we can run each one on its own. Here is what the first step selects, counted two ways.

Step one, on its own

SELECT COUNT(*) AS SHIPPED_ORDERS, COUNT(DISTINCT CUSTOMER_ID) AS CUSTOMERS
FROM ORDERS WHERE STATUS = 'SHIPPED';

Result1 row · 2 columns

SHIPPED_ORDERS	CUSTOMERS
34	8

Thirty-four shipped orders, spread across eight customers. That is the input to step two.

And here is step two. Those orders grouped by customer, with the counts and totals that the filter will be applied to.

Step two, on its own

SELECT CUSTOMER_ID, COUNT(*) AS N_SHIPPED, SUM(AMOUNT) AS SHIPPED_TOTAL
FROM ORDERS WHERE STATUS = 'SHIPPED'
GROUP BY CUSTOMER_ID
ORDER BY SHIPPED_TOTAL DESC;

Result8 rows · 3 columns

CUSTOMER_ID	N_SHIPPED	SHIPPED_TOTAL
101	9	61660.00
107	5	56800.00
105	4	29665.00
109	3	15870.00
103	4	14135.25
104	3	6826.25
102	4	4230.50
106	2	3120.00

Eight rows, one per customer, which matches the eight customers counted in the step-one grid. Eight rows is the number going into the filter.

The filter is SHIPPED_TOTAL > 5000. Read the SHIPPED_TOTAL column in that grid from the bottom up and two values fall below it. Customer 106 on 3120.00, and customer 102 on 4230.50. Every other value in the column is above 5000.

Eight rows in, two dropped, six rows out, and six rows is exactly what the pipeline returned. Both grids are on this page, so that is arithmetic you can redo rather than a claim you have to accept.

Why it is worth naming the steps

This is the real argument for naming your steps. When a pipeline gives you a row count you did not expect, you can copy any single CTE's body into a worksheet and run it. With a stack of nested derived tables you have to unpick the brackets first.

Now let's reference the same CTE twice

Here is the thing a derived table cannot do without being written out twice.

One CTE, DEPT_AVG, holds the average salary per department. The main query uses it in the join, to put each person's department average beside them. Then it uses it again, further down, in the WHERE clause, for the smallest of those averages.

Query

WITH DEPT_AVG AS (
  SELECT DEPT_ID, AVG(SALARY) AS AVG_SALARY
  FROM EMPLOYEES
  GROUP BY DEPT_ID
)
SELECT E.FIRST_NAME,
       E.DEPT_ID,
       E.SALARY,
       A.AVG_SALARY,
       E.SALARY - A.AVG_SALARY AS VS_DEPT
FROM EMPLOYEES E
JOIN DEPT_AVG A ON A.DEPT_ID = E.DEPT_ID
WHERE E.SALARY > (SELECT MIN(AVG_SALARY) FROM DEPT_AVG)
ORDER BY VS_DEPT DESC;

Result6 rows · 5 columns

FIRST_NAME	DEPT_ID	SALARY	AVG_SALARY	VS_DEPT
Daniel	10	131000.00	115166.66666667	15833.33333333
Priya	30	96500.00	83950.00000000	12550.00000000
Lena	20	104000.00	95625.00000000	8375.00000000
Aisha	10	118000.00	115166.66666667	2833.33333333
Tomas	20	87250.00	95625.00000000	-8375.00000000
Marcus	10	96500.00	115166.66666667	-18666.66666667

Six rows, sorted by how far above their own department's average each person is. VS_DEPT is positive at the top and negative at the bottom. The name DEPT_AVG appears in two places in the query while the averaging is written once.

Six rows out of eight employees, so who is missing?

Two people from our sample data are not in that grid, and they are absent for two different reasons.

Missing	Why	Which part of the query removed them
Grace	Her DEPT_ID is NULL, so there is no department average to match her to	The JOIN — an inner join, so no match means no row
Sofia	Her salary is below the lowest department average in the grid above, 83950.00000000	The WHERE clause — the second reference to DEPT_AVG

Both of those are checkable on this page. Grace's NULL department and Sofia's salary are in the INSERT in our sample data. The lowest AVG_SALARY is in the grid just above. It belongs to Priya's department, and it is the smallest value in that column.

Why one reference matters

Writing that average subquery twice instead of naming it once is not just more typing. It is two copies of the same expression that somebody can edit independently, at which point the query filters against one number and reports another.

Can we use a CTE in the next statement?

No, and this is the one people get wrong. A CTE exists for one statement. Not for the session, not for the worksheet, not until we drop it. The statement ends and the name is gone.

The query we just ran defined DEPT_AVG and finished successfully. Here is the very next statement asking for it.

The next statement — this one fails

SELECT * FROM DEPT_AVG;

Query failed — Snowflake returned an error

002003 (42S02): SQL compilation error:
Object 'DEPT_AVG' does not exist or not authorized.

DEPT_AVG was never a thing on disk and never a thing in the session. It was a name, valid inside the brackets of one query. Snowflake is reporting exactly what it would report for any identifier we had not created.

Here is how a CTE compares with the two things people usually mistake it for.

	Lives for	Created by	Needs dropping?
CTE	One statement	WITH	No — it was never created
Temporary table	The session	CREATE TEMPORARY TABLE	No, but it occupies the session until it ends
View	Until dropped	CREATE VIEW	Yes

That is the right way to decide between them. If two separate statements need the same intermediate result, a CTE cannot give it to us. It is the wrong tool and the error above is what we get. If one statement needs it, in two places or twenty, a CTE is the whole answer.

Let's name the columns up front

One small feature to finish with. We can declare a CTE's column names in brackets after its name. Then the subquery does not have to name them at all. No AS aliases, and no expression text leaking into the headers.

Query

WITH TOTALS (WHO, HOW_MANY, HOW_MUCH) AS (
  SELECT CUSTOMER_ID, COUNT(*), SUM(AMOUNT) FROM ORDERS GROUP BY CUSTOMER_ID
)
SELECT * FROM TOTALS ORDER BY HOW_MUCH DESC;

Result8 rows · 3 columns

WHO	HOW_MANY	HOW_MUCH
101	9	61660.00
107	5	56800.00
105	5	36565.00
109	3	15870.00
103	5	15635.25
104	4	9076.25
102	6	6060.50
106	4	3760.00

Eight rows under the headers WHO, HOW_MANY and HOW_MUCH. The subquery selects CUSTOMER_ID, COUNT(*) and SUM(AMOUNT) with nothing named, and the column list supplies all three names positionally.

Watch out

Positionally is the thing to be careful about. The names are matched to the subquery's columns by order, not by any resemblance, so reordering the SELECT list later relabels the output without producing an error. The count has to match, though — too few or too many names and the statement fails.

And this grid names the customers that dropped out

This query aggregates all the orders, not just the shipped ones. So HOW_MANY sums to 41. Add the column up and compare it with the thirty-four shipped orders we counted earlier. Seven orders in this table are not shipped.

This grid is also keyed by CUSTOMER_ID. The grid where we used two CTEs at once is the same aggregation keyed by CUSTOMER_NAME. Both are sorted by the same descending total, and the totals match row for row.

So line the two up on the total and 102 is Blue Ridge Supply and 106 is Delta Print Co. Those are the two customers whose shipped totals fell below the pipeline's filter.

So what does WITH give us?

A CTE is a name for a query, written before the query that uses it. There is nothing else to it mechanically, and that is why the list of what it buys us is short and worth memorising.

1. The answer does not change. The reading order does — we ran the same query both ways at the start of this post and both grids came back identical.
2. One WITH, as many CTEs as you like, separated by commas.
3. A later CTE can read an earlier one by name, which is how a query becomes a pipeline you can check a step at a time.
4. The main query can reference the same CTE twice, written once. A derived table cannot.
5. It lasts for one statement. The next statement does not know the name.

And one thing that is not on that list: speed. Nothing on this page was timed, so this page does not tell you a CTE is faster, slower, or materialised. It reads better. That is the claim being made.

Key points

• A CTE is a derived table with its name written at the top of the query instead of after a bracket in the middle of it. The result is identical — this post runs the same query both ways and shows both grids.
• Write WITH once and separate the definitions with commas. There is no second WITH and no comma before the main query.
• A later CTE can read an earlier one by name, which turns a query into a pipeline of named steps.
• Every step is an ordinary query, so you can run any one of them on its own — which is how the filter in this post is checked rather than asserted.
• The main query can reference the same CTE more than once, written once. A derived table has to be spelled out again each time.
• A CTE is not a temporary table. It lives for one statement, and the next statement gets Object does not exist or not authorized.
• You can declare the column names up front with WITH NAME (A, B, C) AS (...). They are matched to the subquery's columns by position.
• An inner join inside a CTE pipeline drops unmatched rows exactly as it would anywhere else — a NULL key still means no match.
• Nothing here is a performance claim. The argument for WITH is readability and the two things a derived table cannot do.

Next in this series

Episode 44 — recursive CTEs. A CTE that refers to itself. The next post runs the recursion by hand, one step at a time, so you can watch the rows arrive in the order Snowflake finds them.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the demo script for this episode, including the compilation error in section 7. The EMPLOYEES, DEPARTMENTS, CUSTOMERS and ORDERS data is invented demo data created by that script. No performance or benchmark claims are made anywhere in this series. Clause behaviour follows the Snowflake CTE documentation.