Data modeling · Intermediate · 45 min
A warehouse for orders, returns, and refunds
Use separate fact grains for events that can happen more than once.
Task
Design a warehouse that reports ordered value, returned quantity, and refunded amount by product and business day. An order contains several lines, a line can be returned in pieces, and refunds can occur in multiple payments.
Requirements
- Define the grain and stable key of each fact table. Do not put one order total on every line.
- Distinguish the order date, return date, and refund date. State how reports use them.
- Support partial returns and partial refunds without multiplying measures when joining facts.
- Include product dimensions, source reconciliation, late corrections, and a published reporting mart.
Fact grainDimensional modelingPartial refunds
Loading exercise…