Meta marketplace listings
Design a listings model that supports activation metrics, seller history, purchases, and one conformed customer identity.
Concept
conformed-dimensions
The primary modeling idea this problem reinforces.
Requirements
5
Business needs the model must satisfy.
A marketplace team wants to understand why listing activation dropped after a seller quality policy changed. The interviewer asks for the metrics first: activated listings, conversion to purchase, seller tier mix, and buyer repeat rate. Then they ask how the model handles sellers whose tier or trust status changes over time.
Marketplace prompts combine lifecycle events with identity modeling. If seller status is overwritten or buyer and seller identity split into unrelated dimensions, conversion and policy-impact analysis become inconsistent.
- Keep one conformed customer dimension for buyers and sellers.
- Add an effective-from so seller-status history is recoverable as of a listing event (the floor).
- Grade up by closing each seller version with an effective-to, so current status cannot overwrite historical listing context.
- Create a listing lifecycle fact with listing id, lifecycle time, and status.
- Connect both listing lifecycle and purchase facts to the customer dimension through explicit surrogate-key foreign keys.
Template
Marketplace listings 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.
This preview is read-only. Open the workspace to edit and verify; saving needs a free account.
Open workspaceTry the question first.
The discussion has other people's approaches and solutions. Give it a real attempt before you read them.