Common Table Expressions

Le Common Table Expressions (CTE) sono un costrutto SQL introdotto dalla clausola WITH, che permette di dare un nome a una subquery e referenziarla per nome nel resto della query, trattando le subquery come variabili invece che come blocchi annidati. Sahar Massachi, nella Parte 2 della serie “Stop using Slowly-changing Dimensions!” su DataExpert.io, le definisce il suo “secondo one weird trick” per il data engineering, dopo il date stamping: “CTEs are just fucking fantastic”.¹

Come funziona

Invece di annidare subquery dentro subquery, ogni passo intermedio si dichiara con un nome all’inizio della query, dentro un blocco WITH:

WITH
  today_users AS (
    SELECT user_id, followers FROM dim_users
    WHERE ds = '2025-01-16' AND followers >= 1000
  ),
  past_users AS (
    SELECT user_id, followers FROM dim_users
    WHERE ds = '2024-10-01' AND followers < 200
  ),
  past_device AS (
    SELECT user_id, device FROM dim_devices
    WHERE ds = '2024-10-01'
  )
SELECT ...
FROM past_users pu
JOIN today_users tu ON pu.user_id = tu.user_id
JOIN past_device pd ON tu.user_id = pd.user_id

La query si legge dall’alto verso il basso: ogni CTE è un passo nominato, il SELECT finale li compone. La query di retention che chiude la Parte 2 della serie è costruita così, con cinque CTE (today_users, past_users, past_device, user_device, views) composte in un SELECT finale con join multipli; è l’esempio con cui Massachi mostra che una domanda storica complessa, con gli snapshot datestampati e le CTE, resta complessa solo nella logica di business.¹

Perché conta

Le CTE trasformano il SQL da linguaggio di interrogazione a linguaggio componibile: la subquery nominata è la stessa astrazione della funzione nominata in programmazione, si definisce una volta, si referenzia per nome, si compone. Ciò che in SQL annidato sarebbe un muro di parentesi diventa una sequenza di passi leggibili. La lettura è coerente con il functional data engineering: ogni CTE è una trasformazione senza effetti collaterali, componibile con le altre, e il SQL, già lingua franca del warehouse, si avvicina alla scrittura di codice ordinario.¹

Prestazioni

Motori come Presto e Trino appiattiscono le CTE in fase di ottimizzazione: vengono inlineate, senza overhead di esecuzione, quindi si possono usare liberamente. La raccomandazione di Massachi è verificare che il proprio motore lo faccia (“make sure your query engine flattens them for free”) e poi farne uso liberale: sono il modo standard di scrivere query di warehouse con più di un join o una subquery.¹

Voci correlate

• Date stamping — il primo “one weird trick” della stessa serie
• Pipeline (data engineering) — il SQL come lingua franca che le CTE rendono componibile
• Functional data engineering — le CTE come funzioni pure del SQL
• Slowly Changing Dimensions — la query di retention che le usa come esempio
• Portale: Data engineering

Bibliografia e sitografia

• Sahar Massachi, SCD-2 considered harmful! (Stop using Slowly-changing Dimensions! Part 2), DataExpert.io, 4 novembre 2025. blog.dataexpert.io

Footnotes

1. Massachi, SCD-2 considered harmful! Part 2, DataExpert.io, 2025. ↩ ↩² ↩³ ↩⁴