SQL style guide for analytics teams (adopt in a week, not a quarter)

# SQL Style Guide — Analytics Team This guide applies to every SQL file in the dbt repo and any ad-hoc query checked in for review. When your PR fails the linter, it is not the linter that is wrong. The goal is a codebase any analyst on the team can read at 2am during an incident. ## 1. Formatting Keywords are UPPERCASE (SELECT, FROM, WHERE, JOIN). Identifiers are lowercase snake_case. Indent with 4 spaces — never tabs. Target a 100-character line length; break long lines at commas or logical operators. Leading commas or trailing commas? Trailing. Diffs are cleaner, dbt generates them that way, and arguments about this are not worth the calories. Every top-level clause starts on its own line: SELECT customer_id, SUM(order_total) AS total_spent FROM orders WHERE status = 'completed' GROUP BY customer_id; ## 2. Keywords, Functions, Whitespace Built-in functions are lowercase: coalesce, sum, date_trunc. One blank line between CTEs. No trailing whitespace. End every file with a newline. ## 3. CTEs vs Subqueries Prefer CTEs. Inline subqueries are allowed only for trivial scalar lookups (e.g., a single MAX in a WHERE clause). If a CTE is used once and is shorter than five lines, consider inlining. If a CTE is used three or more times, extract it to an intermediate model. Name CTEs after what they produce, not how they produce it: orders_last_90_days is better than filter_orders. ## 4. Joins Always specify the join type explicitly — INNER JOIN, LEFT JOIN, FULL OUTER JOIN. Never write bare "JOIN". Never use comma joins or implicit joins. Two-letter aliases are required (c for customers, o for orders). Single-letter aliases are banned in any query with more than two tables. Join conditions go on separate lines when there are two or more predicates: LEFT JOIN orders o ON o.customer_id = c.id AND o.created_at >= CURRENT_DATE - INTERVAL '30 days' ## 5. Comments and Annotations A model-level comment at the top of every file explains the grain in one sentence. Inline comments are for non-obvious business logic (why, not what). Delete commented-out code before merging — git remembers so the file does not have to. ## 6. Bad vs Good **Bad:** select customer_id,sum(order_total) as total from orders o,customers c where o.customer_id=c.id and status='completed' group by 1; **Good:** SELECT c.customer_id, SUM(o.order_total) AS total_spent FROM customers AS c INNER JOIN orders AS o ON o.customer_id = c.customer_id WHERE o.status = 'completed' GROUP BY c.customer_id; The good version is longer, reads top-to-bottom, makes the join explicit, avoids GROUP BY 1, and lives happily in a diff. ## 7. Enforcement Rules are enforced in this order: - **Pre-commit:** sqlfmt auto-formats on save and pre-commit. No manual formatting debates. - **CI:** sqlfluff runs on every PR. Failing lint blocks merge. - **Review:** reviewers focus on logic and naming, not spacing. If a rule feels wrong for a specific case, open a PR against this guide. The answer to "the linter forced me to do X" is either update the linter or update the style guide — never a one-off exception.