Turn data-quality findings into explicit rules

Understand the method

Data quality is fitness for the question you are trying to answer. A blank delivery duration prevents a complete delivery comparison, but it does not automatically invalidate an otherwise verified order amount. A missing order amount directly blocks a sales total until it is resolved or the affected value is explicitly excluded. Avoid assigning one decorative quality score to the entire file. Write specific rules and connect each exception to a calculation or decision.

Translate definitions into tests

Uniqueness means order_id appears once at the analytical order grain. Validity means a field satisfies its declared representation or domain: an ISO calendar date must actually exist; channel must be web or store after an authorised formatting rule; status must be completed, returned or cancelled. order_value_eur must be a non-negative number for this exercise. A negative amount could be legitimate in another accounting model, but the supplied order-value contract does not represent refunds as negative orders.

Completeness depends on required fields. An order ID, date, status, channel and value are required for these grouped financial summaries. Delivery duration can be absent and must remain distinguishable from a valid zero. Consistency asks whether information agrees across records or sources, such as the conflicting channel labels attached to Y007. Accuracy requires an authoritative comparison: a plausible value or a passing format test cannot prove that a source amount is correct.

Count different kinds of exceptions separately

The raw file has one exact duplicate group, A004, and one conflicting duplicate group, Y007. Each group contributes one excess row. It also contains a channel spelling/whitespace variation at A005, an impossible April date at A008, a negative amount at A009, an unrecognised channel at Y003, a blank value at Y005 and invalid delivery text at Y006. These are eight investigation entries. They are not eight interchangeable rows to delete.

There are also genuine missing delivery measurements. The raw blank count is affected by a duplicated blank record; profile it again after duplicate resolution. This illustrates why the order of checks matters. Keep separate counts for blank, malformed, outside-domain and conflicting values. A valid high-value order is not a validity exception merely because it is unusual. We investigate that order in the distribution lesson.

Choose a proportionate action

Use a severity statement that names the consequence. Y005 blocks a full sales sum because its amount is unavailable. Y007 blocks a trustworthy channel breakdown until the conflict is resolved. Y006 affects delivery analysis but leaves an independently verified sales amount usable. A formatting change at A005 is deterministic under the declared lowercase-and-trim rule. Mapping “mobile” to a channel would require source evidence because a device label does not establish whether the order belongs to web or store.

State what tests cannot establish

Passing these rules makes the fixture suitable for the defined exercise. It does not demonstrate that every real transaction was exported, that a reporting pipeline is timely or that an amount reflects accounting revenue. Maintain a separate list of questions for the source owner: export coverage, status timing, treatment of returns and the meaning of absent delivery records. These unanswered questions belong beside your findings rather than disappearing after a clean profile.