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

We run a social media platform that pulls engagement events from the mobile apps in real time, alongside daily monetization files from ad partners and a handful of internal service pulls. Analysts need all of it queryable in the warehouse, but the team keeps redefining metrics like what counts as an active story viewer, so past numbers must stay rebuildable from each source's original, untouched records. Design the pipeline that lands these varied-cadence sources and shapes them for analytics.

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 redefining what counts as an active story viewer and recomputing the past from it. That single sentence 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 that 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, S3 / SFTP daily)
  • internal_pulls (source, Airbyte daily)
  • schema_registry (transform, Confluent Schema Registry)
  • raw_landing (storage, Iceberg lakehouse)
  • orchestrator (orchestrator, Airflow)
  • elt_transforms (transform, dbt on warehouse)
  • quality_gate (quality gate, Great Expectations)
  • analytics_warehouse (storage, BigQuery)
  • ops_dashboard (consumer, Grafana)
  • bi (consumer, Looker)
  • 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