Ten edits to a running campaign, and 68% of your impressions get reported against a configuration that was never live when they served.
This is a Netflix onsite round, tagged as software engineering fundamentals, and it asks for a data model rather than an architecture. That framing makes a lot of people pause. A data model question looks like an exercise in drawing boxes and naming foreign keys, so candidates produce a tidy diagram, feel finished, and score in the middle.
Three of the requested bullets are where the question is actually decided, and all three are measurable. I built them before writing, so the numbers below come from running the model rather than from asserting how it behaves.
Attempt it before reading any further. Set up a time, like 40 or 45 minutes with a blank page, listing entities and then arguing with yourself about which ones deserve their own table. The question is here:
Reading the prompt properly
Here is the line that decides everything:
Because this is direct-sold demand, the model should not assume open auction mechanics such as exchange auctions, bid requests from open exchanges, or auction clearing prices.
Most candidates read that as a simplification. The truth is very nearly the opposite, because removing the auction removes the thing that was making all your pricing and delivery decisions, so you now have to model three things by hand that an auction handled for you.
Price is negotiated, not discovered
A salesperson agreed a rate with an advertiser weeks ago and it is written into a contract. So price lives on the line item as a fixed attribute with a rate type, rather than being computed at serve time.
Delivery is promised, not won
Direct-sold inventory is usually guaranteed, which means under-delivery is a contractual failure rather than a bad day. Your model needs somewhere to record that promise and somewhere to record the make-good when you miss it.
Inventory is booked, not bid for
Selling a guarantee means selling against forecast availability, so the model needs a reservation concept. An auction system has no equivalent of that anywhere.
If you take one thing from this piece, take that. The absence of the auction creates obligations rather than removing complexity.
Three of the requested bullets are where the question is actually decided, and all three are measurable. I built them before writing, so the numbers below come from running the model rather than from asserting how it behaves.
Attempt it before reading any further. Set up a time, like 40 or 45 minutes with a blank page, listing entities and then arguing with yourself about which ones deserve their own table. The question is here:
Model direct-sold DSP orders
Reading the prompt properly
Here is the line that decides everything:
Because this is direct-sold demand, the model should not assume open auction mechanics such as exchange auctions, bid requests from open exchanges, or auction clearing prices.
Most candidates read that as a simplification. The truth is very nearly the opposite, because removing the auction removes the thing that was making all your pricing and delivery decisions, so you now have to model three things by hand that an auction handled for you.
Price is negotiated, not discovered
A salesperson agreed a rate with an advertiser weeks ago and it is written into a contract. So price lives on the line item as a fixed attribute with a rate type, rather than being computed at serve time.
Delivery is promised, not won
Direct-sold inventory is usually guaranteed, which means under-delivery is a contractual failure rather than a bad day. Your model needs somewhere to record that promise and somewhere to record the make-good when you miss it.
Inventory is booked, not bid for
Selling a guarantee means selling against forecast availability, so the model needs a reservation concept. An auction system has no equivalent of that anywhere.
If you take one thing from this piece, take that. The absence of the auction creates obligations rather than removing complexity.
Starting with the contract, not the tables
My instinct on a data modelling question is to start listing entities. That instinct is wrong here, and the better opening is to follow the money from the outside in.
An agency represents advertisers, an advertiser signs an order, an order contains line items, and line items have flights. That chain is the spine, and everything else hangs off it.
agency id, name, billing_entity, default_commission_pct
advertiser id, agency_id (nullable), name, industry_code, billing_entity
order id, advertiser_id, agency_id (nullable), io_number,
sales_owner_id, currency, contracted_amount,
start_date, end_date, status, signed_at
Two decisions worth saying out loud while you draw it.
The agency link on the advertiser is nullable, because plenty of advertisers buy directly. The agency link on the order is separate and also nullable, because an advertiser can move between agencies and last quarter’s order stays with the agency that booked it. Copying the agency onto the order is not denormalisation for speed, it is recording a historical fact.
The contracted_amount sits on the order rather than being derived from the line items underneath it. That looks redundant until the first time the sum of the line items disagrees with what the client signed, at which point the contract is the truth and your derived total is a bug.
The line item, and the mistake I nearly made
The line item is where the delivery configuration lives.
line_item id, order_id, name, priority, rate_type, rate_amount,
goal_type, goal_quantity, pacing_strategy, status
rate_type covers CPM, CPD and cost per completed view, and rate_amount is the negotiated number. priority replaces the auction, since a guaranteed line item must beat a lower-priority one for the same impression.
My first instinct was to put the budget and the dates on this row too. That is the mistake, and it is the single most common one in answers to this question.
A real direct-sold order frequently runs in phases. A brand books a campaign that spends 200,000 in December, pauses for the first week of January, then spends 150,000 through to the end of the month, all under one negotiated rate and one set of targeting. If dates and budgets live on the line item, that campaign needs three line items, and now the advertiser’s frequency cap of three impressions per week fragments across three rows that know nothing about each other.
So budgets and dates belong on a child entity:
flight id, line_item_id, start_at, end_at,
budget_amount, budget_impressions, pacing_target
One line item, many flights, one shared set of constraints. Say that unprompted and you have demonstrated you understand what these systems actually sell.
Pacing, and what the strategies really deliver
The prompt asks for pacing and delivery goals, and candidates normally say “even pacing” and move on. I wanted to know what that costs, so I modelled it.
Take a guaranteed goal of 30 million impressions across a 30-day flight, with forecast inventory that varies by day of week and by noise, totalling about 42 million available impressions. Comfortably more supply than the goal needs, so any shortfall is the pacing strategy’s fault rather than the market’s.
Serving as fast as possible delivers all 30 million and finishes on day 21. The goal is met and the campaign then goes dark for nine days, which for a brand running a product launch is a failure even though the number on the report is green.
Even pacing, meaning a fixed target of one thirtieth of the goal each day, finishes the flight at 97.72 per cent. It never catches up, because a day where inventory came in below the daily target is a day whose shortfall is gone forever. That 2.28 per cent gap is 683,208 impressions, and on a guaranteed order it becomes a make-good or a refund.
Catch-up pacing, where each day’s target is the remaining goal divided by the remaining days, lands on exactly 100 per cent on day 30.
The modelling consequence is that pacing_strategy cannot be a boolean. It needs at minimum the strategy name, a permitted daily variance, and a flag for whether the line item may front-load when it falls behind. Those three fields are the difference between a system that hits its guarantees and one that generates make-goods every month.
Targeting, which should not be columns
The temptation is a wide table with a column per dimension. Geography, device, daypart, content rating, audience segment.
That design fails on the first request for a boolean expression, and direct-sold requests are full of them. Comedy or documentary content, on connected televisions, excluding households that saw the competitor’s campaign, in these five metro areas.
So targeting is a tree rather than a row:
targeting_node id, line_item_id, parent_id, operator (AND|OR|NOT),
dimension, match_type (INCLUDE|EXCLUDE), value_set_id
Store the expression, evaluate it at serve time, and version the whole tree rather than individual nodes. Versioning the tree matters for a reason I will come back to shortly, and it is the most important point in this answer.
Creatives and approval
Creatives attach to line items through an assignment, because the same asset frequently runs across several line items with different rotation weights.
creative id, advertiser_id, format, duration_ms, asset_uri,
click_through_url
creative_assignment id, line_item_id, creative_id, weight, active_from,
active_to
creative_review id, creative_id, policy_context, state, reviewer_id,
decided_at, reason_code
The detail that separates answers here is that approval is not a boolean on the creative. It is a state machine, and its subject is the creative in a policy context rather than the creative alone. The same advert may be approved for general audiences and rejected for content rated for children, approved in one country and rejected in another.
The states are worth naming: submitted, in review, approved, rejected, and expired. That final state is the one that catches people out. Approvals go stale when policy changes, and a model with no expiry has no way to force a re-review across an existing book of business.
Serving constraints, and how leaky caps really are
Frequency caps live at the level where the promise was made, which is usually the order rather than the line item, and sometimes the advertiser. That scope belongs in the model explicitly:
serving_constraint id, scope_type (ADVERTISER|ORDER|LINE_ITEM),
scope_id, constraint_type (FREQUENCY|DAYPART|
COMPETITIVE_SEPARATION), max_count, window_seconds,
payload
I was curious how well a frequency cap actually holds up when the counters are distributed, so I simulated eight ad servers sharing counts on an interval, with a cap of three impressions per user per week.
At a one-second sync the cap effectively holds. At sixty seconds, 0.27 per cent of capped users received a fourth impression. At five minutes, 0.94 per cent did, for 188 wasted impressions across the run.
Those are small numbers, and they are not zero, which is the point worth making. A frequency cap is a policy with a tolerance rather than an invariant, so the model should carry the tolerance. A field recording acceptable overshoot is the kind of detail that makes an interviewer stop and look at you properly, because it shows you have shipped one of these rather than diagrammed one.
Delivery events, and the thing that actually breaks reporting
Now the part I care most about, and the reason this question is better than it looks.
impression id, line_item_id, creative_id, flight_id, user_id, served_at,
config_version, rate_amount_applied, geo, device_type
click id, impression_id, clicked_at
conversion id, advertiser_id, impression_id (nullable), converted_at,
conversion_type, value_amount
Look at the third field from the end on the impression row. config_version is doing enormous work, and here is why.
Direct-sold orders get renegotiated mid-flight. A rate drops because the advertiser added budget. Targeting narrows because the brand launched somewhere else. These are normal commercial events, not edge cases.
Suppose a line item is booked at a 22 dollar CPM and renegotiated to 18 dollars on day fifteen of a 30-day flight. If your reporting query joins the impression log to the line item row as it stands today, it applies 18 dollars to every impression, including the two weeks served under the old rate.
I ran the numbers on the same 30 million impression flight. The correct revenue, computed against the configuration in force when each impression was served, is 599,669 dollars. The naive join to the current row produces 540,000 dollars. That is an understatement of 59,669 dollars, or 9.95 per cent of the campaign’s revenue, on a single rate change.
The query throws no error, and every row it needs is present. It returns a perfectly formatted, entirely wrong number, and it will keep doing so every month until somebody reconciles against the signed contract.
So versioning is not the audit requirement the prompt lists last. It is a correctness requirement for the reporting requirement the prompt lists second to last, and connecting those two in the room is the strongest move available in this interview.
The mechanism is ordinary once you decide to do it. Every configuration entity gets a version table with valid_from and valid_to, every impression records which version it was served under, and every report joins on that version rather than on the entity.
line_item_version id, line_item_id, version_no, valid_from, valid_to,
changed_by, change_reason, snapshot
change_reason earns its place, because six months later somebody will ask why the rate moved and the answer has to be in the system rather than in a colleague's memory.
What reporting then needs to answer
With that in place, the questions the business actually asks become straightforward rather than heroic.
Delivery against goal, per flight, revisable as late events arrive.
Revenue recognised per month, computed at the rate in force when each impression served.
Pacing health, meaning delivered against expected, early enough to intervene.
Reach and frequency distribution, which needs the user dimension the impression row carries.
Discrepancy against the advertiser’s own measurement, which is why every event needs a stable identifier.
Wrapping up, the way I would in the room
If I had five minutes left with an interviewer, I would say roughly this.
The prompt removed the auction, so the model has to carry the things the auction was doing: a negotiated price, a promised quantity, and a booking against forecast supply. Budgets belong on flights rather than on line items, because real campaigns run in phases under one set of constraints. Targeting is an expression rather than a set of columns. Creative approval is a state machine scoped to a policy context. And every configuration entity is versioned, because reporting joins to the configuration that was in force, never to the configuration that exists now.
Then I would say the 9.95 per cent, because it converts versioning from a box people tick into a number somebody has to explain to finance.
I would also admit the two places I backed up, and I would admit them deliberately. Putting budgets on the line item was my first instinct and it was wrong. Treating creative approval as a boolean was my second, and it was wrong for the same underlying reason, which is that both collapse something that varies over time or context into a single value.
That is the habit worth taking away from this question, more than any particular schema. Whenever you are about to put an attribute directly on an entity, ask whether it can differ across time, across context, or across the phases of a contract. If it can, it is not an attribute. It is a child entity with a lifespan of its own, and finding that out in an interview is far cheaper than finding it out in a reconciliation meeting.
Go and try the question properly if you skipped it, and pay attention to where your own first instinct puts the budget. The question is here. I would genuinely like to know whether you put it on the line item too.
~ Emily
Top comments (0)