What makes an automated statement-count check trustworthy instead of a flaky test the team eventually deletes?

Teams rarely fail to write the first statement-count assertion. They fail to still have it a year later. The assertion that survives is the one that fails **only** when the access pattern changed, and that tells the person who broke it exactly what to do next. Everything below is about engineering those two properties. ## Count only the work under test A test does more with the database than the operation being measured. Fixtures are inserted, a schema may be prepared, the first use of a connection can emit setup or metadata statements, transaction control is issued. If any of that lands inside the measured window the number moves for reasons that have nothing to do with the code under test. - Start the counter **after** setup has run and the connection has been used at least once. - Measure a single explicit window around the operation, reset at its start, read at its end. - Decide whether transaction control statements are inside the window and apply that decision everywhere. - Make sure nothing shared between tests leaks in — a cache warmed by an earlier test changes the count of a later one, which is how an assertion becomes order-dependent. ## Assert on the property, not on a number you happened to observe There are three shapes of assertion, and they are not equally good. | Assertion | Fails when | Weakness | |---|---|---| | Exact count equals `n` | any change at all, including harmless ones | noisy; gets "fixed" by editing the number without thought | | Count is at most `n` | the pattern gets worse | silently allows the count to fall and never notices an improvement regressing later | | Count does not grow when the row count grows | the pattern multiplies | needs two runs with different data, but is the closest to the real property | The third is the one that actually encodes what you care about: run the operation over one set of rows, run it over a larger set, assert the counts are the same. It ignores the arbitrary baseline entirely, so it does not break when someone legitimately adds a lookup, and it cannot be satisfied by editing a number. Where that is too expensive, an upper bound is a reasonable compromise; an exact count is the most brittle and should be reserved for a path whose statement sequence is genuinely part of its contract. ## Make the failure message do the work A count assertion that says `expected 3 but was 47` has told the reader almost nothing, and the cheapest way to make it green is to change the 3. A failure that dumps the statements grouped by text, with the repeated shape and its repetition count at the top, converts the failure into a diagnosis: the reader can see which read multiplied. This single detail is the difference between an assertion the team fixes and one the team deletes. ## Plan for the legitimate raise The count *will* need to change, because features add reads. That is not a defect in the assertion; the defect is having no story for it. Two rules keep it honest: 1. The expected number changes **in the same change** as the code that moved it, so a reviewer sees the access pattern change as a diff and can ask about it. A number quietly bumped in a follow-up commit is invisible. 2. Raising it needs a reason in the commit or the review, the same way any other budget increase does. Lowering it never needs one. ## The failure modes worth naming out loud - **A count that depends on the amount of data** — if the fixture size varies between runs, an exact assertion on a per-row pattern is guaranteed to flake, and the flake is telling you the pattern is wrong; do not fix it by widening the bound until it passes. - **Non-deterministic ordering or caching** — an identity map, a shared cache, or a batch grouping decision that depends on timing all move the number; either make the caches cold at the window's start or assert a bound. - **Assertions written after the fact against whatever the code currently does** — this bakes in a per-row read as the expected baseline. When adding assertions to existing code, look at the number first and decide whether it is a baseline or a bug. - **Testing a path nobody uses** — a count assertion around a repository method proves nothing about the request that composes several of them. Measure at the boundary the user actually goes through. ## Why this is a senior question Anyone can add an assertion. The judgment being probed is whether you understand that a test's *cost* is what determines whether it lives: an assertion that fails for reasons the reader cannot interpret is a tax, and teams remove taxes. Designing the window, choosing the property over the number, and making the failure self-explaining are what turn a clever check into a durable guard rail.