PROJECT 28Intermediate
Excel Files to SQL Views
Business scenario
Each store manager sends a monthly Excel file with stock counts. An analyst copies them into one workbook by hand — slow and error-prone.
Problem statement
Design a repeatable process that loads the files into a database table and exposes clean SQL views for reporting.
Learning objectives
- Design a staging table and a clean view
- Validate incoming files
- Make the process repeatable
Instructions
Simulate store files by splitting inventory_monthly (or orders) CSV by store/month. Load into a staging table, apply validation (required columns, types, duplicates), and create views (v_stock_latest, v_stock_monthly).
Tasks
- Staging table DDL.
- Loader (Python or Power Query) with validation log.
- SQL views with business logic.
- Runbook for monthly operation.
Deliverables
- Loader script
- SQL DDL and views
- Runbook
Evaluation rubric
| Criterion | Weight | Excellent | Proficient | Developing | Insufficient |
|---|---|---|---|---|---|
| Design | 25% | Staging vs presentation separated; idempotent loads. | Correct and complete with minor gaps in rigour or presentation. | Partly correct; important steps missing, unvalidated or unclear. | Missing, incorrect, or not supported by the data. |
| Validation | 25% | Errors caught and logged. | Correct and complete with minor gaps in rigour or presentation. | Partly correct; important steps missing, unvalidated or unclear. | Missing, incorrect, or not supported by the data. |
| Views | 20% | Correct, documented logic. | Correct and complete with minor gaps in rigour or presentation. | Partly correct; important steps missing, unvalidated or unclear. | Missing, incorrect, or not supported by the data. |
| Runbook | 15% | Someone else could operate it. | Correct and complete with minor gaps in rigour or presentation. | Partly correct; important steps missing, unvalidated or unclear. | Missing, incorrect, or not supported by the data. |
| Presentation | 15% | Concise, well-labelled outputs a manager can act on without explanation. | Correct and complete with minor gaps in rigour or presentation. | Partly correct; important steps missing, unvalidated or unclear. | Missing, incorrect, or not supported by the data. |