How common table expressions simplify complex SQL logic.
📖 This article is part of my series on SQL Best Practices for Clear & Concise Data Transformation. Each post tackles a common anti-pattern and shows a cleaner way to write queries that are easier to test, reuse, and maintain.
Press enter or click to view image in full size
Why this matters
Ever opened a “one-shot” SQL query that tries to filter, dedupe, join, and aggregate all at once? It’s tough to read and even harder to debug. By breaking the work into small, named CTEs, each with a single job, you make the logic obvious, testable, and easy to evolve.
The pattern
Design queries in clear, modular stages:
- Preparation CTEs: Pull from sources, apply point-in-time logic (e.g., SCD2 with
valid_from/valid_to), and push filters close to scans. - Transformation CTEs: Perform joins, unions (kept pure), aggregations, and calculations.
- Final query: Select only the fields needed for output; keep this step thin.
Best practices
Logical segmentation
- Each CTE does one well-defined thing (filtering, transformation, deduplication, combining).
- Keep Preparation, Transformation, and Final steps separate.
- Avoid mixing unrelated operations in one CTE.
Descriptive naming
- Use names that explain intent (e.g.,
filtered_triggers,deduplicated_orders,combined_events). - Consistent naming improves readability, maintenance, and debugging.
Simplify complex logic
- Break complex logic into smaller CTEs; go step-by-step.
- Don’t cram many operations into a single CTE.
Reuse CTEs
- Reuse intermediates instead of duplicating logic downstream.
- Centralize common filters and normalization in one prep CTE.
Combine related transformations
- If transformations are tightly related, group them into a single CTE to reduce unnecessary hops.
Example skeleton
WITH
– Preparation
orders_src AS (
SELECT * FROM bronze.orders WHERE order_date >= DATE ‘2025-01-01’
),
customers_pti AS ( – point-in-time (SCD2) example
SELECT c.
FROM dim.customer_scd2 c
WHERE CURRENT_DATE BETWEEN c.valid_from AND COALESCE(c.valid_to, DATE ‘9999-12-31’)
),
– Transformations
orders_prepared AS (
SELECT order_id,
customer_id,
CAST(order_ts AS TIMESTAMP) AS order_ts,
CAST(revenue AS NUMERIC) AS revenue
FROM orders_src
),
orders_enriched AS (
SELECT o., c.customer_tier
FROM orders_prepared o
LEFT JOIN customers_pti c ON c.customer_id = o.customer_id
),
daily_rollup AS (
SELECT DATE_TRUNC(‘day’, order_ts) AS day,
customer_tier,
SUM(revenue) AS revenue_day
FROM orders_enriched
GROUP BY 1, 2
),
– Final
final AS (
SELECT day, customer_tier, revenue_day
FROM daily_rollup
)
SELECT * FROM final;
Common mistakes to avoid
- Mixing filtering, casting, deduping, and joining in a single CTE.
- Doing transformations inside a
UNION ALL(keep unions pure and schema-aligned). - Overusing
SELECT *across stages (be explicit; it protects against schema drift). - Inconsistent naming that hides intent (
t1,t2,tmp). - Deep CTE chains without reuse (signals duplicated logic).
Quick checklist
- One responsibility per CTE.
- Clear stage separation: Preparation → Transformation → Final.
- Pure unions with aligned schemas (no casts/filters/literals inside the union).
- Minimal final select (projection + light filters only).
- Consistent, descriptive naming; explicit columns instead of
*.
Wrap-up
Modular CTE design turns big, brittle SQL into readable, testable, and reusable pipelines. It surfaces intent, localizes bugs, and makes feature changes safer.
Series navigation
📌 This is Part 3 in the series SQL Best Practices for Clear & Concise Data Transformation.
👈 Start with Part 1: SQL Best Practice: Avoid Inline [**UNION ALL**](https://medium.com/p/53be9ce1421b#)
👉 Then read Part 2: SQL Best Practice: Keep [**UNION ALL**](https://medium.com/p/53be9ce1421b#) Pure