Core•6–10 hours

AI Metric Reconciliation Lab

Validate AI-generated SQL and metrics with a semantic contract, deterministic controls, authoritative reconciliation, and regression cases before a number reaches a dashboard or decision.

metric contractsSQL validationdata reconciliationfailure analysisregression testing

Scenario

Task

AI generated SQL for a key KPI, but the dashboard shows a different number. The query executes successfully, yet that proves nothing: the difference may come from grain, timezone, join cardinality, filters, late-arriving data, or the business definition itself. Build a verification flow that separates syntactic success from semantic correctness and leaves a reproducible evidence trail.

Step-by-step execution

1. Freeze semantics before SQL generation

Outcome: AI receives an explicit metric contract instead of guessing business rules from column names.

Tasks

→Record grain and unit of analysis
→Lock numerator, denominator, filters, and exclusions
→Define timezone, period close, and freshness rules
→Name the authoritative table or report and owner

Checks

✓A metric has one versioned contract
✓Undefined business rules are marked as open questions
✓AI may not resolve policy ambiguity on its own

2. Treat generated SQL as a candidate artifact

Outcome: Successful execution can no longer hide a logical error.

Tasks

→Store prompt, context, and exact query
→Validate schema and referenced fields
→Add row-count, null, and duplicate checks
→Check join keys and one-to-many or many-to-many fan-out

Checks

✓Query success is not an acceptance criterion
✓Unknown fields or implicit casts fail explicitly
✓Join cardinality is checked separately from totals

3. Perform authoritative reconciliation

Outcome: The key number is reproduced independently and any discrepancy is explained.

Tasks

→Compare the KPI with the authoritative report or source
→Recalculate totals through an independent query or formula
→Check edge segments and boundary dates
→Use tolerance only with domain justification

Checks

✓A mismatch is never rounded into PASS
✓Tolerance has an owner and rationale
✓Every discrepancy is localized to definition, data, query, or source state

4. Turn failures into a regression suite

Outcome: A later SQL, schema, or model change cannot silently reintroduce a known error.

Tasks

→Simulate wrong join, stale partition, timezone shift, missing filter, and duplicate rows
→Record the expected deterministic signal
→Assign severity and release action
→Version dataset, query, and contract fingerprints

Checks

✓Critical reconciliation failure blocks publication
✓Regression cases reproduce without an LLM judge
✓Metric-contract changes trigger baseline re-verification

Acceptance criteria

The metric contract includes grain, definitions, filters, time semantics, freshness, and an authoritative source
Generated SQL is stored as a versioned candidate artifact with assumptions
Join cardinality, duplicates, nulls, totals, and edge segments have deterministic checks
The key KPI is independently reconciled or has an explicit unresolved discrepancy
At least five failure cases become permanent regression tests