How 100x-home-llc/data_engineer_assessment works
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)