# atulgawade12/projectcarsale architecture

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

- Interactive diagram: https://gitdiagram.com/atulgawade12/projectcarsale
- Repository: https://github.com/atulgawade12/projectcarsale

## Overview

##### Project type and purpose
- This is a Cloud-based data engineering pipeline implementing the Medallion Architecture (Bronze → Silver → Gold) for car sales data.
- It ingests CSV data from GitHub/raw storage into Azure SQL, lands raw data in Azure Data Lake Storage (ADLS) Bronze, transforms via Databricks into a Silver “big table,” models a star schema in Gold, and exposes results to Power BI.

##### File structure
- Notebooks/ : Databricks notebooks for each stage (Database_notebook, Silver_notebook, Gold_dim_*.ipynb, Gold_fact_sales.ipynb)
- Queries/ : SQL scripts for table creation, stored procedures, watermark logic
- raw_data/ : sample CSV input files (SalesData.csv, IncrementalSales.csv)
- powerBi_databricks.pbids : Power BI data connection file
- README.md : detailed architecture, tools, workflow

##### README for additional insights
- Confirms layers: Bronze (Parquet in ADLS), Silver (transformed Parquet/Delta), Gold (Delta star schema)
- Lists tools: Azure Data Factory (ADF), Azure SQL DB, ADLS Gen2, Databricks (+ Unity Catalog), Power BI
- Describes incremental load via watermark table and SCD Type 1

##### Essential elements
- Component names: “ADF SourcePrep,” “ADF Incremental,” “Databricks Silver_notebook,” etc.
- Data formats: CSV, Parquet, Delta
- Incremental logic: watermark table → lookup → copy → stored procedure
- SCD Type 1 upserts in Gold notebooks
- Power BI connection via Databricks SQL endpoint

## Diagram (Mermaid)

```mermaid
flowchart TD
    subgraph "External Data Source"
        Raw1["SalesData.csv"]:::external
        Raw2["IncrementalSales.csv"]:::external
    end

    subgraph "Azure Data Factory" 
        ADF1["ADF SourcePrep Pipeline"]:::orchestration
        ADF2["ADF Incremental Pipeline"]:::orchestration
    end

    subgraph "SQL Scripts"
        TableQuery["Table Creation SQL"]:::database
        Watermark["Watermark Table Logic"]:::database
        StoredProc["Stored Procedure"]:::database
    end

    AzureSQL["Azure SQL DB"]:::database
    Bronze["ADLS Bronze (Parquet)"]:::bronze
    Silver["ADLS Silver (Delta)"]:::silver
    Gold["ADLS Gold (Delta Star Schema)"]:::gold

    subgraph "Databricks Workspace"
        DB_Work["Databricks Workspace"]:::compute
        subgraph Notebooks
            NB1["Database_notebook"]:::compute
            NB2["silver_notebook"]:::compute
            NB3["Gold_dim_branch"]:::compute
            NB4["Gold_dim_date"]:::compute
            NB5["Gold_dim_dealer"]:::compute
            NB6["Gold_dim_model"]:::compute
            NB7["Gold_Fact_sales"]:::compute
        end
    end

    PowerBI["Power BI"]:::external

    %% Data flows
    Raw1 -->|Copy CSV| ADF1
    Raw2 -->|Copy CSV| ADF1
    ADF1 -->|"Initial Load"| AzureSQL
    TableQuery --> AzureSQL
    AzureSQL -->|Execute Notebook| NB1
    NB1 -->|"Write Parquet"| Bronze
    AzureSQL -->|Lookup Watermark| ADF2
    Watermark --> ADF2
    StoredProc --> ADF2
    ADF2 -->|"Incremental Parquet"| Bronze
    Bronze -->|Bronze to Silver Transform| NB2
    NB2 -->|"Write Delta"| Silver
    Silver -->|Dimension Upserts| NB3
    Silver -->|Dimension Upserts| NB4
    Silver -->|Dimension Upserts| NB5
    Silver -->|Dimension Upserts| NB6
    Silver -->|Fact Upserts| NB7
    NB3 -->|"Delta Writes"| Gold
    NB4 -->|"Delta Writes"| Gold
    NB5 -->|"Delta Writes"| Gold
    NB6 -->|"Delta Writes"| Gold
    NB7 -->|"Delta Writes"| Gold
    Gold -->|Serve Data| PowerBI

    %% Click Events
    click Raw1 "https://github.com/atulgawade12/projectcarsale/blob/main/raw_data/SalesData.csv"
    click Raw2 "https://github.com/atulgawade12/projectcarsale/blob/main/raw_data/IncrementalSales.csv"
    click TableQuery "https://github.com/atulgawade12/projectcarsale/blob/main/Queries/azureSQLdb_TableQuery.txt"
    click Watermark "https://github.com/atulgawade12/projectcarsale/blob/main/Queries/watermark_table.txt"
    click StoredProc "https://github.com/atulgawade12/projectcarsale/blob/main/Queries/StoredProcedureQuery.txt"
    click NB1 "https://github.com/atulgawade12/projectcarsale/blob/main/Notebooks/Database_notebook.ipynb"
    click NB2 "https://github.com/atulgawade12/projectcarsale/blob/main/Notebooks/silver_notebook.ipynb"
    click NB3 "https://github.com/atulgawade12/projectcarsale/blob/main/Notebooks/Gold_dim_branch.ipynb"
    click NB4 "https://github.com/atulgawade12/projectcarsale/blob/main/Notebooks/Gold_dim_date.ipynb"
    click NB5 "https://github.com/atulgawade12/projectcarsale/blob/main/Notebooks/Gold_dim_dealer.ipynb"
    click NB6 "https://github.com/atulgawade12/projectcarsale/blob/main/Notebooks/Gold_dim_model.ipynb"
    click NB7 "https://github.com/atulgawade12/projectcarsale/blob/main/Notebooks/Gold_Fact_sales.ipynb"
    click PowerBI "https://github.com/atulgawade12/projectcarsale/blob/main/powerBi_databricks.pbids"
    click README "https://github.com/atulgawade12/projectcarsale/blob/main/README.md"

    %% Styles
    classDef bronze fill:#bf8f00,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:#87cefa,stroke:#333,stroke-width:1px
    classDef orchestration fill:#98fb98,stroke:#333,stroke-width:1px
    classDef database fill:#add8e6,stroke:#333,stroke-width:1px
    classDef external fill:#f4a460,stroke:#333,stroke-width:1px
```
