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

SeniorPrefer not to say

Apr 2026

Summary

Netflix system design round focused entirely on ads infrastructure. One big question, lots of surface area, and I felt like I was playing catch-up the whole time.

Questions Asked (1)

Design the data model for an ads demand system, covering advertisers, campaigns, line items, ads, targeting criteria, bids, budgets, and impression/click events. Walk through your fact vs dimension table choices, how you'd handle slowly-changing dimensions for campaigns, your partitioning strategy for event data at impression scale, and how the schema supports queries like demand forecasting, fill rate analysis, and pacing reports.

Data ModelingSystem DesignTechnical Trade-offs

Author's notes

This is a beast of a question and I underestimated how interconnected the pieces are.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the scope and requirements, then design a star schema with fact tables for events and dimension tables for entities, explicitly handling slowly changing dimensions for campaigns. Explain partitioning and indexing strategies for impression-scale data, and show how the schema enables forecasting, fill rate, and pacing queries.

Pro tip: Emphasize trade-offs between normalization and denormalization, and discuss how you'd handle late-arriving facts and dimension updates to ensure accurate reporting.

1. Clarify Requirements and Scope

Ask about data volume, query patterns, latency requirements, and update frequency to tailor the design. Confirm whether the system needs real-time or batch processing.

2. Identify Entities and Relationships

List core entities: advertisers, campaigns, line items, ads, targeting criteria, bids, budgets, and events. Define relationships and cardinalities (e.g., one campaign has many line items).

3. Design Fact and Dimension Tables

Create fact tables for impressions and clicks with foreign keys to dimensions. Use dimension tables for advertisers, campaigns, line items, ads, targeting, bids, and budgets. Consider snowflaking vs. star schema based on query needs.

4. Handle Slowly Changing Dimensions (SCDs)

For campaigns and line items, use SCD Type 2 to track historical changes (e.g., budget updates, targeting changes). Add valid_from and valid_to timestamps and a current flag.

5. Partition and Optimize Event Data

Partition impression and click facts by date (and possibly advertiser/campaign) to manage scale. Use columnar storage, compression, and appropriate indexes. Consider pre-aggregations for common queries.

6. Support Analytical Queries

Show how the schema enables demand forecasting (join campaigns with historical events), fill rate analysis (impressions vs. requests), and pacing reports (budget vs. spend over time).

Key Points to Mention

Star schema with fact tables for impressions and clicks, and dimension tables for advertisers, campaigns, line items, ads, targeting, bids, and budgets.
Slowly changing dimensions (Type 2) for campaigns and line items to track historical changes and enable point-in-time reporting.
Partitioning strategy: partition fact tables by date (and possibly advertiser_id) to handle high volume; use columnar storage and compression.
Trade-offs: denormalization for query performance vs. normalization for storage and update efficiency; use of surrogate keys.
Handling late-arriving facts and dimension updates: use of effective dates and reprocessing strategies.
Query patterns: demand forecasting (aggregate historical performance by targeting), fill rate (impressions/requests), pacing (spend vs. budget over time).

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