Meta·Software Engineer·Onsite - System Design / Architecture·Senior

Senior

Jul 2026

Summary

Meta data engineering interview, system design round focused on event storage for a consumer app. The question was dense and covered a lot of ground in one shot.

Questions Asked (1)

Design a relational schema to store user events for a consumer app. Include table definitions with columns and data types, primary and foreign keys, partitioning and indexing strategy, and sample rows. Also explain your normalization versus denormalization decisions, how you'd handle late-arriving or duplicate events, and how you'd support backfills and incremental fact tables.

Data ModelingSystem DesignTechnical Trade-offs

Author's notes

This was a lot to unpack in one question.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying requirements (event types, volume, query patterns) and then propose a normalized core schema with an append-only events table, plus denormalized dimension tables for fast analytics. Discuss partitioning by time, indexing for common access patterns, and strategies for late/duplicate events, backfills, and incremental fact tables.

Pro tip: Emphasize idempotency and exactly-once semantics using event IDs and deduplication logic, and mention how partitioning and clustering keys in a columnar store (like BigQuery or Snowflake) can drastically improve performance for analytical queries.

1. Clarify Requirements and Assumptions

Ask about event types, expected volume, query patterns (OLTP vs OLAP), and latency requirements. State assumptions to guide schema design.

2. Design Core Tables and Relationships

Define an events table with columns like event_id, user_id, event_type, timestamp, and properties (JSON). Add dimension tables for users, devices, etc., with primary and foreign keys.

3. Choose Partitioning and Indexing Strategy

Partition the events table by date (e.g., daily) to manage large volumes and enable efficient time-range queries. Index on user_id, event_type, and timestamp for common filters.

4. Address Data Quality and Evolution

Explain handling of late-arriving events (e.g., using event timestamp and watermarks) and duplicates (e.g., deduplication via event_id). Discuss backfills by reprocessing partitions and incremental fact tables using merge/upsert.

5. Justify Normalization vs Denormalization

Normalize transactional data to reduce redundancy and ensure consistency. Denormalize for analytical queries by creating wide fact tables or materialized views to improve read performance.

Key Points to Mention

Use of a surrogate key (event_id) and natural keys (user_id, timestamp) for uniqueness and deduplication.
Partitioning by event_date to enable partition pruning and efficient backfills.
Indexing strategy: composite indexes on (user_id, event_timestamp) and (event_type, event_timestamp) for common query patterns.
Handling late events: store event_timestamp and ingestion_timestamp; use watermarks or a grace period for windowed aggregations.
Deduplication: use event_id with a unique constraint or MERGE statement to ignore duplicates.
Incremental fact tables: use slowly changing dimensions (SCD) type 2 for user attributes and merge/upsert for fact tables.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.