Build a data platform for a global SaaS product
Six sources, six consumers, and a starting point of one nightly CSV export that two people rely on. Eleven requirements arrive in order, then ten things go wrong. The exercise is not to draw the finished architecture — it is to arrive at it one forced step at a time and keep it trustworthy.
Millions of users across several regions, growing.
PostgreSQL → a nightly CSV export → one dashboard. It works. Two people rely on it.
Nothing is added because it is standard. Every component must be forced by a requirement that has actually arrived, and every one you add is something you now operate.
The sources
Each one is awkward in its own specific way, and the awkwardness is the design constraint — not the volume.
Transactional tables — users, accounts, subscriptions, orders. Current state only.
It holds the truth and forgets history. Hard deletes leave no trace, and the schema changes whenever a feature ships.
High-volume clickstream and product usage, emitted by web and mobile clients.
Clients are offline sometimes, so events arrive late by minutes or by days. Duplicates are normal. The client version decides the payload shape.
Charges, refunds, disputes and payouts, available by API and by webhook.
You do not control it. It is the authoritative source for money, its webhooks are at-least-once, and its idea of a "transaction" does not match yours.
Tickets, conversations and satisfaction scores in a SaaS product.
API-only, rate limited, paginated, and it contains free text written by customers — which means it contains everything.
Partner files arriving on a schedule that is loosely honoured.
CSV with no schema, occasional encoding changes, and a partner who reissues a corrected file for last Tuesday without telling anyone.
Prompts, tool calls, retrieved documents, outcomes and user feedback from an in-product assistant.
The most useful field for debugging is the one containing the customer's own words, and the volume grows with usage rather than with headcount.
The consumers
Design from this column, not from the one above it. What each of these needs — and what each of them does when the data is wrong — is what decides every choice you are about to make.
A small number of headline metrics, defined once, that do not change after being reported.
Daily is fine. Correct is not optional.
Trust collapses across every dataset at once, including the ones that were right.
Revenue reconciled to the payment provider, reproducible months later, with every adjustment explainable.
Monthly close. Reproducibility matters far more than latency.
A restatement, and a conversation about controls rather than about pipelines.
Event-level behaviour, cohorts, funnels, experiment results.
Hours. Tolerant of small gaps, intolerant of silent definition changes.
A shipped feature is judged on a broken metric, and the decision is not revisited.
Features computed identically for training and for serving, with the values that were true at prediction time.
Training is batch; serving is immediate. The two must agree.
A model trained on features it will never see again, degrading quietly in production.
A sampled, privacy-filtered set of production traces with outcomes attached.
Weekly refresh, versioned so a score is comparable across runs.
Evaluation scores move because the dataset moved, and nobody can tell that from the model improving.
An index of documents with an embedding version, a chunk strategy and a source version recorded.
Minutes for new documents; a full re-index is a migration.
Retrieval silently returns worse results, which no monitor detects and every user feels.
Ask these first, in this order
Before any component is chosen. The order is the argument: you cannot decide batch or streaming until you know the grain, and you cannot decide the grain until you know who is asking.
- 1What is the source of truth for each business concept?
Three systems hold a customer's country and they disagree. Until one is authoritative, every reconciliation is an argument rather than a check.
- 2What is the grain of each dataset?
If you cannot say what one row represents, you cannot write a correct aggregate against it — and the wrong one will not raise an error.
- 3Batch or streaming, per source?
It is a per-source decision driven by what a consumer needs, not a platform-wide identity. Most sources here should be batch.
- 4How do we replay?
Every fix you will ever make depends on being able to reprocess. Decide the mechanism and its window before you need it.
- 5How do we deduplicate, per source?
At-least-once is the normal case across every source here. The business key differs per source and an id-based dedup will miss producer retries.
- 6How is schema evolution handled at each boundary?
Each hop chooses to absorb, reject or pass on a change. Deciding that per-hop in advance is what separates a platform that survives upstream changes from one that breaks weekly.
- 7How is data partitioned?
It is the highest-leverage and least-visible decision in the platform, and it is expensive to change once a hundred queries depend on it.
- 8How do we model facts and dimensions, and where does history need preserving?
Finance needs the customer's country at order time. That is a Type 2 dimension, and if it is not built before the change happens the information is gone.
- 9How is data quality measured, and by whom?
Every check has a blind spot, so quality is a portfolio. Ownership decides whether it is maintained.
- 10How is freshness monitored, per dataset, against a stated SLO?
A platform average hides the one table that stopped on Friday. The word "complete" in "latest complete data" does all the work.
- 11How do we backfill without corrupting what is already correct?
A backfill is a write to production data. Bounded range, isolated compute, validation against the source, and an atomic publish.
- 12How do we trace lineage, in both directions?
Upstream during an incident, downstream before a change. Without the second one, blast radius is discovered rather than known.
- 13How is sensitive data classified, minimised, masked and deleted?
The copy inherited the obligation and none of the mechanism. Designing this on day one is far cheaper than discovering it during an audit.
- 14How do we control and attribute cost?
A shared platform makes every cost everyone's and therefore nobody's. Attribution to datasets is what makes a conversation about it possible.
The requirements, in the order they arrive
Each one forces exactly one change. Read the trigger, decide what you would build, then open it and compare against the question that should have been answered first.
Then things go wrong
Ten injected failures. Each carries the plausible wrong fix, because the wrong fix is where the learning is — most of these have a response that makes the symptom disappear while making the platform worse.
Where to go next
Each of the injected failures has a lab where it is the whole subject.