# mrnidhi/etl-azure architecture

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

- Interactive diagram: https://gitdiagram.com/mrnidhi/etl-azure
- Repository: https://github.com/mrnidhi/etl-azure

## Overview

##### Project type and purpose
- This is an end-to-end Azure data pipeline project implementing the Medallion (Bronze→Silver→Gold) architecture.
- It ingests raw Olist CSV files (from GitHub and a sample SQL DB), lands them in ADLS Gen2, applies transformations in Databricks, and serves the cleansed/aggregated data to Azure Synapse and Power BI.

##### File structure
- Top-level folders:
– ADF/
· linkedService/ – JSON definitions for all source & sink connections (GitHub, HTTP, SQL DB, ADLS Gen2, Synapse).
· dataset/ – JSON definitions for CSV, JSON, SQL table datasets.
· pipeline/ – DataProcessingPipeLine.json describing the ETL orchestration.
· factory/ – factory metadata exports.
– DataBricks/
· PySpark scripts for Silver & Gold layers.
- CSV files at root are the raw source data for Bronze.
- SQLSynapsePath.json and the notebook show optional local-to-DB ingestion steps.

##### README for additional insights
- The README explicitly describes a Medallion pattern:
Bronze = raw files in ADLS
Silver = cleaned/standardized tables in ADLS
Gold = aggregated, business-ready tables in ADLS → Synapse
- Key technologies: Azure Data Factory, ADLS Gen2, Azure Databricks (PySpark), Azure Synapse Analytics, Power BI.

## Diagram (Mermaid)

```mermaid
flowchart TD
    %% Ingestion layer
    subgraph "Ingestion: Azure Data Factory" 
        direction TB
        LS_GitHub["GitHub Linked Service"]:::ingestion
        DS_CSV_GitHub["CSV Dataset (GitHub)"]:::ingestion
        LS_SQLDB["SQL DB Linked Service"]:::ingestion
        DS_SQLDB["SQL Table Dataset"]:::ingestion
        PipelineADF["DataProcessing Pipeline"]:::ingestion
    end

    %% Bronze layer
    subgraph "Bronze Layer (Raw Data in ADLS Gen2)" 
        direction TB
        ADLS_Bronze["ADLS Gen2 Bronze Storage"]:::bronze
        subgraph Raw_CSVs
            olist_customers("olist_customers_dataset.csv"):::bronze
            olist_geolocation("olist_geolocation_dataset.csv"):::bronze
            olist_order_items("olist_order_items_dataset.csv"):::bronze
            olist_order_payments("olist_order_payments_dataset.csv"):::bronze
            olist_order_reviews("olist_order_reviews_dataset.csv"):::bronze
            olist_orders("olist_orders_dataset.csv"):::bronze
            olist_products("olist_products_dataset.csv"):::bronze
            olist_sellers("olist_sellers_dataset.csv"):::bronze
            product_category("product_category_name_translation.csv"):::bronze
        end
    end

    %% Silver transformation
    subgraph "Silver Layer (Clean & Standardize)" 
        direction TB
        DB_Silver["Databricks Silver Notebook"]:::compute
    end

    %% Gold transformation
    subgraph "Gold Layer (Aggregate & Business-Ready)" 
        direction TB
        DB_Gold["Databricks Gold Notebook"]:::compute
    end

    %% Serving & Visualization
    subgraph "Serving & Visualization"
        direction TB
        ADLS_Gold["ADLS Gen2 Gold Storage"]:::gold
        Synapse["Azure Synapse Analytics"]:::analytics
        PowerBI["Power BI Dashboard"]:::visualization
    end

    %% Optional local ingestion
    Notebook["Local-to-DB Notebook"]:::compute

    %% Data Flow
    LS_GitHub -->|ingest CSVs| DS_CSV_GitHub
    DS_CSV_GitHub -->|pipeline input| PipelineADF
    LS_SQLDB -->|ingest tables| DS_SQLDB
    DS_SQLDB -->|pipeline input| PipelineADF
    PipelineADF -->|write raw files| ADLS_Bronze

    ADLS_Bronze -->|read Bronze| DB_Silver
    DB_Silver -->|write Silver| ADLS_Bronze
    ADLS_Bronze -->|intermediate| DB_Silver

    DB_Silver -->|write Silver outputs| ADLS_Bronze
    ADLS_Bronze -->|feed Gold| DB_Gold
    DB_Gold -->|write Gold outputs| ADLS_Gold

    ADLS_Gold -->|serve data| Synapse
    ADLS_Gold -->|visualization data| PowerBI

    Notebook -.->|optional ingestion| LS_SQLDB
    Notebook -.->|optional ingestion| ADLS_Bronze

    %% Click Events
    click LS_GitHub "https://github.com/mrnidhi/etl-azure/blob/main/ADF/linkedService/GitHubToADFLinkedService.json"
    click DS_CSV_GitHub "https://github.com/mrnidhi/etl-azure/blob/main/ADF/dataset/CSVDataFromGithub.json"
    click LS_SQLDB "https://github.com/mrnidhi/etl-azure/blob/main/ADF/linkedService/SQLDBToADFLinkedService.json"
    click DS_SQLDB "https://github.com/mrnidhi/etl-azure/blob/main/ADF/dataset/SQLTablefromDB.json"
    click PipelineADF "https://github.com/mrnidhi/etl-azure/blob/main/ADF/pipeline/DataProcessingPipeLine.json"
    click DB_Silver "https://github.com/mrnidhi/etl-azure/blob/main/DataBricks/Olist Data Processing - SILVER.py"
    click DB_Gold "https://github.com/mrnidhi/etl-azure/blob/main/DataBricks/Olist Data Processing - GOLD.py"
    click ADLS_Gold "https://github.com/mrnidhi/etl-azure/blob/main/ADF/linkedService/AzureSynapseAnalyticsbyPool.json"
    click Synapse "https://github.com/mrnidhi/etl-azure/blob/main/ADF/dataset/JsonForSQLSynapse.json"
    click Notebook "https://github.com/mrnidhi/etl-azure/blob/main/Table_form_local_to_DB_Filess.ipynb"

    %% Styles
    classDef ingestion fill:#f2a654,stroke:#333,stroke-width:1px
    classDef bronze fill:#cd7f32,stroke:#333,stroke-width:1px
    classDef silver fill:#C0C0C0,stroke:#333,stroke-width:1px
    classDef gold fill:#FFD700,stroke:#333,stroke-width:1px
    classDef compute fill:#8dd3c7,stroke:#333,stroke-width:1px
    classDef storage fill:#bebada,stroke:#333,stroke-width:1px
    classDef analytics fill:#80b1d3,stroke:#333,stroke-width:1px
    classDef visualization fill:#fb8072,stroke:#333,stroke-width:1px
```
