All at Once

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
L4

Problem

Application data lives as nested, schemaless documents in MongoDB, about 400 million of them, of which roughly 2 percent change each day. The nightly full export into Redshift takes eight hours. Design a batch pipeline that pulls only the changed documents, reshapes them, and stages each refresh before it loads into the warehouse.

Two databases. One direction. No data left behind.

Business requirements

Worked solution and explanation

Why this problem exists in real interviews

This is an atomic warehouse load dressed up as a MongoDB-to-Redshift migration. Anyone can flatten a document. The trap is loading each flattened table straight into the tables analysts are querying, one after another. Reports run all day, so a query that lands mid-load joins today's orders to yesterday's customers, the revenue total stops reconciling, and the team writes a 'do not query during the load' runbook entry instead of fixing the gap.

The second pressure is volume. The eight-hour export rereads roughly 400 million documents to move the 8 million or so that changed. Pulling only the changes shrinks the run, and staging the refresh makes it all-or-nothing. Those are two separate guarantees, and the design gives each one its own stage.

Walk the requirements

Step 1: Pull only the changed documents

Read the source's change feed instead of exporting every collection, and persist a resume position after each run so the next run starts exactly where the last one stopped. Two percent of the collection is the whole job. The other 98 percent never needs to move.

Step 2: Flatten nested documents into relational tables before the warehouse

Each repeated structure becomes its own table (order header, order line, address), joinable on natural keys. Keep the flattening rules in one transform, separate from the load, so a new source field is a one-place change. A Spark job handles orders that nest several hundred line items without trouble.

Step 3: Assemble each refresh in staging, then load it in one move

Write the flattened files to object storage. The load reads only from there: COPY every table into a staging schema, validate it, then swap it into production in one transaction, either a rename or a view repoint. Reports running mid-load see the prior refresh or the new one, never half of each.

The shape that fits

Read only the changed documents, flatten them, land each refresh in object storage, then load it into the warehouse in one step so reports never see a half-loaded run.
  • mongodb_source (source, MongoDB)
  • changed_document_extraction (transform)
  • flattening_transform (transform, Spark)
  • staging_area (storage, object storage)
  • warehouse_tables (storage, Redshift)
  • mongodb_source -> changed_document_extraction
  • changed_document_extraction -> flattening_transform
  • flattening_transform -> staging_area
  • staging_area -> warehouse_tables

Common follow-up questions