# Gemma SQL Style Checklist (Validation View)

Distilled from `~/dev/internal/gemma-sql-style/README.md`. Only rules that can be checked statically against `.sql`, `.yml`, and dbt project files are included. For full prose, narrative rationale, and edge cases, the source guide is authoritative — link to it from any finding's `details` when helpful.

When auditing a file, evaluate these rules. Each rule has a stable kebab-case identifier you can use as the `rule` field in findings.

---

## SQL conventions (apply to all `.sql` files)

### Naming

- `snake-case-identifiers` — All column and table names must be snake_case. Bad: `userId`, `CreatedAt`, `OrderID`. Good: `user_id`, `created_at`, `order_id`. **major**.
- `boolean-prefix` — Boolean columns must start with `is_`, `has_`, `was_`, `does_`, `can_`, or `should_`. Bad: `active`, `verified`. Good: `is_active`, `has_verified_email`. **major**.
- `date-suffix` — Date columns end in `_on` or `_date`. Bad: `created`, `signup`. Good: `created_on`, `signup_date`. **minor**.
- `timestamp-suffix` — Timestamp columns end in `_at`. Bad: `created_time`. Good: `created_at`. **minor**.
- `consistent-pluralization` — Base/raw table names should use the plural form of the entity (`base_stripe_invoices`, not `base_stripe_invoice`). At minimum, pluralization choice must be consistent across the repo. **minor**.

### Formatting

- `keywords-uppercase` — SQL keywords in UPPERCASE (`SELECT`, `FROM`, `WHERE`, `JOIN`, `ON`, `GROUP BY`, `ORDER BY`, `WITH`, `AS`). Functions UPPERCASE too (`COALESCE`, `SUM`, `COUNT`). **minor**.
- `indent-2-spaces` — Indentation uses 2 spaces, never tabs, never 4 spaces. **minor**.
- `line-length-88` — Lines should be ≤ 88 characters where reasonable. Wrap long expressions across lines using the leading-comma layout. **minor**.
- `leading-commas` — Use leading commas in `SELECT` lists and similar lists. The first column has no comma; every subsequent column starts with `, `. Bad: `id, name, email`. Good:
  ```
  SELECT
      id
    , name
    , email
  ```
  **major** when violated systematically across a file (this is a strong Gemma convention).
- `explicit-aliases` — Use `AS` explicitly when aliasing columns and tables. Bad: `users u`. Good: `users AS users` (or a meaningful business alias). **minor**.
- `meaningful-table-aliases` — Avoid one-letter aliases (`u`, `c`). Either use the table name itself or a business-meaningful alias (`managers`, `employees` when the same table plays two roles). **minor**.
- `no-select-star` — Avoid `SELECT *` in models. Allowed in throw-away CTEs that immediately rename or filter, but never in the final output of a model. **major** in models, **info** in CTEs.

### CTEs

- `cte-uppercase-with` — `WITH` and `AS` are uppercase, CTE names are snake_case. **minor**.
- `final-cte` — Models should end with a `final` CTE that is selected from. The closing `SELECT * FROM final` makes the output explicit. **minor**.

### Joins

- `qualified-columns-in-joins` — When joining two or more tables, every referenced column must be qualified with its table name or alias. **major** (silent bugs otherwise).
- `explicit-join-type` — Always write `INNER JOIN` / `LEFT JOIN` / `FULL OUTER JOIN` explicitly. Never bare `JOIN`. **minor**.

### Comments

- `comment-syntax-single` — Single-line comments use `--`. **info**.
- `comment-syntax-multi` — Multi-line SQL comments use `/* ... */` with a leading `*` on each interior line. **info**.
- `jinja-comment-syntax` — Jinja-related comments use `{# ... #}` (single-line) or the Jinja multi-line variant. SQL comments must NOT be used inside macros (they break when the macro is itself inside a comment). **major** when violated inside a macro.

---

## dbt conventions (apply to dbt projects)

### File and folder structure

- `dbt-folder-structure` — `models/` should follow the standard layered structure:
  - `models/base/<source>/base_<source>_<table>.sql` — base models
  - `models/interim/` (optional) — cleaning, joins, aggregations that fundamentally alter columns
  - `models/analytics/` — facts and dimensions
  - `models/reporting/` (optional) — KPIs / report-shaped tables
  - `models/export/` (optional) — reverse-ETL outputs
  Deviations are allowed but should be consistent and documented in CLAUDE.md or the target-structure doc. **minor** for deviations, **major** if the layers are mixed (e.g., facts directly under `models/`).
- `base-model-naming` — Base models: `base_<source>_<table>.sql`. **major**.
- `fact-dim-prefix` — Analytics models prefixed `fact_` or `dim_`. **major**.
- `interim-prefix` — Interim models prefixed `interim_`. **minor**.

### Layering rules

- `base-only-from-source` — Only `base_*` models may select from `{{ source(...) }}`. Higher-layer models must select from other models (`{{ ref(...) }}`). **critical** (architectural integrity).
- `base-minimal-transformations` — Base models should only rename, cast, and apply minimal transformations guaranteed to be useful forever. Joins, aggregations, and window functions belong in `interim_*` or higher. **major**.
- `no-cross-layer-skip` — Reporting models should generally not select directly from base; they should go through analytics layer. **minor** (often legitimate for trivial passthroughs).

### Configuration

- `default-materialization-table` — The default materialization is `table` unless there's a reason otherwise. View-by-default is a smell. **info**.
- `directory-config-in-project-yml` — Configuration that applies to a whole directory should be in `dbt_project.yml`, not repeated per-model. **minor**.
- `model-specific-config-inline` — Sort/dist/cluster keys and other model-specific settings belong inline in the model via the `config()` macro. **info**.

### Testing

- `primary-key-tests` — Every model's primary key must be tested with `unique` AND `not_null`. **critical** for dim/fact tables, **major** for base.
- `source-freshness` — Sources should declare `freshness:` blocks unless freshness is monitored by the ETL tool. **minor**.

### Documentation

- `model-description-required` — Every analytics/reporting model should have a `description:` in its YAML doc block. **minor**.
- `derived-column-description` — Any column that is not a direct rename of a source column should have a `description:` explaining how it is calculated or derived. **minor**.
- `surrogate-key-documented` — Generated surrogate keys (e.g., from `dbt_utils.generate_surrogate_key`) should be documented as such, listing their source columns. **minor**.

### Jinja

- `jinja-delimiter-spacing` — Use spaces inside Jinja delimiters: `{{ this }}`, not `{{this}}`; `{% if x %}`, not `{%if x%}`. **minor**.
- `dry-via-macros` — Repeated calculations should be extracted to macros. **info** (heuristic — flag only when the same expression appears 3+ times across files).

### YAML

- `yaml-indent-2` — YAML files use 2-space indents. **minor**.
- `yaml-list-newline-separation` — When list items are dictionaries, separate them with blank lines for readability (only flag if the file mixes both styles). **info**.

---

## What this checklist deliberately does NOT cover

- **SQLFluff linting** — Per repo CLAUDE.md, do NOT rely on SQLFluff; its defaults conflict with leading-comma layout. Do not generate findings derived from SQLFluff output.
- **Performance tuning** — Out of scope for this skill. The logic validator may flag obvious dependency anti-patterns, but no performance benchmarks.
- **Auto-fixes** — This skill only reports. The user (or a separate skill) decides what to apply.