A clean table needs a contract. Design useful quality gates.

Give one row a meaning

An original order feed contains an order identifier, customer identifier and amount in integer units. Before selecting a cleaning function, define one output row: one accepted business order. A raw delivery is not necessarily a new order. A retry can repeat an identifier, while a correction can reuse an identifier with changed values.

Write a compact contract. Specify the identifier scope, whether amounts can be zero, the representation of missing values, and the customer relationship. A column named amount does not tell a reader whether it stores major currency units, minor units or a quantity. Those meanings belong in the contract.

Make rejection explainable

Consider these independent example arrivals:

Order	Customer known?	Amount units	Decision
o-11	yes	0	Accept if zero is permitted
o-12	no	80	Reject missing relationship
o-13	no	-5	Reject invalid amount under an amount-first rule
o-14	yes	NULL	Reject missing amount

An order can violate several rules at once. Decide whether the output contains every violation or one prioritized reason. Those are different grains. If a rejected order has two violation rows, summing violation rows will overstate the number of rejected orders.

Reconcile the populations

Keep separate counts for raw arrivals, distinct identifiers, accepted business records and rejected business records. These numbers need not be equal. Retries increase arrivals without increasing unique orders. A conflict may prevent an identifier from entering the accepted dataset at all.

Define the reconciliation equation at the chosen grain. If each unique identifier must end up accepted or rejected, compare unique inputs with those two mutually exclusive outputs. If some records remain pending reference data, include that explicit third state. Do not hide pending records by silently dropping unmatched joins.

Choose a failure policy deliberately

A diagnostic rule can record violations while letting processing continue. A stricter rule can exclude bad rows. Another policy can stop publication entirely. The right choice depends on the downstream contract and failure impact; a dashboard metric and a financial settlement feed may need different responses.

In a layered pipeline, preserve the original delivery and enough lineage to explain the decision. An intermediate table should make transformations and exceptions inspectable. A business-ready aggregate should have a clear owner, freshness expectation and documented grain. Layer names alone do not create those guarantees.

Treat a rule change as a version change

If zero amounts become invalid tomorrow, the same historical data may produce different results. Record the rule version and decide whether to replay history. Otherwise a changed rejection count may look like a source incident when it actually came from a policy update.

Test missing references, duplicate delivery, conflicting identifiers, empty inputs and a rule boundary. Then explain what the finite fixture suite leaves unproven. Local SQL practice can develop the reasoning; a real pipeline still needs platform-specific operational validation, access controls and a recovery plan.