Skip to content

Add a proctored exam grade fact (tfact_exam_grade) to the dimensional layer #2741

Open

Description

@quazi-h

Epic: #2073 · Blocks: #2088

Description/Context

Proctored exam grades have no home in the dimensional layer. The only place they surface is marts__micromasters_dedp_exam_grades, which still reads three intermediate models. #2319 and #2403 move semester and passing_grade onto dim_course_run, but #2088 can't close until the grades themselves come from a dimensional fact.

tfact_grade isn't a good fit on its own. The exam grades come from three places, and only one of them is in tfact_grade today:

Source Grain In tfact_grade?
MicroMasters grades_proctoredexamgrade (joined to exams_examrun) learner × exam run No
MITxOnline dedicated exam course runs (course run title ends in "proctored exam", e.g. course-v1:MITxT+14.009PEx+2T2022) learner × course run grade Yes, as an ordinary course-run grade with nothing marking it as an exam
MITxOnline exams embedded in a regular course run (subsection titled "proctored exam") learner × subsection score No

The current logic for the two MITxOnline paths is in int__mitxonline__proctored_exam_grades, and for MicroMasters in int__micromasters__dedp_proctored_exam_grades. The mart dedups learners who appear in both MicroMasters and MITxOnline for the same exam run; the fact needs to keep that rule.

This was flagged in the July warehouse audit (#2375) and listed in dimensional_warehouse_todo.md, but never written up as an issue.

Plan/Design

  1. Check that every exam run with grades has a current dim_course_run row. The fact needs a courserun_fk for each; MicroMasters exam runs that aren't MITxOnline course runs may be missing. If they are, add them to dim_course_run first.
  2. Add tfact_exam_grade: one row per learner per exam attempt, with user_fk, courserun_fk, platform_fk, the grade, the source it came from, and the grade timestamp. semester and passing_grade stay on dim_course_run.
  3. Leave tfact_grade as is. Adding exam rows there would double-count the dedicated exam runs it already holds.
  4. Rebuild marts__micromasters_dedp_exam_grades on tfact_exam_grade, dim_course_run, dim_course and dim_user (which already carries the MicroMasters and MITxOnline emails and usernames), diff it against the current output, and remove it from dimensional_layering_baseline.txt. That closes Migrate marts__micromasters_dedp_exam_grades to use dimensional layer #2088.

Metadata

Metadata

Assignees

No one assigned

    Labels

    enhancementNew feature or requestproduct:data-platformIssues related to the Data Platform product

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions