The Consent Stitcher

A medium Pipeline Design interview practice problem on DataDriven. Design the pipeline on the canvas: sources, transforms, storage and checks, then get feedback on it.

Domain: Pipeline Design
Difficulty: medium
Seniority: L6

Problem

Our health platform gets 100 million visitors a month, monetized through ads and premium membership. Design a pipeline that stitches sessions, linking anonymous events to the user within minutes of login with 5-minute freshness on engagement metrics, and handles the consent propagation required when users change their privacy settings.

Consent was given. Or was it? Stitch the records together.

Business requirements

Most visitors browse anonymously and some create an account mid-session; analytics today counts the pre-login and post-login parts as two separate users.
Health profiles, symptom data, and medication tracker entries are HIPAA-governed; advertising teams can never see them.
Treating a CCPA opt-out as a flag that reports skip is not real opt-out; advertising analytics has to physically forget the user.
Medical articles are updated to reflect new research; an advertiser buying readers of our current arthritis content shouldn't get readers of an old superseded version.

Worked solution and explanation

What this really is

This is an identity merge dressed up as analytics hygiene, with three governance rules bolted on. Almost everyone draws a stream that links the anonymous id to the user id at login. Candidates separate on the other three questions. Is the health boundary enforced by the platform or by a BI filter? Is opt-out a real erasure or a flag? Is engagement joined to the article as it was read, or as it reads today? Get any of them wrong and the design passes the demo but fails the HIPAA review, the CCPA audit and the advertiser's invoice.

Walk the requirements

Step 1: Collapse the session into one user

session_stitcher holds state per open session. On the login event it writes the anonymous-id-to-user-id mapping into user_sessions and re-keys the pre-login page_views. It runs as a real-time stream because a nightly batch stitch would leave the day's dashboards double-counting until morning.

Step 2: Split the tiers before they land

content_engagement routes health events (symptoms, medication tracker entries) to health_warehouse and strips health detail from the events bound for advertising_warehouse. Two separate warehouse databases with their own role grants mean an advertising query cannot reach health rows however it is written. A column mask in the dashboard stops the dashboard. It does not stop a SQL client.

Step 3: Make opt-out physical and provable

A change in consent_flags triggers opt_out_orchestrator, which pseudonymizes the user's identifier in advertising_warehouse and user_sessions. It then writes one completion row per request to opt_out_receipts. The alert fires when a request passes the regulatory window unfinished.

Step 4: Attribute to the version that was read

content_engagement joins each event to article_versions on the event timestamp: the version with valid_from <= read_at < valid_to. Joining on the article id alone silently credits old-version readers to today's arthritis content.

The reference design

Flink stitches sessions in real time. `content_engagement` attributes each read to its article version and splits the health and advertising tiers into separate warehouses. Consent changes drive a pseudonymization job that leaves a receipt.

page_views (source, Kafka)
consent_flags (source, CDC)
session_stitcher (transform, Flink); freshness: real-time
user_sessions (storage, PostgreSQL)
article_versions (storage, PostgreSQL)
content_engagement (transform, Flink); freshness: < 1min
health_warehouse (storage, warehouse)
advertising_warehouse (storage, warehouse)
opt_out_orchestrator (transform, Airflow); alerting: Opt-out pseudonymization overdue
opt_out_receipts (storage, PostgreSQL)
health_app_team (consumer)
advertising_team (consumer)

page_views -> session_stitcher
session_stitcher -> user_sessions
session_stitcher -> content_engagement
article_versions -> content_engagement
content_engagement -> health_warehouse
content_engagement -> advertising_warehouse
health_warehouse -> health_app_team
advertising_warehouse -> advertising_team
consent_flags -> opt_out_orchestrator
opt_out_orchestrator -> advertising_warehouse
opt_out_orchestrator -> user_sessions
opt_out_orchestrator -> opt_out_receipts

Ingest + stitch: page_views, consent_flags, session_stitcher, user_sessions
Versioned engagement + governed tiers: article_versions, content_engagement, health_warehouse, advertising_warehouse
Erasure + consumers: opt_out_orchestrator, opt_out_receipts, health_app_team, advertising_team

Opt-out as a flag: do_not_target = true on the user row. Every report has to remember to filter on it, and the identifier still sits in every fact table. An auditor asks you to prove the user is forgotten, and you cannot.
Opt-out as erasure: The orchestrator replaces the user id with a random token in advertising_warehouse and user_sessions, then records the request in opt_out_receipts. Aggregates survive and the person does not. Each receipt is the proof.

Common follow-up questions

A login arrives 20 minutes after the session's last anonymous event. Does the stitch still happen? (Tests session-window state retention and late data in `session_stitcher`.)
A user opts out while a backfill of `advertising_warehouse` is running. What stops the backfill from restoring their identifier? (Tests whether erasure is replayed against backfills rather than applied once.)