Credit for Every Touch

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

Ad spend from two ad platforms, CRM opportunities and web sessions land separately for each of about 40 clients, the largest sending roughly 50 million sessions a month. Design the pipeline that stitches them into attribution credits for campaign reporting.

They saw the ad, clicked the email, then bought. Who gets credit?

Business requirements

• Attribution has to be fresh enough for daily campaign decisions.
• Some sources don't expose an updated_at field, so incremental processing has to cope without knowing exactly what changed.
• Data quality is inconsistent across sources, and the pipeline has to handle them reliably.
• Attribution gives credit to every touchpoint in the customer journey before a conversion, across ad, CRM and web data.

Worked solution and explanation

What this really is

This is a replay-safety problem dressed up as marketing attribution. The credit math is the easy part. With no updated_at, there is no watermark to load only what changed, and the ad platforms keep revising the last week of numbers. So the only honest incremental load is to re-pull a whole date range and replace what you had. The trap is an append-style incremental load. It works on day one, then every re-pull stacks a second copy of the same clicks, and every touchpoint in the revised window gets credited twice.

Walk the requirements

Step 1: Let the orchestrator own the daily run

orchestrator (Airflow) runs once a day, after the ad platforms finish exporting and before the earliest client reads. It holds dependency edges to both date_range_loader and attribution_transform, so credit never computes over a half-loaded day. Its error path reaches on_call_alerts when a run fails or slips.

Step 2: Re-pull the window, overwrite the partition

date_range_loader (Spark) pulls each source for the trailing seven days, the window the ad platforms can still revise, and lands it in raw_landing under partition_overwrite. A rerun of any date replaces that date, so nothing accumulates.

Step 3: Validate before anything is credited

load_validation (Great Expectations) checks the landed partitions for missing IDs and malformed fields before attribution_transform reads them. About one load in twenty fails here, and it fails the run instead of quietly feeding attribution.

Step 4: Credit every touchpoint, then overwrite

attribution_transform (dbt) assembles ad, CRM and web touchpoints into each customer journey and spreads credit across every touch before the conversion. It writes to attribution_warehouse, which also uses partition_overwrite, so recomputing a range restates credit instead of doubling it.

The reference design

Append-only incremental load

Each run inserts whatever the source returned for the window. A source with no updated_at returns rows you already have, so revised clicks land twice and credit inflates every day the windows overlap.

Range re-pull with overwrite

Each run replaces the date partitions it pulled, in raw_landing and in attribution_warehouse. Running the same day twice, or ten times, leaves exactly one copy.

Common follow-up questions

• A client switches to a time-decay model and wants 90 days of credit restated. What runs? (Tests whether the overwrite design lets one client's history be rebuilt by re-running the same path over a longer range, without touching anyone else's partitions.)
• An ad platform starts revising numbers for 30 days instead of 7. What changes? (Only the re-pull window grows. The overwrite semantics already absorb it, at the cost of more data scanned per run.)
• A source's schema changes and `load_validation` starts failing every day. What does the morning report show? (Tests whether the candidate can let healthy sources publish with the broken one flagged, rather than holding the whole run.)