Every Dataset Needs a Paper Trail

A hard 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
hard
Seniority
L6

Problem

Clinical trial systems, commercial sales databases and patient support program feeds deliver about 40 million rows a day, mostly sales, and no dataset from any of them is exempt from governance. All of it lands in one shared warehouse that analysts and researchers read from. Design the ingestion pipeline and governance framework.

The FDA has opinions about your data pipeline.

Business requirements

Worked solution and explanation

The trap

This is a placement problem dressed as an ingestion pipeline. Anyone can draw three sources flowing into a warehouse, then drop a quality box, a lineage box and an access box somewhere on the canvas. What separates candidates is whether each control sits on every path, at the one point where it can actually stop or record something. Put the check beside the load instead of in front of it, and bad rows go live anyway. Reconstruct lineage from the repo, and the first audit question becomes a code search. Filter inside each dashboard, and every new tool is a hole.

Walk the requirements

Step 1: Funnel every source through one gate

clinical_trial_systems, commercial_sales and patient_support all feed quality_check before anything else touches them. Sales is the biggest and least sensitive feed, which is exactly why candidates give it a shortcut. The mandate covers every dataset, so the gate is inline and blocking: a dataset that fails does not move on.

Step 2: Record lineage from the writer

warehouse_loader is the only stage that writes governed_warehouse, so it is the stage that publishes each run's inputs, outputs and code version to lineage_catalog. Because the trace is emitted at load time, it describes what actually ran, not what the code was supposed to do.

Step 3: Enforce access between the warehouse and its readers

access_policy sits between governed_warehouse and warehouse_readers, so analysts in dashboards and researchers in notebooks hit the same policy. Filtering inside each reporting tool trusts every query author, and every new tool, to remember the rule.

The reference design

Every source passes `quality_check` before `warehouse_loader` writes the warehouse; the loader records lineage, and readers reach data only through `access_policy`.
  • clinical_trial_systems (source)
  • commercial_sales (source)
  • patient_support (source)
  • quality_check (quality gate, Great Expectations)
  • warehouse_loader (transform, dbt)
  • lineage_catalog (consumer, OpenLineage)
  • governed_warehouse (storage, warehouse)
  • access_policy (quality gate, custom)
  • warehouse_readers (consumer)
  • clinical_trial_systems -> quality_check
  • commercial_sales -> quality_check
  • patient_support -> quality_check
  • quality_check -> warehouse_loader
  • warehouse_loader -> governed_warehouse
  • warehouse_loader -> lineage_catalog
  • governed_warehouse -> access_policy
  • access_policy -> warehouse_readers
Controls bolted on beside the flow
A quality job that scans the warehouse after the load, a lineage page maintained by hand from the repo, and row filters in each dashboard. Each control exists, and each one can be bypassed or fall out of date.
Controls on the flow
quality_check in front of warehouse_loader, lineage emitted by the loader on every run, and access_policy in front of every reader. A dataset cannot go live without passing all three.

Common follow-up questions