# m-dudash/imdb-etl architecture

> Architecture diagram of the GitHub repository m-dudash/imdb-etl, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2025-04-16). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/m-dudash/imdb-etl
- Repository: https://github.com/m-dudash/imdb-etl

## Overview

##### 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.

## Diagram (Mermaid)

```mermaid
flowchart TD
    %% Data Sources Subgraph
    subgraph "Data Sources"
        CSV["CSV Files (IMDb_files)"]:::datasource
        ERD["ERD File (IMDB_ERD.mwb)"]:::datasource
        ETL["ETL SQL (IMDB_SQL.sql)"]:::datasource
    end

    %% Intermediate Processing Nodes
    EX["Extract Process"]:::process

    STG["Staging Layer: movie_staging, ratings_staging, names_staging, genre_staging, director_staging, role_staging"]:::staging
    TRANS["Transformation Layer: dim_movies(SCD2), dim_genres, dim_persons(SCD2), fact_ratings"]:::transform
    WH["Data Warehouse - Snowflake"]:::warehouse
    VIS["Dashboards (Visualization)"]:::visualization
    VSQL["Dashboard SQL (dashboard_sql.txt)"]:::visualization

    %% Connections from Data Sources to Extract Process
    CSV -->|"Extract"| EX
    ERD -->|"Extract"| EX
    ETL -->|"Extract"| EX

    %% Flow through ETL
    EX -->|"Load_to_Staging"| STG
    STG -->|"Transform"| TRANS
    TRANS -->|"Load"| WH
    WH -->|"Analyze"| VIS
    VIS -->|"SQL_Config"| VSQL

    %% Click Events
    click CSV "https://github.com/m-dudash/imdb-etl/tree/main/IMDb_files"
    click ERD "https://github.com/m-dudash/imdb-etl/blob/main/IMDB_ERD.mwb"
    click ETL "https://github.com/m-dudash/imdb-etl/blob/main/IMDB_SQL.sql"
    click VIS "https://github.com/m-dudash/imdb-etl/tree/main/dashboards"
    click VSQL "https://github.com/m-dudash/imdb-etl/blob/main/dashboard_sql.txt"

    %% Styles
    classDef datasource fill:#add8e6,stroke:#000,stroke-width:2px;
    classDef process fill:#d4edda,stroke:#000,stroke-width:2px;
    classDef staging fill:#fff3cd,stroke:#000,stroke-width:2px;
    classDef transform fill:#f8d7da,stroke:#000,stroke-width:2px;
    classDef warehouse fill:#d1c4e9,stroke:#000,stroke-width:2px;
    classDef visualization fill:#e2e3e5,stroke:#000,stroke-width:2px;
```
