The Metric That Moved

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
mid
Asked in
Snap

Problem

Engagement events stream from the mobile apps at about 20,000 per second, monetization files arrive once a day from a dozen ad partners, and three internal services are pulled nightly. Definitions like an active story viewer get rewritten every quarter. Design the pipeline that lands these sources and shapes them into warehouse tables for analysts.

Business requirements

Worked solution and explanation

Why this problem exists in real interviews

The prompt reads like a generic extract-transform-load question, but the load-bearing clause is the one about rewriting what counts as an active story viewer and recomputing the past from it. That single requirement decides the whole architecture: it rules out transforming before you load. If you shape the data on the way in and only keep the shaped tables, then the first time a definition changes you have no originals to replay, and two years of history are frozen at the old definition. The real skill being probed is whether you place the transform after the load, not before it, and whether you can say why that costs more warehouse compute and why it is worth it here.

The default answer people reach for is a transform-then-load pipeline: parse the app events and partner files, apply the business rules, and write clean, narrow tables straight into the warehouse. It is cheaper and the tables look tidy. Then the metrics team changes the definition of an active viewer, asks for the last 24 months recomputed, and the pipeline cannot do it: the raw signal the new rule needs was dropped at ingest. Now you are re-ingesting from partners who no longer have the files, and app events that were never stored raw are simply gone.


Break down the requirements

Step 1: Decide where the transform runs before anything else

The reproducibility requirement is not a nice-to-have here; it is the axis the design turns on. Because a metric changes about once a quarter and each change triggers a 24-month backfill, the transforms must be a pure function of a raw layer you still hold. That forces load-raw-then-transform. State the tradeoff out loud: in-warehouse transforms burn more warehouse compute and storage than transform-on-ingest, and that is the price you pay to be able to rebuild history.

Step 2: Split streaming from batch per source, not globally

Live app engagement events are the only source that earns a streaming path, and mostly for the small real-time ops dashboard. Partner monetization files land daily; internal service pulls are batch. Most app-event analytics tolerate a few hours of lag. Streaming all of it would multiply cost for zero analyst benefit. Two freshness tiers, sized independently, is the correct shape.

Step 3: Guard the boundary between raw and analyst tables

Partners rename fields without warning and the app team versions its schema weekly. A schema registry versions each source and flags drift at ingest, so a rename fails loudly instead of quietly nulling a column three tables downstream. Quality checks sit between the raw landing zone and the warehouse tables analysts read, and an orchestration layer owns the cross-source dependencies and the reruns.


The reference architecture

Every source lands raw and schema-versioned before business logic runs. An orchestrator owns cross-source scheduling and the backfill reruns. Transforms read raw and write the warehouse, so a metric change is a rerun over old partitions. Only the live app stream also feeds the real-time ops dashboard directly.
  • app_event_stream (source, Kafka)
  • partner_files (source)
  • internal_pulls (source, Airbyte daily)
  • schema_registry (transform, schema registry)
  • raw_landing (storage, Iceberg lakehouse)
  • orchestrator (transform, Airflow)
  • elt_transforms (transform, dbt on warehouse)
  • quality_gate (quality gate, Great Expectations)
  • analytics_warehouse (storage, warehouse)
  • ops_dashboard (consumer, Grafana)
  • bi (consumer, dashboard)
  • app_event_stream -> schema_registry
  • partner_files -> schema_registry
  • internal_pulls -> schema_registry
  • schema_registry -> raw_landing
  • app_event_stream -> ops_dashboard
  • raw_landing -> elt_transforms
  • elt_transforms -> quality_gate
  • quality_gate -> analytics_warehouse
  • analytics_warehouse -> bi
  • orchestrator -> raw_landing
  • orchestrator -> elt_transforms
  • Sources: app_event_stream, partner_files, internal_pulls
  • Land raw: schema_registry, raw_landing
  • Control plane: orchestrator
  • Transform + gate: elt_transforms, quality_gate
  • Serve: analytics_warehouse, ops_dashboard, bi
Transform-then-load (the trap)
Business rules run at ingest; only shaped tables are stored. Cheaper storage, tidy tables. But when the active-viewer definition changes, the raw signal the new rule needs is gone, and the 24-month backfill is impossible.
Load-raw-then-transform (the fit)
Sources land raw and partitioned; rules run as warehouse transforms over raw. Costs more warehouse compute and storage, but a definition change is a deterministic rerun over historical partitions. History is always rebuildable.

Common follow-up questions