Design an incremental load pipeline from a transactional DB to a warehouse

WHAT THIS TESTS: This question tests whether you can architect a production-grade data movement layer between an OLTP source and an OLAP destination. The interviewer cares about your awareness of consistency boundaries, the ability to reason about mutating data including inserts updates and deletes, and your knowledge of specific incremental patterns beyond naive polling. They also want to see if you consider operational realities like pipeline failures, late-arriving data, and schema changes.

A GOOD ANSWER COVERS: A strong answer presents at least two distinct techniques and explicitly weighs their trade-offs. First, timestamp or high-water-mark polling: the pipeline records the maximum modified timestamp from the last successful run and selects rows where the updated_at column exceeds that mark. This is simple but misses deletes, struggles with late-arriving rows, and assumes monotonic clocks. Second, Change Data Capture by reading the database transaction log such as MySQL binlog or PostgreSQL WAL: this captures every mutating event including deletes, preserves ordering, and enables exactly-once semantics when paired with a durable offset store. Third, incremental queries over open table formats such as Apache Hudi or Apache Iceberg: ingest into the lake first, then use Hudi incremental query or Iceberg incremental read to pull only appended or changed files into the warehouse, using a MERGE SQL command in Amazon Redshift to apply upserts. A great candidate also discusses merge logic, idempotency via deterministic job runs, and failure recovery by checkpointing watermarks or offsets outside the pipeline process.

COMMON WRONG ANSWERS: The biggest red flag is recommending dual writes, where a single job writes simultaneously to the data lake and the warehouse. This pattern is easy to implement but error-prone because the two transactions are independent; a failure in one creates an inconsistency that is hard to reconcile. Another weak pattern is suggesting a daily full extract without acknowledging the performance cost and network overhead. Candidates also stumble by proposing simple timestamp filters without a plan for out-of-order updates, deleted records, or schema evolution.

LIKELY FOLLOW-UPS: The interviewer may ask how you would handle hard deletes in the source that must be propagated to the warehouse, how you guarantee exactly-once delivery during a job restart, or how you would evolve the schema of the destination table without breaking downstream dashboards. They might also probe the latency trade-off between near-real-time CDC and batch incremental loads, or ask how you would backfill a specific partition after discovering a bug in the transformation logic.

ONE CONCRETE EXAMPLE: Suppose you are syncing a large orders table into Amazon Redshift. You configure AWS Glue to read the MySQL binlog via CDC, writing change events into S3 as Apache Hudi tables. A subsequent Glue job runs every fifteen minutes, querying Hudi incrementally for all commits since the last checkpoint, then uses the Redshift MERGE command to insert new orders and update changed ones while deleting records marked as removed in the binlog. If the Glue job fails after writing to Redshift but before committing the checkpoint, the next run reuses the same deterministic batch ID, making the MERGE idempotent and preventing duplicates.

Source: aws.amazon.com