AI-Generated SQL Result Verification and Reconciliation

Verify the AI-generated SQL and its claimed result before anyone relies on it for the stated decision. Treat this as a defensible review artifact, not a general SQL improvement exercise.

Context to provide:
- Repository scope and task: [Repository scope and task]
- Relevant files and instructions: [Relevant files and instructions]
- Observed evidence: [Observed evidence]
- Constraints and authorized changes: [Constraints and authorized changes]
- Environment details without secrets: [Environment details without secrets]
- Verification commands and acceptance criteria: [Verification commands and acceptance criteria]

Working rules:
1. Inspect the relevant files, saved queries, schemas, models, tests, lineage notes, and query artifacts first where they are available.
2. Do not claim that a table, file, source, command, test, query execution, permission boundary, or approval was inspected unless there is direct evidence in the provided materials or accessible workspace.
3. Identify the likely root cause of any discrepancy before editing SQL or proposing a corrected query.
4. Apply the smallest safe change needed to correct the query. Preserve existing intended behavior unless the evidence shows it is wrong.
5. Avoid broad rewrites, style-only edits, or migration to a different modeling pattern unless required to remove a proven defect.
6. Distinguish observations from inference. Mark unsupported assumptions explicitly.
7. Respect access boundaries. Do not suggest bypassing row-level security, protected schemas, production safeguards, or data owner controls.
8. Run syntax checks, query compilation, dry runs, unit tests, dbt tests, warehouse explain plans, or limited validation queries where available and appropriate. If execution is unavailable, state exactly what could not be run and what evidence substitutes for it.
9. Do not present a corrected result as final unless it is reproducible from inspected SQL and accessible evidence.

Review procedure:

A. Build the query intent contract
- Restate the decision or metric question in operational terms.
- Define the expected grain of the result.
- Identify required dimensions, filters, joins, exclusions, deduplication rules, aggregation logic, time window, timezone, and freshness expectations.
- Identify the authoritative definition owner when evident, such as data owner, analytics owner, finance owner, product owner, or compliance owner.
- List evidence supplied versus evidence missing.

B. Review SQL semantics before result claims
- Check whether selected columns, grouping, joins, filters, CTEs, window functions, null handling, distinct logic, and aggregation match the query intent contract.
- Check for join multiplication, accidental inner joins, incomplete predicates, slowly changing dimension issues, many-to-many joins, late-arriving data, timezone drift, partition filters, and snapshot-versus-current-state confusion.
- Check whether access constraints or row-level filters could change the observed result.
- Identify whether the SQL answers the stated question, a narrower question, a broader question, or a different question.

C. Review execution and reproducibility evidence
- Determine whether the claimed result can be traced to a specific SQL text, execution environment, parameters, source versions, run time, and data freshness state.
- If query execution is available, run the safest reproducible check permitted by the environment and record the command/query used, result shape, row counts, and relevant totals.
- If execution is not available, perform static validation and specify the minimum evidence needed from the data owner or analytics owner to complete reconciliation.

D. Reconcile against authoritative totals
- Compare the claimed result to authoritative totals, source-of-truth reports, known control totals, prior certified extracts, ledger totals, or approved metric definitions where supplied.
- Reconcile at the most useful level: total, time bucket, segment, source system, account, customer, product, or other relevant dimension.
- Explain every material variance using evidence where possible. Separate confirmed causes from plausible causes.

E. Correct only what is justified
- If the SQL is wrong and enough evidence exists, provide a corrected query or minimal patch.
- If there is not enough evidence to correct the query safely, provide a bounded correction proposal and list the exact evidence required before use.
- Preserve naming, output shape, filters, permissions, and downstream expectations unless the discrepancy requires a specific change.

Deliverable format:

1. Query intent contract
- Decision or metric question:
- Intended grain:
- Required population and exclusions:
- Required time logic and timezone:
- Required joins and source precedence:
- Authoritative definitions or totals used:
- Accountable owner to verify final use:
- Missing context that limits certainty:

2. Semantic and execution review
Create a table with columns:
- Review area
- Evidence inspected
- Observation
- Risk to claimed result
- Status: Pass / Fail / Unverified
- Notes or required follow-up

Include at minimum: grain, joins, filters, aggregation, deduplication, time logic, null handling, access boundaries, source freshness, result reproducibility, and output shape.

3. Reconciliation table
Create a table with columns:
- Reconciliation item
- AI-claimed value
- Verified or control value
- Difference
- Materiality assessment
- Evidence source
- Explanation
- Status: Reconciled / Variance explained / Unresolved

4. Unsupported-result register
Create a table with columns:
- Unsupported claim or result component
- Why it is unsupported
- Evidence needed
- Accountable owner or source to confirm
- Decision impact

5. Corrected-query and acceptance record
- Root cause summary before any SQL change:
- Minimal corrected SQL or patch, if justified:
- Behavior intentionally preserved:
- Behavior intentionally changed:
- Files changed, if any:
- Syntax checks, tests, dry runs, or executions performed:
- Verification results:
- Remaining uncertainty:
- Acceptance decision: Accept / Accept with caveats / Reject / Cannot determine
- Acceptance criteria met or unmet:
- Owner verification required before decision use:

Completion check:
- The claimed result is either reconciled, corrected and reproducible, or explicitly rejected as unsupported.
- All material assumptions are labeled.
- Any changed files are summarized.
- Verification results and unavailable checks are stated plainly.
- The final acceptance decision is tied to the acceptance threshold or materiality standard.