# The Consent Stitcher

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

Canonical URL: <https://datadriven.io/problems/the_consent_stitcher>

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.

## 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.

> **Stitch at login, govern at the engine**
>
> The stitch is stateful streaming keyed by session: when the login event arrives, every buffered anonymous event in that session is rewritten to the user id. Everything else is about where each rule is enforced. Isolation lives in separate warehouse databases, erasure is a pseudonymization job with a receipt, and version attribution happens in the join.

### 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

**Pipeline stages**

- 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)

**Data flow**

- 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

**Layers**

- 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

*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.*

**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.

> **Today's article is not the one they read**
>
> Candidates version the article table and then join engagement on `article_id` at query time, which picks up the current row. Resolve the version inside `content_engagement` using the event timestamp, while the version in effect is still known, and keep that version on the stored row.

> **Where is the boundary enforced?**
>
> Point at the two warehouses and say which role can read each one. That signals seniority. "We mask it in the dashboard" is the answer that ends the HIPAA conversation badly.

## 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.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/the_consent_stitcher)
- [System Design Interview Questions](https://datadriven.io/data-engineering-system-design)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.