Katkat
Back

Model ads demand data for reporting

NetflixSoftware Engineer · Onsite
MediumSoftware EngineerOnsiteSystem Design

Last updated

This question tests the ability to design a robust data pipeline and schema for tracking ad performance metrics, focusing on handling event-level data, aggregations, and dimension consistency.

Problem Statement

You are designing a reporting backend for an ad-supported video streaming service. The system must process millions of ad impression events per minute, store them, and serve hourly reports to advertisers (e.g., total impressions, unique viewers, and completion rates by ad campaign).

Functional Requirements:

  1. Ingest impression events (timestamp, ad_id, campaign_id, user_id, device_type).
  2. Maintain a history of campaign metadata (e.g., campaign name, budget, targeting criteria) as these attributes may change over time.
  3. Serve queries that aggregate performance by campaign and device, even if the campaign metadata has been updated.

Non-functional Requirements:

  • Low latency for data ingestion.
  • Consistency in reporting (e.g., if a campaign is renamed, historical reports should reflect the name at the time of the impression).
  • Ability to handle massive write throughput.

The Goal:

Design a system architecture that covers the ingestion pipeline, data storage schema, and the strategy for handling campaign metadata updates. Describe how you would ensure that metrics remain accurate even when attributes like campaign names change.

Hints

Hint 1

Consider using a stream processing engine like Apache Kafka or Flink to handle the high volume of incoming impression events.

Hint 2

Look into the 'Slowly Changing Dimension' (SCD) pattern, specifically Type 2, to track changes in campaign attributes over time while preserving historical context.

Hint 3

Think about the benefits of using a columnar data warehouse (e.g., Snowflake, BigQuery) versus a relational database for performing analytical queries on massive datasets.

Follow-up questions

  • How would you handle a scenario where an event arrives out of order or is delayed due to network issues?
  • If the reporting requirement changes from hourly to real-time (sub-minute latency), how would your architecture change?
  • How would you optimize the storage schema if your primary query pattern is 'select all impressions for a specific campaign over a specific date range'?