Movies DBT Project: Technical Implementation & Data Engineering

🎬 Movies DBT Project: Technical Implementation & Data Engineering

This project is a hands-on demonstration of building a robust, modular data pipeline for movie analytics using dbt, DuckDB, and MotherDuck. Below, I’ll walk through the specific engineering work, models, tables, and integrations I’ve implemented.

🏗️ Data Modeling & Table Design

A major part of this project was designing a normalized schema for movie data. I started by identifying the core entities and relationships in the datasets:

  • Movies Table:
    Stores unique movie records, including title, release year, and identifiers from various sources (IMDB, TMDB, etc.).
  • Genres Table:
    Maps movies to their genres, supporting many-to-many relationships and enabling genre-based analytics.
  • Ratings Table:
    Aggregates ratings from multiple sources (IMDB, Rotten Tomatoes, user reviews), with logic to standardize scales and handle missing data.
  • People Table:
    Contains information about actors, directors, and crew, linked to movies via join tables.
  • Cast Table:
    Connects people to movies, specifying roles (actor, director, etc.) and supporting analysis of collaborations.
  • Box Office Table:
    Tracks financial performance, including domestic and international revenue, inflation adjustments, and currency normalization.
  • Snapshots:
    Implemented dbt snapshots to track changes in movie metadata over time, supporting slowly changing dimension analysis.

Each table was defined in SQL within the database/tables/ directory, and the schema was version-controlled for reproducibility.

🧩 dbt Models & Transformations

I developed a suite of dbt models to transform raw ingested data into analytics-ready tables:

  • Staging Models:
    These models clean and standardize raw CSV/Parquet/TSV files, handling missing values, inconsistent formats, and deduplication.
  • Intermediate Models:
    Join staging tables to enrich movie records with genre, rating, and cast information.
    For example, I built models to aggregate ratings from different sources and calculate weighted averages.
  • Mart Models:
    Final models are designed for reporting and dashboarding, such as top-rated movies by genre, box office trends, and actor/director collaboration networks.
  • Analyses:
    Advanced SQL queries in the analyses/ directory provide insights like genre popularity over time, rating distributions, and financial performance breakdowns.

All models are documented with descriptions and tested for integrity using dbt’s built-in testing framework.

🔗 Connections & Integrations

DuckDB & MotherDuck

  • Local Development:
    I used DuckDB for fast, local analytics and iterative model development.
    The database was initialized and managed via Python scripts (database/create.py and database/upload.py), which automate schema creation and data ingestion.
  • Cloud Sync with MotherDuck:
    Once models were validated locally, I connected DuckDB to MotherDuck for cloud-based storage and scalable analytics.
    This allowed me to share datasets, run distributed queries, and collaborate with others in real time.
  • Version Control:
    All schema and model changes are tracked in Git, ensuring reproducibility and easy rollback.

Supabase Integration

  • API Exposure:
    I built scripts in movies/supabase/ to sync selected tables (e.g., movies, ratings, box office) to Supabase, making analytics available via RESTful endpoints.
  • Realtime Data:
    Supabase integration enables live updates and supports web/mobile applications that consume movie analytics.

🛠️ Engineering Work & Automation

  • Automated Ingestion:
    Python scripts automate the loading of raw data, schema creation, and error correction.
    This includes handling large files, inferring column types, and logging ingestion issues.
  • Incremental dbt Builds:
    dbt models are configured for incremental builds, so only new or changed data is processed, improving efficiency.
  • Testing & Validation:
    I wrote custom dbt tests to check for duplicates, nulls, referential integrity, and business logic (e.g., valid rating ranges).
  • Documentation:
    All models and tables are annotated with descriptions, making the project self-documenting and easy to onboard.

📊 Analytics & Insights

With the pipeline in place, I was able to generate a range of analytics:

  • Top Movies by Genre and Year
  • Box Office Trends and Outliers
  • Actor/Director Collaboration Networks
  • Rating Distributions Across Sources
  • Genre Popularity Over Time

These insights are available via dbt analyses, Supabase APIs, and can be visualized in dashboards or notebooks.

🏁 Summary

This project demonstrates end-to-end data engineering: from raw ingestion and schema design, through modular dbt transformations, to cloud analytics and API integration.
It showcases advanced modeling, automation, and integration with modern platforms like DuckDB, MotherDuck, and Supabase, providing a scalable foundation for movie data analytics and collaborative data science.