Standardizing Longitudinal Student Retention Records

Role: Principal Academic Data Architect and Higher Education Systems Specialist

Context

• Educational Institution: {{institution_name}}
• Source Systems: {{source_sis_platforms}}
• Analysis Cohort Window: {{retention_cohort_window}}
• Demographic Taxonomy: {{demographic_attribute_standards}}
• Target Warehouse Schema: {{target_reporting_schema}}
• Privacy & Governance Policy: {{pii_sanitization_policy}}

Task

Develop a comprehensive data cleaning framework that reconciles legacy schema discrepancies, standardizes demographic identifiers, and resolves student enrollment status anomalies across {{retention_cohort_window}} to enable accurate longitudinal retention modeling for {{institution_name}}.

Method

1. Profile ingested tables from {{source_sis_platforms}} to catalog historical schema shifts, missing keys, and column drift.
2. Map disparate status codes (e.g., withdraw, leave of absence, stop-out) to unified state categories defined by {{target_reporting_schema}}.
3. Establish deterministic deduplication logic using fuzzy matching across hashed IDs adhering to {{pii_sanitization_policy}}.
4. Reconcile temporal demographic changes against {{demographic_attribute_standards}} to maintain longitudinal data fidelity.
5. Audit credit hour accumulation records to identify and flag impossible semester progression or phantom registrations.
6. Formulate deterministic rules to handle transfer credits, dual-enrollment flags, and concurrent degree programs.
7. Generate validation rules and unit tests to ensure final dataset aligns with {{target_reporting_schema}}.

Constraints

• MUST maintain full idempotency so re-running cleaning scripts preserves deterministic primary keys.
• MUST NOT destroy raw historical state; all transformations must produce reversible transformation audit logs.
• Transformations must preserve null semantics between genuine zero-credit terms and unrecorded terms.
• PII handling must strictly conform to {{pii_sanitization_policy}} without unmasked token leakage.

Output format

Provide the framework across four structured sections:

1. Ingestion & Schema Reconciliation Matrix (table mapping source columns to target).
2. Anomaly Resolution & Deduplication Logic (numbered rules).
3. State Normalization Rules (status code crosswalk).
4. Post-Cleaning Data Quality Test Suite (at least 5 automated validation checks).

Self-review

• Confirm every variable ({{institution_name}}, {{source_sis_platforms}}, {{retention_cohort_window}}, {{demographic_attribute_standards}}, {{target_reporting_schema}}, {{pii_sanitization_policy}}) is meaningfully utilized.
• Verify all 4 constraints are explicitly addressed in the logic.
• Ensure no placeholder text or unexpanded templates exist.