The Whiteboard Exercise

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

L5

Problem

Design the nightly pipeline that brings an e-commerce marketplace's data into the warehouse: the transactions from Postgres, the customer detail from the CRM through its rate-limited API, and the product detail from the partner catalog, delivered daily as CSV files over SFTP.

Marker in hand. Draw the whole thing.

Business requirements

• Roughly 50 business analysts open the daily dashboard at 8am and need yesterday's data by then, with all three sources loaded.
• Analysts slice transactions by customer, product, channel, and date in interactive queries; querying raw transaction rows for every report doesn't scale.
• The partner sometimes ships product-catalog CSV files with extra columns or changed headers; the nightly load can't fail when a layout shift appears.

Worked solution and explanation

What this really is

This is a deadline-bound multi-source merge dressed up as a generic ETL whiteboard. Three sources need three extraction shapes: an incremental pull from Postgres, a rate-limited paginated CRM API, and a partner CSV dropped over SFTP. All three must converge into one dimensional model before 50 analysts open the dashboard at 8am. Anyone can draw boxes from source to warehouse. What separates candidates is who owns the clock and where a bad file stops. Load raw transactions into a flat table and every slice scans full history. Let the partner's renamed header reach the loader unguarded and the whole night fails, so 8am arrives with no data at all.

Walk the requirements

Step 1: Extract each source on its own terms

Postgres pulls incrementally on a watermark. The CRM API needs pagination with exponential backoff, because the rate limit will throttle you mid-run and a naive retry loop only hammers it harder. The catalog is a file pickup. One uniform pull box hides three different failure modes, and the interviewer will ask about each one.

Step 2: Give the 8am deadline an owner

Airflow schedules the nightly DAG and orders the dependencies so dimensions load before facts. All three sources flow through it, so the midnight-to-8am window is one place's job. Without it, nobody notices the CRM pull crawling at 3am until the analysts do.

Step 3: Gate the partner file before staging

The schema_validator accepts additive columns as nullable and quarantines renames or missing columns. The night continues on yesterday's catalog while the team fixes the mapping and replays. Failing on any difference turns every partner change into a missed morning.

Step 4: Model the slices, not the rows

dbt aggregates the staged data into a star: a transaction fact keyed to dim_customer from the CRM, dim_product from the catalog, plus channel and date. Analysts slice the modeled tables instead of scanning raw transaction history on every report.

The reference design

One nightly flat load

Raw transactions land in one wide table. Every report scans full history, and a partner header change crashes the loader.

Orchestrated star schema

Facts keyed to dim_customer and dim_product, built by dbt. Drifted files are stopped at the gate, and one orchestrator owns ordering for the 8am window.

Common follow-up questions

• The pipeline runs late at 3am. How do you find out before the analysts do? (Alerting on the orchestrator and on the freshness of the warehouse tables, an addition beyond the minimal reference.)
• The partner adds a column the team actually wants. How does it reach the dashboard? (Additive drift passes the gate as nullable, lands in staging, and enters the model on the next deploy.)
• The CRM moves to nested JSON, cursor pagination, and a lower rate limit. What changes? (Only the CRM extraction and its landing mapping change. Staging, transform, and warehouse stay put.)