> ## Documentation Index
> Fetch the complete documentation index at: https://docs.textql.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Module 3 · Validate Against Your Schema

> The dry run is discovery; validation/validate_tql.py is the mechanical gate. It verifies every governed surface against your warehouse: each logical name resolves, each referenc… (~25 min)

## 3.1 · The dry run — required

```text Prompt theme={null}
Look at the ontology repo, then inspect my warehouse. Run validation/dry-run-prompt.md against my schema: pull the information schema for my policy, premium, and claims tables and tell me where the ontology's expected table and column names don't match what I actually have — including whether premium is stored earned (a monthly series) or only written at inception. Propose the exact changes to ontology/schema.tql.
```

<Check>
  **You'll see:** Ana discover your schema, diff it against the ontology, and hand you a precise list of fixes — table backings, column names, and the all-important earned-vs-written premium question. (The ready-made version lives in `validation/dry-run-prompt.md`.)
</Check>

## 3.2 · Run the validator — required

The dry run is discovery; `validation/validate_tql.py` is the mechanical gate. It verifies every governed surface against your warehouse: each logical name resolves, each referenced column exists, each query compiles. **Ana runs it for you** — no terminal needed:

```text Prompt theme={null}
Run validation/validate_tql.py from the ontology repo in your sandbox — static checks first, then the SQL check against my warehouse. Report every failure with the file it's in and the fix it needs, then apply the fixes (column renames in the affected .tql, table renames in schema.tql only) and re-run until clean.
```

<Check>
  **You'll see:** the typo'd-backing, wrong-alias, and missing-column bug classes caught here — instead of surfacing as wrong numbers in front of stakeholders.
</Check>

<Note>
  **Prefer the terminal?** — The same gate runs locally: `python3 validation/validate_tql.py` (static — no warehouse needed) · `--check-sql` (paste the output into Ana: rows = missing columns) · `--dsn "&lt;dsn&gt;" --explain` (live column check + compile test).
</Note>

## 3.3 · Apply the fixes as a PR — required

```text Prompt theme={null}
Make those changes and open a pull request.
```

<Check>
  **You'll see:** Ana edit the files and open a reviewable PR in **your** repo. Every physical table name lives in one place (`ontology/schema.tql`) — re-point it and the metric logic stays put. The join keys the surfaces rely on are `policy_id`, `policyholder_id`, and `claim_id`.
</Check>

<Frame caption="Validation lands as a reviewable PR — not silent edits.">
  <img src="https://textqllabs.github.io/workshops/insurance-starter/assets/ins-m2-pr.png" alt="Schema validation PR" />
</Frame>

<Note>
  **Why this step matters** — Every carrier's warehouse differs from the reference shape somewhere — a renamed column, a missing table, a different grain. Finding those *before* you trust a number is the difference between a defensible loss ratio and a debugging session in front of the actuary.
</Note>

## 3.4 · Decide your grain & verify your joins — required

Insurance data fans out across several grains — **policy vs. coverage vs. claim vs. transaction** — and joining across them carelessly multiplies counts. Settling this is the single most important decision in this module, and it's a prompt:

<table>
  <tr><th>Table</th><th>Grain</th><th>Count this for…</th></tr>
  <tr><td>`policy`</td><td>1 / policy</td><td>policies in force, retention, new business</td></tr>
  <tr><td>`coverage`</td><td>1 / coverage line (many per policy)</td><td>peril/limit exposure — **not** policy counts</td></tr>
  <tr><td>`premium_earned`</td><td>1 / policy / month</td><td>earned premium (sum the series over the window)</td></tr>
  <tr><td>`claim`</td><td>1 / claim</td><td>frequency, severity, loss ratio, reserves</td></tr>
  <tr><td>`claim_transaction`</td><td>1 / payment or reserve move</td><td>loss development only</td></tr>
</table>

```text Prompt theme={null}
Read ontology/notes/grain.md, then inspect my tables. Confirm the grain of each: is there one policy table and a separate coverage table (many coverages per policy)? Is premium stored as a monthly earned series or only as written-at-inception? Does the claim table carry one row per claim, with claim_transaction as the payment/reserve ledger? Tell me where counting coverage rows as policies, or claim_transaction rows as claims, would fan out — and record the decision in databases/[ourschema]/README.md (copy databases/policy_core/ as the template).
```

<Check>
  **You'll see:** the grain decision made and written down *before* anything gets edited — it shapes every count downstream. The trap to confirm: a policy fans out into many coverage lines, and a claim into many transactions; **anchor policy counts on `policy` and claim counts on distinct `claim_id`**.
</Check>

```text Prompt theme={null}
Verify every join the ontology relies on against my warehouse: does the key (policy_id / policyholder_id / claim_id) exist on both sides, what's the overlap rate, and is the grain 1:1 or 1:N? Then confirm the bases: is "incurred" available as paid_loss + case_reserve, and is loss_date (accident date) populated separately from report_date? Record each verdict in databases/[ourschema]/README.md and flag any join or basis we shouldn't trust.
```

<Check>
  **You'll see:** a join-by-join verdict list plus a basis check — because loss ratio needs incurred losses and accident-year dating, and a paid-only or report-date warehouse changes the number.
</Check>

```text Prompt theme={null}
Find the dataset-specific literals the surfaces hard-code — the policy/claim status enums, the renewal_status values, line_of_business spelling, and the cause_of_loss values — check each against what's actually in my warehouse, and propose the corrections in the same PR.
```

<Check>
  **You'll see:** the enumerations the validator can't know — `status = 'open'`, the renewal flag, the LOB and cause-of-loss spellings — corrected from your real data instead of assumed.
</Check>

<Note>
  **The full checklist** — This module covers the core; `MIGRATION.md` is the complete 8-step re-point (discover → grain → schema.tql → identity → validator → literals → governance → glossary → goldens). Half a day with warehouse access; most of it is verification, not editing.
</Note>

<Note>
  **Written-only premium?** — If your warehouse stores premium **only as written-at-inception** (no monthly earned series), the ratios can't sum earned directly — you must earn it pro-rata over `effective_date → expiration_date` first. Flag it in the dry run; `notes/premium-definition.md` has the derivation. This is the difference between a correct loss ratio and one that flatters a growing book.
</Note>

### ✅ Checkpoint

* [ ] Ana produced a concrete mismatch list (or confirmed a clean match)
* [ ] The policy/coverage/claim/transaction grain is decided and written down
* [ ] The fixes landed as a PR you can review — not silent edits — and you merged it (or know who reviews it)