Explain Common Table Expressions (CTEs) and their benefits.

Architectural Logic: CTEs are named subqueries in a WITH clause, evaluated as defined (or materialized, depending on engine). They provide logical decomposition without forcing physical materialization. Why: Readability and reuse—complex pipelines split into stages (raw → cleansed → aggregated). Recursion for hierarchies (org charts, bill-of-materials). Some engines inline CTEs; others (e.g., BigQuery) can materialize for reuse. Scalability: In BigQuery, repeated CTE references may be computed once (materialized) or inlined—check EXPLAIN. Deep CTE chains can increase plan complexity. Cost: Recursive CTEs have depth limits and can explode rows; use with termination conditions. Best practice: Use for clarity; avoid deep nesting; for heavy reuse, consider temp tables in batch pipelines.