Model data for an ads platform
Company: Netflix
Role: Software Engineer
Category: System Design
Difficulty: hard
Interview Round: Technical Screen
## Problem
Design a **data model** for an online advertising system that supports creating ads and analyzing delivery/performance.
### Core product needs
The system should support:
- Advertisers with multiple **campaigns**.
- Campaigns containing **ad groups** (or similar grouping) and **creatives/ads**.
- **Targeting** (e.g., geo, device, interests, custom audiences).
- Budgeting and pacing (daily/lifetime budgets).
- Reporting/analytics on delivery events: **impressions, clicks, conversions**.
### Tasks
1. Propose a relational schema (tables + key columns) for the “source of truth” entities.
2. Decide how to represent targeting in the model (normalized tables vs JSON/kv vs hybrid), and justify.
3. Design how event logs (impression/click/conversion) should be stored for high volume and how they join back to ads/campaigns for reporting.
4. List the most important indexes/partition keys.
5. Provide 2–3 example queries the business will need (e.g., campaign spend yesterday, CTR by creative, conversions by audience).
### Assumptions
- You may assume a typical ads hierarchy: Advertiser → Campaign → AdGroup → Ad/Creative.
- Event volume is large (analytics-scale). State any additional assumptions you need.
Quick Answer: This question evaluates a candidate's competency in data modeling and database architecture for large-scale advertising systems, including relational schema design, representation of targeting data, high-volume event ingestion and storage, and analytics-ready joins, indexes, and partitioning.