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

Package workflow snapshot

Choose an accumulating snapshot when the business needs one package lifecycle, not only status events.

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

5

Business needs the model must satisfy.

Read the concept guide: Fact table grain
Scenario

A logistics team has package status events from carriers. Executives want pickup-to-delivery hours by carrier and service level, with one row per package that updates as milestones arrive.

Why this matters

Transaction events are useful, but they are not always the best primary answer. Pipeline reporting often needs milestone dates side by side on one workflow row.

Requirements
  • Create a package workflow fact where one row represents one package lifecycle.
  • Keep the package id queryable on the fact.
  • Add pickup and delivered milestone timestamps.
  • Add a transit-hours or elapsed-hours measure.
  • Connect the fact to carrier context through an explicit surrogate-key relationship.
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

Package status starter workspace

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.