Common Table Expressions (CTEs) and subqueries often produce identical results, but they are not interchangeable in a data engineering context. The choice between them affects readability, debuggability, and in some databases, query performance. Getting this right matters when you are writing transformations that run millions of rows in production.
This tutorial explains the practical differences, when each approach is better, and the advanced CTE patterns data engineers use most.
The Dataset
CREATE TABLE orders (
order_id INT,
customer_id INT,
product_id INT,
amount DECIMAL(10,2),
order_date DATE,
status VARCHAR(20)
);
CREATE TABLE customers (
customer_id INT,
name VARCHAR(100),
region VARCHAR(30),
segment VARCHAR(20)
);
CREATE TABLE products (
product_id INT,
product_name VARCHAR(50),
category VARCHAR(30),
cost DECIMAL(10,2)
);
-- Sample data
INSERT INTO customers VALUES
(201, 'Alice', 'North', 'Enterprise'),
(202, 'Bob', 'South', 'SMB'),
(203, 'Carol', 'North', 'Enterprise'),
(204, 'David', 'East', 'SMB');
INSERT INTO products VALUES
(1, 'Laptop', 'Hardware', 800.00),
(2, 'Phone', 'Hardware', 400.00),
(3, 'License', 'Software', 50.00),
(4, 'Monitor', 'Hardware', 200.00);
INSERT INTO orders VALUES
(1001, 201, 1, 1200.00, '2024-01-05', 'completed'),
(1002, 202, 2, 800.00, '2024-01-07', 'completed'),
(1003, 201, 3, 150.00, '2024-01-10', 'completed'),
(1004, 203, 1, 1200.00, '2024-02-01', 'completed'),
(1005, 204, 4, 350.00, '2024-02-05', 'returned'),
(1006, 202, 1, 1200.00, '2024-02-10', 'completed'),
(1007, 203, 2, 800.00, '2024-02-15', 'completed'),
(1008, 201, 4, 350.00, '2024-03-01', 'completed');
Subqueries — What They Are and When They Work
A subquery is a SELECT statement nested inside another query. There are two types that matter in data engineering work:
Inline subquery (in FROM clause):
SELECT region, total_revenue
FROM (
SELECT c.region, SUM(o.amount) AS total_revenue
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.status = 'completed'
GROUP BY c.region
) AS region_revenue
WHERE total_revenue > 1000;
Correlated subquery (references outer query):
SELECT
o.order_id,
o.amount,
(SELECT AVG(amount) FROM orders o2 WHERE o2.customer_id = o.customer_id) AS customer_avg
FROM orders o;
The correlated subquery runs once per row of the outer query — making it extremely slow on large tables. For a 10 million row table, it executes 10 million separate subqueries. Avoid correlated subqueries in pipeline SQL and replace them with window functions or CTEs.
CTEs — Cleaner, Reusable, Debuggable
A CTE defines a named result set you can reference multiple times in the same query. The syntax starts with WITH.
WITH
completed_orders AS (
SELECT o.*, c.region, c.segment
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.status = 'completed'
),
revenue_by_region AS (
SELECT
region,
SUM(amount) AS total_revenue,
COUNT(*) AS order_count
FROM completed_orders
GROUP BY region
),
top_regions AS (
SELECT region, total_revenue
FROM revenue_by_region
WHERE total_revenue > 1000
)
SELECT * FROM top_regions
ORDER BY total_revenue DESC;
Output:
| region | total_revenue |
|---|---|
| North | 3050.00 |
| South | 2000.00 |
Why this is better than a nested subquery:
- Each CTE is a named step you can read independently
- When a query fails, you can run each CTE in isolation to find where the problem is
completed_ordersis defined once and can be referenced by bothrevenue_by_regionand any other CTE — no repeated code
Performance: CTE vs Subquery
The honest answer is: in most modern databases, they compile to the same execution plan. But there are meaningful exceptions.
PostgreSQL: CTEs are optimisation fences by default before PostgreSQL 12. The database cannot “see through” a CTE to push filters down. This means:
-- This CTE in PostgreSQL < 12 scans ALL orders before filtering
WITH all_orders AS (
SELECT * FROM orders
)
SELECT * FROM all_orders WHERE status = 'completed';
-- This subquery allows the filter to be pushed into the scan
SELECT * FROM (SELECT * FROM orders) sub WHERE status = 'completed';
In PostgreSQL 12+ this was fixed. You can also explicitly use WITH ... AS MATERIALIZED or AS NOT MATERIALIZED to control the behaviour.
Snowflake, BigQuery, Databricks: CTEs are inlined by the optimizer in all cases — performance is identical to subqueries.
When a CTE is genuinely faster: If you reference the same logic multiple times, a CTE can save the database from executing the same scan twice.
-- Without CTE: orders table scanned twice
SELECT * FROM orders WHERE amount > (SELECT AVG(amount) FROM orders);
-- With CTE: orders table referenced once
WITH avg_order AS (
SELECT AVG(amount) AS avg_amt FROM orders
)
SELECT * FROM orders
CROSS JOIN avg_order
WHERE orders.amount > avg_order.avg_amt;
Chained CTEs for Multi-Step Pipeline Transformations
Chained CTEs are the SQL equivalent of a pipeline: each step builds on the previous one. This pattern maps directly to a Bronze → Silver → Gold transformation.
-- Step 1 (Bronze equivalent): raw data with basic cleaning
WITH raw_orders AS (
SELECT *
FROM orders
WHERE order_id IS NOT NULL
AND amount > 0
),
-- Step 2 (Silver equivalent): enrich with dimension data
enriched_orders AS (
SELECT
o.order_id,
o.order_date,
o.amount,
o.status,
c.name AS customer_name,
c.region,
c.segment,
p.product_name,
p.category,
o.amount - p.cost AS gross_margin
FROM raw_orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id
),
-- Step 3 (Gold equivalent): aggregate for reporting
monthly_revenue AS (
SELECT
DATE_TRUNC('month', order_date) AS month,
region,
category,
SUM(amount) AS total_revenue,
SUM(gross_margin) AS total_margin,
COUNT(*) AS order_count
FROM enriched_orders
WHERE status = 'completed'
GROUP BY 1, 2, 3
)
SELECT
month,
region,
category,
total_revenue,
ROUND(total_margin / total_revenue * 100, 2) AS margin_pct,
order_count
FROM monthly_revenue
ORDER BY month, total_revenue DESC;
Output:
| month | region | category | total_revenue | margin_pct | order_count |
|---|---|---|---|---|---|
| 2024-01-01 | North | Hardware | 1200.00 | 33.33 | 1 |
| 2024-01-01 | North | Software | 150.00 | 66.67 | 1 |
| 2024-01-01 | South | Hardware | 800.00 | 50.00 | 1 |
| 2024-02-01 | North | Hardware | 2000.00 | 40.00 | 2 |
| 2024-02-01 | South | Hardware | 1200.00 | 33.33 | 1 |
| 2024-03-01 | North | Hardware | 350.00 | 42.86 | 1 |
This is directly publishable as a Gold layer table in your warehouse.
Recursive CTEs — For Hierarchical Data
Recursive CTEs are the correct way to traverse hierarchies in SQL — org charts, product categories, bill of materials, or any parent-child relationship.
CREATE TABLE employees (
employee_id INT,
name VARCHAR(50),
manager_id INT,
department VARCHAR(30)
);
INSERT INTO employees VALUES
(1, 'CEO', NULL, 'Executive'),
(2, 'VP Sales', 1, 'Sales'),
(3, 'VP Eng', 1, 'Engineering'),
(4, 'Sales Mgr', 2, 'Sales'),
(5, 'Eng Lead', 3, 'Engineering'),
(6, 'Sales Rep', 4, 'Sales'),
(7, 'Engineer', 5, 'Engineering');
-- Recursive CTE: traverse from CEO down to all levels
WITH RECURSIVE org_chart AS (
-- Anchor: start at the top (no manager)
SELECT
employee_id,
name,
manager_id,
0 AS depth,
CAST(name AS VARCHAR(200)) AS path
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive step: join each employee to their manager
SELECT
e.employee_id,
e.name,
e.manager_id,
oc.depth + 1,
CAST(oc.path || ' > ' || e.name AS VARCHAR(200))
FROM employees e
JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT
REPEAT(' ', depth) || name AS org_structure,
depth,
path
FROM org_chart
ORDER BY path;
Output:
| org_structure | depth | path |
|---|---|---|
| CEO | 0 | CEO |
| VP Engineering | 1 | CEO > VP Eng |
| Eng Lead | 2 | CEO > VP Eng > Eng Lead |
| Engineer | 3 | CEO > VP Eng > Eng Lead > Engineer |
| VP Sales | 1 | CEO > VP Sales |
| Sales Mgr | 2 | CEO > VP Sales > Sales Mgr |
| Sales Rep | 3 | CEO > VP Sales > Sales Mgr > Sales Rep |
The REPEAT(' ', depth) creates the visual indentation based on hierarchy level.
Note: PostgreSQL and most modern databases use WITH RECURSIVE. Databricks/Spark SQL uses the same syntax.
Common Mistakes
Mistake 1 — Forgetting the anchor in a recursive CTE
Every recursive CTE needs two parts: the anchor (starting condition) and the recursive step. Omitting the anchor causes an infinite loop or error.
Mistake 2 — No termination condition
Add a depth < 10 or similar guard in your recursive step to prevent infinite loops on circular references in your data:
WHERE oc.depth < 10 -- safety limit
Mistake 3 — Over-nesting subqueries
If you have more than 2 levels of nested subqueries, convert to CTEs. Deeply nested subqueries are nearly impossible to debug when they return wrong results.
Quick Reference
| Approach | Use When |
|---|---|
| Subquery (inline) | Simple one-off filter or join, not reused |
| Correlated subquery | Avoid — use window functions instead |
| CTE | Multi-step logic, reusing the same result, debugging |
| Chained CTEs | Bronze → Silver → Gold transformations |
| Recursive CTE | Hierarchies, org charts, parent-child traversal |
What to Learn Next
Now that you can build multi-step SQL transformations with CTEs, the next practical step is applying these patterns to incremental load pipelines — specifically watermark-based loads that only process new and changed data since the last pipeline run.