Design tables for event-driven metrics
Overview
This question evaluates relational data modeling and data engineering competencies for high-volume event analytics, including schema design, partitioning and clustering strategies, duplicate and late-arriving event handling, and normalization versus denormalization trade-offs.
Design a Relational Schema for Consumer-App Event Analytics
Context and Assumptions
You are designing the event store for a high-volume consumer app. The schema must support common product analytics and growth metrics efficiently, including:
- Activity: DAU/WAU/MAU, stickiness
- Retention cohorts (N-day, week-over-week)
- Sessions: counts and lengths
- Funnels and conversions (e.g., view → add_to_cart → purchase)
- Revenue (ARPU/ARPPU), feature usage, geo/device/app-version breakdowns
Assume at-least-once delivery from clients/services, possible late-arriving events, and high cardinality in event parameters. The store will be queried in a columnar data warehouse or relational engine that supports partitioning and clustering.
Task
Design a relational schema to store user events so that the metrics above can be computed efficiently. Provide:
- Table definitions with columns and data types
- Primary and foreign keys
- Partitioning and indexing strategy
- Sample rows
- Explanations for normalization versus denormalization choices
- How to handle late-arriving or duplicated events
- How to support backfills and incremental fact tables
Solution
Login required
Related Questions