Design a Real-Time Multi-Source Change Data Pipeline

Design a real-time data-processing system that consumes changes from operational databases, combines data from multiple sources, and maintains a queryable output dataset with low latency. Walk through the architecture and explain how correctness is preserved through updates, deletes, ordering differences, stateful processing, failures, scaling, and delivery to the destination. ### Constraints & Assumptions - The source describes a continuously updated derived dataset, not a one-time data export. - Source databases and the destination are unspecified. Discuss the capabilities your design requires instead of assuming a particular product or connector guarantees them. - No throughput, latency target, retention period, source count, or recovery objective is supplied. Treat these as sizing and consistency questions; do not invent numeric requirements. - **Practice example for making update semantics concrete:** one source contains customers keyed by customer ID; another contains orders keyed by order ID with a customer ID. Maintain an inner-joined order-and-customer view. This example illustrates a multi-source combination rather than a company-specific schema. - Explain whether the output is eventually consistent across independent sources and what stronger guarantees would require. ### Clarifying Questions to Ask - Does “combines” mean joins, aggregations, enrichment, or a combination, and which source keys connect the records? - Are transaction logs or change streams available, and do they carry stable keys, source positions, transaction boundaries, and enough information to process deletes? - Must the output reflect an atomic cross-source snapshot, or may independently captured source changes become visible at different times? - Can the destination perform keyed upserts and deletes, and can a write be retried safely after an ambiguous acknowledgment? - What end-to-end latency, recovery time, data volume, and query freshness are required? ### Part 1 — Capture Changes and Initialize State Choose an architecture from operational databases to the queryable destination. Explain how an initial snapshot meets the ongoing change stream without losing or applying changes incorrectly. Identify where replayable data and processing progress are retained. #### What This Part Should Cover - Consistent snapshot and change-position coordination at each source. - Durable change transport, replay boundaries, and schema/version information. - A path to the queryable destination that separates source capture from downstream processing pressure. ### Part 2 — Maintain the Derived Dataset Correctly Use the customer/order example to explain inserts, updates, deletes, and arrivals from different sources in different orders. Describe the state required to retract obsolete output and recompute affected rows. #### What This Part Should Cover - Stable source identity and ordering within the scope the source actually guarantees. - Customer and order state, reverse lookup of dependent orders, and output deletion semantics. - The difference between convergence after all changes arrive and atomic consistency across databases. ### Part 3 — Recover and Deliver Without Corrupting Results Explain what happens when a processor crashes before or after writing to the destination, when a write acknowledgment is lost, or when a change is delivered twice. Define the relationship among checkpoints, state, source offsets, and destination commits. #### What This Part Should Cover - A recovery boundary that prevents committed progress from getting ahead of durable effects. - Idempotent or transactional output writes, including deletes and duplicate events. - Replay, poison records, reconciliation, and the limits of an “exactly once” claim. ### Part 4 — Scale the Pipeline and Keep Output Queryable Describe partitioning, state redistribution, backpressure, skew, and destination capacity. Explain how you would detect growing lag or silent divergence while queries continue to read the output dataset. #### What This Part Should Cover - Partitioning that brings join-related keys together without claiming a total order across all sources. - State and replay retention based on correctness requirements, not arbitrary expiration. - Query visibility during updates and backfills, plus metrics and reconciliation that distinguish freshness from correctness. ```hint Track a customer deletion An order may still exist in its source when its customer is deleted. Determine which joined rows must disappear and what state is needed to find them. ``` ```hint Place the crash boundary Suppose the destination accepts an update but the processor crashes before saving its progress. Work out what replay will attempt and how the destination can recognize it. ``` ### What a Strong Answer Covers - A complete capture-to-query architecture with explicit assumptions about source and sink capabilities. - Concrete propagation of changes through a stateful multi-source join, including retractions. - Honest ordering and consistency guarantees, durable recovery, and destination semantics that match those guarantees. - Scaling and operational evidence that addresses both processing lag and incorrect derived data. ### Follow-up Questions - How would a customer update affect the design if that customer has far more orders than other customers? - What changes if the destination supports append-only writes but no keyed updates or deletes? - How would you rebuild the output after a transformation change while keeping a clearly identified version available for queries?