02.02 · Walkthrough · Free
CTEs for Readable Transformations
Refactor a nested SQL query into named CTEs that separate filtering, joins, aggregations, and final feature selection.
Curated for this lesson
SQL for ML Engineers
Advanced SQL Tutorial | CTE (Common Table Expression)
The CTE-focused SQL tutorial title directly supports refactoring nested queries into readable named transformations.
CTEs turn dense, nested SQL into a readable transformation pipeline with named steps for filtering, joining, aggregation, and final feature selection. For ML feature queries, that structure makes grain changes, time windows, and data leakage risks easier to spot during review and safer to modify later.
What this lesson answers
- how to refactor nested SQL into CTEs
- when should ML feature queries use CTEs
- do CTEs make SQL queries faster
Notes
Common table expressions, or CTEs, let you break a complex SQL transformation into a sequence of named intermediate steps. For an ML engineer, this is especially useful because feature-building queries often combine several concerns at once: filtering training examples to the right time window, joining event tables to entity tables, aggregating behavior, and selecting the final model-ready columns. When all of that logic is packed into nested subqueries, it becomes hard to review, test, or safely modify. Refactoring into CTEs makes the query read more like a pipeline, where each block has a clear responsibility and a meaningful name.
A good pattern is to start with CTEs that define the input population and basic filters, then add CTEs for joins, then aggregations, and finally a last SELECT that chooses and renames the final features. This separation helps prevent common ML data mistakes, such as applying filters after aggregation when they should happen before, accidentally changing row grain during a join, or leaking future information into training data. Each CTE should produce a result that you could explain in one sentence: “eligible users,” “orders before prediction date,” “user-level purchase aggregates,” or “final training features.”
CTEs do not automatically make a query faster, and in some databases they may or may not be materialized depending on the optimizer. Their main value in this lesson is readability and correctness. Encourage engineers to use CTEs as a way to make transformation intent explicit, keep each step at a consistent grain, and make code review easier. A well-structured CTE query should let someone trace how raw tables become model features without mentally unpacking several layers of nested SQL.
Common questions
- How should I structure CTEs in an ML feature query?
- Start by defining the eligible training population and time filters, then add joins, then aggregate at the required entity grain, then finish with a final select for model-ready columns. Each CTE should have one clear job and a name that says what rows or features it represents.
- Why are CTEs better than nested subqueries for feature engineering?
- Nested subqueries force reviewers to reconstruct the transformation from the inside out. CTEs expose the sequence directly, so filtering, joins, aggregation, and output shaping can be checked independently. That makes it easier to catch misplaced filters, accidental grain changes, and feature logic that would leak future information.
- Do CTEs improve SQL performance?
- CTEs are mainly a readability and correctness tool, not a performance guarantee. Some databases inline them, while others may materialise them depending on the optimiser and query shape. Use them to make intent clear first, then inspect the execution plan if runtime or cost becomes a problem.
Short definition: what is CTEs for Readable Transformations?