How to Format SQL Queries: CTEs, JOIN Alignment, Window Functions, and SQLFluff Standards

Why SQL Readability is Critical in Data Engineering

SQL is a declarative relational query language where complex queries often span dozens of joins, window aggregations, and subqueries. When queries are formatted as single-line walls of text, debugging query execution plans (EXPLAIN ANALYZE), index scan bottlenecks, and accidental Cartesian products becomes nearly impossible.

The 5 Core SQL Formatting Principles

• Uppercase All Reserved Keywords: Write SELECT, FROM, WHERE, JOIN, GROUP BY, and ORDER BY in UPPERCASE to visually differentiate commands from column and table identifiers.
• One Column Per Line: In the SELECT clause, format each column or calculation on its own line with leading or trailing commas.
• Explicit JOIN Conditions: Always place ON clauses on a new indented line directly following the JOIN target table.
• Align WHERE Predicates: Indent boolean conjunctions (AND, OR) cleanly beneath the WHERE clause.
• Use Common Table Expressions (CTEs) Over Deeply Nested Subqueries: Break complex query trees into readable sequential CTE blocks using WITH.

Formatting Common Table Expressions (CTEs)

WITH active_users AS (
    SELECT
        user_id,
        email,
        created_at
    FROM users
    WHERE status = 'active'
      AND created_at >= NOW() - INTERVAL '30 days'
),

monthly_usage AS (
    SELECT
        user_id,
        COUNT(session_id) AS total_sessions,
        SUM(bytes_transferred) AS total_bytes
    FROM transfer_sessions
    GROUP BY user_id
)

SELECT
    u.user_id,
    u.email,
    COALESCE(m.total_sessions, 0) AS total_sessions,
    COALESCE(m.total_bytes, 0) AS total_bytes
FROM active_users u
LEFT JOIN monthly_usage m
    ON u.user_id = m.user_id
ORDER BY total_bytes DESC
LIMIT 50;

Formatting Complex JOINs & Window Functions

SELECT
    department_id,
    employee_name,
    salary,
    -- Format window functions with multiline OVER partitions
    DENSE_RANK() OVER (
        PARTITION BY department_id
        ORDER BY salary DESC
    ) AS salary_rank,
    AVG(salary) OVER (
        PARTITION BY department_id
    ) AS dept_avg_salary
FROM employees
WHERE termination_date IS NULL;

Automating Standards with SQLFluff

SQLFluff is the standard modular linter and formatter for SQL across dialects (PostgreSQL, Snowflake, BigQuery, MySQL):

[sqlfluff]
dialect = postgres
max_line_length = 100
indent_unit = space

[sqlfluff:rules:capitalisation.keywords]
capitalisation_policy = upper

[sqlfluff:rules:capitalisation.functions]
extended_capitalisation_policy = upper