Skip to content
Back to challenges
Grain and Fact TypesConcept Challengeintermediate30 min

Inventory: movement vs snapshot

Recognize the periodic snapshot trap when daily inventory state must exist even on days with no movement.

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

A fulfillment team has inventory movement events, but executives need end-of-day on-hand quantity by product and warehouse for every calendar date. Some products sit untouched for days and still need reporting rows.

Why this matters

Movement facts explain why inventory changed. They do not by themselves store the daily state that operations teams trend, reconcile, and alert on.

Requirements
  • Create a daily inventory snapshot fact where one row represents one product in one warehouse on one snapshot date.
  • Add snapshot date and on-hand quantity columns to the fact.
  • Declare a key that covers product, warehouse, and snapshot date.
  • Connect the fact to product and warehouse dimensions through explicit surrogate-key relationships.
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

Daily inventory snapshot starter workspace

The model users start from in the full workspace.

Entities

3

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.