Data warehouse ETL architecture.
Land the raw data first and transform it later, because the transformation you want in six months is not the one you would write today.
The components
Every row below is read from the graph that produced the diagram above, so the two cannot disagree.
| Component | Tier | Why it is there |
|---|---|---|
| Source databases | Client | Supporting component |
| SaaS APIs | External | Supporting component |
| CDC connector | Application | Reads the write-ahead log, not a nightly SELECT |
| Ingest jobs | Application | Supporting component |
| Orchestrator | Application | DAGs with real dependencies and retries |
| Transform layer | Application | SQL models, version controlled and tested |
| Data quality | Application | Fails the DAG, does not just log a warning |
| Data lake | Data | Raw, immutable, replayable |
| Kafka | Data | Supporting component |
| Warehouse | Data | Supporting component |
| BI & dashboards | Infrastructure | Supporting component |
Design decisions worth arguing about
A diagram shows what was chosen. It does not show what it cost, and that is usually the part that matters in a review or an interview.
Extract, load, then transform
Transforming before landing means the only data you keep is what your current logic produced, so a bug or a changed definition requires re-extracting from sources that may no longer have the history. Landing raw first makes every transform replayable. It costs storage for data you may never query, which is cheap, and it is the decision people most regret not making.
Change data capture rather than nightly full extracts
Reading the write-ahead log gives near-real-time changes and puts almost no load on the source database, where a nightly full table scan does the opposite on both counts. The cost is operational: CDC is more moving parts, it is sensitive to schema changes, and a connector that falls behind is not obvious until someone notices yesterday's numbers.
Transforms as version-controlled SQL
Transformation logic buried in a scheduler's UI or in notebooks cannot be reviewed, tested or rolled back. Expressing it as SQL models in a repository makes it ordinary software. The cost is that analysts have to work through code review, which is a genuine friction and is also the entire reason the numbers become trustworthy.
Quality checks that fail the run, not that log a warning
A warning nobody reads is not a check. Failing the DAG means bad data does not reach the dashboards, at the cost of dashboards being stale instead of wrong. Stale is recoverable and visible; wrong is neither, and a decision made on wrong numbers is not undone by fixing them later.
How it changes with scale
Warehouse cost is driven by query patterns rather than by storage, so partitioning and clustering to match how people actually query is where the money is. Pipeline runtime grows with the number of models more than with data volume, which makes DAG structure and incremental models the lever once the model count gets large.
Where it breaks first
Silent schema drift. A source adds or renames a column, the pipeline keeps running because nothing errors, and a downstream metric quietly changes meaning. This surfaces weeks later as a disagreement between two dashboards, which is why schema assertions belong in the pipeline rather than in a runbook.
Draw this yourself
Open the Diagram tab and describe the system. The agent emits a semantic graph rather than coordinates, so you can edit the components and the layout re-solves instead of drifting.
When the shape is right, Implement in code turns the canvas into a markdown specification, every component, every relationship and the notes, and starts a real turn in the Code tab with it.
Questions about this design
More templates
- URL Shortener System DesignThe canonical read-heavy system: roughly a hundred reads for every write, and a redirect that has to be fast enough that nobody notices it happened.
- Chat Application System DesignLong-lived connections change everything: the hard part is not storing messages, it is knowing which of your servers is holding the socket you need to write to.
- Payment System DesignThe only system on this list where being approximately right is indistinguishable from being wrong, and where the provider, not you, holds the truth.
- RAG Pipeline ArchitectureAlmost every RAG system that disappoints is failing at retrieval, not generation, and the architecture is what decides whether you can tell.