Design a Warehouse for Key Metrics

Q: Design a Warehouse for Key Metrics

This question evaluates a data engineer's skills in dimensional modeling, data warehousing, and analytics infrastructure, covering facts and dimensions, grain definition, surrogate keys, partitioning and clustering strategies, and operational concerns like deduplication, late-arriving events, null/anonymous users, and slowly changing dimensions.

Q: How do I approach System Design interview questions?

System Design questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master system design interviews.

Question

Design a Warehouse Model for Marketplace Analytics

You are designing a warehouse model for an e-commerce marketplace with buyers, sellers, orders, order_items, payments, and page_view events. The goal is to support the following metrics:

Daily session-to-purchase conversion rate
Average order value (AOV)
7-day buyer retention by signup cohort
Cancellation rate by seller
Daily GMV by category

Provide a dimensional model proposal that specifies:

Fact and dimension tables
The grain of each table
Key columns (primary and foreign keys)
Important attributes and data types
Partitioning and clustering strategy
Handling of late-arriving events, deduplication, null/anonymous users, and slowly changing seller attributes
Justification for star vs. snowflake choices and use of surrogate keys

No SQL is required.

Design a Warehouse for Key Metrics

Design a Warehouse Model for Marketplace Analytics

Solution

Comments (0)

Design a Warehouse for Key Metrics

Overview

Design a Warehouse Model for Marketplace Analytics

Solution

Comments (0)