Data Architecture Project — Cinema Atlas App
How three grad students turned movie data into a connected analytical platform using Databricks, AWS, and a lot of API calls.
When most people think about movie data, they think about ratings. A number between 1 and 10. Maybe a Rotten Tomatoes score. But movies are so much more than a single metric — they’re networks of people, genres, cultural movements, box office trajectories, and creative lineages that stretch across decades.
That idea became Cinema Atlas, a data architecture project I built with two teammates — Aatish Lobo and Tianyi Luo — as part of our graduate program at the University of San Francisco. The goal was ambitious: take raw data from multiple public sources, design a pipeline to clean and connect it, and surface the results through an interactive web application where you can actually see how cinema is connected.
This is the story of how we built it, the architecture decisions we made along the way, and what we learned about building real data systems — not just running queries in a notebook.
The Idea: Cinema as a Graph, Not a Spreadsheet
The project started with a simple observation: existing movie databases treat films as isolated records. You can look up a movie, see its cast, maybe check its rating. But you can’t easily ask questions like:
- What connects Parasite to The Dark Knight beyond both being popular?
- Which directors act as bridges between otherwise unrelated clusters of film?
- How did a film’s revenue and popularity actually change over time — not just the final number, but the trajectory?
These are graph questions. Relationship questions. And answering them requires a different kind of data architecture than a simple table of movies and ratings.
We decided to build a platform that models cinema as a connected system — films linked to people, genres, production companies, cultural movements, and each other — with a time dimension that captures how audience reception evolves after release.
Three Sources, One Pipeline
The first challenge was data. No single source has everything you need to model cinema as a connected system, so we pulled from three:
TMDB (The Movie Database) became our primary spine. Their API gives rich per-film data: cast, crew, genres, production companies, revenue, popularity scores, and user reviews. We ingested every movie released between 2000 and 2026 with meaningful audience data — about 13,000 films.
IMDb brought authoritative ratings and a different lens on crew data. While TMDB focuses on above-the-line talent (directors, writers, actors), IMDb’s public datasets gave us below-the-line crew — cinematographers, editors, composers, production designers, costume designers. These are the people who shape a film’s visual and sonic identity but rarely get the spotlight.
Wikidata added the cultural context that neither TMDB nor IMDb captures. Through SPARQL queries against the Wikidata knowledge base, we pulled cultural movement tags (Dogme 95, Afrofuturism, New Hollywood), festival circuit data (Cannes, Sundance, Toronto), source material relationships (which films are based on which books or plays), and creative influence links.
Each source has a completely different format and access pattern. TMDB is a REST API returning JSON per film. IMDb publishes daily TSV files compressed into multi-gigabyte archives. Wikidata speaks SPARQL. Stitching them together was where the real engineering started.
The Medallion Architecture: Bronze, Silver, Gold
We structured the pipeline using a medallion architecture on Databricks — a pattern where data moves through progressively cleaner layers.
Bronze: Keep Everything
The bronze layer is the archive. Every API response, every TSV row, every SPARQL result lands here in its original form, timestamped with when we ingested it. We never delete from bronze, and we never modify it. If something goes wrong downstream, we can always replay from the raw data.
For TMDB, this meant appending every API response as a new row stamped with a load_ts (load timestamp). When we re-pull a film's data during our daily refresh, the new response becomes a new row — the old one stays. This is what makes time-series analysis possible later: we're not overwriting yesterday's revenue number with today's, we're keeping both.
For IMDb, the pattern was different. Their ratings data is inherently a time series (we snapshot the full ratings file periodically, so each film gets a new rating observation per snapshot). Their title metadata, on the other hand, changes rarely, so we used a MERGE strategy — update existing records, insert new ones.
Silver: Clean, Normalize, Connect
Silver is where the data becomes useful. We deduplicate, validate, and transform the raw bronze data into a normalized star schema — the kind of structure you’d want if you were writing analytical queries.
A single TMDB movie record in bronze contains nested JSON with genres, production companies, spoken languages, and production countries all embedded inside it. In silver, these fan out into proper dimension and bridge tables: genres, film_genres, production_companies, film_production_companies, and so on. One raw record becomes rows across a dozen related tables.
Get Kaio’s stories in your inbox
Join Medium for free to get updates from this writer.
The most interesting silver table is audience_trends — our Slowly Changing Dimension Type 2 table. Instead of keeping only the current metrics for each film, it preserves every historical snapshot. When we pull fresh data for a film during the daily refresh, the old snapshot gets marked as historical and the new one becomes current. This means we can chart exactly how a film's revenue, popularity, and vote count changed over weeks and months after release.
The Merge Layer: Resolving Identities Across Sources
This was the most technically challenging part of the project. TMDB and IMDb use completely different identifier systems. A film is id: 550 in TMDB and tconst: tt0137523 in IMDb. A person is person_id: 287 in TMDB and nconst: nm0000093 in IMDb. To build a unified view, we had to resolve these identities.
For films, the join was straightforward — TMDB stores an imdb_id field for most movies, so we could match directly on tmdb.imdb_id = imdb.tconst. This gave us a merged film table with the best of both sources: TMDB's rich metadata and IMDb's authoritative ratings.
For people, it was harder. TMDB stores an imdb_id for some people, but not all. So we built a two-pass entity resolution system. The first pass matches everyone who has a direct IMDb ID link — high confidence, exact match. The second pass takes the remaining unresolved people and looks for them using a film-anchored fuzzy name matching approach: if a TMDB person and an IMDb person both worked on the same film and their names are similar enough (we used a 90% similarity threshold after normalizing for accents and punctuation), we call it a match.
Every resolved person gets tagged with the method used and a confidence score, so downstream consumers can decide how much to trust each match.
Gold: Thematic Embeddings
The gold layer is where we got creative. Using a sentence transformer model, we embedded every film’s plot synopsis into a high-dimensional vector space. Films with similar themes end up near each other in this space — not because they share a genre tag, but because the language describing them is similar.
We then connected each film to its 12 nearest thematic neighbors and projected the whole space down to 2D using UMAP. The result is a continuous thematic landscape where films blend naturally — no hard cluster boundaries. A film like Get Out sits between horror and social commentary, connected to both regions. That’s exactly how we experience films, and it’s something genre labels alone can’t capture.
From Data to Interface
A pipeline is only useful if someone can interact with what it produces…
The home page shows catalog-level metrics and a releases-over-time chart.
The film profile page is the centerpiece — poster, trailer, genre chips,
cast with headshots, key crew, user reviews, and a time-series chart…
The analytics page has leaderboards and genre distribution charts.
And of course the connection graph page…
What We Learned
Design the schema before writing the pipeline
We spent significant time upfront on schema design — drawing out the star schema, deciding on grain for each table, identifying the right keys. That investment paid for itself many times over. When it came time to write the silver transformations, the target was clear. When we built the web app, the queries wrote themselves because the schema was already optimized for the questions we wanted to ask.
Append-only bronze is a lifesaver
Early in the project, we discovered a bug where our incremental pipeline was re-ingesting about 87 films every day that it shouldn’t have been. Because bronze was append-only, those phantom duplicates didn’t corrupt anything downstream — our data quality layer simply deduplicated to the latest version per film. We fixed the root cause (a mismatch between TMDB’s primary_release_date and our stored release_date), and the redundant rows in bronze became harmless clutter rather than a data integrity crisis.
Multi-source projects are identity resolution projects
The technical core of connecting TMDB, IMDb, and Wikidata wasn’t the API calls or the SPARQL queries — it was figuring out that Film A in one system is the same as Film B in another system. For films we had a shared key (IMDb ID). For people we didn’t, and that’s where the real engineering happened. Entity resolution is one of those problems that sounds simple (“just match the names”) but gets complex fast when you account for accents, transliterations, people with the same name, and the fact that “Robert Rodriguez” the director and “Robert Rodriguez” the cinematographer are different humans.
Your first visualization is a prototype, not a product
We initially built our dashboard in Streamlit — it’s fast to prototype and great for exploring data. But when we showed it to the team, the feedback was honest: it looked like a data science notebook, not a product. That pushed us to rebuild in Next.js, which forced us to think about architecture (API routes, client-side rendering, proper routing) but produced something we’re actually proud to share. The Streamlit version wasn’t wasted — it proved out every query and visualization before we committed to the production stack.
What’s Next
Cinema Atlas is a living project. The Wikidata cultural enrichment layer — movements, festivals, influences — is being integrated into the graph view, which will let users explore not just who made a film, but what cultural currents it belongs to. The Gold-layer thematic embedding map is ready to be visualized as an interactive landscape. And the pipeline continues to run daily, ingesting new releases and tracking how the world receives them.
If you’re interested in the code, architecture details, or want to explore the data yourself, the project is open source on GitHub.
Kaio Farkouh is a data engineer and graduate student at the University of San Francisco. He builds data pipelines by day and explores how data can tell stories about the things we love — like movies.
