# hrishikesh26/tasty-bytes-snowflake-data-analysis-with-sql architecture

> Architecture diagram of the GitHub repository hrishikesh26/tasty-bytes-snowflake-data-analysis-with-sql, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2025-05-25). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/hrishikesh26/tasty-bytes-snowflake-data-analysis-with-sql
- Repository: https://github.com/hrishikesh26/tasty-bytes-snowflake-data-analysis-with-sql

## Overview

##### Project Type and Purpose
- This is a data-engineering and analytics pipeline built entirely on Snowflake, ingesting POS and customer data from AWS S3 and external weather data via Snowflake Marketplace, transforming and modeling the data through layered views and UDFs, then visualizing results in a Streamlit + Altair dashboard.

a. External Data Sources
- AWS S3 bucket (“s3://sfquickstarts/tasty-bytes-builder-education/”)
- Snowflake Marketplace (Weather Source LLC dataset)
b. Snowflake Data Warehouse
- PUBLIC layer (file format + S3 stage)
- RAW_POS schema (order_header, order_detail, truck, menu, location, franchise, country)
- RAW_CUSTOMER schema (customer_loyalty)
- HARMONIZED schema (orders_v, daily_weather_v, customer_loyalty_metrics_v, weather_hamburg, winds peed_hamburg)
- ANALYTICS schema (A_ORDERS_V, A_CUSTOMER_LOYALTY_V, UDFs: fahrenheit_to_celsius, inch_to_millimeter)
c. Transformation Scripts
- Ingestion SQL scripts (`Ingestion/*.sql`, `warehouse schema.sql`)
- Harmonization & analytics transformations (`Transformation/transformations.sql`)
- UDF creation scripts (`Delivery and udfs/udf.sql`, `udfCreation.sql`)
d. Visualization Layer
- Streamlit app (`Streamlit dashboard/dashboard.py`) using Python + Altair
- Static images/media (`Media/`)

1. External Sources ➔ PUBLIC Layer
- S3 bucket ➔ CSV_FORMAT & S3LOAD stage
- Marketplace tables ➔ joined in harmonized views
2. PUBLIC ➔ RAW Schemas
- COPY INTO RAW_POS.<tables>
- COPY INTO RAW_CUSTOMER.customer_loyalty
3. RAW ➔ HARMONIZED
- RAW_POS + RAW_CUSTOMER tables ➔ `honey harmonized.orders_v`
- Weather source tables ➔ `harmonized.daily_weather_v`
4. HARMONIZED ➔ ANALYTICS
- orders_v, customer_loyalty_metrics_v ➔ analytics views
- UDFs (fahrenheit_to_celsius, inch_to_millimeter) apply on weather data
5. ANALYTICS ➔ Streamlit Dashboard
- Python connector queries analytics views
- Altair plots daily sales vs. weather metrics

##### Architectural Patterns & Design Principles
- Layered Data Vault / ELT architecture (Public → Raw → Harmonized → Analytics)
- Modular SQL scripts organized by function: ingestion, transformation, UDF deployment
- Reusable UDFs for unit conversions, promoting DRY principles
- Separation of concerns: data modeling in SQL, visualization in Python

## Diagram (Mermaid)

```mermaid
flowchart TD
    %% External Sources
    subgraph "External Data Sources"
        S3["AWS S3\ns3://sfquickstarts/tasty-bytes-builder-education/"]:::dataSource
        Marketplace["Snowflake Marketplace\n(Weather Source LLC)"]:::dataSource
    end

    %% Snowflake Data Warehouse
    subgraph "Snowflake Data Warehouse"
        subgraph "PUBLIC Layer" 
            CSV_FORMAT["CSV_FORMAT file format"]:::snowflake
            S3_STAGE["S3LOAD stage"]:::snowflake
        end
        subgraph "RAW Layer"
            RAW_POS["RAW_POS schema"]:::snowflake
            RAW_CUSTOMER["RAW_CUSTOMER schema"]:::snowflake
        end
        subgraph "HARMONIZED Layer"
            OrdersV["orders_v view"]:::snowflake
            DailyWeatherV["daily_weather_v view"]:::snowflake
            CLMetricsV["customer_loyalty_metrics_v view"]:::snowflake
        end
        subgraph "ANALYTICS Layer"
            AOrdersV["A_ORDERS_V view"]:::snowflake
            ACustomerV["A_CUSTOMER_LOYALTY_V view"]:::snowflake
        end
    end

    %% UDFs
    subgraph "UDFs"
        UDFCreate["udfCreation.sql"]:::udf
        UDFDefs["udf.sql"]:::udf
    end

    %% Visualization
    subgraph "Visualization Layer"
        Streamlit["Streamlit App"]:::viz
        Media["Static Assets"]:::viz
        Browser["End-user Browser"]:::external
    end

    %% Data Flow
    S3 -->|"COPY INTO PUBLIC"| CSV_FORMAT
    S3 -->|"COPY INTO PUBLIC"| S3_STAGE
    Marketplace -->|"CREATE VIEW"| DailyWeatherV

    CSV_FORMAT -->|"LOAD"| RAW_POS
    S3_STAGE -->|"LOAD"| RAW_POS
    CSV_FORMAT -->|"LOAD"| RAW_CUSTOMER
    S3_STAGE -->|"LOAD"| RAW_CUSTOMER

    RAW_POS -->|"CREATE VIEW"| OrdersV
    RAW_CUSTOMER -->|"CREATE VIEW"| CLMetricsV
    Marketplace -->|"CREATE VIEW"| DailyWeatherV

    OrdersV -->|"CREATE ANALYTICS VIEW"| AOrdersV
    CLMetricsV -->|"CREATE ANALYTICS VIEW"| ACustomerV
    DailyWeatherV -->|"APPLY UDFs"| AOrdersV

    UDFDefs -->|"DEFINITION" | OrdersV
    UDFDefs -->|"DEFINITION" | DailyWeatherV
    UDFCreate -->|"CREATES UDFs" | UDFDefs

    AOrdersV -->|"Python query"| Streamlit
    ACustomerV -->|"Python query"| Streamlit
    Streamlit -->|"Renders dashboard"| Browser
    Media -->|"Assets"| Streamlit

    %% Click events
    click CSV_FORMAT "https://github.com/hrishikesh26/tasty-bytes-snowflake-data-analysis-with-sql/blob/main/Ingestion/warehouse schema.sql"
    click S3_STAGE "https://github.com/hrishikesh26/tasty-bytes-snowflake-data-analysis-with-sql/blob/main/Ingestion/warehouse schema.sql"
    click RAW_POS "https://github.com/hrishikesh26/tasty-bytes-snowflake-data-analysis-with-sql/blob/main/Ingestion/ingestion.sql"
    click RAW_CUSTOMER "https://github.com/hrishikesh26/tasty-bytes-snowflake-data-analysis-with-sql/blob/main/Ingestion/ingestion.sql"
    click OrdersV "https://github.com/hrishikesh26/tasty-bytes-snowflake-data-analysis-with-sql/blob/main/Transformation/transformations.sql"
    click DailyWeatherV "https://github.com/hrishikesh26/tasty-bytes-snowflake-data-analysis-with-sql/blob/main/Transformation/transformations.sql"
    click CLMetricsV "https://github.com/hrishikesh26/tasty-bytes-snowflake-data-analysis-with-sql/blob/main/Transformation/transformations.sql"
    click AOrdersV "https://github.com/hrishikesh26/tasty-bytes-snowflake-data-analysis-with-sql/blob/main/Transformation/transformations.sql"
    click ACustomerV "https://github.com/hrishikesh26/tasty-bytes-snowflake-data-analysis-with-sql/blob/main/Transformation/transformations.sql"
    click UDFCreate "https://github.com/hrishikesh26/tasty-bytes-snowflake-data-analysis-with-sql/blob/main/Delivery and udfs/udfCreation.sql"
    click UDFDefs "https://github.com/hrishikesh26/tasty-bytes-snowflake-data-analysis-with-sql/blob/main/Delivery and udfs/udf.sql"
    click Streamlit "https://github.com/hrishikesh26/tasty-bytes-snowflake-data-analysis-with-sql/blob/main/Streamlit dashboard/dashboard.py"
    click Media "https://github.com/hrishikesh26/tasty-bytes-snowflake-data-analysis-with-sql/tree/main/Media/"

    %% Styles
    classDef dataSource fill:#cfe2ff,stroke:#3182BD,stroke-width:2px;
    classDef snowflake fill:#d4edda,stroke:#28A745,stroke-width:2px;
    classDef udf fill:#e2d5f5,stroke:#9F7AEA,stroke-width:2px;
    classDef viz fill:#d1f2eb,stroke:#319795,stroke-width:2px;
    classDef external fill:#e2e3e5,stroke:#6c757d,stroke-width:2px;
```
