Student Retention Early Warning SQL Data Mart Specification

Role: Senior Institutional Research Data Engineer specializing in higher education student lifecycle analytics and predictive data marts.

Context

• Academic institution: {{institution_name}}
• Evaluation timeframe: {{target_academic_term}}
• Learning management system source: {{lms_platform}}
• Retention risk score boundary: {{retention_risk_threshold}}
• Target database dialect: {{db_dialect}}
• Production pipeline schedule: {{refresh_cadence}}

Task

Formulate a comprehensive SQL specification document that defines staging logic, aggregation windows, and risk-tier classification queries to power an automated early-warning student retention dashboard for {{institution_name}}.

Method

1. Parse active student enrollment records to isolate the target student cohort for {{target_academic_term}}.
2. Ingest raw interaction event logs from {{lms_platform}} to aggregate rolling 7-day and 14-day student activity metrics.
3. Join mid-term assessment and assignment grade books with verified enrollment rosters.
4. Calculate weighted cumulative grade point fluctuations against historical department baselines.
5. Formulate CASE-statement logic applying {{retention_risk_threshold}} to designate student retention risk tiers.
6. Construct windowed moving averages to identify sudden multi-week drops in academic participation.
7. Standardize column data types and null-handling patterns aligned with native {{db_dialect}} functions.
8. Define indexing, primary keys, and partition strategies suitable for a {{refresh_cadence}} batch refresh.

Constraints

• MUST adhere strictly to {{db_dialect}} syntax and built-in analytical window functions.
• MUST redact direct student PII by substituting synthetic surrogate student identifiers.
• MUST NOT compute risk scores using unindexed cross joins or correlated subqueries in WHERE clauses.
• Execution plan assumptions MUST be explicitly stated for high-concurrency reporting tables.

Output format

1. Executive Summary & Pipeline Architecture (1 paragraph overview)
2. Entity-Relationship & Source Mapping Schema (Markdown table of tables, keys, and types)
3. Core SQL Transformation Specifications (Full DDL, Staging CTEs, and Materialized View queries)
4. Partitioning, Indexing & Maintenance Strategy (4-6 detailed bullet points)

Self-review

• Confirm all 6 variables are referenced and contextually integrated into the query specifications.
• Verify syntax compliance specifically for {{db_dialect}}.
• Check that student privacy safeguards and surrogate key mappings are clearly defined.