# 100x-home-llc/data_engineer_assessment architecture

> Architecture diagram of the GitHub repository 100x-home-llc/data_engineer_assessment, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2025-11-25). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/100x-home-llc/data_engineer_assessment
- Repository: https://github.com/100x-home-llc/data_engineer_assessment

## Overview

##### Project Type and Purpose
- This is a standalone Data Engineering pipeline: it ingests raw JSON data, applies cleaning and normalization logic (guided by an Excel “Field Config” file), and loads it into a relational MySQL database.
- Key deliverables:
- Python ETL code (under `src/`)
- Initial and final MySQL schemas (managed via Docker)
- Data files in `data/`

##### File Structure
- Top‐level artifacts:
- `docker-compose.initial.yml` + `Dockerfile.initial_db`: brings up “initial” MySQL instance (empty schema).
- `docker-compose.final.yml` + `Dockerfile.final_db`: brings up “final” MySQL instance (target, normalized schema).
- `data/`: raw JSON (`fake_property_data_new.json`) & business‐logic spreadsheet (`Field Config.xlsx`).
- `src/`: (to be created by candidate) Python + SQL scripts for ETL.
- No frontend or service layers—this is a simple batch pipeline orchestrated by Docker Compose.

##### Extract Additional Insights from README
- Workflow:
1. `docker-compose -f docker-compose.initial.yml up` → spin up initial DB
2. Run Python ETL script → reads JSON + Excel config → creates normalized tables + inserts data into final DB
3. `docker-compose -f docker-compose.final.yml up` → spin up final DB (for validation)
- Dependencies: Python ≥ 3.8, MySQL 8, Pydantic, other libraries in `requirements.txt`.

##### Main Components & Their Interactions
a. Data Sources
- Raw JSON file (fake_property_data_new.json)
- Field Config spreadsheet (Field Config.xlsx)
b. ETL Application
- Python script(s) in `src/`
- Schema‐creation SQL scripts (DDL)
c. Containers & Databases
- initial_db container (MySQL)
- final_db container (MySQL)
d. Orchestration
- Docker Compose brings containers up/down
e. External Tools
- Pydantic for data validation

- Rectangle (Application): Python ETL
1. JSON → Python ETL (Ingestion)
2. Excel → Python ETL (Config/Metadata)
3. Python ETL → Initial MySQL DB (Stage or validation step, if used)
4. Python ETL → Final MySQL DB (Normalized load)

##### Architectural Patterns & Principles
- ELT/ETL pattern: Extract → (Staging) → Transform & Load
- Configuration-driven design: business rules externalized in Excel
- Containerized environments for repeatable infrastructure (Docker Compose)

## Diagram (Mermaid)

```mermaid
flowchart TD
    subgraph "Data Sources"
        RawJSON["📄 Raw JSON Data Source"]:::file
        FieldConfig["📄 Field Config Spreadsheet"]:::file
    end

    subgraph "ETL Application"
        PythonETL["🐍 Python ETL Service\n(src/)"]:::app
    end

    subgraph "Containers & Orchestration"
        subgraph "Initial MySQL (Staging)"
            InitialDB["🗄️ Initial MySQL DB\n(port:3306)"]:::db
            InitialCompose["docker-compose.initial.yml"]:::config
            InitialDockerfile["Dockerfile.initial_db"]:::config
        end
        subgraph "Final MySQL (Normalized)"
            FinalDB["🗄️ Final MySQL DB\n(port:3306)"]:::db
            FinalCompose["docker-compose.final.yml"]:::config
            FinalDockerfile["Dockerfile.final_db"]:::config
        end
    end

    README["📘 README.md"]:::doc

    RawJSON -->|"ingest JSON"| PythonETL
    FieldConfig -->|"load config"| PythonETL
    PythonETL -->|"write to staging"| InitialDB
    PythonETL -->|"write to final"| FinalDB

    InitialCompose -->|"orchestrates"| InitialDB
    InitialDockerfile -->|"builds"| InitialDB
    FinalCompose -->|"orchestrates"| FinalDB
    FinalDockerfile -->|"builds"| FinalDB
    README -->|"documents workflow"| InitialCompose
    README -->|"documents workflow"| FinalCompose

    click RawJSON "https://github.com/100x-home-llc/data_engineer_assessment/blob/main/data/fake_property_data_new.json"
    click FieldConfig "https://github.com/100x-home-llc/data_engineer_assessment/blob/main/data/Field Config.xlsx"
    click InitialCompose "https://github.com/100x-home-llc/data_engineer_assessment/blob/main/docker-compose.initial.yml"
    click InitialDockerfile "https://github.com/100x-home-llc/data_engineer_assessment/blob/main/Dockerfile.initial_db"
    click FinalCompose "https://github.com/100x-home-llc/data_engineer_assessment/blob/main/docker-compose.final.yml"
    click FinalDockerfile "https://github.com/100x-home-llc/data_engineer_assessment/blob/main/Dockerfile.final_db"
    click README "https://github.com/100x-home-llc/data_engineer_assessment/blob/main/README.md"

    classDef file fill:#cce5ff,stroke:#333,stroke-width:1px
    classDef app fill:#d4edda,stroke:#333,stroke-width:1px
    classDef db fill:#ffe5b4,stroke:#333,stroke-width:1px
    classDef config fill:#e2e3e5,stroke:#333,stroke-width:1px
    classDef doc fill:#f5e1f7,stroke:#333,stroke-width:1px
```
