← All work
Data Engineer (team of 2)2025

IMDb Analytics Pipeline

A production-style data engineering pipeline that ingests, profiles, cleans, and models ~190 million IMDb records into a Snowflake warehouse powering self-service Power BI and Tableau dashboards.

GitHub
IMDb analytics data pipeline
190M
Total rows
7
Source datasets
91M
Largest table
51M
Alternate titles
2-layer
Snowflake warehouse
2025
Delivered

The problem

Scale breaks naive loads

  • Volume

    190M rows across 7 datasets, one of them 91M on its own

  • Dirty by design

    IMDb encodes missing values as a literal string, and packs multiple values into single fields

  • Query shape

    Analysts ask by year and genre, which a raw load cannot answer without scanning everything

The corpus

7 datasets, 190M rows
The seven IMDb datasets with record counts and their role in the model
DatasetShareRowsRole
title.principals90,984,000Cast and crew per titlemany-to-many bridge
title.akas51,409,000Alternate titles by regionlocalisation
name.basics14,195,000People master datapeople dimension
title.basics11,464,000Core title metadatatitle dimension
title.crew11,464,000Directors and writerscreative ownership
title.episode8,815,000Episode to series linksTV hierarchy
title.ratings1,536,000Ratings and vote countsthe measures

One dataset carries 48% of the corpus. title.principals is the many-to-many bridge between titles and people, which is why it is both the largest table and the one whose join strategy decides warehouse performance.

Six stages

Decisions, not descriptions

Arrow keys to step

Stage 01of 06

Ingest

Azure Data Factory → Data Lake Gen2

  • 7 IMDb .tsv.gz datasets pulled to a raw zone
  • Landed unmodified in Azure Data Lake Gen2
  • ADF pipelines schedule and monitor the pulls
  • Compression kept until load

Decision

Chose
Land raw, transform later
Over
  • Transform in flight on ingest

Because

  • A reload never needs to re-download ~190M rows
  • Raw files are the audit trail when a number is questioned
  • Cleaning belongs in a dedicated stage, not the fetch

Cost

Two copies of the data: raw and modelled.

7
datasets
190M
rows landed

Architecture

ADF to Power BI
IMDb analytics architecture: seven raw IMDb datasets are profiled and cleaned in Alteryx and Python, orchestrated by Azure Data Factory into a two-layer Snowflake warehouse of staging and curated dimensional tables, and served through Power BI and Tableau dashboards, with row-count reconciliation and primary-key uniqueness checks across every layer.

Tuning & trust

Where the warehouse is won

COPY INTO

Data Lake stage → Snowflake staging

Parallel bulk load across the warehouse. Row-by-row INSERT at ~190M rows is orders of magnitude slower and far heavier on the warehouse.

Clustering

title.principals (91M rows)

Clustering the cast/crew bridge on its join key prunes micro-partitions, so the largest join scans a fraction of the table rather than all 91M rows.

Staging / curated split

two-layer Snowflake

Cleaning runs once into staging; BI reads the modelled curated layer, so heavy transforms never re-run inside a dashboard query.

Reconciliation

every pipeline layer

Row-count reconciliation and primary-key uniqueness checks across layers are what let the dashboards be trusted at all.

A star schema is the easy half. On Snowflake the win is in how the 91M-row cast/crew bridge is loaded and clustered, and in the reconciliation checks that prove the modelled layer still matches the raw load — the first things worth asking about in a warehouse review.

Stack

Everything used

Storage & ingest

  • Azure Data Factory
  • Azure Data Lake Gen2

Profiling & cleaning

  • Alteryx
  • Python

Warehouse

  • Snowflake
  • Two-layer
  • COPY INTO
  • Clustering keys

Modelling

  • ER Studio
  • Star schema
  • Conformed dimensions
  • Bridge tables

Serving

  • Power BI
  • Tableau

Quality

  • Row-count reconciliation
  • PK-uniqueness checks
See these across every project