Institutional Research Student Retention SQL Audit Checklist

Role: Lead Institutional Research Data Officer specializing in higher education analytics and federal census compliance.

Context

• Academic Institution: {{academic_institution_name}}
• Census Term Code: {{census_term_code}}
• Enrollment SQL Script: {{enrollment_sql_script}}
• FERPA Masking Standard: {{ferpa_masking_standard}}
• Cohort Exclusion Criteria: {{cohort_exclusion_criteria}}
• Credit Threshold: {{credit_threshold}}

Task

Generate a comprehensive validation checklist to verify that {{enrollment_sql_script}} produces an accurate, repeatable, and audit-compliant student retention cohort for {{academic_institution_name}} for the {{census_term_code}} census snapshot.

Method

1. Verify that census freeze date filters match the official registrar cut-off timestamp for {{census_term_code}}.
2. Audit first-time, full-time student classification logic against the minimum {{credit_threshold}}.
3. Validate exclusion filters to ensure adherence to {{cohort_exclusion_criteria}} (e.g., deceased, military leave).
4. Inspect degree-seeking flags and non-credit course exclusions to avoid improper denominator inflation.
5. Check cohort tracking joins to ensure historical student identifiers remain persistent across academic years.
6. Evaluate aggregated demographic breakdowns against {{ferpa_masking_standard}} cell suppression rules for small populations.
7. Test script idempotency by running duplicate detection on student ID within the primary enrollment cohort.

Constraints

• Output MUST follow a hierarchical checklist format organized by reporting milestone.
• MUST NOT approve cohorts where dual-enrollment high school students are misclassified as matriculated undergrads.
• Checklist items must provide concrete SQL syntax hints (e.g., CASE statements, NULL checks).
• All validation points must be actionable by a student information systems database analyst.

Output format

• Snapshot Context Summary (bulleted metadata)
• Cohort Inclusion & Credit Load Checks (4 checklist items with [ ] checkboxes)
• Mandatory Exclusion & Exception Checks (3 checklist items with [ ] checkboxes)
• Privacy & Aggregation Suppression Checks (3 checklist items with [ ] checkboxes)
• Institutional Research Sign-off Checklist (3 items with sign-off lines)

Self-review

• Does the checklist test for both numerator and denominator accuracy in retention formulas?
• Are the suppression rules aligned with {{ferpa_masking_standard}}?
• Are credit load validation rules reflecting the {{credit_threshold}} parameter correctly?