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

Amazon inventory snapshots

Choose periodic snapshot grain for warehouse-level inventory SLAs while explaining movement-fact 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 fulfillment analytics team needs daily 6 a.m. on-hand inventory by warehouse, product, and region for 450 warehouses. The SLA dashboard must load in under two minutes, and planners also ask how stockouts changed after replenishment policy updates. Design the warehouse model and defend snapshot versus movement-grain choices.

Why this matters

Inventory can be modeled as movements, snapshots, or both. The stated SLA needs reproducible daily state without replaying every receipt, pick, adjustment, and transfer for every dashboard query.

Requirements
  • Add an inventory snapshot fact at warehouse-product-day grain.
  • Record snapshot date and on-hand quantity.
  • Connect snapshots to warehouse and product dimensions through surrogate foreign keys.
  • Explain how movement-grain facts can support audit without being the SLA dashboard grain.
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

Inventory snapshot 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.