## 🧩 Challenge 01 — Work Plan

**From Spreadsheets to Structure: Step-by-Step**

This work plan guides participants through the practical stages of **Challenge 01 – Spaghetti → Schema**, the first milestone in the *Grocery Retail – Driven by Data* series.
It defines a clear, outcome-based roadmap—from initial data ingest to a validated relational schema and baseline analytical proof.

---

### 🗺️ Workflow Overview

| Phase                              | Focus                                                                                           | Outcome                                              |
| :--------------------------------- | :---------------------------------------------------------------------------------------------- | :--------------------------------------------------- |
| **1. Problem Definition**          | Frame the business context, data goals, and evaluation criteria.                                | Clear problem statement and notebook stub            |
| **2. Data Ingestion**              | Read, validate, and profile raw CSVs with explicit types and date parsing.                      | Clean pandas DataFrames with basic integrity checks  |
| **3. Data Documentation**          | Build a complete, human-readable **Data Dictionary**.                                           | Column definitions, types, quirks, and examples      |
| **4. Entity-Relationship Design**  | Reverse-engineer tables and relationships from the messy sheets.                                | Conceptual **ERD** showing PK/FK logic and rationale |
| **5. Schema & DDL Implementation** | Normalize to 3NF and generate PostgreSQL DDL scripts with constraints and indexes.              | Executable schema (ddl.sql + indexes.sql)            |
| **6. ETL Pipeline**                | Create repeatable scripts to clean, type-coerce, and load data into the schema.                 | Automated, idempotent load with audit logs           |
| **7. Exploratory Analysis**        | Validate the schema by running descriptive queries and visualizing key dimensions.              | 3–5 EDA visuals + short narrative insight            |
| **8. Baseline Model**              | Demonstrate analytic readiness with a simple predictive example (e.g., daily-sales regression). | Model notebook + metric (MAE/MAPE/RMSE)              |
| **9. Presentation Summary**        | Communicate the workflow and findings.                                                          | 6–10 slide summary deck                              |
| **10. Submission Package**         | Assemble all deliverables in the prescribed folder structure.                                   | Reproducible, well-documented project zip            |

---

### 🧰 Practical Notes

* **Environment:** Python 3 + PostgreSQL 15 recommended.
* **Libraries:** `pandas`, `sqlalchemy`, `matplotlib / seaborn`, `scikit-learn` (for baseline).
* **Version Control:** Use GitHub or Kaggle Code Versions for traceability.
* **Reproducibility:** All scripts should be **re-runnable end-to-end** with no manual edits.
* **Validation Checks:** Row counts ✓ Foreign keys ✓ No duplicate IDs ✓ Type coercion ✓

---

### ✅ Deliverables Checklist

| Category             | Files / Outputs                                                  |
| :------------------- | :--------------------------------------------------------------- |
| **Docs & Design**    | `docs/architecture.md`, ERD image, design notes                  |
| **SQL Schema**       | `sql/ddl.sql`, `sql/indexes.sql`                                 |
| **ETL Scripts**      | `scripts/load_data.py`, ETL logs                                 |
| **Notebooks**        | `01_eda.ipynb`, `03_model_baseline.ipynb`                        |
| **Reports & Slides** | `reports/executive_summary.pdf`, `slides/presentation_deck.pptx` |

---

### 🧭 Outcome

By following this plan, participants will deliver a **fully reproducible relational data foundation** that:

* Integrates scattered retail spreadsheets into one coherent schema.
* Provides clear documentation and validation evidence.
* Demonstrates the first analytical bridge between data structure and business insight.

This milestone sets the stage for **Challenge 02 (Errors → Integrity)**, where the same database will face systematic data-quality tests and imputation tasks.