SQL Style & Code Review

SQL style is not decoration. After you understand internals, scaling, and analytics, craft is what lets another person trust your SQL at review time.

This lesson gives you a style guide that scales, then puts it in the right order: make SQL readable so it can be reviewed, but review the meaning before you argue about whitespace.

Pick A House Style

SQL has a real casing split. The classic style writes keywords in UPPERCASE and identifiers in snake_case. Many newer analytics teams prefer lowercase keywords; dbt's own style guide mandates lowercase. Both styles can be readable. Mixing them inside one project is what makes a codebase feel noisy.

This course has used the classic house style from the first SELECT:

• keywords in UPPERCASE
• identifiers in snake_case
• strings in single quotes
• one clause per line once a query grows past a tiny example
• short table aliases in joins, with qualified columns
• CTE pipelines when a query has named transformations

The professional answer is not "uppercase always wins." It is "pick one, document it, and let the machine enforce it." Humans should spend review time on grain, joins, NULLs, and business meaning.

Let CI End The Formatting Argument

Formatters and linters are social tools as much as technical tools. A formatter makes layout boring. A linter catches rules the formatter cannot safely infer, such as keyword casing, ambiguous aliases, forbidden wildcard projection, or project-specific naming rules.

SQLFluff is the common CI linter in modern SQL projects. A useful CI log is not a lecture; it is a gate:

CI: sqlfluff
  fail: keyword casing does not match the project guide
  fail: CTE name "cte1" violates the project naming pattern
  pass: layout can be auto-formatted
  status: fix style before merge

The naming failure comes from a pattern the team configured, not from the linter judging meaning on its own — a linter can enforce "names must match this shape," while only a reviewer can say whether a name is honest.

That changes the review culture. The argument ends when the machine enforces the agreed style. The reviewer can write, "CI has the style pass; I am reviewing the grain and join logic."

Review Meaning Before Style

Imagine this pull request says it reports users, purchase events, and revenue by home branch:

SELECT u.home_branch,
       count(*) AS users,
       count(*) FILTER (WHERE e.event_type = 'purchase') AS purchase_events,
       sum(e.purchase_amount) AS revenue
FROM cafe_users u
JOIN cafe_app_events e
  ON e.user_id = u.user_id
GROUP BY u.home_branch
ORDER BY u.home_branch;

 home_branch | users | purchase_events | revenue
-------------+-------+-----------------+---------
 campus      |    27 |               5 |   65.40
 center      |    41 |               8 |  114.60
 harbor      |    29 |               3 |   38.40
(3 rows)

The formatting is decent. The join key is correct: e.user_id = u.user_id. But the alias users is a lie. count(*) counts joined event rows, not people. The branch report has 27, 41, and 29 event rows, while the seed has 12 users total. A style-only review would miss the bug because the SQL looks tidy.

Review SQL in this order:

1. What is the output grain? Here it should be one row per home_branch.
2. What does each input row represent? cafe_users is user grain; cafe_app_events is event grain.
3. Do the join keys preserve, multiply, or drop rows? This join deliberately expands users to events.
4. What is the NULL story? Non-purchase events have NULL purchase_amount; sum() skips them, but count(*) still counts them.
5. Does each name tell the truth at 2 a.m.?

Now revise the query so the shape tells the truth. The first CTE changes event rows into one row per user. The second CTE changes user rows into one row per branch. The final query only formats the report:

WITH user_purchase_totals AS (
  SELECT u.user_id,
         u.home_branch,
         count(e.event_id) FILTER (WHERE e.event_type = 'purchase') AS purchase_events,
         COALESCE(
           sum(e.purchase_amount) FILTER (WHERE e.event_type = 'purchase'),
           0
         ) AS revenue
  FROM cafe_users u
  LEFT JOIN cafe_app_events e
    ON e.user_id = u.user_id
  GROUP BY u.user_id, u.home_branch
),
branch_summary AS (
  SELECT upt.home_branch,
         count(*) AS signed_up_users,
         count(*) FILTER (WHERE upt.purchase_events > 0) AS purchasing_users,
         sum(upt.purchase_events) AS purchase_events,
         sum(upt.revenue) AS revenue
  FROM user_purchase_totals upt
  GROUP BY upt.home_branch
)
SELECT bs.home_branch,
       bs.signed_up_users,
       bs.purchasing_users,
       round(
         100.0 * bs.purchasing_users / NULLIF(bs.signed_up_users, 0),
         1
       ) AS purchase_rate_pct,
       bs.purchase_events,
       bs.revenue
FROM branch_summary bs
ORDER BY bs.home_branch;

 home_branch | signed_up_users | purchasing_users | purchase_rate_pct | purchase_events | revenue
-------------+-----------------+------------------+-------------------+-----------------+---------
 campus      |               4 |                3 |              75.0 |               5 |   65.40
 center      |               4 |                3 |              75.0 |               8 |  114.60
 harbor      |               4 |                2 |              50.0 |               3 |   38.40
(3 rows)

This version is longer, but the review is easier. user_purchase_totals says the grain changed to user. branch_summary says the grain changed to branch. LEFT JOIN keeps users even if they have no matching events. COALESCE makes no-revenue users contribute zero to revenue, while NULLIF keeps the rate honest if a future branch somehow has zero users.

Name For The Tired Reader

Naming is not universal law. Some teams use plural table names, some use singular. Pick one pattern and use it consistently. The higher-value rule is that names should tell a reviewer what role a thing plays.

Good table and column names are boring in the best way: cafe_users, purchase_amount, signup_ts, home_branch, loyalty_tier. They avoid abbreviations that only one team member understands. They carry units when units can be confused: amount_usd, duration_seconds, tax_rate_pct.

Good CTE names name transformations, not step numbers. Use names like paid_orders, customer_totals, ranked_customers, user_purchase_totals, and branch_summary. Avoid cte1, temp, final2, and data. A CTE is a promise to the reader: "this named shape is worth remembering for the next few lines."

Review the final query below. Run it once, then change one name or join choice and ask whether the review got easier or harder.

Style makes SQL reviewable. The next lesson turns review assumptions into executable checks, so "this join preserves the grain" becomes something your pipeline can prove.