Movie Analytics ETL Pipeline
End-to-end data engineering project processing 176M+ IMDb records into a dimensional warehouse with dbt and PostgreSQL.
Overview
An end-to-end data engineering project that transforms raw IMDb datasets into a production-ready dimensional data warehouse. The pipeline processes over 176 million records across 5 datasets, implementing comprehensive data quality measures and creating business-ready analytics with interactive dashboards.
Tech Stack
View more →Challenges & Solutions
Processing 176M+ records efficiently
Loading and transforming massive IMDb datasets (176M+ records) while maintaining data quality and managing memory constraints during transformation and load operations.
Solution: Implemented bulk loading with PostgreSQL COPY driven by Python (psycopg2), staging data in a raw schema before transformation. Kept dbt models modular across staging and marts layers so rebuilds stay manageable.
Impact: Successfully processed all five datasets into the warehouse with an 80% data quality test pass rate across dbt layers.
Maintaining data relationships across complex schema
IMDb datasets contain deeply linked entities (titles, people, ratings, crew) with missing and orphaned references that caused broken joins and duplicate facts.
Solution: Implemented dbt staging and intermediate models with referential integrity filters. Added 30+ dbt tests covering nulls, uniqueness, and foreign key consistency. Built scripts to identify and patch orphaned records.
Impact: Achieved referential integrity across all fact and dimension tables with reproducible test coverage and clean lineage between sources and marts.
Designing a warehouse for analytics-ready insights
Needed a well-structured dimensional model to support analytics on movies, people, and ratings efficiently.
Solution: Designed a star schema with fact tables for ratings and dimensions for titles, people, and genres. Applied dbt documentation blocks for column-level metadata and lineage visualization. Tuned surrogate keys and joins for performant aggregation queries.
Impact: Delivered a clean dimensional warehouse powering downstream analytics and dashboards with minimal join complexity and intuitive naming conventions.