`fact_video_segments` is not incrementally populated

[felipemontoya]

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.

[felipemontoya]

I pinged the aspects channel with this and @saraburns1 was kind to give me some review there. Moving it here for context.

the problem/research definitely makes sense, couple questions on the proposal

fact_video_segments becomes a plain view over the intervals

i'd be interested in how long this will make the superset query. the view will query the entire mv every time so just want to make sure the dashboard would still load in a reasonable amount of time

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(), 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 ). 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.

can you clarify what start_event_id is (and why we need it?)
and i'm not sure what this means: A late end event re-emits its interval under the same key and replaces it.

one of the worries i had with refreshable mvs was the refresh schedule - users want real(ish) time data but we also dont want to be querying a huge table every 10 seconds. what are your thoughts on this?

did you look into APPEND INCREMENTAL? not sure if thats something that would be useful https://clickhouse.com/docs/reference/statements/create/view#refreshable-materialized-view If APPEND INCREMENTAL is specified, each refresh runs the query over only the rows committed to the source table since the previous refresh, and appends the result.

[felipemontoya]

and i'm not sure what this means: A late end event re-emits its interval under the same key and replaces it.

An example, with a refresh every 5 minutes:

• A learner presses play at 10:00. The 10:05 refresh sees the play with nothing after it, so it writes the interval as open, marked invalid.
• The learner pauses at 10:07. The 10:10 refresh re-reads a 'look back window' (I wrote a day before, but we could make this tunable) which still includes the 10:00 play, now followed by the pause. It writes the same interval, closed at the pause position.
• Both rows have the same start_event_id, and the newer one wins (when reading correctly with FINAL).

"Late" only means the pause arrived after an earlier refresh had already seen the play. That's the normal case, and it's what the current plain MV can't handle correctly. I can't say for sure if this happened less often when Ralph was doing big inserts with a large window, but it happens every time now.

one of the worries i had with refreshable mvs was the refresh schedule - users want real(ish) time data but we also dont want to be querying a huge table every 10 seconds. what are your thoughts on this?

Agreed, we don't want to scan a large table every few seconds. With the refined approach, a refresh never scans the whole table. It first finds the learner/video pairs that got new events since the previous refresh, then re-reads only those pairs over the lookback window. The cost therefore depends on how many people are watching right now, not on history: on a quiet instance a refresh does almost nothing.

I think there's a balance to be found between how often we query the table and how small we can make it. That's what I mean by tunable. If we were talking about refreshes every minute looking back events for one hour it could be acceptable.
Those would naturally be left al dials that we can further modify as we get more data in heavy load.

did you look into APPEND INCREMENTAL? not sure if thats something that would be useful https://clickhouse.com/docs/reference/statements/create/view#refreshable-materialized-view If APPEND INCREMENTAL is specified, each refresh runs the query over only the rows committed to the source table since the previous refresh, and appends the result.

I did not know about this so I looked into it now. In my reading it doesn't fit as the main mechanism. It would move the problem, one level up.

APPEND INCREMENTAL runs the query over only the rows committed since the previous refresh. A play committed before one refresh and its pause committed after it are never in the same run, which is the plain-MV problem again, just per refresh instead of per insert block. In that moment we are better left with a configurable look back window.

Claude flagged two more issues:

• Query restrictions. It needs a single plain MergeTree source with enable_block_number_column and enable_block_offset_column turned on, and it rejects joins, UNION and subqueries. The approach above needs a subquery to select the changed pairs.
• Availability. It isn't supported by ClickHouse 25.8, the version tutor-contrib-aspects ships: REFRESH ... APPEND INCREMENTAL is a syntax error there. Relying on it would need a prior upgrade there.

It could still help later for the first step: "rows committed since the previous refresh" is exactly the set of learner/video pairs that changed, and it goes by insert time, so late-delivered events wouldn't be missed. That's still possible for the future.

[saraburns1]

Thanks! That all makes sense.

One more question is - can we force a full refresh without the look back period? Usually we can specify '--full-refresh' for a model, just want to make sure that will still do what we intend.

[felipemontoya]

Yes, that would still happen every time we run dbt run --full-refresh.

In part that is the mechanism that hid this issue from view because there were some video events in the tables. Thing is those were only landing because of the full refresh.

[saraburns1]

Great. I'm on board with this proposal!