Data quality

Validation happens at the boundary — the earliest point at which a record enters a system — because that is the only place where rejecting it is cheap. Once a malformed value is inside, it has been joined, aggregated and reported, and removing it means finding every derived figure it touched.

Duplicates are prevented with a constraint rather than cleaned up with a job. A uniqueness rule on the field that actually identifies the entity costs one line and prevents the entire category; a deduplication routine running nightly is a permanent tax accepted in place of that line, and it is never quite right.

Fields with a fixed set of valid answers are modeled as that set instead of as free text. A status column typed as an open string will eventually hold a value nobody planned for, entered by someone who needed one, and every report filtering on that column silently excludes it.

Reconciliation runs against the systems of record on a schedule. Counts and totals are compared, and a difference is reported with its date instead of surfacing later as a discrepancy somebody noticed by accident. The value of this is entirely in the timestamp: knowing that a divergence started on a particular Tuesday narrows the cause to whatever changed that Tuesday.

Missing is distinguished from zero and from unknown. A null revenue figure means nothing was recorded; a zero means nothing was earned, and these are different facts about the world. Coercing the first into the second produces averages that are confidently and invisibly wrong, and it is the most common defect in reporting built quickly.

Corrections leave a record. When a value is fixed, what it was, what it became, when and by whom are retained, so that a figure which changed between two readings can be explained rather than argued about.

Quality is treated as a standing property, not a project with an end date. Systems that produce bad records will keep producing them, so a cleanup that does not change the input path buys a few months of tidiness and no more. The durable fix is always upstream, at the form, the import or the integration that admitted the value.

Where a figure is known to be unreliable, it carries that on the surface instead of being silently withheld. A number marked as provisional is usable with judgement; a number removed without explanation gets reconstructed by hand in a spreadsheet, which is how a business ends up with a second set of books nobody governs.