references/query-specification.md

# Translate requirements into an SQL contract

A useful query specification states **result grain**, business measure, unique key, source tables, join relationships, filters, effective timezone, treatment of NULL/missing values, handling of ties, target SQL dialect and expected output columns.

## Example contract
"One row per customer per calendar month; total captured payments, excluding refunds; report date from America/Chicago; customer identifier is textual; months without activity omitted." This differs materially from "one row per order" or "one row per refund."

Start from source grains before writing SQL. A payment can have multiple captures; an order can have several items. A two-way join can multiply an order-level measure when neither side is unique on the join key. Correct the grain upstream or aggregate at the intended level.

## Constraints
Prefer half-open timestamp ranges for event-time intervals. Clarify whether status is historical or current, and which date is used when source and business calendars differ. Name chosen assumptions so a user can correct them. When fields or keys are unknown, a clearly marked schema placeholder is better than an invented column.

## Validation contract
Produce at least one key uniqueness test for dimension-like outputs and a total reconciliation for monetary outputs. Use COUNT(DISTINCT) carefully: it can hide duplicate fanout rather than explain it.