Design tables for event-driven metrics

Q: Design tables for event-driven metrics

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.

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.

Q: What difficulty level is this interview question?

This is a hard difficulty System Design question, commonly asked during Technical Screen rounds at Meta.

Q: What role is this question designed for?

This question is commonly asked for Data Engineer candidates at Meta during technical interviews.

Question

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

Design tables for event-driven metrics

Quick Overview

Design tables for event-driven metrics

Design a Relational Schema for Consumer-App Event Analytics

Context and Assumptions

Task

Submit Your Answer to Earn 20XP