Clean an order-line export · SQL

Inputs
  orders(ingest_id, order_id, line_no, customer_code, order_ts, amount, status) — all text, any cell may be missing.
  customers(customer_code, region) — already validated: codes are trimmed and unique when present; a code may be missing.

Normalize
  customer_code: remove ASCII whitespace (space, tab, CR, LF) at both ends; an empty result is missing. Keep leading zeros.
  status: remove ASCII whitespace at both ends, then lowercase.
  amount: remove ASCII whitespace at both ends.
  line_no: whole number.

Quarantine each invalid row to exceptions with the FIRST failing reason, checked in this order:
  1. bad_timestamp     order_ts is missing, or is not exactly YYYY-MM-DD, "T" or one space, HH:MM[:SS[.f up to 6 digits]],
                       then an explicit offset (Z or ±HH:MM, at most ±14:00); hour 00–23, minute/second 00–59;
                       the date must exist on the calendar. A timestamp without an offset is invalid (never assume a zone).
  2. bad_amount        amount is missing or not: optional "-", 1–10 digits, optionally "." and 1–2 digits. Refunds (negative) are valid.
  3. bad_status        status is not one of: shipped, pending, cancelled.
  4. unknown_customer  customer_code is missing or has no matching customer. A missing code never matches a missing code.

Deduplicate the remaining rows
  The grain is (order_id, line_no). Keep the row with the latest order_ts instant; on a tie keep the smallest ingest_id
  (code-point order). Every other row goes to exceptions with reason "duplicate".

Outputs (row order does not matter)
  clean(order_id text, line_no int64, customer_code text, region text, order_ts timestamptz [UTC], amount decimal(12,2), status text)
  exceptions(ingest_id text, reason text)