Skip to content
Back to challenges
Amazon-style Warehouse ModelingConcept Challengeadvanced30 min

Amazon order warehouse

Choose order-line grain while defending operational lookup and analytical aggregation tradeoffs.

How this preview works
Preview this problem before signing in. Sign in to save progress and submit work.

Concept

fact-table-grain

The primary modeling idea this problem reinforces.

Requirements

4

Business needs the model must satisfy.

Read the concept guide: Fact table grain
Scenario

Amazon-style prompt: a retail operations team processes 18 million order lines per day. Customer support needs fast lookup by order id and fulfillment status, while finance needs revenue and item quantity by product category, Prime status, and order date. Design the warehouse model that supports both without hiding item-level detail.

Why this matters

Order models are where normalize-vs-denormalize pressure gets concrete. A single order-header table is tempting for lookup, but it loses item-level measures and product context needed for analytics.

Requirements
  • Model one fact row as one order line.
  • Keep order id and line id on the fact as degenerate dimensions.
  • Record ordered time, line status, quantity, and line amount.
  • Connect order lines to customer and product dimensions through surrogate foreign keys.
Read-only starter workspace
Inspect the authored starting ERD, then open the workspace to edit and run the checks. Saving your progress needs a free account.

Template

Order management warehouse starter

The model users start from in the full workspace.

Entities

2

Seeded tables visible in the public preview.

Dialect

postgresql

Generated SQL target for this starter model.

Mini Map

This preview is read-only. Open the workspace to edit and verify; saving needs a free account.

Open workspace

Try the question first.

The discussion has other people's approaches and solutions. Give it a real attempt before you read them.