When should the expected one-to-many relationship at a nightly match be a declared property that fails the run rather than a number someone reads?

## What is actually being decided Every match carries an expectation about how the two sides pair: one row per key on the reference side and many on the transaction side, or one to one, or an expansion that is deliberate. That expectation exists whether or not anyone writes it down. The decision here is only about **where it is recorded and what happens when reality disagrees** - not about how the pairing works. The usual failure is not choosing wrongly. It is not choosing: the expectation stays in the head of whoever wrote the step, the data changes, the table inflates, and a number reaches a report with nobody in the path who knew what it was supposed to be. ## The ladder of responses | response | when a violation surfaces | what it costs | |---|---|---| | nothing recorded | when someone disbelieves a downstream number | days or never; the cause is far from the symptom | | count measured and logged | when someone reads the log | needs a reader; usually has none | | count published beside the output | when a consumer looks at the run's own numbers | cheap, and creates a record to compare across runs | | declared on the step, failing the run | immediately, at the step, named | a stopped pipeline, including for legitimate change | | declared, failing, with the output quarantined | immediately, and yesterday's output still stands | the most operational machinery to build | Most teams should be further down this ladder than they are, and very few should be at the top of it everywhere. ## The questions that place a given seam on that ladder 1. **What does a wrong number buy?** A total that drives an invoice, a payout or a regulatory filing is in a different class from an exploratory count. The cost of being wrong, multiplied by how long wrong survives, is the whole case for failing hard. 2. **How stable is the pairing?** A reference maintained deliberately by a team changes shape rarely, and a change is genuinely news. A feed assembled from several upstream systems may add a second row per key for perfectly good reasons, and a hard failure there trains people to disable the check. 3. **Who is awake?** A failure at 03:00 with nobody on call is only better than a wrong number if the failure leaves the previous output intact. If it takes the whole pipeline down until morning and the wrong number would have been caught at 09:00 anyway, you have converted a data defect into an availability incident. 4. **How reversible is publication?** Where consumers pull from a location you can leave untouched on failure, failing is cheap. Where the step's output is broadcast, failing late is worse than not publishing at all. 5. **What does the tool actually support?** Some tools accept the expected relationship on the match itself and raise when the data violates it. Where that exists, using it removes the distance between the assumption and its test. Where it does not, the portable form is a uniqueness assertion before the step and a predicted count checked after, and the decision above is unchanged - only the mechanism differs. ## Where the check belongs At the seam, with the step that depends on it. The same two tables can be matched in three places with three different expectations: one step wants exactly one row per key, another is content with an expansion it then reduces. A single global rule about a table cannot express that, and it fails in the wrong place, blaming a table rather than the step whose assumption was violated. Separately from anything agreed with whoever produces the data, the consuming step still has its own expectation and still has to decide what to do about it. ## What a violation should do When you do choose to fail, decide these before writing the check, not during the incident: - **Does the previous output stand?** Leaving yesterday's result in place is usually better than publishing an inflated one and almost always better than publishing nothing at all. - **What is kept for diagnosis?** The offending key values and their occurrence counts on each side are small, and they are the difference between a five-minute fix and a re-run to reproduce. - **Who decides it is the new normal?** A violation that turns out to be legitimate needs a person to change the declaration. If the fastest route back to green is to delete the check, the check will be deleted. ## The failure mode of declaring everything A check that fires often for reasons nobody considers defects is worse than no check, because it teaches a team to route around the mechanism. Reserve the hard stop for seams where the expectation is genuinely invariant and a wrong number is expensive, publish the measured relationship everywhere else, and re-examine the placement when a check has fired three times and been overridden three times - that is data about the expectation, not about the pipeline.