Batch Genealogy Traceability Data Cleansing Audit

Role: Senior Quality Informatics Architect specializing in manufacturing batch genealogy reconciliation and regulated traceability systems.

Context

• Target production unit: {{manufacturing_unit}}
• Integrated source platforms: {{erp_mes_sources}}
• Observed data discrepancy: {{duplicate_record_rate}}
• Allowed timestamp variance: {{timestamp_skew_tolerance}}
• Compliance standard: {{regulatory_standard}}
• Defect resolution protocol: {{target_quarantine_policy}}

Task

Author a forensic data cleansing and reconciliation report that identifies, isolates, and rectifies conflicting batch genealogy records across {{erp_mes_sources}} in {{manufacturing_unit}}, ensuring strict audit compliance under {{regulatory_standard}}.

Method

1. Map data models between {{erp_mes_sources}} to trace primary key mismatches, split-lot parentage breaks, and orphan work-order steps.
2. Quantify the operational impact of {{duplicate_record_rate}} across work-in-progress (WIP) tracking and raw material consumption records.
3. Establish chronological reconciliation algorithms to correct timestamp skew within {{timestamp_skew_tolerance}} across unsynchronized line controllers.
4. Design entity-matching heuristics to resolve fragmented material lot identifiers and sub-assembly transfer events.
5. Define deterministic validation rules to eliminate phantom inventory records and circular lot references.
6. Formulate a compliant data quarantine and remediation workflow aligned with {{target_quarantine_policy}} that logs all cleansing actions in an audit-ready trail.
7. Detail automated reconciliation jobs, exception escalations, and human-in-the-loop review thresholds for irreversible lot-merging actions.

Constraints

• MUST enforce full compliance with data integrity mandates in {{regulatory_standard}}.
• MUST NOT allow destructive deletion of source records; all adjustments must use immutable append-only versioning.
• Cleansing logic MUST flag any unresolved genealogy breaks exceeding {{timestamp_skew_tolerance}} for quarantine.
• All proposed data transformations must maintain unambiguous chain-of-custody.

Output format

Provide a formal data governance and remediation report organized as follows:

1. Audit Context & Lineage Integrity Assessment (max 250 words)
2. Source Mapping & Discrepancy Breakdown (detailed table of mismatch types)
3. Record Reconciliation & Cleansing Rules (step-by-step logic specifications)
4. Quarantine & Exception Handling Protocol (aligned with {{target_quarantine_policy}})
5. Audit Trail & Verification Requirements (schema definitions for change logs)
6. Ongoing Automated Hygiene Plan (preventative maintenance schedule)

Self-review

• Does the remediation plan strictly comply with {{regulatory_standard}} record retention rules?
• Are orphan record resolution mechanisms robust against false-positive batch merges?
• Is the audit trail format completely immutable and reproducible?