Clean an order-line export

Task: the rules

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)

Compact layout

Steps

0 of 200

Input table:Inputs are read-only. Every step works on a copy.

Input table orders, rows 1 to 25 of 43
#
1	ing-1010-rtg	SO-00011	1	005642	2026-11-01T01:30:00-05:00	·42.10· (raw value " 42.10 ")	pending
2	ing-1027-nwa	SO-00018	1	005642	2026-02-30T10:00:00Z	N/A	pending
3	ing-1006-eed	SO-00016	3	003024	2026-01-15T10:00:00+01:00	007.5	shipped!
4	ing-1014-uy3	SO-00011	2	006281	2026-03-08T01:30:00-05:00	12.50	·Shipped (raw value " Shipped")
5	ing-1037-di1	SO-00012	1	005642	2026-13-01T00:00:00Z	100	pending
6	ing-1033-rvd	SO-00015	3	·002518⇥ (raw value " 002518\t")	2026-06-30T23:59:59.123456+05:30	0	·Shipped (raw value " Shipped")
7	ing-1011-jdm	SO-00013	1	003024	2024-02-29T12:00:00Z	0	shipped
8	ing-1008-qpf	SO-00023	2	·003024⇥ (raw value " 003024\t")	2026-11-01T01:30:00-05:00	007.5	pending
9	ing-1039-cr5	SO-00010	1	·008814⇥ (raw value " 008814\t")	2026-11-01T01:30:00-04:00	9999999999.99	·Shipped (raw value " Shipped")
10	ing-1026-j7a	SO-00013	2	008814	2024-02-29T12:00:00Z	0	shipped
11	ing-1007-m5f	SO-00017	2	007600	2026-11-01T01:30:00-04:00	-0	cancelled⇥ (raw value "cancelled\t")
12	ing-1013-ni	SO-00024	3	004475	2026-06-30T23:59:59.123456+05:30	5.	PENDING
13	ing-1038-an8	SO-00019	3	007600	2026-03-08T01:30:00-05:00	007.5	·Shipped (raw value " Shipped")
14	ing-1019-iis	SO-00021	3	004475	2026-01-15T10:00:00+01:00	·42.10· (raw value " 42.10 ")	shipped
15	ing-1022-e6t	SO-00011	2	002518	2026-11-01T01:30:00-04:00	-0	cancelled⇥ (raw value "cancelled\t")
16	ing-1020-e8w	SO-00021	3	004475	2026-01-15T10:00:00+01:00	·42.10· (raw value " 42.10 ")	shipped
17	ing-1018-fwm	SO-00011	2	··⇥ (raw value " \t")	2026-11-01T01:30:00-04:00	12.50	cancelled⇥ (raw value "cancelled\t")
18	ing-1009-c3d	SO-00014	3	001419	2026-11-01T01:30:00-04:00	-15.25	returned
19	ing-1030-dme	SO-00012	3	004475	2024-02-29T12:00:00Z	9999999999.99	ship ped
20	ing-1004-xu4	SO-00015	2	005642	2026-11-01T01:30:00-05:00	·42.10· (raw value " 42.10 ")	cancelled⇥ (raw value "cancelled\t")
21	ing-1002-8ky	SO-00011	1	008814	2026-11-01T01:30:00-04:00	12.50	cancelled⇥ (raw value "cancelled\t")
22	ing-1025-j4w	SO-00023	2	003024	2026-01-15T10:00:00+01:00	0	cancelled⇥ (raw value "cancelled\t")
23	ing-1031-upv	SO-00022	1	002518	2026-03-08T01:30:00-05:00	5.	pending
24	ing-1029-gda	SO-00013	1	·006281⇥ (raw value " 006281\t")	2026-13-01T00:00:00Z	1e3	·Shipped (raw value " Shipped")
25	ing-1024-imk	SO-00023	2	003024	2026-01-15T09:00:00Z	0	cancelled⇥ (raw value "cancelled\t")

Preview · this dataset, in your browser, not a grade

Final output not ready yet: column "region" does not exist. Add steps that produce every output column, or adjust them in Final output.

Check answer · graded on the server, including datasets you can't see

Press Check answer when you're ready. Your pipeline runs on the visible data and on hidden datasets that follow the same rules.

Learn from this exercise

A worked solution becomes available after your first check or once you've used every hint.

Skip to next: Pick one surviving record per account →Review queue

Clean an order-line export

Preview · this dataset, in your browser, not a grade

Check answer · graded on the server, including datasets you can't see

Learn from this exercise

AI tutor · may be wrong; the grader decides