Implementation · Data quality · Worked artifact

A small migration file, made reviewable.

This is the kind of compact review record I want a handoff to carry: source rows stay traceable, straightforward mappings are visible, and unresolved decisions have an owner.

Fictional demonstration. This 10-row fixture uses invented identifiers and neutral categories. It illustrates a review method, not a historical client file, a measured employer result, or a claim about data accuracy.

Download Excel workbook Download input CSV Download reviewed CSV

The Excel workbook includes editable fictional inputs, working formulas, and an exception review. The CSVs preserve the original example below.

Start with the question.

Can every row be traced, categorized, and related to another record without a reviewer having to invent an answer?

Scenario

A migration owner receives a small legacy export of generic records. Before loading it into a new system, the reviewer checks the category vocabulary, identifier uniqueness, and parent-child relationships.

The output is not a “cleaned” file that hides uncertainty. It is a decision record: rows that pass can move forward, explicit corrections are visible, and holds say exactly what someone still needs to decide.

Source rows: 10
Output rows: 10
Ready: 2
Corrected: 3
Held: 5
Dropped: 0

Reconciles: 2 ready + 3 corrected + 5 held for review = 10 output rows; 10 input rows − 0 dropped = 10 output rows.

Write the rules before the edits.

The same checks drive the visible decisions below, so another reviewer can reproduce the result.

Input rules

Lineage: source_row_id is required and unique. Each input row appears once in the output.
Identifier: legacy_record_id must be unique. A duplicate group is held together; no survivor is guessed.
Category: legacy_category is required and must appear in the explicit map. Blank or unmapped values are held.
Relationship: standalone has no parent; child needs an existing, different parent ID. A self-reference is held.
Status: only rows passing every rule are ready. A mapped value is corrected; any unresolved issue is held-for-review.

Explicit category map

Legacy category to reviewed category
Legacy value	Reviewed value	Action
Standard	standard	Pass through when all other checks pass.
Core	standard	Correct with the explicit map.
Addon	supplemental	Correct with the explicit map.
Archived	historical	Correct with the explicit map.
Legacy-Blue	—	Hold; no approved mapping exists.

A category map can propose a value; it cannot override a separate relationship hold.

Input: preserve the source.

These are the ten fictional rows as supplied. The blank category, duplicate identifier, unmapped value, and self-reference remain visible.

Download input CSV

Input rows — 10 records
source_row_id	legacy_record_id	legacy_category	relationship_type	parent_legacy_id	active_flag
IN-001	LEG-1001	Standard	standalone	—	Y
IN-002	LEG-1002	Core	child	LEG-1001	Y
IN-003	LEG-1003	Addon	child	LEG-1001	Y
IN-004	LEG-1004	Standard	standalone	—	Y
IN-005	LEG-1004	Standard	standalone	—	Y
IN-006	LEG-1006	—	child	LEG-1001	Y
IN-007	LEG-1007	Legacy-Blue	standalone	—	Y
IN-008	LEG-1008	Archived	child	LEG-1008	Y
IN-009	LEG-1009	Archived	child	LEG-1001	N
IN-010	LEG-1010	Standard	child	LEG-1001	Y

Source row IDs are synthetic and are carried into the reviewed file as the lineage key.

Checks become decisions.

Each issue has a visible disposition. “Held” means the next owner has a question to answer, not that the reviewer silently fixed it.

1 group · 2 rows

Duplicate identifier

Affected rows: IN-004 and IN-005

LEG-1004 appears twice. Both rows stay in the output; no survivor is selected and no row is deleted.

1 row

Blank required category

Affected rows: IN-006

The missing category is not guessed. A category steward must provide the source value before loading.

1 row

Unmapped legacy category

Affected rows: IN-007

Legacy-Blue is absent from the map. The mapping owner must decide; the row remains held.

1 row

Relationship inconsistency

Affected rows: IN-008

LEG-1008 points to itself as its parent. The implementation owner must correct the relationship.

Why not choose a duplicate survivor?

The fixture has no evidence for which copy of LEG-1004 is authoritative. Retaining both rows protects lineage and makes the decision explicit; a migration owner can later document a merge or survivor rule.

Reviewed output: ready, corrected, held.

The output keeps one row for every input row. Status is text, not color alone, and the decision plus source lineage travel with each record.

Download reviewed CSV

Ready all rules pass
Corrected explicit mapping applied
Held for review decision still needed

Reviewed output — 10 records with source lineage
source_row_id	legacy_record_id	legacy_category	relationship_type	parent_legacy_id	reviewed_category	review_status	decision	source_lineage
IN-001	LEG-1001	Standard	standalone	—	standard	Ready	All checks pass; no value changed.	Input row IN-001 retained.
IN-002	LEG-1002	Core	child	LEG-1001	standard	Corrected	Mapped Core to standard using the explicit category map.	Input row IN-002 retained.
IN-003	LEG-1003	Addon	child	LEG-1001	supplemental	Corrected	Mapped Addon to supplemental using the explicit category map.	Input row IN-003 retained.
IN-004	LEG-1004	Standard	standalone	—	standard	Held for review	Duplicate identifier LEG-1004 with IN-005; retain both rows and select no survivor.	Input row IN-004 retained; duplicate group with IN-005.
IN-005	LEG-1004	Standard	standalone	—	standard	Held for review	Duplicate identifier LEG-1004 with IN-004; retain both rows and select no survivor.	Input row IN-005 retained; duplicate group with IN-004.
IN-006	LEG-1006	—	child	LEG-1001	—	Held for review	Required legacy_category is blank; category steward must provide the source value.	Input row IN-006 retained.
IN-007	LEG-1007	Legacy-Blue	standalone	—	—	Held for review	Legacy category Legacy-Blue has no approved mapping; mapping owner must decide.	Input row IN-007 retained.
IN-008	LEG-1008	Archived	child	LEG-1008	historical	Held for review	Child row points to itself (LEG-1008); implementation owner must correct the relationship.	Input row IN-008 retained.
IN-009	LEG-1009	Archived	child	LEG-1001	historical	Corrected	Mapped Archived to historical using the explicit category map.	Input row IN-009 retained.
IN-010	LEG-1010	Standard	child	LEG-1001	standard	Ready	All checks pass; no value changed.	Input row IN-010 retained.

The reviewed CSV preserves all ten input rows. The two-row duplicate group is held intact, and the unmapped Legacy-Blue value remains blank until an owner approves a mapping.

End with a usable handoff.

The next person should know what is unresolved, who can decide it, and what to check afterward.

Next check

After the owners update the authorized source record, rerun all five rules against the reviewed CSV and reconcile source-row lineage before loading.

Guardrail: no row is load-ready merely because a category can be mapped; every relationship and identifier check must also pass.

Unresolved decisions: Confirm a survivor or merge rule for LEG-1004; supply IN-006’s category; approve a target for Legacy-Blue; correct IN-008’s self-reference.
Responsible role: Migration owner coordinates the duplicate decision; category steward supplies the blank value; mapping owner approves the unmapped value; implementation owner corrects the relationship.
Evidence to carry forward: Keep the source row ID, the reviewed status, the decision text, and the owner’s resolution note with the next check.

Composite, anonymized examples. No real member information. Views are my own and don't represent HCSC (HealthSpring).