Design a pipeline that ingests two real-time data sources into a data warehouse

You are given two real-time data sources and asked to design the data pipeline that brings them into the data warehouse: how data gets from the sources into the warehouse, what happens inside the warehouse, and how the design scales. The report does not name the two sources, so what they contain, how they relate to each other and who consumes them are for you to establish with the interviewer. The round is spoken only, with no whiteboard, so keep the answer structured and concise enough to follow by ear. ```hint Two streams, one record Ask how a record from one source relates to records from the other, and what the pipeline should do when one side's matching event shows up much later than the other's. ``` ```hint Freshness drives the shape Find out which consumers truly need data within seconds before you choose between streaming everything and loading in small batches. ``` ### Clarifying Questions - How often does each source send data, how many events per second arrive on average and at peak, and how large is each event? - Who consumes the data downstream (dashboards, analysts, finance, machine learning, operational alerts), and does each need it in real time or is a batch load enough? - Can a source send the same event more than once, and does every event carry a unique id and the time it happened? - How late can an event arrive, and can the two sources fall out of step with each other? - Who owns the producers, and how often do their schemas change? - How long must raw and modeled data be kept, and will history need to be reprocessed? ### What a Strong Answer Covers - Requirements and explicit assumptions (frequency, consumers, freshness, duplicates, scale) and a capacity estimate before any tool is named - An end-to-end architecture from ingestion through raw, cleaned and modeled layers, with the data model of each layer - Tool choices justified by the requirements, with their trade-offs - Where the data contract lives, what it enforces and why that placement helps - Transformation and orchestration, data quality checks and how results are validated - Deduplication, late events and two sources that are out of step - Backfill, failure recovery, scaling, cost and the metrics that show the system is healthy ### Follow-up Questions - How would you keep the cost of this pipeline under control? - How does your design change if the volume is ten times larger, or a tenth of what you assumed? - An event arrives a day late. Walk through what happens to it and to the tables that depend on it. - Which metrics would you monitor to know the pipeline is working, and which of them would page someone?