# AI data cleaning: fictional source and decision packet

OpenMax editorial teaching material · Version 1 · September 4, 2026.
This is not customer data, a product export or an authorization to change real records.

## Source manifest and field dictionary

Source `CLEAN-070-RAW-v1` contains eight physical rows. The intended grain is one observation per job, but the manifest explicitly identifies R3 as a repeated delivery of R2, not a second event or a revision. Retain the complete raw extract. Candidate version `CLEAN-070-CAND-v1` excludes R3 from use while preserving its reference to R2.

All raw business fields below are strings. Backticks show exact strings; `""` denotes an empty string, not a quote-containing value. Spaces inside ` Done ` are significant raw characters. Source row IDs identify physical received rows and are not job IDs.

| source_row | job_id | region | quantity | status | completed_date |
|---|---|---|---|---|---|
| R1 | `0001` | `NA` | `10` | `done` | `2026-09-01` |
| R2 | `0002` | `EU` | `20` | ` Done ` | `2026-09-01` |
| R3 | `0002` | `EU` | `20` | ` Done ` | `2026-09-01` |
| R4 | `0003` | `EU` | `1,200` | `done` | `2026-09-01` |
| R5 | `0004` | `EU` | `""` | `done` | `2026-09-01` |
| R6 | `0005` | `EU` | `5` | `BETA` | `2026-09-01` |
| R7 | `""` | `EU` | `7` | `done` | `2026-09-01` |
| R8 | `0006` | `EU` | `30` | `done` | `03/04/2026` |

- `job_id`: exactly four ASCII digits, stored as text; leading zeros are meaningful. Empty is invalid. Nonempty IDs must be unique at the stated grain after the documented delivery exclusion.
- `region`: `NA` and `EU` are the only permitted fictional region labels; `NA` is not missing. No real country or customer identity is implied.
- `quantity`: nonnegative integer individual units. ASCII digits are permitted; grouped integers use English-US commas, with a first group of one to three digits and all later groups of exactly three. An empty string means unknown, not zero. No other unit or locale conversion is supplied.
- `status`: `done` and the exact raw variant ` Done ` map to `done`. `BETA` has no approved mapping. This is an explicit narrow crosswalk, not permission to normalize arbitrary strings globally.
- `completed_date`: a valid calendar date in `YYYY-MM-DD`. No slash-date format is supplied. Date-only values in this fixture need no time-zone conversion. The report period is September 2026; selecting a slash interpretation to change inclusion is prohibited.
- Candidate eligibility requires a valid key, region, quantity, approved `done` status, and a valid in-period date. Missing or unresolved requirements cause a hold. A reference-owner table is not part of this eight-row fixture; the null-join probe below is a separate input.

## Rules and row-level decisions

The rule set is `CLEAN-070-RULES-v1`. Its authority is this fictional dictionary and manifest, not a model suggestion. `D1` identifies repeated delivery; `I1` preserves and validates identifiers; `R1` checks region; `Q1` parses or holds quantity; `S1` applies the crosswalk; `T1` validates date and period; `G1` combines eligibility conditions.

| Row | Before → candidate treatment | Disposition | Rule and reason |
|---|---|---|---|
| R1 | `0001` and `NA` unchanged; `10` → integer 10 | Eligible | I1/R1/Q1/S1/T1 pass; no missing-token substitution |
| R2 | ` Done ` → `done`; `20` → integer 20 | Eligible | S1 explicitly permits mapping; other checks pass |
| R3 | Preserve complete raw payload; reference retained R2 | Excluded delivery | D1 manifest instruction, not arbitrary first-row deduplication |
| R4 | `1,200` → integer 1200; original string preserved | Eligible | Q1 supplied grouping rule; other checks pass |
| R5 | Empty quantity → unknown normalized quantity | Hold | Q1 required measurement absent; request source correction |
| R6 | `BETA` unchanged in raw; no standard status assigned | Hold | S1 no mapping; request category definition |
| R7 | Empty ID retained; no invented or default ID | Hold | I1 invalid key; request valid source identifier |
| R8 | `03/04/2026` preserved; normalized date unavailable | Hold | T1 unknown format; request unambiguous source date |

Other resolvable fields can have derived values in a held record, but that does not make the whole record eligible. A hold is not deletion. The original strings and the source-row link remain available for every disposition.

## Reconciliation and release boundary

- Raw physical rows: 8 = 1 delivery exclusion + 3 eligible + 4 held.
- After delivery exclusion: 7 observations, including one missing job ID. Do not call this seven valid unique job IDs.
- Eligible: R1, R2, R4; quantities 10 + 20 + 1200 = 1230 units.
- Held: R5, R6, R7, R8; known quantities 5 + 7 + 30 = 42 units, plus one unknown quantity.
- Raw available-value subtotal: 1292. Subtract duplicate delivery 20 → 1272 = 1230 + 42. The full total remains unknown.
- Eligibility among post-exclusion observations: 3/7 ≈ 42.86%; among received physical rows: 3/8 = 37.5%. Neither is AI accuracy.

This candidate is not approved for a complete September-volume report. A limited use of the three-record subset requires an explicit owner decision accepting the partial population and describing exclusions and holds. No real owner has granted such permission through this worksheet. Record the recipient, purpose, candidate version, decision time and original-data reference before any real release.

## Two separate library-behavior probes

Executed locally with pandas 3.0.1. The online references checked on September 4, 2026 were labeled 3.0.5. The probes do not test OpenMax or all import/merge implementations.

```python
from io import StringIO
import pandas as pd

sample = 'job_id,region,quantity\n0001,NA,10\n0002,EU,\n'
default = pd.read_csv(StringIO(sample))
preserved = pd.read_csv(StringIO(sample), dtype=str, keep_default_na=False)
assert default.loc[0, 'job_id'] == 1
assert pd.isna(default.loc[0, 'region'])
assert preserved.loc[0, 'job_id'] == '0001'
assert preserved.loc[0, 'region'] == 'NA'
assert preserved.loc[1, 'quantity'] == ''

left = pd.DataFrame({'key': ['0001', None], 'units': [10, 7]})
right = pd.DataFrame({'key': ['0001', None], 'owner': ['Team1', 'Unassigned']})
joined = left.merge(right, on='key', validate='many_to_one', indicator=True)
assert joined.loc[joined['key'].isna(), 'owner'].tolist() == ['Unassigned']
valid_left = left[left['key'].notna() & left['key'].ne('')]
valid_right = right[right['key'].notna() & right['key'].ne('')]
candidate = valid_left.merge(valid_right, on='key', validate='many_to_one')
assert len(candidate) == 1
assert len(left) - len(valid_left) == 1
```

These settings demonstrate a boundary, not a universal recipe. Preserving all strings also leaves empty quantities as strings; later field-specific validation must decide their meaning. Excluding invalid keys from a join does not authorize dropping the original records from the reconciliation.

Sources: [pandas read_csv](https://pandas.pydata.org/docs/reference/api/pandas.read_csv.html) and [pandas merge](https://pandas.pydata.org/docs/reference/api/pandas.merge.html).