movie-ratings-analytics

The dataset is MovieLens 20M: 20,000,263 ratings, 465,564 tag applications and an 11.7M-row movie–tag relevance matrix, across 27,278 films and 138,493 users. It lands in five raw tables in Snowflake, and dbt models it in layers: staging, then dimensions and facts, then marts.

movie-ratings-analytics · dbt on SnowflakeAirflow + Cosmos · @monthly

Every model, column and test is documented in the published dbt docs, with the full lineage graph from raw sources to marts.

Lineage

Sixteen dbt nodes over five sources. Each node’s outline shows its materialisation.

model lineage · 16 dbt nodes over 5 sourcesfrom ref() and source()

source
view
ephemeral
table
incremental
snapshot
seed

Materialisation as a layer policy

Defaults are set per layer in dbt_project.yml rather than per model, so each one is a stated position. A model that overrides its layer explains why in its own file.

Layer	Default	Why
staging	view	Rename and cast only. Nothing worth storing.
intermediate	ephemeral	Inlined into its single consumer.
dim / fct	table	Joined constantly by marts and ad-hoc queries.
marts	table	Expensive to compute, small output, read repeatedly.

Three models override the default:

fct_genome_scores is a view. It applies a ROUND() and nothing else, so a table would store a second copy of 11.7M rows to hold a rounded float. The expensive work in that branch happens in mart_tag_genre_affinity, and that is where the table lives.
fct_ratings is incremental, and volume isn’t the reason. A measured full rebuild of all 20,000,263 rows takes 3.0 seconds on an XS warehouse. It’s incremental because of its shape: an append-mostly event stream, merged on (user_id, movie_id). The boundary filter is >=, not >, because 34.7% of ratings share their second with another rating, and a strict > would silently drop rows tied with the high-water mark.
fct_movie_rating_daily is incremental with a lookback window. A rolling 28-day measure changes for each of the previous 27 days whenever a new rating lands, so a high-water-mark filter would freeze the recent tail at wrong values. The model reprocesses a window and merges, and it reads twice as far back as it writes, because the oldest reprocessed day needs its own 28 days of history.

Checked against the source

Every claim the project makes about the data was checked against the raw CSVs instead of being assumed: zero duplicate (user, movie) pairs, which is what makes the merge safe; zero null ratings; ratings bounded between 0.5 and 5.0; a seed that matches the genre vocabulary exactly.

Two of those checks changed the code. Every one of the 7,801 tagging users has also rated, so a planned is_tag_only_user flag would have been false for all 138,493 users, and it was dropped. And release_year is null for 23 films, a few of them because of malformed titles, so the column’s documentation names the number and the cause.

A filter that removes nothing is a test that never runs.

WHERE rating IS NOT NULL would remove zero of 20,000,263 rows. It looks like validation while asserting nothing, and it would keep looking like validation on the day the property stopped holding. So those properties are tests, and tests can fail.

Snapshots: SCD Type 2 on the source

snap_movies keeps the history of movie metadata, because titles get corrected and genres get reclassified, which is the slow change SCD Type 2 exists for. Tags are immutable events, so snapshotting them would capture nothing.

check strategy on title and genres. raw_movies has no updated_at column, so the source forces this choice.
It snapshots the source, not dim_movies. If a snapshot reads a model, refactoring that model rewrites history: switching INITCAP to UPPER would record a change to all 27,278 films.
hard_deletes='invalidate'. A deleted row closes its open record instead of staying current forever.

The source is a static 2015 extract, so committed SQL scripts stage reproducible drift (a genre reclassification, a title correction and a hard delete) to give the snapshot something to record.

Tests

106 tests. Three kinds are worth pointing at:

no_overlapping_versions, a custom generic test. dbt maintains dbt_valid_from and dbt_valid_to but never checks that the result is coherent. The test asserts that validity windows tile each entity’s timeline (no overlaps, no gaps, exactly one open record, no window closing before it opens) and reports which of the four failure modes fired.
n_ratings_28d >= n_ratings_7d. A relationship between columns, not a range on one: a 28-day window must contain at least as much as the 7-day window inside it. It catches the frame-definition errors the lookback design is most exposed to.
Source-level tests, so key violations fail at the boundary instead of three models downstream.

Freshness

Freshness is measured from warehouse load metadata, not event timestamps. The newest rating in MovieLens is from 2015, so event time would report permanent staleness, and a broken loader would look identical to a healthy one. Thresholds match a monthly load cadence. The genome tables have freshness disabled outright, because MovieLens regenerates them every few years, and an alert nobody can act on trains people to ignore alerts.

CI

GitHub Actions runs dbt build on every pull request against a schema named for that pull request, and drops it afterwards with if: always(), so a failed build still cleans up.

Source freshness runs but doesn’t block. A stale source is a data problem, not a problem with the code under review.
The schema-drop macro refuses any target but ci. It issues DROP SCHEMA … CASCADE, so a forgotten --target fails loudly instead of running against prod.
Slim CI is deliberately deferred. At sixteen nodes a full build is cheap enough that state-based selection isn’t worth its complexity yet.

Orchestration

Airflow runs the project monthly, matching the source’s load cadence, through Astronomer Cosmos. Cosmos parses the dbt manifest and emits one Airflow task per model, with each model’s tests running right after it. Retries are per model, the graph view shows real lineage, and a failure names the model that failed. A single dbt build task would retry everything, re-running a 20M-row incremental that had already succeeded.

What changes at 100× volume

fct_ratings becomes dependent on incremental loading rather than choosing it, and would need clustering on rating_timestamp, probably with delete+insert on a partition boundary instead of a two-column merge.
dim_users recomputes per-user aggregates over the full history on every run, so it would have to become incremental too.
mart_user_cohort_retention falls over first: COUNT(DISTINCT user_id) across a 2B-row join would move to an approximate count or a pre-aggregated user-month table.
int_movie_tag_relevance stays fine, because the genome matrix doesn’t grow with rating volume.

The README covers each decision in more depth, including what I would do differently.