Preparing your design workspace

Loading the problem and your latest saved attempt…

Design an Hourly ETL from a Document Database to a Warehouse

Copy changing, nested documents from an app's document database into warehouse tables on a schedule, with every change and delete caught, no duplicates, and no half-loaded tables.

Problem statement

Analysts want the app's data in a cloud data warehouse, refreshed on a schedule. The app keeps that data in a document database such as MongoDB, and the warehouse is a columnar one such as Amazon Redshift. Design the batch pipeline that moves the data across.

For this exercise, assume the app is business software with about 40 collections, such as employees and pay runs. Documents are nested, with sub-objects and arrays, and different documents in one collection may have different fields. The app updates documents in place and sometimes deletes them for good.

A run starts every hour. After a run, each warehouse table must match its collection as of that run's cutoff time: every new, changed and deleted document is reflected, nothing appears twice, and analysts never see a table that is half loaded. The pipeline must not slow down the app.

Also assume the warehouse keeps only the current state of each document; history tables, real-time streaming, reverse loads back into the app, and the analysts' own reports are out of scope.

What to build

  1. Each hourly run picks up every document created, changed or deleted since the previous run's cutoff, without slowing the app's database.
  2. Nested documents become typed warehouse tables, with arrays in child tables, and a new field in the source does not break a run.
  3. A run makes each table match its collection as of the cutoff, with no duplicates, and analysts see either the old or the new version of a table, never a mix; repeating a run gives the same result.
  4. Operators can reload one collection from scratch and can see each run's status, cutoff, row counts and failures.

At scale

  • For this exercise, assume about 2 billion documents and about 1 TB of data in total, with the largest collection holding 500 million documents (about 300 GB).
  • For this exercise, assume about 20 million documents change per day, up to three times that on pay-run days, and about 1% of changes are hard deletes.
  • For this exercise, assume each hourly run must finish within 30 minutes, so warehouse data is never more than 90 minutes old, and a full reload of the largest collection must finish within 12 hours.
  • For this exercise, assume the app's database runs as a primary with two replicas, and the app's read and write latency must not rise noticeably while the pipeline runs.