> ## 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. Pull the information schema for my custody / portfolio-accounting tables and tell me where the ontology's expected table and column names don't match what I actually have. 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. (A ready-made version of this check 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. If you have a resolved household table, point `household_grain` at it: one line.
</Check>

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

<Note>
  **Why this step matters** — Every firm'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 metric and a debugging session in front of stakeholders.
</Note>

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

Two decisions shape everything downstream, and both are prompts. The first is the most important number-corrupting trap in this domain:

```text Prompt theme={null}
Read ontology/notes/grain.md, then inspect my data. Confirm: is `position` a per-date snapshot (one row per account × security × as_of_date), and is `account_value` a series? What's the snapshot cadence (daily vs month-end), and which valuation date is my horizon? Set analysis_end_date in schema.tql to a real snapshot date, and record the decision in databases/[ourschema]/README.md.
```

<Check>
  **You'll see:** the snapshot-vs-series decision made and written down *before* anything gets edited — summing position snapshots across dates multiplies AUM, the #1 bug in this domain (`notes/grain.md`).
</Check>

```text Prompt theme={null}
Per ontology/notes/identity-resolution.md, find my identity / household resolution layer (household / relationship / master / crosswalk tables), and verify every join the ontology relies on: does the key exist on both sides, what's the overlap rate, and is the grain 1:1 or 1:N? Point household_grain at the resolved id, record each verdict in databases/[ourschema]/README.md, and flag any join we shouldn't trust.
```

<Check>
  **You'll see:** a join-by-join verdict list, and identity resolved *before* any client/AUM count — so the same investor under multiple custodian ids (post-M\&A, joint/trust, multi-custodian) isn't double-counted.
</Check>

```text Prompt theme={null}
Find the dataset-specific decisions the validator can't know: is net_external_flow clean (client flows only, no market movement)? Is market_value in one reporting currency? Which security identifiers are present (FIGI public; CUSIP/GICS licensed)? Check each against my warehouse and propose the corrections in the same PR.
```

<Check>
  **You'll see:** the things a compile check can't catch — dirty flows that void returns, multi-currency books, missing FIGI — 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 & cadence → schema.tql → identity → validator → literals → governance → glossary → goldens). Half a day with warehouse access; most of it is verification, not editing.
</Note>

<Note>
  **Different warehouse dialect?** — The starter is authored in ANSI SQL with a portable float idiom (`* 1.0`) that works on Redshift, Spark/Databricks, DuckDB, and BigQuery. On any engine, extend the dry-run ask: *"…also flag any engine-specific SQL in the .tql surfaces and propose \[dialect] equivalents."* Dialect adaptation rides the same PR loop as schema fixes.
</Note>

### ✅ Checkpoint

* [ ] Ana produced a concrete mismatch list (or confirmed a clean match)
* [ ] The fixes landed as a PR you can review — not silent edits
* [ ] You merged the PR (or know who reviews it)