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

Our product team stores application data as nested, schemaless documents in MongoDB, but the analytics team needs it as flat tables in Redshift they can query with ordinary joins. The nightly full export takes eight hours and refreshes only once a day, while reports run against Redshift throughout the day, so design a batch pipeline that pulls just the documents that changed, reshapes them, and stages them before loading into the warehouse.

Two databases. One direction. No data left behind.

Business requirements

Worked solution and explanation

Why this problem exists in real interviews

Application data is nested and updates continuously. Reporting wants flat tables that standard SQL can join. Reports run all day, so the load can never produce a moment when half the tables are updated and half aren't. The interesting pressure isn't the flattening, it's the atomicity.

First instinct is to flatten and load each table as it's transformed. The orders table updates first, then line_items, then customers. A report that runs partway through the load joins the new orders to yesterday's customers, gets a number that doesn't reconcile, and an analyst files a data-quality ticket the next morning. The bug isn't in the flattening; it's in not making the run visible all-or-nothing.

Walk the requirements

Step 1: Pull only the changed documents and remember where you stopped

The eight-hour nightly full export rereads all 500M documents to move the ~2M that actually changed. Read the source through its change feed instead and pull only the changed documents, persisting a resume token after each run so the next run starts exactly where the last one stopped. On the canvas this is an 'idempotency: resume_token' annotation on the extraction step, which is also what keeps a reprocessed batch from creating duplicate rows.

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

Reports query with standard SQL joins. That means the extracted documents have to be unpacked into rows where each repeated structure becomes its own table, order header, order line, address, joinable on the natural keys. The flattening rules belong in the transformation layer (Spark, dbt, or whatever your team reaches for), separate from the warehouse load, so when the source adds a field you change one place. Write the flattened output to object storage (S3) so the warehouse can bulk-load it with COPY.

Step 3: Make each load atomic by swapping from staging in one step

COPY the flattened files from S3 into a staging schema in the warehouse. Once every table for that run is built and validated, swap them into the production schema in a single move (a rename, a view repointing, or a transactional batch, whatever your warehouse supports cheaply). On the canvas this is an 'idempotency: staging_table' annotation on the load step. Reports running mid-load see either the prior run or the new one, never half-and-half.

The shape that fits

Read only the changed documents through the source's change feed (resume token remembers where the last run stopped), flatten them, land in S3, then COPY into a staging schema. The swap into production is one atomic step so reports never see a half-loaded run.
  • document_source (source, MongoDB)
  • cdc_extraction (transform, Flink); idempotency: resume_token
  • flattener (transform, Spark)
  • s3_staging (storage, S3)
  • staging_schema (storage, Redshift); idempotency: staging_table
  • warehouse_swap (transform, SQL); idempotency: staging_table
  • warehouse_prod (storage, Redshift); freshness: < 24h
  • reports (consumer, Tableau); freshness: < 24h
  • document_source -> cdc_extraction
  • cdc_extraction -> flattener
  • flattener -> s3_staging
  • s3_staging -> staging_schema
  • staging_schema -> warehouse_swap
  • warehouse_swap -> warehouse_prod
  • warehouse_prod -> reports

Common follow-up questions