Lab 2: Delta Lake Upserts & Time Travel

Deliverable: Git repo with delta_job.py, a versioned Delta table, and EVIDENCE.md.

1. Scenario

ShopLite's order feed now sends intraday updates (status changes, amount corrections). Plain-Parquet overwrite would either duplicate or lose history. Implement the Delta silver layer.

2. Requirements

1. Seed lake/delta_orders/ (v0) from Lab 1 output.
2. Apply two update batches via SQL MERGE on order_id (matched -> update, not matched -> insert).
3. Demonstrate time travel: query versionAsOf 0 vs latest, record both counts in EVIDENCE.md.
4. Corrupt the table deliberately (bad batch vN), then RESTORE to the prior version; show counts before/after.
5. Run OPTIMIZE + ZORDER BY (customer_id); capture file counts before/after.
6. VACUUM ... RETAIN 168 HOURS (dry-run first, then execute).

3. Grading rubric (100 pts)

• MERGE correctness (30): no duplicates, updates applied, matched/not-matched both exercised.
• Time travel + restore proof (25): versioned counts documented.
• File management (20): optimize evidence, sane vacuum retention choice justified.
• Code quality (15): parameterized batch input, rerunnable seed.
• EVIDENCE.md clarity (10).

4. Stretch

Add CHECK (amount >= 0) constraint; show a violating batch being rejected.