Define quality in relation to a decision
Begin with the business consequence of a defect. A missing supplier address may be acceptable for a financial aggregate and unacceptable for a shipment. An invoice status delayed by several hours may be sufficient for a daily report but unsafe for an automated payment decision. The intended use determines the required control.
Assign an owner to each rule and specify its severity, evaluation frequency, and recovery action. Avoid a single quality score that hides different failure types. A useful scorecard lets an operator distinguish completeness, validity, freshness, consistency, and reconciliation issues and understand which consumers should pause.
Layer structural and semantic checks
Validate types, required fields, and ranges at ingestion, then evaluate relationships and business invariants at the appropriate processing stage. A nonnegative integer amount is structurally valid, but the invoice may still reference an unknown supplier or have allocations that violate the approved accounting rules.
Use database constraints for invariants they can safely enforce, and understand their limits. In PostgreSQL, a check constraint that evaluates to null does not reject a row, so required values need a NOT NULL constraint as appropriate. Cross record rules may require transactional logic, reconciliation, or specialized checks instead of a simple field predicate.
CREATE TABLE invoice_reference (
tenant_id text NOT NULL,
id text NOT NULL,
amount_minor bigint NOT NULL CHECK (amount_minor >= 0),
currency text NOT NULL CHECK (currency ~ '^[A-Z]{3}$'),
PRIMARY KEY (tenant_id, id)
);Measure freshness and reconcile at the right grain
Track source event time, ingestion time, and projection update time separately. A pipeline can be actively processing old records while its dashboard looks operationally busy. Measure the age of the business information needed by consumers, not only the time since the last successful worker heartbeat.
Reconcile counts and amounts using compatible grain, time boundaries, and inclusion rules. Account for legitimate source corrections and in flight changes before raising a discrepancy. Keep a record of reconciliation windows and unresolved exceptions so recovery can be checked against evidence instead of merely restarting the pipeline.
Design quarantine and repair as part of the pipeline
When a record fails a critical check, preserve its identity and safe diagnostic context in a controlled exception path. Decide whether the failure blocks a partition, a business entity, or only that record. Isolating bad data without an owner or replay mechanism simply creates an invisible backlog.
After repair, replay through the same validation and idempotency checks used for normal processing. Verify downstream effects and record the reconciliation results. Sample accepted records too: a high pass rate can conceal a faulty validation rule.