Signal and Silence

A medium Data Modeling interview practice problem on DataDriven. Design the schema on the canvas: tables, keys and relationships, then get feedback on it.

Domain
Data Modeling
Difficulty
medium
Seniority
L4

Problem

We run a digital classroom platform for K-12 schools where teachers post assignments and students submit them, sometimes past the due date. Leadership wants engagement insights that separate students who merely saw an assignment surface in their feed from those who actually opened and read it, and that stay accurate even as rosters change: students transfer or drop mid-semester, and the same course runs as different sections under different teachers each school year. Design a model that keeps these engagement rates correct for both a single teacher's roster and a district-wide dashboard.

They opened the assignment. Did they actually read it?

Worked solution and explanation

Strip the costume

This is three identity splits dressed up as an engagement dashboard. 'Algebra I' is two things: a curriculum template and the section Mr. Li teaches this fall. A 'view' is two things: the assignment surfaced in a feed, or a student opened it and read it. A roster is two things: who is enrolled today, and who was enrolled during the week you are measuring. Anyone can draw students, classes and events. What separates candidates is the dated enrollment bridge. Divide last week's reads by today's roster and every student who dropped mid-week leaves the denominator while their reads stay in the numerator. The dashboard trends up on churn alone, and a teacher still cannot tell a student who ignored the work from one who never saw it.

How to build it

Step 1: Split dim_courses from dim_class_instances

The course is the reusable template; the instance is one teacher, one section, one school_year. Curriculum questions roll up on course_key, teacher questions on class_instance_key. Fold them together and a syllabus edit rewrites every historical section.

Step 2: Make enrollment a dated bridge with its own key

bridge_class_enrollment resolves the student to class many-to-many and carries enrolled_at and dropped_at. Give it a surrogate enrollment_key: a student who drops and re-enrolls in the same section is two rows, so (student_key, class_instance_key) cannot be the key.

Step 3: Distinguish events by event_type

One row per event in fact_engagement_events, tagged 'impression' or 'read' and tied to the assignment it concerns. Collapse them into one view count and 'saw it but never opened it' becomes unanswerable.

Step 4: Give submissions their own fact

fact_assignment_submissions is one row per student per assignment with submitted_at, grade and is_late. That grain is not the event grain; mixing them forces a dedupe on every lateness or completion aggregate.

The shape

Every table's grain is its PK; `enrollment_key` lets one student hold several dated stints in the same section.
  • dim_students: student_key BIGINT PK, student_id VARCHAR, grade_level INT, school_id VARCHAR
  • dim_teachers: teacher_key BIGINT PK, teacher_id VARCHAR, subject VARCHAR
  • dim_courses: course_key INT PK, course_name VARCHAR, subject VARCHAR, grade_band VARCHAR
  • dim_class_instances: class_instance_key BIGINT PK, course_key INT FK, teacher_key BIGINT FK, school_year INT, section VARCHAR
  • bridge_class_enrollment: enrollment_key BIGINT PK, student_key BIGINT FK, class_instance_key BIGINT FK, enrolled_at TIMESTAMP, dropped_at TIMESTAMP
  • dim_assignments: assignment_key BIGINT PK, class_instance_key BIGINT FK, title VARCHAR, due_at TIMESTAMP, max_score INT
  • fact_engagement_events: event_id BIGINT PK, student_key BIGINT FK, class_instance_key BIGINT FK, assignment_key BIGINT FK, event_type VARCHAR, event_ts TIMESTAMP
  • fact_assignment_submissions: submission_id BIGINT PK, student_key BIGINT FK, assignment_key BIGINT FK, submitted_at TIMESTAMP, grade DECIMAL, is_late BOOLEAN
  • dim_class_instances.course_key references dim_courses.course_key
  • dim_class_instances.teacher_key references dim_teachers.teacher_key
  • bridge_class_enrollment.student_key references dim_students.student_key
  • bridge_class_enrollment.class_instance_key references dim_class_instances.class_instance_key
  • dim_assignments.class_instance_key references dim_class_instances.class_instance_key
  • fact_engagement_events.student_key references dim_students.student_key
  • fact_engagement_events.class_instance_key references dim_class_instances.class_instance_key
  • fact_engagement_events.assignment_key references dim_assignments.assignment_key
  • fact_assignment_submissions.student_key references dim_students.student_key
  • fact_assignment_submissions.assignment_key references dim_assignments.assignment_key

The query that proves it

Read rate by class instance, last 7 days
SELECT
    ci.class_instance_key,
    COUNT(DISTINCT CASE WHEN e.event_type = 'read' THEN e.student_key END) * 1.0 /
        NULLIF(COUNT(DISTINCT b.student_key), 0) AS read_rate
FROM dim_class_instances ci
JOIN bridge_class_enrollment b
    ON b.class_instance_key = ci.class_instance_key
    AND b.enrolled_at <= CURRENT_DATE
    AND (b.dropped_at IS NULL OR b.dropped_at > CURRENT_DATE - INTERVAL '7 days')
LEFT JOIN fact_engagement_events e
    ON e.class_instance_key = ci.class_instance_key
    AND e.student_key = b.student_key
    AND e.event_ts >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY ci.class_instance_key

The bridge filter and the event_ts filter share one window, so churn cannot move the rate.

Course, class instance, dated bridge
Curriculum edits touch one dim_courses row, teacher attribution is clean, and enrolled_at / dropped_at rebuild any past roster.
Single flat classes table
Fewer joins, but a syllabus change rewrites every section, and a dropped student is either invisible or double counted in read_rate.

Common follow-up questions