Are CTEs different from subqueries?

They are often equivalent in what they can express, but different in how the query is organised. A nested subquery hides an intermediate result inside another expression. A CTE names that result before the main SELECT, so later transformations can refer to it clearly and reviewers can inspect the data flow step by step.

Do CTEs make SQL faster?

Not inherently. Their main benefit is clarity and correctness, not speed. Whether a CTE is inlined, optimised away, or materialised depends on the SQL engine and the query shape. Treat CTEs as a way to make intent and grain explicit, then check the query plan if performance matters.

How should I name CTEs in feature-building SQL?

Use names that describe the relation produced, not the SQL operation used. Names such as eligible_users, historical_orders, user_purchase_aggregates, and final_training_features are more useful than step_a or joined_data. Good names make the expected grain and time boundary easier to verify during review.

What mistakes do CTEs help prevent in ML data pipelines?

They make it easier to see whether filters happen before or after aggregation, whether joins accidentally duplicate rows, and whether event data is restricted to what would have been known at prediction time. CTEs do not prevent those bugs automatically, but they expose the stages where those checks belong.

CTEs for Readable Transformations

CTEs for readable transformations are SQL common table expressions used to split one complex query into named, ordered intermediate results. Instead of hiding filtering, joins, aggregations, and final column selection inside nested subqueries, each CTE exposes one transformation step so the data flow can be read, reviewed, and changed like a pipeline.

The need appears when a single SQL statement starts doing too many jobs at once. Feature-building queries often define the training population, restrict events to a valid time window, join entity and activity tables, aggregate behaviour, and emit model-ready columns. If those operations are buried in nested subqueries, reviewers must reconstruct the grain, timing, and intent from syntax alone, which is where subtle data leakage and row-count mistakes creep in.

A CTE is written in a WITH clause and given a name, then referenced later as if it were a temporary table within the same query. A readable transformation usually starts with filtered base rows, then adds joins, then grouped aggregates, then a final SELECT that chooses and renames the output fields. The mechanism is simple: each named block produces a relation, and later blocks consume it, making the dependency chain explicit.

The trade-off is that readability is not the same as performance. A common misunderstanding is that CTEs always cache results or always speed up a query. The honest answer is that it depends on the database optimiser: some systems inline them, some may materialise them, and some behave differently by context. Excessive CTEs can also fragment logic, so each one should represent a meaningful step with a stable grain.

Engineers meet this pattern in analytics SQL, feature generation, dbt models, notebook queries, warehouse jobs, and code reviews for ML datasets. A practical test is whether each CTE can be described in one short phrase, such as eligible users, events before prediction time, user-level aggregates, or final features. If a later join changes the row grain or a filter moves across an aggregation, the structure makes that change visible.

Go deeper

Everything DataCTEs for Readable TransformationsRefactor a nested SQL query into named CTEs that separate filtering, joins, aggregations, and final feature selection.Open the lesson

Common questions

Are CTEs different from subqueries?: They are often equivalent in what they can express, but different in how the query is organised. A nested subquery hides an intermediate result inside another expression. A CTE names that result before the main SELECT, so later transformations can refer to it clearly and reviewers can inspect the data flow step by step.
Do CTEs make SQL faster?: Not inherently. Their main benefit is clarity and correctness, not speed. Whether a CTE is inlined, optimised away, or materialised depends on the SQL engine and the query shape. Treat CTEs as a way to make intent and grain explicit, then check the query plan if performance matters.
How should I name CTEs in feature-building SQL?: Use names that describe the relation produced, not the SQL operation used. Names such as eligible_users, historical_orders, user_purchase_aggregates, and final_training_features are more useful than step_a or joined_data. Good names make the expected grain and time boundary easier to verify during review.
What mistakes do CTEs help prevent in ML data pipelines?: They make it easier to see whether filters happen before or after aggregation, whether joins accidentally duplicate rows, and whether event data is restricted to what would have been known at prediction time. CTEs do not prevent those bugs automatically, but they expose the stages where those checks belong.

Every term