Capstone: Design the foundation

The capstone extends the warehouse you've built with a realistic business change, end to end, using every module. Harbor Lane's brief: (1) the fulfillment system now emits a daily returns_YYYY-MM-DD.csv (return_id, order_id, returned_at, reason), and finance wants return metrics net of it; (2) a wholesale channel launches next quarter — same order export format, channel = 'wholesale', business customers that must not pollute consumer metrics. Ship the extension, keep every existing guarantee intact.

Deliverable one: the design doc (before any code)

• Model — Is returns a new fact table (what grain?) or a status mutation on fct_order_lines? Defend the choice; the wrong answer here costs you in every later section. (Strong hint from Module 1: events that happened are facts; returns happened.)
• Pipeline — Which idempotency pattern loads returns? What does the watermark mean when a return references an order from three weeks ago (late-arriving data, now for real)?
• Quality — The full check list for the new feed, each with its family: return_id uniqueness, order_id referential integrity against the fact (what does an orphan return mean — bad data, or a return for a pre-warehouse order?), accepted reason values, freshness and volume with what baseline?
• Resolution — Wholesale 'customers' are businesses. Do they enter dim_customer? (Recommended ruling: no — a separate dim_account; mixing person-entities and business-entities in one resolved dimension breaks both.) What does that imply for the crosswalk and the fact's key columns?
• Metrics — Definitions for return_rate and net_revenue_after_returns (month-of-sale or month-of-return? — decide, document, defend), plus the mandatory-filter change that keeps every existing consumer metric consumer-only. Which metrics need a v2 and a deprecation window?

Write the doc as if a colleague will implement it — because grading treats it that way. The historic failure mode of data work is code-first-think-later; the capstone forces the professional order. Budget two focused hours; the design doc is graded as heavily as the build.