How m-dudash/imdb-etl works
Main Components
• Data Source Files: Represent the raw input data residing in the IMDb_files directory (CSV files such as director_mapping.csv, genre.csv, movie.csv, names.csv, ratings.csv, role_mapping.csv). Also include the original ERD file (IMDB_ERD.mwb) and SQL files.
• Staging Layer: The ETL process first loads data into staging tables (e.g., movie_staging, ratings_staging, names_staging, genre_staging, director_staging, role_staging) where initial cleansing and validation occur.
• Transformation Layer: Data transformation logic converts the staging data into a multidimensional model. Key components include creation of dimension tables (dim_movies, dim_genres, dim_persons) and the central fact table (fact_ratings). Note the use of Slowly Changing Dimensions (SCD Type 2) for tracking changes.
• Data Warehouse (Snowflake): The target environment where the star schema (dimensional model) is loaded. This layer is the basis for efficient querying.
• Visualization/Dashboards: SQL scripts and dashboard images (in the dashboards directory) indicate a reporting layer where analysis is performed (e.g., average ratings by genre, movies count by region, trends over time).
Depicting Relationships and Data Flow
– Dimension Tables: dim_movies, dim_persons, dim_genres.
– Fact Table: fact_ratings.
To denote that SCD Type 2 logic is applied for dim_movies and dim_persons.
Architectural Patterns and Technologies
• ETL Process: The architecture clearly follows the classic ETL (Extract, Transform, Load) pattern.
• Star Schema: A central fact table (fact_ratings) with multiple surrounding dimension tables (dim_movies, dim_genres, dim_persons).
• Data Quality Considerations: The ETL includes error handling (e.g., ON_ERROR = 'CONTINUE' in COPY INTO) and SCD Type 2 for maintaining historical changes.
• Visualization: SQL queries for dashboard generation reflect the analytical layer.