`fact_video_segments` is not incrementally populated

Summary

reporting.fact_video_segments is empty in any deployment that has not run
dbt run --full-refresh after learners watched videos, and it silently stops advancing
from whatever moment it was last full-refreshed.

I believe this is structural rather than environmental: the model is declared as a plain
materialized view, but its SELECT joins two events that can never arrive in the same insert
block. I would like to check that reading with you before proposing a change, in case the
current materialization is deliberate and I'm missing the intended usage.

Everything downstream is affected: the watched_video_segments and
at_risk_watched_video_segments datasets, the "Number of Views across Video Duration" and
"Unique vs. Repeat Views" charts, the Individual Learner and At-Risk Learners dashboards,
and the in-context video graph in Studio and the instructor dashboard.

Evidence

I have been testing this in several ulmo and verawood instances.
Notably:
https://apps.ulmo.openedx.io
https://verawood.releases.edunext.link/
https://ulmo.releases.edunext.link/

During my testing I have played this video with a lot of play pause events and also leaving it running from start to end to no avail.

On a production instance that was upgraded and already had video activity, the table is not
empty but frozen: it holds 1,244 rows while the MV's own SELECT, run as a query, returns
2,973. The rows it has are the back-fill from when the model was first built.

To check any deployment:

SELECT verb_id, count() FROM <xapi_db>.video_playback_events GROUP BY verb_id;  -- data present
SELECT count() FROM <reporting_db>.fact_video_segments;                          -- 0 / frozen
SELECT as_select FROM system.tables
WHERE database = '<reporting_db>' AND name = 'fact_video_segments_mv';
-- wrap that SELECT in `SELECT count() FROM (...)` and compare

Why it cannot work

models/video/fact_video_segments.sql is materialized="materialized_view" with a
ReplacingMergeTree target. A plain ClickHouse MV is an insert trigger: its SELECT sees
only the block being inserted. The model pairs each played event with a later
paused / seeked / completed / terminated event of the same learner and video
(first_value / last_value windows, then starts inner join ends). Those events are
minutes apart, so they land in different blocks, and in each block one side of the join is
empty. The MV never writes a row.

The table only receives dbt's own back-fill (CREATE TABLE ... AS <model sql>, on first
build and on --full-refresh), which is a one-time snapshot.

It is not only this model

The same pattern (a plain MV whose output depends on rows outside the inserted block) shows
up in two more places. The first follows from the pattern and the second is visible in the
SQL:

model	per insert block it computes	effect
video/fact_video_segments	play/end pairing	empty
video/fact_video_engagement	plain MV on fact_video_segments: count(distinct block_id) per learner, full join to the course's video list, ReplacingMergeTree target	inherits the empty input; even with input, each block's partial count would replace the previous one
problems/dim_problem_coursewide_avg	avg() / countIf()/count() per problem_id over the block's rows, ReplacingMergeTree target	the "course-wide" figures cover only the learners in the latest insert block. They look plausible but are wrong.

The last one is a separate problem and probably deserves its own issue; I list it to show
the class.

fact_video_segments also uses a ReplacingMergeTree for a count(1) column
(watch_count), so even a per-block-correct MV would replace counts instead of adding them.

History

The model was added already materialized as a plain MV. It has never produced an
incremental row.

|---|---|
| 297cd373 (2025-05-21) | fix: update video queries to fix inconsistencies in data adds fact_video_segments.sql as a plain MV and removes fact_video_plays.sql / fact_watched_video_duration.sql, which had no config() and were therefore plain views: always current, just not incremental. |
| parent 525f65e8 = v4.0.3 | last release without the problem |
| v5.0.0 (2025-08-01) → v8.0.0, main | affected |

For operators the change arrives with tutor-contrib-aspects v2.4.0 (DBT_BRANCH v5.0.0).

Why CI does not catch it

coverage.yml runs dbt run --full-refresh, which is exactly the back-fill path, and
test_fact_video_segments runs the model SQL as a standalone query. Neither exercises
insert-trigger semantics, so the test is green on a model that is inert in production.

Model logic issues found along the way

These are independent of materialization, but a fix will touch the same SQL:

• Pairing takes the last end before the next play, not the first
  (last_value(event_id) ... unbounded following). For play@0 → seek 30→80 while playing →
  pause@90, the model records 0–90 and counts the skipped 30–80 as watched. Backward seeks
  undercount.
• seeked is only an end. video_playback_events keeps time-from and drops time-to,
  so playback after a seek can't start a new interval.
• Plays that never close (tab closed, no stop_video) are dropped entirely.

Whether the player emits a fresh play_video after seeking during playback decides how much
the first point matters in practice. I have not established that yet.

v2.3.1 (v4.0.3) is the last working release.

Why CI does not catch it

coverage.yml runs dbt run --full-refresh, which is exactly the back-fill path, and
test_fact_video_segments runs the model SQL as a standalone query. Neither exercises
insert-trigger semantics, so the test is green on a model that is inert in production.

Model logic issues found along the way

These are independent of materialization, but a fix will touch the same SQL:

• Pairing takes the last end before the next play, not the first
  (last_value(event_id) ... unbounded following). For play@0 → seek 30→80 while playing →
  pause@90, the model records 0–90 and counts the skipped 30–80 as watched. Backward seeks
  undercount.
• seeked is only an end. video_playback_events keeps time-from and drops time-to,
  so playback after a seek can't start a new interval.
• Plays that never close (tab closed, no stop_video) are dropped entirely.

Whether the player emits a fresh play_video after seeking during playback decides how much
the first point matters in practice. I have not established that yet.

Now a proposal

Both the research and the proposal were done using heavy use of claude code. Now the research I understand it fully, but the proposal not completely. And this is why I want to have a proper conversation before I put effort into fixing upstream. I did fix it for my environment by using a small Tutor plugin that turns the plain fact_video_segments_mv into a ClickHouse refreshable materialized view. The plugin runs as an init task on the clickhouse service at priority 98, after the aspects task that runs dbt run. It reads the model's exact SELECT back from system.tables.as_select, drops the plain MV that dbt created, and recreates it as REFRESH EVERY 10 MINUTE TO fact_video_segments AS . It then triggers the first refresh straight away and waits for it to finish. From then on ClickHouse re-runs the full query on every refresh and swaps the result into the target table in one atomic step. Locally the table went from 0 to 82 rows on the first refresh, and new segments have kept appearing as videos are watched.

It does not look like the correct way to fixing it upstream, so that's where this comes in.

Proposal

Separate pairing (needs per-learner-and-video state and ordering) from per-second
counting (cheap at query time). Give each derived row a deterministic identity, so that
rebuilding it is idempotent and can be done incrementally.

1. video_playback_events also keeps video_position_to (the seek's time-to). It stays
   one row per event, so a plain MV is correct here.
2. New fact_video_watch_intervals: one row per watched interval
   (org, course_key, object_id, actor_id, start_event_id, started_at, start_pos, end_pos, end_verb).
   • Target: ReplacingMergeTree(<version>), ordered by
     (org, course_key, object_id, actor_id, start_event_id).
   • Filled by a refreshable MV in APPEND mode over a lookback window
     (emission_time >= now() - INTERVAL <n>). Each refresh costs time proportional to the
     window, not to history. A late end event re-emits its interval under the same key and
     replaces it.
   • Pairing uses leadInFrame over each learner-and-video stream: an interval closes at the
     next event. This removes the first_value / last_value / self-join chain.
3. fact_video_segments becomes a plain view over the intervals
   (arrayJoin(range(start_pos, end_pos + 1))) with the same columns and name, so the Superset
   datasets are unchanged.
4. fact_video_engagement reads the intervals, since it only needs "which videos did this
   learner watch", as a view or a refreshable MV.

Why not the other options:

• Refreshable MV over the unchanged model: this is what I run downstream today as a
  stopgap, and it works. But every refresh recomputes and rewrites all history at one row per
  second of video. It also swaps the target table instead of inserting, so the plain MV on top
  (fact_video_engagement) most likely never fires. I have not verified that last point.
• Chained plain MVs with an "open plays" state table: in-block play+pause pairs, MV
  execution order, out-of-order and late delivery, and plays that never close all make it
  fragile. It buys no freshness over a few-minute refresh.
• AggregatingMergeTree with groupArrayState per learner and video: correct and truly
  incremental, but every dashboard query has to finalise and walk the event arrays, and the
  arrays grow without limit.

Start with models/video/fact_video_segments.sql and inspect its materialization, query, and ReplacingMergeTree target. Then read fact_video_engagement and coverage.yml, including test_fact_video_segments, to compare standalone and full-refresh coverage with insert-trigger behavior. Done requires an agreed upstream design and verified that new video activity advances the affected datasets and dashboards without relying on a later full refresh.