The Provider That Sometimes Sleeps
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
- Asked in
- Jump Trading
Problem
Our market data provider bills per request, goes dark for hours a few times a year, and its license requires us to keep every raw file unchanged for audit. We need the prior day's price and volume data pulled automatically and loaded into our research database before pre-market models start at 7am. Design the ingestion pipeline.
The models run at dawn. The data has to be there first.
Business requirements
- Quants run pre-market models each morning and depend on the prior day's data being present before they start; a missed session has already broken a model run, so a failed or late pull has to reach the team automatically, not be discovered at model-run time.
- The provider charges per request and has multi-hour outages a few times a year; a naive script burns the daily budget on blind retries before the outage resolves, so the pull and its retry logic have to be driven by an orchestrator.
- Our licensing agreement requires raw files be retained for audit and never modified or deleted after ingestion, so the raw landing zone has to be durable storage written straight from the pull, distinct from the downstream transformed research database.
Worked solution and explanation
Why this problem exists in real interviews
A daily pull from a paid provider with 7am quant deadlines, multi-hour outages a few times a year, and a licensing requirement that raw files be retained unchanged. The trap is a naive retry loop that burns the daily budget during an outage, or treating retention as 'we'll keep it somewhere.'
The default reach is a manual pull script that runs at midnight and a retry loop on failure. The first multi-hour outage burns the daily request budget before it resolves, and the next morning's models miss data. Raw files get overwritten when the team reruns the pull, because retention was never an explicit boundary.
Walk the requirements
Step 1: Pull on schedule with bounded retries that respect the budget
The orchestrator runs the daily pull on a schedule. Failures retry with exponential backoff up to a fixed count. Once that count is spent, the orchestrator pages on-call rather than burning more requests on a provider that is down. A naive retry loop drains the daily budget on retries before a multi-hour outage resolves; the bounded retry is what keeps the next pull affordable.
Step 2: Raw files land immutably for audit
Each pulled file is written straight to the raw archive, and the storage policy denies modification or deletion. A rerun of the pull writes a new versioned file and the original stays. Without immutability the audit cannot prove the file as received; the immutable archive is the contract. The research database is loaded from the archive, never from the provider, so rebuilding it costs no paid requests.
Step 3: Alert before 7am, not at 7am
The orchestrator's alert fires before 7am if the daily file has not arrived or has not loaded. On-call has hours to chase the provider, fire a manual pull, or escalate. In the 'we'll find out at 7am when models break' version, the quant team is the first to notice.
The shape that fits
- provider_api (source, API)
- orchestrator (transform, Airflow); retries: 5; backoff: exponential; alerting: page if pull failed or not landed by 6am
- pull_job (transform)
- immutable_raw_archive (storage, object storage)
- research_database (storage, warehouse); freshness: < 24h
- quants (consumer, Jupyter)
- provider_api -> pull_job
- orchestrator -> pull_job
- pull_job -> immutable_raw_archive
- immutable_raw_archive -> research_database
- research_database -> quants
Common follow-up questions
- The provider has a documented multi-hour maintenance window. How does this design handle a known outage without firing alerts every minute? (Tests whether the candidate sees scheduled maintenance windows as configuration the orchestrator reads. Retries pause during the window and resume, with a single alert if the post-window pull still fails, so the orchestrator treats scheduled and unexpected outages differently.)
- The audit team asks for the file received on a date six months ago. Where does this design point them? (Tests whether the candidate sees that the raw archive is immutable per day: a query by date returns the file as received. The research database may hold transformed data; the archive answers 'what did we get' separately from 'what did we load.')