Gowtham Potureddi

Posted on Jul 18

Data Engineering for Adtech: Auctions, Attribution, Identity Resolution

#python #sql #interview #dataengineering

adtech data engineering is the single most under-appreciated senior-DE specialisation in 2026 — a stack that combines sub-100ms real-time bidding, financial-grade log pipelines, cross-device identity graphs, and privacy-preserving measurement, all against a regulatory backdrop where third-party cookies are dead in Chrome and Apple's App Tracking Transparency has already halved the mobile deterministic signal. Most engineers who wander in from generic analytics DE roles underestimate every one of those constraints on the first sprint. The teams that get it right treat adtech as the intersection of low-latency systems engineering, warehouse-grade batch, and applied identity science — not as "just another clickstream."

This guide is the senior-DE walkthrough you wished existed the first time an interviewer asked "how do you build a rtb pipeline that stays under a 100ms bid budget at 1M QPS?" or "what does an attribution pipeline look like after third-party cookies are gone?" It walks through the RTB auction model (OpenRTB 2.6 bid request/response, header bidding vs SSP waterfall, the DSP bid factory pipeline, win notifications, IVT + viewability), attribution modelling (multi-touch attribution vs marketing-mix modelling vs incrementality, view-through vs click-through, server-side CAPI), identity resolution (deterministic vs probabilistic, UID2 / RampID / ID5, first-party ID graphs, clean rooms), and the financial-grade log ETL stack (dedup with idempotency tokens, cost-per-metric aggregation, SSP invoice reconciliation, Snowflake secure data sharing). Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the SQL practice library →, rehearse on joins problems →, and sharpen the analytics axis with the window-functions drills → and aggregation library →.

On this page

Why adtech DE differs in 2026
RTB auction pipelines
Attribution modelling
Identity resolution + graphs
Log ETL + billing pipelines
Cheat sheet — adtech DE recipes
Frequently asked questions
Practice on PipeCode

1. Why adtech DE differs in 2026

Adtech is a real-time systems + warehouse + identity + privacy problem — no other DE vertical stacks all four at once

The one-sentence invariant: adtech data engineering is the only DE vertical where a 100ms budget, a TB/day log firehose, a cross-device identity graph, and a regulatory privacy regime all live in the same pipeline. Most analytics DE roles pick one or two of those constraints; adtech forces all four. Once you internalise that, every programmatic pipeline interview question stops feeling like a trick and starts feeling like a deduction from the constraint stack.

The four constraint axes interviewers actually probe.

Latency. The RTB bid budget is sub-100ms end-to-end from the SSP's bid request to the winning DSP's bid response — including the network round-trip. That leaves your DSP typically 40–70ms to look up user features, score candidates, apply bid shading, and reply. Anything slower is a no-bid and your inventory dies.
Scale. A tier-1 DSP handles 100K–1M queries per second (QPS) at bid time. Your feature store, model server, and budget pacer all live on the hot path. Any component that can't hold p99 under 100M records/hour drops queries silently and burns budget.
Identity. With Chrome's third-party cookie deprecation complete (2024–2025) and Apple's App Tracking Transparency (ATT) permanently reducing mobile deterministic signal, identity resolution is now a first-class DE problem. First-party ID graphs (email/phone hashes), shared IDs (UID2, RampID, ID5), and cohort-based signals (Topics API, PAAPI FLEDGE) all coexist.
Privacy + compliance. IAB TCF v2.2, GDPR, CCPA/CPRA, DMA (Digital Markets Act), and platform-specific consent regimes (Google's Consent Mode v2, Meta's CAPI consent signal) all constrain what data you can persist, join, and share. Non-compliance is fined per event, not per breach.

The stack split — real-time hot path vs batch cold path.

Hot path (RTB). Kafka / Kinesis in, feature store on the hot line (Redis / DynamoDB / Aerospike), ML scoring service, bid shader, auction, bid response out. Everything must be sub-100ms p99. No warehouse in this loop.
Cold path (measurement + billing). Impression logs → Kafka / Kinesis / Firehose → S3 / GCS → Iceberg / Delta / Hudi → Spark / Trino / BigQuery for aggregation → Snowflake for measurement partner sharing. Latency is minutes to hours; correctness is exact.
Warm path (attribution + audience). Session stitching, MTA weight assignment, audience segment builds, identity graph merges. Latency is 5–60 minutes; runs continuously.

The 2026 reality — what changed since 2022.

Chrome 3rd-party cookies are gone (final deprecation completed 2025). Every impression served in Chrome now carries either a first-party ID (email hash, UID2), a Privacy Sandbox signal (Topics API interest, PAAPI interest group), or nothing.
Apple ATT halved iOS deterministic signal and forced everyone to SKAdNetwork (SKAN 4.0 in 2026) for aggregated conversion measurement. The measurement window is a postback at aggregated-privacy resolution, not per-user event.
IAB Tech Lab TCF v2.2 shipped stricter purpose-string enforcement and cross-border consent chaining. Your ingestion pipeline now stores the TCF consent string alongside every event and filters downstream joins on it.
First-party data resurgence. Retail media networks (Amazon Ads, Walmart Connect, Kroger Precision, Instacart) and streaming platforms (Netflix, Disney+, Hulu) now own the largest CPM inventory in the market. First-party purchase data drives audience quality; DE work is the moat.
Data clean rooms — AWS Clean Rooms, Google Ads Data Hub, Snowflake Data Clean Rooms, InfoSum, Habu (acquired by LiveRamp) — became the default for advertiser-publisher measurement joins under differential-privacy budgets.

What interviewers listen for.

Do you say "the bid budget is sub-100ms including the network round-trip" in the first sentence? — senior signal.
Do you describe "impression logs need idempotent dedup because the same impression can be fired by both browser pixel and server-side" unprompted? — required answer.
Do you push back on "just use last-click attribution" with "last-click undervalues top-of-funnel; you need incrementality"? — senior signal.
Do you mention "third-party cookies are gone in Chrome, so identity is a first-party + shared-ID + cohort problem" without prompting? — senior signal.

Worked example — the same event traversing hot, warm, cold paths

Detailed explanation. A single impression event fans out across all three latency paths in a modern adtech stack. The same impression_id shows up in the RTB win-notify at t=0 (hot path), a session-stitching job at t+5min (warm path), and a billing aggregation at t+6h (cold path). Understanding that fan-out is the entry-level test for adtech DE — most generic analytics engineers see only one of the three.

Question. Trace a single impression event through the hot, warm, and cold paths of a DSP. What component consumes it at each stage, what field does it write, and what is the SLA?

Input.

Field	Value
impression_id	imp_9f8e7d
user_hash	u_abc123
campaign_id	c_42
bid_price_cpm	1.50
creative_id	cr_88
slot	homepage-header
tstamp	2026-07-17 10:00:00Z

Code.

# HOT PATH — DSP bid response + win-notify handler (sub-100ms p99)
def on_bid_request(req):
    features = feature_store.get(req.user_hash)   # < 5ms
    scored = predict(req, features)               # < 20ms
    shaded = bid_shader.apply(scored, req.floor)  # < 5ms
    return build_bid_response(shaded)             # emit response

def on_win_notify(nurl):                          # NURL fired by SSP
    write_kafka("wins", nurl.as_impression_event())

# WARM PATH — session stitching job (Flink, ~5min windows)
def stitch_session(events: DataStream[Event]):
    return (events.key_by(lambda e: e.user_hash)
                  .window(EventTimeSessionWindows.with_gap(Time.minutes(30)))
                  .process(SessionAssembler()))

# COLD PATH — billing aggregation (Spark daily, exact-once at close-of-day)
spark.sql("""
    INSERT OVERWRITE billing_daily PARTITION (dt='2026-07-17')
    SELECT campaign_id,
           COUNT(DISTINCT impression_id) AS impressions,
           SUM(bid_price_cpm) / 1000 AS spend
    FROM impressions_raw
    WHERE dt = '2026-07-17'
    GROUP BY campaign_id
""")

Step-by-step explanation.

At t=0, the SSP sends a bid request; the DSP's hot-path handler runs on_bid_request in under 50ms and emits a bid response. If the DSP wins, the SSP fires the NURL back to the DSP's win handler, which writes an impression event to Kafka. Total hot-path budget from bid request to win-notify persist is ~200ms end-to-end.
At t+5min, a Flink session-stitching job consumes the Kafka wins topic, joins to the click and view streams by user_hash, and assembles per-user session objects. Late-arriving records within a 30-minute session gap are folded into the same session.
At t+6h, a Spark job reads yesterday's partition from the S3 / Iceberg impression table, deduplicates by impression_id, aggregates spend per campaign, and writes to the billing_daily table used for invoicing.
All three paths consume the same impression_id. The dedup key is agreed upstream: the SSP's imp_id field in OpenRTB, propagated through win-notify and session stitching.
If any path breaks the dedup contract, spend numbers diverge across dashboards, sessions attribute wrong, and the finance team files an incident. The impression_id is the atomic unit of adtech DE correctness.

Output.

Path	Latency	Consumer	Writes to	SLA
hot	< 200 ms	RTB win handler	Kafka `wins` topic	p99 < 200 ms
warm	~5 min	Flink stitching	Iceberg `sessions`	p99 < 10 min
cold	~6 h	Spark billing	Iceberg `billing_daily`	close-of-day exact

Rule of thumb. Every adtech impression is the same event in three latency tiers. Design your schema (especially the dedup key) so all three tiers agree; get one tier wrong and the other two diverge silently.

Worked example — 100K QPS bid budget breakdown

Detailed explanation. A common interview probe: "You have 60ms to reply to a bid request. Break down the budget by subsystem." The naive answer is "50ms of ML scoring." The senior answer walks through feature fetch, scoring, shading, and network egress, and shows that the ML step is usually not the bottleneck.

Question. Break down a 60ms end-to-end DSP bid budget into feature fetch, model scoring, bid shading, and network egress. Show which subsystem typically dominates.

Input.

Constraint	Value
End-to-end budget	60 ms
Feature fetch (Redis / DynamoDB)	?
Model scoring (Vertex / Sagemaker)	?
Bid shading	?
Serialisation + network egress	?

Code.

def bid_budget_breakdown_ms():
    return {
        "network_ingress": 5,       # request parse
        "feature_fetch":  10,       # Redis mget p99
        "model_score":    15,       # ONNX runtime CPU
        "bid_shading":     3,       # local lookup
        "budget_pacer":    5,       # Redis DECR
        "compliance_gate": 2,       # TCF v2.2 consent
        "response_build":  5,       # OpenRTB serialise
        "network_egress": 10,       # TLS + hop
        # ---------------------------------
        "total":          55,       # 5ms slack against 60ms SLA
    }

Step-by-step explanation.

network_ingress (5ms) — parse the OpenRTB BidRequest JSON / protobuf. Batching multiple requests per TCP connection helps but adds jitter.
feature_fetch (10ms) — a single MGET against a co-located Redis / Aerospike / DynamoDB with ~20 features per key. p99 dominates because the tail of your KV store is the tail of your budget.
model_score (15ms) — a compiled ONNX / TensorFlow-Lite model on CPU. Deep models with 100+ features can fit under 20ms with quantisation + AVX-512. GPU is rarely justified — bid QPS >> single-request throughput.
bid_shading (3ms) — a local lookup in a shared-memory table of (campaign, slot, hour_of_day) → shading_factor. Fits in the same JVM / process; no network hop.
budget_pacer (5ms) — a Redis DECR against the campaign's remaining budget; if it goes negative, no-bid and refund the decrement. Some pacers precompute a probability of bid to avoid the round-trip per request.
compliance_gate (2ms) — parse the TCF v2.2 consent string, check purposes 1+3+4 for personalised ads, drop the bid if consent is missing. This step is required, not optional.
response_build + network_egress (15ms) — serialise the response and ship it back. TLS handshake is amortised across connections; egress bandwidth is per-response, ~2 KB.

Output.

Subsystem	ms	Share
network_ingress	5	9%
feature_fetch	10	18%
model_score	15	27%
bid_shading	3	5%
budget_pacer	5	9%
compliance_gate	2	4%
response_build	5	9%
network_egress	10	18%
total	55	92% (5ms slack)

Rule of thumb. The tail latency of your feature store is usually the largest surprise. Provision Redis / Aerospike for p99.9, not p50, and always benchmark the MGET at production key-cardinality — a 10x larger key space can double p99 without touching CPU.

Worked example — the impression dedup contract

Detailed explanation. A single impression can fire multiple pixel beacons — a client-side JavaScript beacon, a server-side impression tracker, and the SSP's own log-level dashboard callback. Without a dedup contract, the same impression bills twice. The idempotency key must be agreed at the schema level and enforced at ingest, at stitching, and at billing.

Question. Design an impression dedup contract that survives triple-firing (JS pixel + server pixel + SSP log). Show the key, the enforcement point, and the SQL that catches it.

Input.

Source	Fires	Latency
JS pixel (browser)	on-view	1-2 s
Server pixel	on-render	200 ms
SSP log callback	close-of-hour	60 min

Code.

-- Idempotent dedup at ingest — Iceberg / Delta / BigQuery
CREATE OR REPLACE TABLE impressions_dedup AS
SELECT
    impression_id,
    ANY_VALUE(user_hash)      AS user_hash,
    ANY_VALUE(campaign_id)    AS campaign_id,
    ANY_VALUE(creative_id)    AS creative_id,
    ANY_VALUE(bid_price_cpm)  AS bid_price_cpm,
    MIN(tstamp)               AS first_seen_tstamp,
    COUNT(*)                  AS fire_count,
    ARRAY_AGG(DISTINCT source) AS sources
FROM impressions_raw
WHERE dt = '2026-07-17'
GROUP BY impression_id;

Step-by-step explanation.

Every producer of an impression event must stamp the same impression_id — allocated by the SSP in the bid response and echoed by every downstream pixel. Fail this and no dedup can save you.
At ingest, records land in impressions_raw with a source column (js_pixel | server_pixel | ssp_log) and tstamp. Duplicates are common: 2-3 rows per real impression is normal.
The dedup query groups by impression_id, keeps the earliest timestamp as canonical, and preserves the list of sources in an array — useful for the billing team to reconcile against SSP invoices.
ANY_VALUE is used on invariant fields because the values agree across sources by contract. Downstream systems query impressions_dedup, not impressions_raw.
If fire_count > 1 on a row, that impression fired multiple times — expected. If fire_count = 1 but the SSP claims it fired, you're missing the client pixel — investigate that source.

Output.

impression_id	fire_count	sources	first_seen_tstamp
imp_9f8e7d	3	["js_pixel", "server_pixel", "ssp_log"]	10:00:01
imp_a1b2c3	2	["server_pixel", "ssp_log"]	10:00:02
imp_d4e5f6	1	["ssp_log"]	10:00:03

Rule of thumb. Store the array of sources on the deduplicated row. It costs almost nothing and lets you diagnose "why is our client-side impression count 5% lower than the SSP's log?" in one query.

Senior interview question on adtech DE constraints

A senior interviewer often opens with: "Walk me through what makes adtech data engineering different from generic analytics DE. What are the constraints that force different architecture choices, and what would you optimise first on a new team?"

Solution Using the 4-axis constraint framework

Adtech DE constraint stack — 4 axes
====================================

1. LATENCY
   - Bid budget:  < 100 ms end-to-end
   - Attribution: 5 min – 6 h
   - Billing:     close-of-day exact

2. SCALE
   - Bid QPS:      100K – 1M
   - Impressions:  10 B / day tier-1
   - Log volume:   1 – 10 TB / day

3. IDENTITY
   - 1st-party ID: email hash, phone hash
   - Shared ID:    UID2, RampID, ID5
   - Cohort:       Topics API, PAAPI
   - Aggregated:   SKAN 4.0

4. PRIVACY
   - Consent:      TCF v2.2 string on every event
   - Region:       GDPR, CCPA/CPRA, DMA
   - Sharing:      clean rooms + differential privacy

Step-by-step trace.

Axis	Test question	Weak answer	Senior answer
Latency	"budget for bid response?"	"under a second"	"60ms p99 including network egress"
Scale	"how do you shard the feature store?"	"hash on user_id"	"hash on user_id, co-locate with bid handler, size for p99.9 tail"
Identity	"cookies deprecated — what now?"	"we'll use fingerprinting"	"first-party graph + UID2/RampID + Topics API + SKAN for iOS"
Privacy	"how do you enforce consent?"	"we drop non-consented users"	"TCF v2.2 string on every event, filter at ingest and at every downstream join"

The four axes are not optional — every adtech pipeline hits all four. Skipping one is the fastest way to fail a design interview or ship a pipeline that stops working in production the first time a compliance auditor asks for the consent chain.

Output:

First-90-days priority	Reason
1. Impression dedup contract	Correctness on billing dominates every other metric
2. Sub-100ms bid path SLO	Missed SLA → lost inventory → lost revenue
3. TCF v2.2 consent chain	Regulatory failure is the only non-recoverable outcome
4. Identity resolution schema	Downstream MTA, MMM, audience all depend on this
5. SSP invoice reconciliation	Finance signs the cheques, they will notice a 1% delta

Why this works — concept by concept:

Four axes, not one — the framing — every adtech decision trades between latency, scale, identity, and privacy. Interviewers listen for the framing itself; the numbers matter less than the four-axis structure.
Bid budget is including the network — juniors compute compute-only latency and miss that TLS + hop dominate 20-30ms of the budget. Seniors always include ingress and egress.
Identity is a portfolio, not a single ID — 1st-party + shared + cohort + aggregated all coexist. Any team betting on one signal loses when the platform shifts.
Consent is per-event, not per-user — the TCF v2.2 string travels with the event, so downstream joins can filter without needing a fresh user lookup. This is the pattern that survives regulatory audit.
Cost — the constraint stack is O(events) at ingest and O(events × dimensions) at aggregation. The dominant cost is the hot-path feature store; provision it for p99.9, not p50.

SQL
Topic — SQL
SQL problems for adtech DE

Practice →

SQL Topic — joins Join problems for impression pipelines

Practice →

2. RTB auction pipelines

`rtb pipeline` is a sub-100ms fan-in / fan-out — OpenRTB 2.6 requests fan to N DSPs, one winner returns, one NURL fires

The mental model in one line: an SSP fans a bid request to N DSPs in parallel, waits at most 80-100ms, picks the highest-price valid bid, notifies the winner via NURL, and the winning DSP writes the impression event onto its own log stream. Once you say "fan-out to N DSPs, fan-in the winner, NURL fires on serve," the entire programmatic pipeline interview surface becomes a sequence of consequences from that OpenRTB request/response contract.

The OpenRTB 2.6 contract — what actually crosses the wire.

BidRequest — the SSP-emitted JSON / protobuf payload with imp[] (impression slots), user (identifiers + consent), device, site / app, regs (GDPR + CCPA flags), source (supply chain), and tmax (the timeout budget the DSP must respect).
BidResponse — the DSP's reply with seatbid[].bid[] — a list of bids per seat; each bid carries price, adm (creative markup or reference), nurl (win-notify URL), lurl (loss-notify URL, optional), adomain (advertiser domain — required for brand safety), crid (creative id), and iurl (image URL for review).
nurl — fired by the SSP when the bid wins and the ad is served. Carries auction-clearing metadata (${AUCTION_PRICE}, ${AUCTION_ID}) via macro substitution.
lurl — optional loss notification; carries ${AUCTION_LOSS_CODE} (1 = internal error, 2 = impression cancelled, 100 = below floor, 102 = lost to another bid, 103 = duplicate bid, etc.).

Header bidding vs SSP waterfall.

Waterfall (legacy) — publisher's ad server calls SSPs in a hardcoded priority list; the first one to fill takes it. Undervalues inventory because lower-tier SSPs never see high-value bids.
Header bidding — the publisher runs a client-side or server-side auction (Prebid.js is the reference implementation) across all SSPs before calling the ad server. Every SSP sees the same request, so the true clearing price is realised.
Server-side header bidding (Prebid Server) — moves the auction off the browser to a Docker container to cut client latency; DSPs prefer this because their per-request cost is amortised.

The DSP bid factory — the four stages.

Candidate selection. A pre-filter that reduces the eligible campaign list from ~10K to ~50. Filters by geo, device, category, frequency cap, budget-remaining. Runs in-memory in ~2ms.
Predictor. An ML model (usually gradient boosting or a small deep net) that produces a pCTR × pCVR × value score per candidate. Runs on ONNX runtime in ~15ms.
Shader. A bid-shading model that shrinks the raw bid to the estimated marginal winning price — you don't want to overpay in a first-price auction. Uses historical (campaign, slot, hour) → shading_factor lookups + a real-time floor observation.
Auction. The internal ranking that picks the top-N bids to actually return to the SSP (some SSPs accept multiple bids for post-hoc selection). Applies advertiser competition rules and hard exclusions (blocklisted domains).

Win notifications (NURL) — where impression logs are born.

The SSP fires NURL only after the creative is served in the user's browser. At that moment the impression exists.
Some DSPs write to Kafka the moment they emit the bid; others wait for NURL. Waiting for NURL is correct but adds ~200ms latency to the log write.
NURL can fail (browser closes, ad blocker, network drop). Reconcile with the SSP's log-level dashboards close-of-day; expect 1-5% NURL loss.

Fraud filtering (Invalid Traffic — IVT).

General IVT — bots, crawlers, data-centre IPs. Filtered pre-bid in-memory (~1ms per request).
Sophisticated IVT (SIVT) — humans-for-hire click farms, sophisticated bots. Post-bid detection by third-party (DoubleVerify, IAS, HUMAN Security).
MRC (Media Rating Council) certification is required for SSP measurement; most DSPs partner with a certified vendor rather than build in-house.

Viewability (MRC standard).

Display — ≥ 50% of pixels in view for ≥ 1 continuous second.
Video — ≥ 50% of pixels in view for ≥ 2 continuous seconds.
Measurement is via the OMID SDK (Open Measurement Interface Definition) — a standardised JavaScript viewability library.
Viewability is not the same as impression — an impression can be served but never viewed. Both metrics are logged; billing is usually on impression, quality is on viewability.

Common interview probes on RTB pipelines.

"What is the p99 latency budget for a bid response?" — 60-80 ms, including network egress.
"When does the impression exist?" — after NURL fires, not at bid time.
"How does header bidding change the DSP's world?" — every SSP sees every request; the DSP sees N × requests-per-slot, roughly doubling QPS.
"What is bid shading?" — model the marginal winning price in a first-price auction, bid the shade of the raw value, not the full value.

Worked example — a minimal OpenRTB bid response

Detailed explanation. The bid response is the atomic unit of RTB DE. Missing one required field and the SSP silently drops the bid. Emitting the wrong nurl macro and your win-notify never fires. Interviewers love this because the schema is small enough to walk through end-to-end and every field has a specific correctness implication.

Question. Write a minimal OpenRTB 2.6 BidResponse for a single winning bid on a display slot. Highlight the fields that the SSP treats as required and the ones that trigger silent drops if missing.

Input.

Slot	Value
impression_id	imp_9f8e7d
shaded_price_cpm	1.42
creative_id	cr_88
advertiser_domain	brand.com
campaign_id	c_42

Code.

{
  "id": "req_5g6h7j",
  "seatbid": [{
    "seat": "dsp-42",
    "bid": [{
      "id": "bid_9f8e7d_1",
      "impid": "imp_9f8e7d",
      "price": 1.42,
      "adid": "cr_88",
      "adm": "<script src='https://cdn.dsp/creative/cr_88.js'></script>",
      "adomain": ["brand.com"],
      "crid": "cr_88",
      "iurl": "https://cdn.dsp/review/cr_88.jpg",
      "cid": "c_42",
      "nurl": "https://dsp/nurl?bid=bid_9f8e7d_1&price=${AUCTION_PRICE}",
      "lurl": "https://dsp/lurl?bid=bid_9f8e7d_1&code=${AUCTION_LOSS_CODE}",
      "burl": "https://dsp/burl?bid=bid_9f8e7d_1&price=${AUCTION_PRICE}",
      "w": 300,
      "h": 250,
      "cat": ["IAB1-1"],
      "attr": [1, 2]
    }]
  }],
  "cur": "USD"
}

Step-by-step explanation.

id echoes the BidRequest.id — SSP correlates the response to the request. Mismatch = silent drop.
impid echoes the specific imp[].id from the request. A response with the wrong impid is treated as a bid on a non-existent slot.
price is in the currency declared by cur; usually USD CPM. First-price auctions use this as the clearing price directly; second-price use it as the ceiling.
adomain is required — SSPs enforce brand safety by checking the domain against a publisher blocklist. Missing adomain = auto-drop.
nurl fires when the ad renders — the DSP's impression logger must be ready to receive this. Macros ${AUCTION_PRICE}, ${AUCTION_ID}, ${AUCTION_IMP_ID} are substituted by the SSP before the fire.
lurl (loss URL) is optional but valuable — you learn why you lost. burl (billing URL) fires only on billable impressions (post-viewability); some SSPs bill on burl instead of nurl.
w, h describe the creative dimensions; must match the requested slot dimensions or the SSP drops the bid.

Output.

Field	Required	Silent drop if missing
id, impid, price, adm, adomain, crid, w, h	yes	yes
nurl	recommended	no impression log
lurl, burl	optional	no
cat, attr	recommended	brand-safety drop possible

Rule of thumb. Always echo id and impid from the request. Always include adomain and correct w × h. Bake the required-field validation into your response emitter and fail loud in staging; silent drops are the #1 mystery in RTB debugging.

Worked example — bid shading with historical clearing prices

Detailed explanation. In a first-price auction, bidding your full value overpays on every win. Bid shading estimates the marginal winning price — the smallest bid that still wins — from a rolling window of historical clearing prices for the same slot at the same hour of day. The shading factor is applied to the raw value.

Question. Write the SQL that computes an hourly bid-shading factor per (campaign, slot) from the last 7 days of clearing prices, then apply it in the DSP handler.

Input.

Field	Value
campaign_id	c_42
slot	homepage-header
hour_of_day	10
raw_value_cpm	2.00
historical clearing prices	[1.20, 1.30, 1.40, 1.35, 1.42, 1.38]

Code.

-- Rolling shading factor per (campaign, slot, hour_of_day)
CREATE OR REPLACE TABLE bid_shading AS
SELECT
    campaign_id,
    slot,
    hour_of_day,
    -- shade to the 60th percentile of recent clearing prices,
    -- clamped so we never shade below 0.50 of the raw value
    GREATEST(
        0.50,
        APPROX_PERCENTILE(clearing_price_cpm, 0.60)
            / AVG(raw_value_cpm)
    ) AS shading_factor
FROM impression_wins
WHERE win_tstamp > NOW() - INTERVAL '7 days'
GROUP BY campaign_id, slot, hour_of_day;

# Applied in the DSP handler on the hot path
def apply_shading(raw_value_cpm, campaign_id, slot, hour_of_day):
    factor = shading_table.get((campaign_id, slot, hour_of_day), 0.85)
    return raw_value_cpm * factor

Step-by-step explanation.

The impression_wins table logs every won impression with the raw value the model predicted and the clearing price the SSP charged.
Grouping by (campaign_id, slot, hour_of_day) lets shading track diurnal patterns — homepage inventory at 10am has different competition than 3am.
APPROX_PERCENTILE(..., 0.60) picks the 60th-percentile clearing price — bidding at that level wins ~60% of comparable auctions. Tune this percentile per campaign KPI (win rate vs cost per action).
Dividing by AVG(raw_value_cpm) normalises to a shading factor in [0, 1] that the hot path can multiply the model output by.
The GREATEST(0.50, ...) clamp prevents pathological shading (e.g., a model bug that undervalues a slot 10x still results in a defensible bid).
On the hot path, the shading_table is a local hash-map hydrated from the SQL output every 15 minutes; the lookup is sub-microsecond and adds no latency.

Output.

campaign_id	slot	hour	avg_raw	p60_clear	shading_factor
c_42	homepage-header	10	2.00	1.38	0.69
c_42	homepage-header	22	2.00	0.95	0.50 (clamped)
c_42	article-sidebar	10	1.40	0.98	0.70

Rule of thumb. Rehydrate the shading table every 15 minutes from the last 24-48 hours of wins. Longer windows lag price shifts; shorter windows are too noisy. Log the raw value and the shaded value on every bid — you'll need both to debug win-rate regressions.

Worked example — NURL win-notify reconciliation

Detailed explanation. NURL is fire-and-forget. Browsers close mid-load, ad blockers strip URLs, mobile networks drop the tail of the pageload. Reconciling your NURL fires against the SSP's log-level dashboard is a nightly job that catches missing impressions before they hit billing.

Question. Write the SQL that reconciles DSP-observed NURL fires against SSP-reported impressions for a given day, flagging any campaign where the delta exceeds 2%.

Input.

Source	Table	Grain
DSP	nurl_fires	(nurl_id, campaign_id, tstamp)
SSP	ssp_log	(impression_id, campaign_id, tstamp)

Code.

WITH dsp_counts AS (
    SELECT campaign_id, COUNT(*) AS dsp_impressions
    FROM nurl_fires
    WHERE dt = '2026-07-17'
    GROUP BY campaign_id
),
ssp_counts AS (
    SELECT campaign_id, COUNT(*) AS ssp_impressions
    FROM ssp_log
    WHERE dt = '2026-07-17'
    GROUP BY campaign_id
)
SELECT
    d.campaign_id,
    d.dsp_impressions,
    s.ssp_impressions,
    (s.ssp_impressions - d.dsp_impressions) AS delta,
    ROUND(
        100.0 * (s.ssp_impressions - d.dsp_impressions)
              / NULLIF(s.ssp_impressions, 0),
        2
    ) AS delta_pct
FROM dsp_counts d
FULL OUTER JOIN ssp_counts s USING (campaign_id)
WHERE ABS(
    100.0 * (s.ssp_impressions - d.dsp_impressions)
          / NULLIF(s.ssp_impressions, 0)
) > 2.0
ORDER BY delta_pct DESC;

Step-by-step explanation.

dsp_counts totals the NURL fires the DSP received. ssp_counts totals the SSP-reported impressions from the log-level feed.
FULL OUTER JOIN captures campaigns present in either side — campaigns with SSP impressions but no NURLs (client-side blocking) and campaigns with NURLs but no SSP log (rare, usually a stale nurl fire).
The delta_pct normalises the difference against the SSP count (SSP is authoritative for billing).
The > 2.0 filter surfaces only meaningful mismatches; sub-1% is expected NURL loss.
For each flagged campaign, the ops team investigates: was the creative served on a browser that stripped the nurl? Is the ad blocker rate up? Is there a network egress issue on the DSP's nurl endpoint?

Output.

campaign_id	dsp_impressions	ssp_impressions	delta	delta_pct
c_42	1,001,200	1,038,900	37,700	3.63
c_88	500,300	512,100	11,800	2.30
c_15	250,000	249,800	-200	-0.08 (excluded)

Rule of thumb. Bill against the SSP count, not the DSP count. Use the delta to debug DSP infrastructure but never as the source of truth for revenue.

Senior interview question on RTB pipeline design

A senior interviewer might ask: "Design the data pipeline for a DSP that needs to bid on 500K QPS with a 60ms p99 budget. Where does state live, how do features flow, and how do you keep the impression log correct?"

Solution Using a co-located feature store + async impression logger

DSP data pipeline — 500K QPS · 60ms p99
=======================================

Hot path (bid handler, one process per host)
--------------------------------------------
1. TCP/HTTP listener (Netty / gRPC) receives BidRequest
2. Compliance gate: TCF v2.2 parse + consent check   (2ms)
3. Candidate filter: in-memory campaign index         (2ms)
4. Feature fetch: Redis MGET colocated on host        (10ms)
5. Model score: ONNX runtime CPU quantised            (15ms)
6. Shader lookup: local shared-memory hash            (3ms)
7. Budget pacer: Redis DECR (or precomputed p_bid)    (5ms)
8. Response build + emit                              (5ms)

Async impression logger (separate goroutine / thread)
-----------------------------------------------------
9. Await NURL fire (up to 300ms after bid response)
10. Persist to Kafka topic 'impressions_raw' with source='nurl'
11. Also persist bid+shading metadata for reconciliation

Warm path (Flink job, 5-min windows)
-------------------------------------
12. Session stitching by user_hash
13. Attribution join to clicks/conversions
14. Write to Iceberg 'sessions_5min'

Cold path (Spark daily)
-----------------------
15. Dedup impressions_raw by impression_id
16. Aggregate to billing_daily
17. Reconcile against SSP invoice

Step-by-step trace.

Stage	Where	Latency	State
1-8	bid handler (co-located)	60 ms p99	in-process cache + Redis
9-11	async logger (same host)	~200 ms	Kafka topic
12-14	Flink cluster	~5 min	Iceberg
15-17	Spark cluster daily	6 h	Iceberg

The critical decision is co-locating the feature store with the bid handler — a Redis / Aerospike shard runs on the same host (or in the same rack) as the bid handler, so MGET is <5ms p99 instead of the 20-50ms you'd see over a WAN.

Output:

Component	Sizing	Cost driver
Bid handlers	~500 hosts (each 1K QPS)	CPU + memory
Feature store	~200 Redis shards (100M keys each)	memory
Kafka (impressions)	12 partitions × 3 replicas	throughput
Flink cluster	~50 TaskManagers	state size
Spark cluster	~200 executors nightly	scan cost

Why this works — concept by concept:

Co-located feature store — the tail-latency killer — the tail p99.9 of a WAN Redis is ~50ms; a co-located Redis is 5-10ms. That single decision saves ~40ms of budget and enables the sub-100ms SLA.
Async impression logger — decouple bid latency from write latency — the bid handler must never block on Kafka. A separate thread awaits NURL and writes async; the bid response has already returned to the SSP.
Kafka is the seam between hot and cold — everything downstream of the bid handler is Kafka-consuming. This lets you replay the log to rebuild any aggregate without touching the hot path.
Compliance gate at position 2, not position 8 — check consent before touching the feature store. Non-consented traffic still counts against the QPS budget but must not read personalisation data.
Cost — the dominant cost is the feature store memory footprint (100M keys × 20 features × ~2KB ≈ 4 GB per shard × 200 shards = ~800 GB). The bid handlers are stateless and CPU-bound, easy to autoscale.

SQL
Topic — joins
Join problems for RTB pipelines

Practice →

SQL Topic — aggregation Aggregation drills for auction data

Practice →

3. Attribution modelling

`attribution pipeline` is a graph of touchpoints with a weight function — the model is a business choice, not a technical one

The mental model in one line: every conversion is a graph of prior touchpoints; attribution assigns weights across those touchpoints under a chosen model (last-click, first-click, linear, position-based, data-driven MTA, or MMM), and the pipeline persists the per-touchpoint credit for reporting and optimisation. Once you say "attribution is a weight function over a touchpoint graph," the entire attribution pipeline interview surface stops being about SQL cleverness and starts being about model choice.

The five common attribution models.

Last-click. 100% credit to the last click before conversion. Simple, deterministic, systematically undervalues top-of-funnel channels (display, social awareness).
First-click. 100% credit to the first touch. Rewards discovery channels; blind to closing-touch friction.
Linear. Equal weight across all touchpoints. Fair on average, terrible for optimisation because every touch looks equally valuable regardless of position or type.
Position-based (U-shaped). 40% to first touch, 40% to last touch, 20% split across middle. A defensible compromise between first-click and last-click without heavy modelling.
Data-driven MTA. A Shapley-value or Markov-chain model learns weights from historical data. Requires cross-device stitching and a lot of conversions per campaign to converge.
Marketing Mix Modelling (MMM). Regression at the campaign-week grain (not the user grain) — combines paid, owned, earned, and macro signals. The go-to model in the post-cookie world for cross-channel attribution.

The three canonical touchpoint types.

Click. User clicked the ad and landed on the site. Highest attribution weight in every model except MMM.
View-through. User saw the ad but didn't click; later converted from a different session. Attributable only within a viewthrough window (typically 1-30 days depending on model).
Assisted. Any touchpoint between first-click and last-click. MTA models place non-zero weight here; last-click ignores.

Cross-device stitching — where deterministic wins.

The touchpoint graph spans devices: a user sees an impression on mobile, clicks an ad on desktop, converts on tablet. Without stitching, each device looks like a different user and MTA is broken.
Deterministic stitching: hashed email (SHA-256 of lowercased email) is present on ~30-50% of touches (login walls, newsletter tracking). Stitch by exact hash match.
Probabilistic stitching: fill the rest with a model over IP + user-agent + geolocation + behavioural fingerprint. Precision drops but coverage rises.

Incrementality — the ground truth attribution can never fake.

Attribution says "who got credit"; incrementality says "would the conversion have happened anyway?"
Holdout tests. A % of the audience is randomly excluded from serving; conversion delta between exposed and holdout = incrementality lift.
Ghost bids. In RTB, the DSP bids but does not serve for the control group; the log-level record proves the user would have seen the ad.
Meta Conversion Lift, Google Brand Lift — platform-managed incrementality studies. Their number becomes the truth for the campaign week.

Conversion API (CAPI) — server-side event forwarding.

Browser-side event pixels (Meta Pixel, Google gtag) are blocked by iOS Safari ITP, ad blockers, and cookie deprecation. Coverage in 2026 is ~40-60% on iOS.
CAPI (Meta CAPI, Google Ads Enhanced Conversions, TikTok Events API) accepts server-side events with hashed PII. Coverage jumps to ~90%+ because the browser is bypassed.
Best practice: dual-tag — fire both pixel and CAPI, dedup on event_id. Preserves optimisation signal even when the browser signal is missing.

Common interview probes on attribution.

"What is the difference between last-click and multi-touch attribution?" — last-click assigns 100% to the last click; MTA distributes weights across all touches.
"Why does last-click undervalue display?" — because display is usually a discovery touch; users click a search ad after seeing the display, and search gets 100% under last-click.
"How do you validate an attribution model?" — run an incrementality test in parallel; a model that agrees with holdout lift is calibrated.
"What is a viewthrough window?" — the maximum days between a view and a conversion for that view to receive credit. Typical 1 day for direct-response, 30 days for brand.

Worked example — assigning last-click credit via a window function

Detailed explanation. Last-click is the workhorse of attribution SQL — appears in every DE interview because it exercises window functions cleanly. The pattern: partition by user, order by timestamp, keep the last touch before each conversion. This is the entry point to the full MTA family of queries.

Question. Given a stream of touchpoints and conversions per user, assign 100% credit to the last touchpoint before each conversion. Use SQL window functions.

Input — events (unified stream).

user_id	tstamp	event_type	channel
u1	10:00	touch	display
u1	10:05	touch	social
u1	10:10	touch	search
u1	10:15	conversion	(n/a)
u2	09:30	touch	display
u2	09:45	conversion	(n/a)

Code.

WITH ranked AS (
    SELECT
        user_id,
        tstamp,
        event_type,
        channel,
        ROW_NUMBER() OVER (
            PARTITION BY user_id
            ORDER BY tstamp DESC
        ) AS rev_rank,
        MIN(CASE WHEN event_type = 'conversion' THEN tstamp END)
            OVER (PARTITION BY user_id) AS conversion_tstamp
    FROM events
),
last_touch AS (
    SELECT user_id, channel AS attributed_channel, tstamp
    FROM ranked
    WHERE event_type = 'touch'
      AND tstamp < conversion_tstamp
    QUALIFY ROW_NUMBER() OVER (
        PARTITION BY user_id
        ORDER BY tstamp DESC
    ) = 1
)
SELECT * FROM last_touch;

Step-by-step explanation.

ranked computes the conversion timestamp per user with a windowed MIN(...) FILTER (event_type='conversion') — every touch row now knows the conversion time for its user.
last_touch keeps only touches that occurred before the conversion (tstamp < conversion_tstamp).
QUALIFY ROW_NUMBER() (BigQuery / Snowflake syntax; equivalent in Spark via subquery) picks the single most-recent touch per user.
The output is one row per user with the attributed channel — the "last-click winner."
On DBs without QUALIFY, wrap in an outer WHERE rn = 1 after the row-number CTE.

Output.

user_id	attributed_channel	tstamp
u1	search	10:10
u2	display	09:45

Rule of thumb. Use QUALIFY on Snowflake / BigQuery for readability; fall back to WHERE rn = 1 on Postgres / Spark. Always exclude touches after the conversion timestamp — a touch that happened post-conversion cannot have caused it.

Worked example — multi-touch attribution with linear weights

Detailed explanation. The natural extension: split credit equally across all touches. This tests whether the candidate understands COUNT() OVER (...) and per-row division — the same pattern extends to Shapley or position-based weights once you generalise.

Question. Assign linear (equal-weight) MTA credit across all touches within 30 days before each conversion.

Input — same as above plus.

user_id	tstamp	event_type	channel
u1	10:15	conversion	(n/a)
u1	10:00	touch	display
u1	10:05	touch	social
u1	10:10	touch	search

Code.

WITH conversions AS (
    SELECT user_id, tstamp AS conv_tstamp
    FROM events
    WHERE event_type = 'conversion'
),
touches_before_conv AS (
    SELECT
        c.user_id,
        c.conv_tstamp,
        t.channel,
        t.tstamp
    FROM conversions c
    JOIN events t
      ON t.user_id = c.user_id
     AND t.event_type = 'touch'
     AND t.tstamp <= c.conv_tstamp
     AND t.tstamp >= c.conv_tstamp - INTERVAL '30 days'
),
weighted AS (
    SELECT
        user_id,
        conv_tstamp,
        channel,
        1.0 / COUNT(*) OVER (PARTITION BY user_id, conv_tstamp) AS credit
    FROM touches_before_conv
)
SELECT channel, ROUND(SUM(credit), 2) AS attributed_conversions
FROM weighted
GROUP BY channel
ORDER BY attributed_conversions DESC;

Step-by-step explanation.

conversions isolates each conversion event with its timestamp.
touches_before_conv joins each conversion to every touch by the same user within the 30-day viewthrough window. This is the "graph of touchpoints per conversion."
weighted divides 1.0 by the count of touches per conversion, giving each touch an equal fractional credit.
The outer aggregate sums the fractional credits per channel — one conversion contributes 1.0 total credit distributed across its channels.
For non-linear weights (position-based, Shapley), replace the 1.0 / COUNT(*) step with a weight table lookup or a CASE on ROW_NUMBER() position.

Output.

channel	attributed_conversions
display	0.33
social	0.33
search	0.34

Rule of thumb. Always use a 30-day lookback window unless the campaign KPI says otherwise. Users converting more than 30 days after their last touch are effectively organic — attributing them to a stale channel distorts optimisation.

Worked example — CAPI dual-tag dedup on event_id

Detailed explanation. Dual-tagging fires both the browser pixel and the server-side CAPI. Meta and Google dedup them on event_id, but you must generate a stable event_id at the moment of the conversion. Miss the dedup and you double-count conversions; dedup incorrectly and you lose signal.

Question. Design the dual-tag flow for a purchase event. Show the stable event_id, the browser fire, and the server CAPI fire.

Input.

Field	Value
purchase_id	ord_1a2b3c
user_hash	sha256(email_lowercase)
value_usd	45.00
tstamp	2026-07-17 10:00:00Z

Code.

// Browser (client-side)
const eventId = purchaseId;   // stable ID, echoed to CAPI
fbq('track', 'Purchase', {
  value: 45.00,
  currency: 'USD',
  content_ids: ['sku-42']
}, {eventID: eventId});

# Server (CAPI, same eventId)
requests.post(
    "https://graph.facebook.com/v18.0/{pixel_id}/events",
    params={"access_token": TOKEN},
    json={
        "data": [{
            "event_name": "Purchase",
            "event_time": int(tstamp.timestamp()),
            "event_id": purchase_id,      # SAME as browser eventID
            "action_source": "website",
            "user_data": {
                "em": [sha256(email.lower())],
                "ph": [sha256(phone_e164)],
                "fbc": fbclid_cookie,
                "fbp": browser_pixel_id,
            },
            "custom_data": {
                "value": 45.00,
                "currency": "USD",
                "content_ids": ["sku-42"],
            },
        }]
    }
)

Step-by-step explanation.

The event_id (Meta) or transaction_id (Google) is the dedup key. It must be stable across browser and server and globally unique per event. purchase_id is a natural fit.
The browser pixel fires first (fast, often blocked). The server CAPI fires from the backend order-confirmation handler (slow, ~99% delivered).
Meta receives both events. It matches on event_id and keeps whichever arrived first, merging user signals from both (browser has fbp, server has email + phone).
Hashing PII is required — Meta rejects unhashed email/phone. Always lowercase + trim before hashing.
Include fbc (Facebook click id from cookie or URL) and fbp (browser pixel id) to strengthen the user match on the server side — CAPI without these has lower attribution precision.

Output.

Fire	Latency	Block rate (2026)	Match signal
Browser pixel	200 ms	~50% (iOS Safari + ad blockers)	fbp, fbc
Server CAPI	500 ms	~1%	email hash, phone hash, IP, UA
Deduped in Meta	—	—	keeps first arrived by event_id

Rule of thumb. Always dual-tag conversion events. Meta / Google reward higher-match-quality events with better optimisation; a CAPI-only pipeline is more resilient than a pixel-only pipeline but a dual pipeline is best.

Senior interview question on attribution pipeline design

A senior interviewer might ask: "You inherit an attribution pipeline that uses last-click, and marketing says display is 'not working'. Walk through how you'd move the team to a data-driven MTA + incrementality workflow. What are the technical steps, and what are the political ones?"

Solution Using an MTA + incrementality dual-track

Attribution modernisation — dual track
======================================

Track A — Technical (weeks 1-8)
-------------------------------
1. Unify touch and conversion streams into a single 'events' table
   (schema: user_id, tstamp, event_type, channel, campaign, cost).
2. Deploy cross-device stitching:
   - deterministic: hashed email + phone from login events
   - probabilistic: IP + UA + geo fallback (LiveRamp / in-house)
3. Backfill 90 days of stitched user_paths.
4. Ship 3 attribution SQL views side-by-side:
   - last_click_credit
   - linear_credit
   - shapley_credit (from a Python job with a lightweight Markov model)
5. Add CAPI dual-tag on top 10 conversion events.

Track B — Incrementality (weeks 4-12)
-------------------------------------
6. Design holdout tests on the top 3 spend campaigns:
   - 90/10 holdout split, ghost bids for control
   - 4-week test duration
7. Measure lift = (exposed_conv_rate - holdout_conv_rate) / holdout_conv_rate.
8. Publish 'lift per channel' alongside 'attribution per channel'.
9. Realign optimisation targets on lift, not on last-click.

Political changes (throughout)
------------------------------
10. Weekly attribution review: model choice + lift delta.
11. Sunset last-click for optimisation; keep as compliance/reporting only.
12. Move budget to channels where lift > 0, regardless of last-click ranking.

Step-by-step trace.

Week	Milestone	Outcome
1-2	Unified events table	one source of truth
3-4	Stitching deployed	user_paths coverage 70%+
5-6	3 attribution views live	side-by-side reports
7-8	CAPI dual-tag	signal loss down 30%+
4-8	Holdout tests running	lift per channel measured
9-12	Optimisation on lift	budget shift within campaigns

The technical track is the easy half. The political track — convincing marketing to move budget away from last-click winners — is where the framework earns its keep. The lift number gives you an unarguable ground truth.

Output:

Verdict	Reasoning
Move to MTA + incrementality within 12 weeks	Last-click hides top-of-funnel value; MTA + lift exposes it
Keep last-click view for legacy reporting	Finance and legal already reconcile on last-click
Optimise on lift, report on last-click	Different consumers, different views
Publish both attribution and lift weekly	Trust builds from side-by-side transparency

Why this works — concept by concept:

Attribution is a model, not a fact — every model is an opinion about credit. The pipeline supports multiple models simultaneously; the choice is business, not technical.
Incrementality is the ground truth — a channel with high last-click credit but zero incremental lift is an accounting artefact. Lift resolves the argument that attribution cannot.
Cross-device stitching is required — a user with a mobile impression and a desktop conversion is invisible to per-device attribution. Stitching lifts coverage from ~50% to ~85%.
CAPI restores signal that pixels lost — post-ITP, browser pixels lose 30-50% of conversion events on iOS Safari. CAPI closes that gap without requiring the user to change anything.
Cost — MTA is O(touches × conversions) per user; scales to billions of touches with distributed SQL. Incrementality tests cost 5-15% of spend but pay back in reallocation gains.

SQL
Topic — window functions
Window function problems for attribution

Practice →

SQL Topic — aggregation Aggregation drills for MTA

Practice →

4. Identity resolution + graphs

`identity resolution` in 2026 is a portfolio of deterministic + probabilistic + shared-ID + cohort signals — no single ID wins

The mental model in one line: an identity graph maps physical users to a portfolio of IDs — hashed PII, device IDs, shared IDs (UID2, RampID, ID5), cohort signals — and the pipeline maintains a shifting, versioned mapping of person_id → {IDs} under an opt-out contract. Once you say "no single ID wins in 2026," the entire identity resolution interview surface becomes a question of how you weight and merge signals under a privacy regime.

Deterministic vs probabilistic matching.

Deterministic. Exact match on a canonical, hashed identifier — usually sha256(lowercase(trim(email))) or sha256(phone_e164). Precision ~99%; recall depends on how often users authenticate.
Probabilistic. Model over IP + user-agent + geo + behavioural fingerprint. Precision 70-90% depending on tuning; recall much higher because it doesn't require authentication.
Best practice. Deterministic where available, probabilistic to fill in the gaps. Never rely on probabilistic alone for regulated verticals (finance, healthcare).

Shared-ID landscape in 2026.

UID2 (Unified ID 2.0) — open-source, email-derived deterministic ID with a rotating salt. Requires user consent per session. Native support in every major SSP and DSP.
LiveRamp RampID (formerly IdentityLink) — cross-device identity graph as a service. RampID is portable across platforms; the AbiliTec API resolves PII to RampID.
ID5 — probabilistic + first-party graph, mainly EU. Handles cookieless environments natively.
Google Privacy Sandbox — Topics API (cohort by interest), PAAPI FLEDGE (on-device auction), Attribution Reporting API (aggregated conversion measurement). Native browser signals; no ID at all.
Apple SKAdNetwork 4.0 — aggregated conversion postbacks for iOS apps. No user ID; measurement is at aggregated privacy resolution.

First-party ID graphs — the moat.

Every touchpoint the brand owns writes a first-party event: newsletter signup, purchase, login, product view. Each event stamps user_hash = sha256(email) when known.
The ID graph is built as a person_id → {emails, phones, device_ids} mapping via a MATCH on shared identifiers (recursive CTE over shared edges).
Retention: keep the graph for as long as user consent allows (typically 24-36 months under GDPR "necessary" purpose).
Opt-out enforcement: a nightly job scans the opt-out queue and removes matching records from every downstream store — the graph itself, audiences, and log tables.

Household resolution.

Multiple people share a household (IP, device pool). Household resolution merges individual person_ids under a household_id for use cases where the household is the unit (streaming TV, insurance).
Typical signal: shared IP + shared postal code + overlapping device usage patterns. Precision is model-dependent (~70-85%).
Use for reach measurement and cross-device frequency capping; do not use for personalisation because assumptions about household composition are fragile.

Opt-out enforcement — the non-negotiable.

Every opt-out event (Do-Not-Sell request, CCPA delete, GDPR erasure) must trigger deletion across every store containing the person.
Best practice: a central opt_out_ledger table with (person_id, opt_out_type, tstamp) — every downstream job reads this before every join and filters accordingly.
Failure to enforce is a per-event fine. In 2024-2025 alone, several tier-1 platforms paid nine-figure settlements for opt-out failures.

Cookieless identity — beyond the ID.

Contextual. Bid on the content, not the user. Article about running shoes → bid for running shoe advertisers. No user data required.
Cohort-based. Topics API groups users by 3-week rolling interests; PAAPI runs an on-device auction with no server round-trip.
Clean rooms. Advertiser and publisher upload hashed PII into a walled compute environment; queries return aggregate results only, under a differential-privacy budget.

Common interview probes on identity.

"What's the difference between deterministic and probabilistic matching?" — deterministic is exact hash match; probabilistic is a model score.
"How do you build a first-party ID graph?" — recursive CTE on shared hashed identifiers, versioned per snapshot.
"How does UID2 work?" — email-derived ID with rotating salt, user consent per session, portable across SSPs.
"What is a data clean room?" — a walled compute environment that answers aggregate queries against joined PII without revealing per-row data.

Worked example — deterministic hash join across two touchpoint sources

Detailed explanation. The bread-and-butter identity SQL: two touchpoint streams — one from newsletter signups (has email), one from ad impressions (has device_id). Login events in a third table map email → device_id. Stitch the three so an ad impression can be attributed to the newsletter signup that preceded it.

Question. Given signups(email), impressions(device_id), and logins(email, device_id), write the SQL that finds every impression served to a device belonging to a user who signed up.

Input — signups.

email_hash	signup_tstamp
h1	09:00
h2	09:15

Input — logins.

email_hash	device_id	tstamp
h1	d1	09:05
h1	d2	09:10
h2	d3	09:20

Input — impressions.

device_id	tstamp	campaign
d1	10:00	c_42
d2	10:05	c_42
d3	10:10	c_88
d4	10:15	c_42

Code.

WITH known_devices AS (
    SELECT DISTINCT s.email_hash, l.device_id
    FROM signups s
    JOIN logins l USING (email_hash)
    WHERE l.tstamp <= NOW()
)
SELECT
    i.device_id,
    i.campaign,
    i.tstamp,
    k.email_hash
FROM impressions i
JOIN known_devices k USING (device_id)
ORDER BY i.tstamp;

Step-by-step explanation.

known_devices builds the email_hash → device_id map from the login history. One user can bind multiple devices, and the same device can bind to only one user (in this simplified model).
Joining impressions to known_devices on device_id gives every impression that was served to a device attributable to a known signup.
Impressions to unknown devices (d4 in this example) are dropped — they cannot be attributed at this precision.
In production, extend with a probabilistic_devices table via an OR condition — deterministic first, probabilistic fill.
Wrap the whole query with a filter against the opt_out_ledger to exclude opted-out users before joining.

Output.

device_id	campaign	tstamp	email_hash
d1	c_42	10:00	h1
d2	c_42	10:05	h1
d3	c_88	10:10	h2

Rule of thumb. Always run the opt-out filter before the identity join, not after. Otherwise you leak PII into intermediate cache tables — a compliance violation even if the final output looks correct.

Worked example — recursive CTE for a first-party ID graph

Detailed explanation. A user has multiple emails (personal, work), multiple phones, multiple device IDs. A first-party ID graph collapses all of them under a single person_id. This is the classic recursive CTE — build edges between shared identifiers, then walk the graph to find connected components.

Question. Given a table of edges(id_a, id_b) where each row asserts two identifiers belong to the same person, produce a (id → person_id) mapping via a recursive CTE.

Input — edges.

id_a	id_b
email:alice@a.com	email:alice@b.com
email:alice@b.com	phone:+1-555-0100
device:d1	email:alice@a.com
email:bob@c.com	phone:+1-555-0200
device:d2	email:bob@c.com

Code.

WITH RECURSIVE ids AS (
    SELECT id_a AS id FROM edges
    UNION
    SELECT id_b AS id FROM edges
),
walk AS (
    -- seed: every id is its own root
    SELECT id, id AS root FROM ids

    UNION

    -- step: follow an edge to a smaller root
    SELECT w.id, LEAST(w.root, e.id_b) AS root
    FROM walk w
    JOIN edges e ON e.id_a = w.root

    UNION

    SELECT w.id, LEAST(w.root, e.id_a) AS root
    FROM walk w
    JOIN edges e ON e.id_b = w.root
),
final AS (
    SELECT id, MIN(root) AS person_id
    FROM walk
    GROUP BY id
)
SELECT * FROM final ORDER BY person_id, id;

Step-by-step explanation.

ids collects the universe of identifiers from both sides of the edge table.
walk seeds each identifier with itself as its root, then iteratively follows edges to smaller roots (lexicographically) until it converges.
In each iteration, the recursive step joins the current (id, root) to any edge whose one side matches the root and updates the root to the smaller neighbour.
final picks the smallest reachable root per identifier — that's the person_id.
This is a distributed-friendly connected-components algorithm; production versions use GraphFrames on Spark or an in-database GraphOp for scale.

Output.

id	person_id
device:d1	device:d1
device:d2	device:d2
email:alice@a.com	device:d1
email:alice@b.com	device:d1
email:bob@c.com	device:d2
phone:+1-555-0100	device:d1
phone:+1-555-0200	device:d2

Rule of thumb. Recursive CTEs work up to a few million edges. Above that, use Spark GraphFrames or a purpose-built graph store (Neptune, DGraph). Always version the graph — a snapshot per day lets you reproduce audience segments and attribution retroactively.

Worked example — clean-room aggregate query with differential privacy

Detailed explanation. A brand and a publisher want to measure ad-driven purchases without sharing per-user data. Both upload hashed PII into a clean room; queries return only aggregates that satisfy a differential-privacy budget (typically ε ≤ 1, δ ≤ 1e-6). This is the pattern that survives the post-cookie regulatory regime.

Question. Write a clean-room-safe query that counts exposed-conversion overlap between a publisher's impression logs and a brand's purchase table. Show the query and the DP guardrail.

Input.

Party	Table	Grain
Publisher	pub.impressions	(user_hash, campaign, tstamp)
Brand	brand.purchases	(user_hash, sku, tstamp)

Code.

-- Executed inside the clean room; neither party sees raw joined rows.
WITH exposed AS (
    SELECT DISTINCT user_hash, campaign
    FROM pub.impressions
    WHERE tstamp BETWEEN '2026-07-01' AND '2026-07-14'
),
converted AS (
    SELECT DISTINCT user_hash
    FROM brand.purchases
    WHERE tstamp BETWEEN '2026-07-01' AND '2026-07-14'
),
overlap AS (
    SELECT e.campaign, COUNT(DISTINCT c.user_hash) AS conv_users
    FROM exposed e
    JOIN converted c USING (user_hash)
    GROUP BY e.campaign
    HAVING COUNT(DISTINCT c.user_hash) >= 100    -- k-anonymity floor
)
SELECT
    campaign,
    -- DP noise: Laplace(scale = 1 / epsilon)
    ROUND(conv_users + LAPLACE_NOISE(1.0)) AS dp_conv_users
FROM overlap;

Step-by-step explanation.

Both parties upload hashed PII into the clean room. Neither party can SELECT * on the other's raw tables — the clean room's SQL engine enforces this at parse time.
exposed builds the set of publisher-exposed users for the campaign window; converted the set of brand-converting users; overlap joins and counts.
HAVING COUNT >= 100 enforces k-anonymity — no output row can represent fewer than 100 users, preventing re-identification of small cohorts.
LAPLACE_NOISE(1 / ε) adds calibrated noise to each count to satisfy differential privacy. ε=1.0 is the common budget for measurement queries.
The clean-room engine (AWS Clean Rooms, Snowflake DCR, Google ADH, InfoSum) tracks cumulative ε per user and blocks queries that would exceed the budget.

Output.

campaign	dp_conv_users
c_42	1,254
c_88	302
c_15	(excluded — under 100 threshold)

Rule of thumb. Design clean-room queries at the campaign or audience-segment grain, never at the individual user grain. K-anonymity + DP noise together give a defensible measurement number that survives regulator review.

Senior interview question on identity resolution architecture

A senior interviewer might ask: "Design a first-party ID graph for a retail brand in 2026. The brand has 40M customers across web, iOS, Android, and email. Walk through the graph model, the ingestion, the opt-out enforcement, and how you'd expose it to a DSP for onboarding audiences."

Solution Using a versioned graph + opt-out ledger + clean-room export

First-party ID graph — retail brand (40M customers)
====================================================

Schema
------
person_master(person_id PK, first_seen, last_updated)
identity_edge(person_id FK, id_type, id_value, source, seen_tstamp, is_active)
   id_type ∈ {'email_hash', 'phone_hash', 'device_id', 'ip_hash', 'shared_id_uid2'}
opt_out_ledger(person_id FK, opt_out_type, effective_from)

Ingestion (streaming — Kafka + Flink)
-------------------------------------
1. Every customer event (login, purchase, newsletter open, app install)
   emits an 'identity_touch' record with all known IDs.
2. Flink job hashes and normalises IDs.
3. For each new (id_type, id_value), attempt to attach to existing
   person_id via a graph merge. If ambiguous → hold in review queue.

Batch consolidation (Spark daily)
---------------------------------
4. Snapshot the full graph; run connected-components on the edge table.
5. Reassign person_ids where components merged.
6. Emit person_master_v{date} and identity_edge_v{date}.

Opt-out enforcement (Spark nightly)
-----------------------------------
7. Scan opt_out_ledger for entries effective in the last 24h.
8. Delete matching rows from identity_edge, person_master, all
   audience tables, all downstream log tables.
9. Emit a compliance receipt per deletion (regulator audit).

DSP audience export (weekly, via clean room)
--------------------------------------------
10. Build audience segments as SELECT person_id FROM ...  filters.
11. Hash to UID2 tokens inside the clean room.
12. Export UID2 tokens to DSP, never raw PII.
13. Log every export with (segment_id, size, ε_spent) for governance.

Step-by-step trace.

Layer	Store	Update cadence	Consumer
Streaming ingest	Kafka + Flink state	real-time	audience UI
Graph snapshot	Iceberg person_master	daily	analytics + ML
Opt-out	Iceberg opt_out_ledger	nightly	every downstream job
DSP export	UID2 tokens (never PII)	weekly	DSP for onboarding
Compliance	receipt log	per event	regulator audit trail

The design has three properties every 2026 identity system must have: versioning (reproducibility), opt-out ledger (compliance), clean-room export (privacy). Miss any one and the system fails an audit.

Output:

Property	How it's satisfied
Versioning	snapshot per day, immutable Iceberg tables
Opt-out enforcement	central ledger, every job filters on it
Privacy-safe export	UID2 tokens via clean room, never raw PII
Onboarding coverage	UID2 native support in every major DSP
Auditability	per-deletion receipt log

Why this works — concept by concept:

Central person_master + edge table — the graph is a first-class relational schema, not a set of ad-hoc joins. Every downstream analytics job speaks the same identity language.
Versioned snapshots — reproducibility for audits and ML training. If a person_id merged between two days, both graphs exist forever.
Opt-out ledger with pre-join filter — the pattern that satisfies both GDPR erasure and CCPA delete. Filtering pre-join prevents PII leakage into intermediate tables.
UID2 export via clean room — the DSP receives a hashed, rotating token — never raw PII. UID2 is the closest thing to a portable cross-DSP audience currency in the post-cookie world.
Cost — the graph itself is O(edges); connected-components is O(V + E) on Spark. Storage is dominated by the log-level identity_touch stream, typically ~100 GB / day per 10M active users.

SQL
Topic — joins
Join problems for identity graphs

Practice →

SQL Topic — SQL SQL drills for recursive CTEs

Practice →

5. Log ETL + billing pipelines

`impression logs` at TB/day scale are a financial-grade pipeline — dedup, exact aggregation, reconciliation are non-negotiable

The mental model in one line: impression logs are the ledger of a DSP's spend and the SSP's revenue; the pipeline dedupes by impression_id, aggregates to campaign / day granularity, reconciles against SSP invoices, and shares safe subsets with measurement partners under a clean-room protocol. Once you say "log ETL is a financial pipeline, not an analytics pipeline," the whole billing / reconciliation / SLA interview surface stops being about SQL tricks and starts being about exact-once contracts.

The scale envelope.

Log volume. A tier-1 DSP writes 1-10 TB / day of impression + bid + click + conversion + NURL events. A large SSP writes 100+ TB / day of log-level dashboards.
Retention. 30 days hot (Iceberg on S3 with warm partitions), 12-24 months cold (Iceberg with cold partitions, Parquet-compressed). Some finance teams require 7 years for tax audit.
Query frequency. Billing runs once per day; measurement partners query 24/7. Cost management dictates that measurement queries should read pre-aggregated tables, not the raw log.

The dedup contract.

Idempotency token. Every event carries a globally unique impression_id (or click_id, conversion_id). The token is generated at the SSP and echoed by every downstream consumer.
Multi-source firing. The same impression can be recorded by 2-3 sources (JS pixel, server pixel, SSP log). Dedup by impression_id.
Grain of dedup. Impression events are deduped by impression_id. Clicks by click_id. Conversions by conversion_id + event_id for MTA joins.
Late-arriving events. SSP log dumps arrive up to 24h after the impression. Reprocess yesterday's partition at close-of-day-plus-one to fold in late data.

Aggregation contracts.

CPM (cost per mille). SUM(bid_price) / (COUNT(impressions) / 1000). Grain: campaign × day. Financial-grade — must be exact.
CPC (cost per click). SUM(bid_price for clicked impressions) / COUNT(clicks). Grain: campaign × day.
CPA (cost per action). SUM(bid_price for conversion-attributed impressions) / COUNT(conversions). Grain: campaign × day. Depends on attribution model.
Viewable CPM (vCPM). SUM(bid_price for viewable impressions) / (COUNT(viewable impressions) / 1000). Reports quality; often billed separately.

Reconciliation — the finance function.

Every SSP sends an invoice at end of month with (campaign, impressions, spend). The DSP's internal billing_daily must reconcile within a 1-2% delta.
Deltas above 2% trigger an investigation: NURL loss, dedup miss, clock skew, currency conversion.
Reconciliation runs weekly; disputes are settled at end-of-month invoicing.

Price floor optimisation.

SSPs publish per-slot floors that bids must exceed. Floors are set to maximise seller revenue.
DSPs read historical floor observations and choose the campaigns most likely to clear that floor for a given slot.
Machine-learned floor prediction is a mid-priority DE workload — runs weekly, feeds the DSP's shading table.

Seller-Defined Audiences (SDA).

New IAB standard for the post-cookie world: the SSP describes the audience (interest, demographic, context) alongside the bid request; the DSP bids based on this instead of user IDs.
SDA taxonomy is standardised (IAB Content Taxonomy 3.0, Audience Taxonomy 1.1). DSPs must ingest and index these.

Snowflake secure data sharing.

Snowflake's Secure Data Sharing (SDS) lets a data owner share a specific table / view with another Snowflake account, live, without copying data.
Measurement partners (Nielsen, Comscore, iSpot) subscribe to a DSP's billing_daily share and query directly. No pipeline to build, no data to move.
Combine with row-level policies to expose only the partner's own campaigns.

Common interview probes on log ETL.

"How do you dedup impression logs?" — group by impression_id, keep earliest; track sources for diagnostics.
"What is your reconciliation SLA?" — daily reconcile within 2% of SSP invoice; anything above triggers investigation.
"How do you handle late-arriving SSP logs?" — reprocess the impacted partition at close-of-day-plus-one.
"What's the difference between CPM and vCPM?" — CPM is per impression; vCPM is per viewable impression (MRC-viewable).

Worked example — dedup impression logs with `ROW_NUMBER`

Detailed explanation. The workhorse dedup query. Multiple sources fire the same impression; group by the idempotency key, keep the earliest, preserve the list of sources for observability. This is the same pattern used at every ad platform.

Question. Given impressions_raw(impression_id, source, tstamp, campaign_id, bid_price_cpm) with duplicate rows per impression, write the SQL that dedupes and preserves the earliest timestamp + list of sources.

Input.

impression_id	source	tstamp	campaign_id	bid_price_cpm
imp_1	js_pixel	10:00:01	c_42	1.50
imp_1	server_pixel	10:00:02	c_42	1.50
imp_1	ssp_log	10:59:00	c_42	1.50
imp_2	ssp_log	10:01:00	c_88	0.90

Code.

WITH ranked AS (
    SELECT
        impression_id,
        source,
        tstamp,
        campaign_id,
        bid_price_cpm,
        ROW_NUMBER() OVER (
            PARTITION BY impression_id
            ORDER BY tstamp ASC
        ) AS rn
    FROM impressions_raw
    WHERE dt = '2026-07-17'
),
canonical AS (
    SELECT
        impression_id,
        MIN(tstamp)             AS first_seen,
        ANY_VALUE(campaign_id)  AS campaign_id,
        ANY_VALUE(bid_price_cpm) AS bid_price_cpm,
        COUNT(*)                AS fire_count,
        ARRAY_AGG(DISTINCT source ORDER BY source) AS sources
    FROM impressions_raw
    WHERE dt = '2026-07-17'
    GROUP BY impression_id
)
SELECT * FROM canonical
ORDER BY first_seen;

Step-by-step explanation.

Rows land in impressions_raw with an impression_id and a source. Duplicates are expected — same impression_id from 2-3 sources.
The dedup uses a GROUP BY impression_id with MIN(tstamp) as the canonical timestamp and ARRAY_AGG(DISTINCT source) to preserve the set of sources.
ANY_VALUE on invariant fields (campaign_id, bid_price_cpm) works because they agree across sources by contract.
fire_count > 1 is normal and expected; fire_count = 1 from a single source is either brand-new or a lost pixel — worth alerting on.
Downstream (billing) queries canonical, never impressions_raw. Storing the source list lets ops diagnose "why is browser pixel coverage dropping?" without re-querying raw.

Output.

impression_id	first_seen	campaign_id	bid_price_cpm	fire_count	sources
imp_1	10:00:01	c_42	1.50	3	["js_pixel","server_pixel","ssp_log"]
imp_2	10:01:00	c_88	0.90	1	["ssp_log"]

Rule of thumb. Prefer GROUP BY over ROW_NUMBER() = 1 for dedup when you want to aggregate meta-fields (like the source array). ROW_NUMBER is cleaner when you want to preserve one specific row verbatim; GROUP BY when you want an aggregate representative row.

Worked example — viewability calculation from OMID events

Detailed explanation. Viewability is per MRC standard: ≥ 50% of pixels in view for ≥ 1 continuous second (display) or ≥ 2 seconds (video). The OMID SDK emits impression_viewed events with viewable_duration_ms. The pipeline joins impressions to OMID events and computes viewability rate per campaign.

Question. Given impressions(impression_id, ad_type) and omid_events(impression_id, viewable_duration_ms), compute the viewability rate per ad_type per campaign.

Input — impressions.

impression_id	campaign_id	ad_type
imp_1	c_42	display
imp_2	c_42	display
imp_3	c_88	video
imp_4	c_88	video

Input — omid_events.

impression_id	viewable_duration_ms
imp_1	1500
imp_2	400
imp_3	2200
imp_4	1000

Code.

WITH viewability AS (
    SELECT
        i.campaign_id,
        i.ad_type,
        i.impression_id,
        CASE
            WHEN i.ad_type = 'display' AND o.viewable_duration_ms >= 1000 THEN 1
            WHEN i.ad_type = 'video'   AND o.viewable_duration_ms >= 2000 THEN 1
            ELSE 0
        END AS is_viewable
    FROM impressions i
    LEFT JOIN omid_events o USING (impression_id)
)
SELECT
    campaign_id,
    ad_type,
    COUNT(*)                                    AS impressions,
    SUM(is_viewable)                            AS viewable_impressions,
    ROUND(100.0 * SUM(is_viewable) / COUNT(*), 2) AS viewability_pct
FROM viewability
GROUP BY campaign_id, ad_type
ORDER BY campaign_id, ad_type;

Step-by-step explanation.

The LEFT JOIN to omid_events preserves impressions with no OMID event (measurement gaps count as non-viewable).
The CASE applies the MRC standard: display ≥ 1000ms, video ≥ 2000ms of continuous viewable time.
Aggregation by (campaign_id, ad_type) gives the viewability rate, the metric the buyer reports on.
SUM(is_viewable) gives the viewable impressions used to compute vCPM.
Impressions missing OMID events entirely are still counted as impressions (they billed) but not as viewable — this is the standard treatment.

Output.

campaign_id	ad_type	impressions	viewable_impressions	viewability_pct
c_42	display	2	1	50.00
c_88	video	2	1	50.00

Rule of thumb. Report both impressions and viewable_impressions — brand advertisers care about viewability, direct-response advertisers care about total. Never silently drop non-viewable impressions from billing; that breaks reconciliation with the SSP.

Worked example — SSP invoice reconciliation with delta thresholds

Detailed explanation. Finance runs a monthly reconciliation between the DSP's internal billing and the SSP's invoice. Small deltas (< 2%) are expected (NURL loss); large deltas trigger investigation. The reconciliation SQL surfaces the deltas grouped by campaign, sorted by absolute size.

Question. Given billing_daily(campaign_id, dt, spend_usd) and ssp_invoice(campaign_id, dt, spend_usd), produce a reconciliation report for a given month, flagging campaigns with >2% absolute delta.

Input — billing_daily (DSP-side).

campaign_id	dt	spend_usd
c_42	2026-07	12,500.00
c_88	2026-07	5,200.00

Input — ssp_invoice.

campaign_id	dt	spend_usd
c_42	2026-07	12,750.00
c_88	2026-07	5,250.00

Code.

WITH dsp AS (
    SELECT campaign_id,
           DATE_TRUNC('month', dt) AS month,
           SUM(spend_usd)          AS dsp_spend
    FROM billing_daily
    WHERE dt >= '2026-07-01' AND dt < '2026-08-01'
    GROUP BY campaign_id, DATE_TRUNC('month', dt)
),
ssp AS (
    SELECT campaign_id, dt AS month, spend_usd AS ssp_spend
    FROM ssp_invoice
    WHERE dt = '2026-07-01'
)
SELECT
    d.campaign_id,
    d.month,
    d.dsp_spend,
    s.ssp_spend,
    (s.ssp_spend - d.dsp_spend)             AS delta_usd,
    ROUND(100.0 * (s.ssp_spend - d.dsp_spend)
              / NULLIF(s.ssp_spend, 0), 2)  AS delta_pct,
    CASE
        WHEN ABS(
            100.0 * (s.ssp_spend - d.dsp_spend)
                  / NULLIF(s.ssp_spend, 0)
        ) > 2.0 THEN 'INVESTIGATE'
        ELSE 'OK'
    END AS status
FROM dsp d
FULL OUTER JOIN ssp s USING (campaign_id, month)
ORDER BY ABS(s.ssp_spend - d.dsp_spend) DESC;

Step-by-step explanation.

dsp rolls up DSP-side spend from billing_daily to campaign × month.
ssp reads the SSP-invoice line items for the same month.
FULL OUTER JOIN captures campaigns present in either side. A campaign in only the SSP invoice is a NURL-loss suspect; a campaign in only the DSP billing is a phantom bill (rare).
delta_usd and delta_pct measure absolute and relative differences.
The CASE flags any campaign above the 2% threshold. The ops team investigates: NURL loss rate, clock skew, currency conversion, or a real SSP error.

Output.

campaign_id	month	dsp_spend	ssp_spend	delta_usd	delta_pct	status
c_42	2026-07	12,500.00	12,750.00	250.00	1.96	OK
c_88	2026-07	5,200.00	5,250.00	50.00	0.95	OK

Rule of thumb. Reconcile weekly, dispute at end of month. Store the reconciliation output as a first-class table (billing_reconciliation) — the finance team asks for the trailing 12 months every quarter.

Senior interview question on log ETL + billing pipeline design

A senior interviewer might ask: "Design the log ETL + billing pipeline for a DSP that processes 5 TB / day of impressions. Include the ingestion, dedup, aggregation, reconciliation, and measurement-partner sharing layers. What are your SLAs and how do you enforce exact-once?"

Solution Using a Kafka → Iceberg → Snowflake stack with idempotent dedup

DSP log ETL + billing pipeline — 5 TB/day
==========================================

Ingestion (real-time)
---------------------
1. Bid handlers, NURL handlers, click trackers write to Kafka
   (12 partitions × 3 replicas, ~2 MB/s per partition).
2. Kafka Connect → S3 raw partition (per-hour Parquet, gzip).
3. Iceberg external table over the raw partitions.

Dedup (hourly Spark, incremental)
---------------------------------
4. Read the latest hour partition from impressions_raw.
5. Dedup by impression_id, keep earliest tstamp, preserve source array.
6. Write to impressions_dedup with hourly partitioning.

Aggregation (daily Spark, close-of-day)
---------------------------------------
7. Read the full day of impressions_dedup + clicks_dedup + conversions_dedup.
8. Compute billing_daily: (campaign, dt, impressions, clicks, conversions,
   spend, viewable_impressions).
9. Write to billing_daily (Iceberg, insert-overwrite by partition).
10. Emit a completion marker in `pipeline_metadata` for downstream jobs.

Reconciliation (weekly)
-----------------------
11. Fetch SSP invoice line items via API / SFTP.
12. Join billing_daily to ssp_invoice by (campaign, week).
13. Emit reconciliation_report; alert on deltas > 2%.

Sharing (measurement partners, live)
------------------------------------
14. Snowflake Secure Data Share on billing_daily with row-level
    filter per partner.
15. Partner queries directly; no data movement.
16. Log per-query ε consumption for governance.

Step-by-step trace.

Layer	Store	Cadence	SLA
Ingest	Kafka + S3 raw	real-time	p99 < 5 s to raw
Dedup	Iceberg impressions_dedup	hourly	+1 h from raw
Aggregate	Iceberg billing_daily	daily	close-of-day + 6 h
Reconcile	reconciliation_report	weekly	< 2% delta
Share	Snowflake SDS	live	partner-driven

The design enforces exact-once by making impression_id the primary dedup key at every layer. Every downstream job reads from a deduped table, never from raw. Idempotency is the property that lets you rerun any layer without corrupting billing.

Output:

Component	Sizing	Cost driver
Kafka cluster	30 nodes	throughput + retention
S3 raw	5 TB / day × 30 d = 150 TB	storage
Iceberg dedup	500 GB / day × 30 d = 15 TB	Parquet compression 10×
Spark cluster	200 executors nightly	scan + shuffle
Snowflake share	per-query compute	measurement partner queries

Why this works — concept by concept:

Idempotency at every layer — dedup on impression_id means any layer can be reprocessed without corrupting downstream. This is the property that lets you rerun yesterday's billing after a bug fix.
Iceberg as the seam — Iceberg's insert-overwrite by partition + snapshot isolation lets billing runs be atomic. Consumers always see either the old snapshot or the new; never a partial write.
Reconciliation as first-class — the delta table is stored, not just alerted on. Finance re-queries historical reconciliations at every quarter-close.
Snowflake SDS eliminates the sharing pipeline — measurement partners query live views instead of receiving data drops. No ETL to build, no schema drift, no data staleness.
Cost — the dominant cost is Spark daily aggregation (200 executors × 6 hours). Kafka retention (7 days at 5 TB/day = 35 TB) is second. Iceberg storage is cheap because Parquet + zstd compresses ~10×.

SQL
Topic — aggregation
Aggregation drills for billing pipelines

Practice →

SQL
Topic — window functions
Window function drills for log dedup

Practice →

Cheat sheet — adtech DE recipes

RTB budget breakdown. ~60 ms end-to-end: 5 network in, 10 features, 15 model, 3 shading, 5 pacer, 2 consent, 5 build, 10 network out. Feature-store tail latency dominates surprises — provision for p99.9.
OpenRTB 2.6 required fields. id, impid, price, adm, adomain, crid, w, h. Missing adomain = silent drop by SSP. Always include nurl and echo ${AUCTION_PRICE} macro.
Impression dedup contract. Group by impression_id, keep MIN(tstamp), preserve ARRAY_AGG(DISTINCT source). Bill on deduped table, never raw. Reconcile weekly against SSP invoice with 2% delta threshold.
NURL reconciliation. Expect 1-5% NURL loss (browser close, ad blockers, network drop). Reconcile DSP nurl_fires vs SSP log daily; delta > 2% per campaign triggers investigation.
Bid shading. Rolling p60 of clearing prices per (campaign, slot, hour_of_day) over 7 days, divided by average raw value. Clamp shading_factor at 0.50 minimum to avoid pathological under-bidding.
Attribution model split. Last-click for legacy reporting + compliance; MTA (linear or Shapley) for optimisation; MMM for cross-channel; incrementality lift as ground truth. Never optimise on last-click alone in 2026.
CAPI dual-tag recipe. Fire browser pixel with eventID + server CAPI with same event_id. Meta / Google dedup on this key. Improves match quality and preserves signal against iOS ITP + ad blockers.
Identity graph recipe. person_master + identity_edge + versioned snapshots + opt_out_ledger filtered pre-join. Deterministic (hashed email/phone) first, probabilistic fill. Export to DSPs via UID2 tokens inside a clean room.
Cross-device stitching. Deterministic (hashed email) achieves 30-50% coverage. Add probabilistic (IP + UA + geo) to reach 70-85%. Household resolution for TV / streaming use cases only.
Clean-room query pattern. Two-party PII join inside AWS Clean Rooms / Snowflake DCR / Google ADH. K-anonymity floor HAVING COUNT >= 100 + Laplace DP noise ε=1.0. Aggregate output only, never per-row PII.
Financial-grade billing SLA. Impression dedup within 1 hour of ingest; billing_daily by close-of-day + 6h; reconciliation vs SSP invoice weekly, disputed at end-of-month.
Viewability (MRC). Display ≥ 50% pixels for ≥ 1 s; video ≥ 50% pixels for ≥ 2 s. Measured via OMID SDK. Report both impressions and viewable_impressions; bill on impressions unless the contract says vCPM.
Snowflake secure data sharing. Share billing_daily view with measurement partners; row-level policies for per-partner isolation. Zero pipeline, live query, no data movement.

Frequently asked questions

What is adtech data engineering?

Adtech data engineering is the DE specialisation for advertising technology platforms — DSPs (demand-side platforms), SSPs (supply-side platforms), ad exchanges, ad servers, and measurement / attribution vendors. It combines sub-100ms real-time bidding, TB/day log pipelines, cross-device identity graphs, and privacy-preserving measurement (clean rooms, differential privacy). Unlike generic analytics DE, adtech data engineering runs against a hard latency budget (< 100 ms for bid response), enforces financial-grade correctness (billing SLAs, invoice reconciliation), and operates under regulated privacy regimes (IAB TCF v2.2, GDPR, CCPA/CPRA). It is one of the few DE roles where real-time systems engineering, warehouse batch, and applied identity science all live in the same team.

How does an RTB pipeline handle 1M QPS?

An rtb pipeline at 1M QPS is a fleet of stateless bid handlers (typically 500-2000 hosts, each handling 500-2K QPS) co-located with sharded feature stores (Redis / Aerospike, ~200-500 shards holding 100M-1B keys total). The bid response path — parse OpenRTB, fetch features, score via ONNX, apply bid shading, check budget pacer, build response — completes in 50-70 ms p99, leaving ~30 ms for network egress inside the 100 ms SSP timeout. The impression logger runs asynchronously (never blocking the bid response) and writes to Kafka once the NURL fires. Downstream, Kafka Connect lands events in S3 as Parquet, Iceberg exposes them as an external table, and Spark handles hourly dedup + daily billing aggregation. The critical scaling decision is co-locating the feature store with the bid handler; a WAN Redis adds ~40 ms of p99 tail latency and breaks the SLA at high QPS.

What is the difference between last-click and multi-touch attribution?

Last-click attribution assigns 100% of the conversion credit to the last touchpoint (usually a click) before the conversion. Multi-touch attribution (MTA) distributes credit across all touchpoints in the user's journey — via a linear split, position-based weights (40 / 20 / 40 for first / middle / last), or a data-driven Shapley / Markov model that learns weights from historical patterns. Last-click systematically undervalues top-of-funnel channels (display, social awareness) because a user typically clicks a search ad after seeing the display — under last-click, search gets 100% and display gets zero. In the post-cookie world of 2026, most sophisticated advertisers run MTA alongside marketing-mix modelling (MMM) and validate both against holdout-based incrementality tests. Incrementality is the ground truth; MTA is the optimisation surface.

How does identity resolution work without third-party cookies?

Post-cookie identity resolution in 2026 is a portfolio approach — no single ID replaces the third-party cookie. First-party data (hashed email, hashed phone from login and purchase events) provides deterministic identity where the user authenticates. Shared IDs — UID2 (Unified ID 2.0), LiveRamp RampID, ID5 — extend that identity across publishers and DSPs when both sides support the standard. Cohort signals — Google Privacy Sandbox's Topics API and PAAPI (Protected Audience API / FLEDGE) — provide privacy-preserving audience signals in the browser without per-user IDs. Aggregated measurement — Apple SKAdNetwork 4.0 for iOS apps, the Attribution Reporting API for the web — replaces per-user conversion tracking with privacy-safe aggregate postbacks. Data clean rooms (AWS Clean Rooms, Snowflake DCR, Google ADH, InfoSum) provide differential-privacy-protected joins between advertiser and publisher data. The pipeline stitches all four together under a versioned first-party graph with a central opt-out ledger.

What is a data clean room in adtech?

A data clean room is a walled compute environment where two or more parties can join their datasets (typically hashed PII) and run aggregate queries without either party seeing the other's raw data. AWS Clean Rooms, Snowflake Data Clean Rooms, Google Ads Data Hub, InfoSum, and Habu (now LiveRamp) are the leading platforms in 2026. The clean room enforces three privacy guarantees: (1) the SQL engine parses queries and rejects any that would leak per-row data; (2) a k-anonymity floor (typically HAVING COUNT >= 100) prevents small-cohort re-identification; (3) differential-privacy noise (Laplace scale 1/ε) is added to aggregate counts, and a cumulative ε budget per user is enforced. Clean rooms became the default measurement pattern in 2026 because they let brands and publishers measure ad-driven purchases without violating GDPR, CCPA, or platform policy — the only survivor of the third-party cookie deprecation.

How do you deduplicate impression logs at scale?

Impression log dedup at TB / day scale uses the idempotency key pattern — every impression carries a globally unique impression_id allocated by the SSP at bid time and echoed by every downstream consumer (browser pixel, server pixel, SSP log). The Spark job runs hourly on the latest partition, groups by impression_id, keeps the earliest MIN(tstamp) as canonical, and preserves the list of firing sources via ARRAY_AGG(DISTINCT source). The output table impressions_dedup is the single source of truth for billing; the raw impressions_raw is retained only for observability and reprocessing. Late-arriving SSP log dumps (up to 24 h delayed) are folded in by reprocessing yesterday's partition at close-of-day-plus-one. The fire_count per row gives the dedup ratio (typically 2-3 for correctly instrumented pipelines); a sudden drop signals an upstream instrumentation regression. Downstream billing aggregation reads only impressions_dedup, guaranteeing that spend numbers reconcile with SSP invoices within the 2% delta threshold.

Practice on PipeCode

Drill the SQL practice library → for the dedup, aggregation, and reconciliation family of adtech questions.
Rehearse on joins problems → when the interviewer wants identity graph or attribution join depth.
Sharpen window function drills → for the last-click and multi-touch attribution patterns.
Stack the aggregation library → for the CPM / CPC / CPA billing family.
Layer the real-time analytics library → for the RTB feature-store and hot-path probes.
For the broader surface, read top data engineering interview questions →.
Stack the prerequisites with the only 5 skills you need to become a data engineer →.
Sharpen the Spark axis with the Apache Spark internals course →.
For ETL system design, work through the ETL system design course →.

Pipecode.ai is Leetcode for Data Engineering — every RTB, attribution, identity, and log-ETL recipe above ships with hands-on practice rooms where you wire the impression dedup, the last-click and MTA SQL, the identity-graph recursive CTE, and the SSP-invoice reconciliation against real graded inputs. PipeCode pairs every reading with 450+ DE-focused problems and a real-time scoring engine, so you never have to wonder whether your `adtech data engineering` answer holds up under a senior interviewer's depth probes.

Practice SQL now →
Join problems →

Top comments (1)

Luis Cruz • Jul 18

The discussion around real-time bidding (RTB) pipelines staying under a 100ms bid budget at 1M QPS really resonated with me, as I've worked on similar projects where low-latency systems engineering was crucial. I appreciated the breakdown of the OpenRTB 2.6 bid request/response flow and the DSP bid factory pipeline, as it highlighted the complexity of adtech data engineering. One aspect that I think deserves further exploration is the trade-off between deterministic and probabilistic identity resolution methods, such as UID2, RampID, and ID5, and how they impact the accuracy of attribution modeling. Have you considered discussing the implications of using a hybrid approach, combining both deterministic and probabilistic methods to achieve a more comprehensive understanding of user identity?

1. Why adtech DE differs in 2026

Adtech is a real-time systems + warehouse + identity + privacy problem — no other DE vertical stacks all four at once

Worked example — the same event traversing hot, warm, cold paths

Worked example — 100K QPS bid budget breakdown

Worked example — the impression dedup contract

Senior interview question on adtech DE constraints

Solution Using the 4-axis constraint framework

2. RTB auction pipelines

rtb pipeline is a sub-100ms fan-in / fan-out — OpenRTB 2.6 requests fan to N DSPs, one winner returns, one NURL fires

Worked example — a minimal OpenRTB bid response

Worked example — bid shading with historical clearing prices

Worked example — NURL win-notify reconciliation

Senior interview question on RTB pipeline design

Solution Using a co-located feature store + async impression logger

3. Attribution modelling

attribution pipeline is a graph of touchpoints with a weight function — the model is a business choice, not a technical one

Worked example — assigning last-click credit via a window function

Worked example — multi-touch attribution with linear weights

Worked example — CAPI dual-tag dedup on event_id

Senior interview question on attribution pipeline design

Solution Using an MTA + incrementality dual-track

4. Identity resolution + graphs

identity resolution in 2026 is a portfolio of deterministic + probabilistic + shared-ID + cohort signals — no single ID wins

Worked example — deterministic hash join across two touchpoint sources

Worked example — recursive CTE for a first-party ID graph

Worked example — clean-room aggregate query with differential privacy

Senior interview question on identity resolution architecture

Solution Using a versioned graph + opt-out ledger + clean-room export

5. Log ETL + billing pipelines

impression logs at TB/day scale are a financial-grade pipeline — dedup, exact aggregation, reconciliation are non-negotiable

Worked example — dedup impression logs with ROW_NUMBER

Worked example — viewability calculation from OMID events

Worked example — SSP invoice reconciliation with delta thresholds

Senior interview question on log ETL + billing pipeline design

Solution Using a Kafka → Iceberg → Snowflake stack with idempotent dedup

Cheat sheet — adtech DE recipes

Frequently asked questions

What is adtech data engineering?

How does an RTB pipeline handle 1M QPS?

What is the difference between last-click and multi-touch attribution?

How does identity resolution work without third-party cookies?

What is a data clean room in adtech?

How do you deduplicate impression logs at scale?

Practice on PipeCode

`rtb pipeline` is a sub-100ms fan-in / fan-out — OpenRTB 2.6 requests fan to N DSPs, one winner returns, one NURL fires

`attribution pipeline` is a graph of touchpoints with a weight function — the model is a business choice, not a technical one

`identity resolution` in 2026 is a portfolio of deterministic + probabilistic + shared-ID + cohort signals — no single ID wins

`impression logs` at TB/day scale are a financial-grade pipeline — dedup, exact aggregation, reconciliation are non-negotiable

Worked example — dedup impression logs with `ROW_NUMBER`