# Kimball Validation Checklist Distilled from `plugins/gemma-kimball/skills/kimball/references/kimball-concepts.md` (the Kimball expertise content shipped with `gemma-kimball`) and Ralph Kimball's *The Data Warehouse Toolkit*. This checklist is the **soft floor** — every rule here can be overridden by either the project's `CLAUDE.md` or the project's target-structure doc. The kimball validator MUST suppress or downgrade any rule that the project explicitly contradicts. When auditing a dbt project, the kimball validator should look for evidence in: - model file names (`fact_*`, `dim_*`) - column names (`*_key`, `*_id`, `effective_from_*`, `is_current`) - documentation in YAML (`description:` blocks may declare grain or additivity) - the structure of SQL (presence of surrogate-key generation, SCD2 windowing patterns) --- ## 1. The four steps For every fact table candidate, evidence should exist that the four steps were considered: - `kimball-business-process-unclear` — Can you tell from the model's name and description what business process it captures? **major** if a `fact_*` model has no description and a generic name. - `kimball-grain-not-declared` — Every fact table must have its grain declared in plain language in its YAML description. "One row per X per Y per Z." If absent → **major**. If present but ambiguous (e.g., "transaction data") → **minor** with a `details` quote of the offending text. - `kimball-dimensions-unclear` — Every fact table should have foreign keys to dimensions, and the dimension keys should be enumerated in the description. **minor** if missing; this is mostly enforceable by the `primary-key-tests` rule on the dim side. - `kimball-facts-not-flagged-additivity` — Every measure column on a fact table should be documented with its additivity: fully additive, semi-additive, or non-additive. **major** if any measure column lacks this annotation. ## 2. Fact tables - `kimball-fact-naming` — Fact tables are named `fact_` (plural-form noun for the process). E.g., `fact_orders`, `fact_subscription_events`. **major** when violated. - `kimball-non-additive-measure-stored` — A column whose name suggests a ratio, rate, percentage, or derived metric should NOT live as a stored fact. Heuristic match: column ends in `_rate`, `_pct`, `_percentage`, `_ratio`, `_acos`, `_roas`, `_nrr`, `_grr`, `_churn_rate`, `_conversion`, `_margin`. **critical** when found in a `fact_*` model — wrong numbers under aggregation. - `kimball-semi-additive-not-flagged` — Stock/balance/inventory measures (heuristic: `*_balance`, `*_balance_*`, `*_inventory`, `*_mrr`, `*_arr`, `*_open_*`, `*_outstanding_*`) should be documented as semi-additive (cannot sum across time). **major** if absent. - `kimball-degenerate-dimension-as-fk` — A column like `order_number`, `invoice_number`, `booking_reference` should live on the fact table as a degenerate dimension (a column, not a FK). If it is modelled as a FK to a single-attribute dim, suggest converting to a degenerate dim. **info** unless the dim adds no attributes, in which case **minor**. - `kimball-snapshot-fact-time-key` — Periodic-snapshot fact tables (anything named `*_snapshot_*` or `*_daily_*`/`*_monthly_*` aggregating a state) must have a date FK at the snapshot grain and document semi-additivity. **major** when missing. ## 3. Dimension tables - `kimball-dim-naming` — Dimension tables named `dim_` (singular-form noun for the entity, in line with Kimball convention). E.g., `dim_customer`, `dim_product`, `dim_date`. **major** when violated. Note: this contradicts the SQL style guide's "all objects plural" rule for dim tables — defer to `dim_customer` (Kimball convention) and report any clash as a `kimball-vs-style-conflict` info finding so the user can decide. - `kimball-dim-surrogate-key` — Dim tables should expose a surrogate key column named `_key` or `_sk`, in addition to the natural/business key. **major** when only the natural key is present. - `kimball-scd2-required-columns` — A dim that captures history (Type 2 SCD) must include `effective_from`, `effective_to` (or `valid_from`/`valid_to`), and `is_current`. Heuristic detection: model has a `unique` test on `(_id, effective_from)` or comparable. **major** if columns missing on a model that clearly tracks history. - `kimball-conformed-dim-multiple-defs` — Same dimension name used in multiple folders or with materially different column sets across the project = NOT actually conformed. The orchestrator should pre-compute "dim X is referenced by N facts"; if N ≥ 2 and the implementations differ, flag **critical**. - `kimball-role-playing-dim-not-aliased` — When the same dim is joined multiple times in a fact (e.g., `order_date_key`, `ship_date_key`, `return_date_key` all → `dim_date`), the joins should use distinct aliases (`AS order_date`, `AS ship_date`, `AS return_date`). Bare repeated joins to the same table without aliases → **minor**. - `kimball-junk-dim-candidate` — When a fact table has 4+ low-cardinality flag columns (`is_*`, `has_*`), suggest packing them into a junk dimension. **info**. - `kimball-bridge-table-needed` — If a fact references a dim via a column that holds delimited values (e.g., comma-separated category list), recommend a bridge table. Heuristic: column contains `'%,%'` filter or `STRING_SPLIT`/`UNNEST`. **major** at the analytics layer. ## 4. Conformed dimensions - `kimball-conformed-mismatched-cols` — Two `dim_*` files with the same trailing entity name (e.g., `dim_customer` in two folders) but different column sets. **critical**. - `kimball-multiple-date-dims` — More than one date dimension in the project (e.g., `dim_date` AND `dim_calendar`). **major**. ## 5. Additivity (review for every measure) | Measure pattern | Expected additivity | Validator action | |---|---|---| | `revenue`, `cost`, `quantity`, `*_amount`, `units_*` | fully additive | OK; no finding unless `is_*` flag present | | `*_balance`, `*_inventory`, `*_mrr`, `*_arr`, `*_open_*` | semi-additive | flag if not documented as such | | `*_rate`, `*_pct`, `*_ratio`, `*_acos`, `*_nrr`, `*_grr`, `*_conversion`, `*_margin` | non-additive | **critical** if stored as a fact column — must be derived from components | For non-additive cases, the suggested fix should always be: store the components (numerator + denominator), and derive the ratio in the BI tool / reporting layer. ## 6. Reporting layer - `kimball-reporting-skips-analytics-layer` — Reporting models should select from analytics (`fact_*` / `dim_*`), not directly from base. **minor** (overlap with Gemma SQL style; defer to Gemma rule and downgrade to info if the style validator already flagged it). - `kimball-reporting-redundant-metric-store` — A reporting model that materialises `*_rate` / `*_pct` is fine (it's the report layer); a *fact* table that does is not. This is the same rule as `kimball-non-additive-measure-stored` but scoped: do NOT flag reporting tables for storing non-additive metrics — that's their job. --- ## Override handling Before reporting any finding, the kimball validator MUST check the override summary the orchestrator passed it (extracted from CLAUDE.md and the target-structure doc). For each rule: - If CLAUDE.md says e.g. "we use plural for dim tables (dim_customers)" → suppress `kimball-dim-naming` for plural-named dims and add an entry to `skipped_checks` with `reason: "dim plural naming overridden by CLAUDE.md"`. - If the target-structure doc lists a different folder layout → defer to it and downgrade structural findings to `info`. - Never report a `critical` kimball finding when an explicit override exists. When in doubt, downgrade to `info` and let the human decide.