Skip to content

Data lake

The lake is a set of Parquet files partitioned by video_name, in processing/lake/, generated by the Polars pipeline (processing/pipelines/polars_pipeline.py) and queried in SQL by DuckDB on the web side (web/src/lib/server/gold.ts and the analytics module).

Overview

flowchart TD
   subgraph bronze["🧱 BRONZE — raw"]
      B["bronze/annotations/video_id=*/<br/>raw annotations CSV → parquet"]
   end
   subgraph silver["🥈 SILVER (normalized)"]
      S["silver/windows/video_id=*/<br/>7 categories A–G per 15 s window<br/>+ rejects"]
   end
   subgraph gold["🥇 GOLD — analytical product"]
      G1["gold/class_session"] --- G2["gold/student_session"]
      G2 --- G3["gold/engagement_windows"]
      G3 --- G4["gold/timeline"]
      G4 --- G5["gold/student_history"]
      R["ref/sessions.csv · students.csv"]
   end
   CSV["annotations CSV<br/>(merged)"] --> B
   B --> S
   S --> G1 & G2 & G3 & G4 & G5 --> API["API (DuckDB SQL)"]
   R -. reads .-> API

Step by step

1. ingest_bronze — raw

Reads the merged annotations CSV and writes parquet partitioned by video_name. Key functions: partitioning + writing, no business transformation.

2. build_silver — normalization

  • Normalizes free-text categories/labels → codes (A1…G4) via scoring.normalise_category/label (tolerant to variants: “on task” → A1).
  • Segments → 15 s windows (_segments_to_windows), pivot 7 columns A–G.
  • Fills missing windows:
  • Posture C: forward_fill (posture is sampled every 60 s);
  • defaults per category (rule R2): A→A2, B→B4, C→C1, D→D5, E→E1, F→F1, G→G4.
  • Rejected rows (non-normalizable labels) are written to silver/rejects.

3. build_gold — the product

  • _score_windows: score of each window (see Metrics), level, window_flags.
  • _student_session: phase-1 metrics (on-task, shifts, drift, recovery, sustained, flags).
  • _class_session: class aggregates (engagement %, flagged students, averages).
  • _timeline: per-minute average + engaged share (share_engaged).
  • student_history: cumulative history per student.

Lake layout

processing/lake/
├── bronze/
│   └── annotations/video_id=<lesson>.mp4/part-*.parquet
├── silver/
│   ├── windows/video_id=<lesson>.mp4/part-*.parquet
│   └── rejects/part-*.parquet
├── gold/
│   ├── class_session/part-*.parquet
│   ├── student_session/video_id=<lesson>.mp4/part-*.parquet
│   ├── engagement_windows/video_id=<lesson>.mp4/part-*.parquet
│   ├── timeline/video_id=<lesson>.mp4/part-*.parquet
│   └── student_history/video_id=<lesson>.mp4/part-*.parquet
└── ref/
    ├── sessions.csv   (15 lessons — the dashboard list)
    └── students.csv   (31 students)

Important for testers

The dashboard course list is driven by ref/sessions.csv + the gold layer (INNER JOIN on video_id) — not by the Postgres sessions table. If a lesson has no gold row, it disappears from the dashboard.