feat: add fact_grade_status model

[Ian2012]

This PR creates a model to query the latest state of a student grade (approving/failing) with their current grade.

[openedx-webhooks]

Thanks for the pull request, @Ian2012! Please note that it may take us up to several weeks or months to complete a review and merge your PR.

Feel free to add as much of the following information to the ticket as you can:

• supporting documentation
• Open edX discussion forum threads
• timeline information ("this must be merged by XX date", and why that is)
• partner information ("this is a course on edx.org")
• any other information that can help Product understand the context for the PR

All technical communication about the code itself will be done via the GitHub pull request interface. As a reminder, our process documentation is here.

Please let us know once your PR is ready for our review and all tests are green.

[SoryRawyer]

Just so I understand the goal of the query correctly: the result of this query should have one record per learner per course, including their current grade and whether they're passing or failing the class. Is that right?

[Ian2012]

@SoryRawyer Yes, that's the goal. Provide an easy way to check the learner's grading/approving status

[SoryRawyer]

I think fact_grade_status will need to be implemented as a Superset dataset in order to use the primary key on grading_events, since the approach of getting the record associated with the most recent grading event requires either a window function or joining the fact table to itself. Both of those approaches are difficult for ClickHouse to optimize out of the box (the learner problem summary dataset is an example of this).

[Ian2012]

@SoryRawyer This is ready for review now

[SoryRawyer]

@Ian2012 what happens when you try to insert new data into one of the base tables? I'm testing fact_learner_course_grades locally and I don't see new course grades getting added to the new MV but I do see the new records in fact_grades.

[SoryRawyer]

I think I found the source of the problem: MVs need to query tables, not views. For example, this query seemed to update fact_learner_course_grades as I'd expect:

with
    ranked_grades as (
        select
            org,
            course_key,
            actor_id,
            scaled_score as course_grade,
            row_number() over (
                partition by org, course_key, actor_id order by emission_time desc
            ) as rn
        from {{ ref("grading_events") }}
        where object_id like '%/course/%'
    )

select org, course_key, actor_id, course_grade
from ranked_grades
where rn = 1

[Ian2012]

That's an strange behavior that we need to document. It will be in the best practices doc

[Ian2012]

@SoryRawyer This is the new table:

[bmtcril]

Why is documentation.yml deleted here?

[openedx-webhooks]

@Ian2012 🎉 Your pull request was merged! Please take a moment to answer a two question survey so we can improve your experience in the future.