You are a data architect making structural choices from day zero — before code is written, before vendors are locked in, and never on a clean slate. There is always data debt, there are always legacy systems, and the source ecosystem is always messier than the org chart admits. A complete worked answer: the sequential design process and the artifact each step produces, the decision register that converts vague goals into a latency budget, source forensics, one-way versus two-way doors, identity and bitemporal schema design, contracts, the coexistence plan — and the production metrics that would actually make you roll back.
Seven steps, each producing a named artifact. It starts with a decision register, not a requirements document — decisions carry latency and accuracy budgets that you can architect against.
Sort every open choice by reversibility. Grain, identity, bitemporality and metric semantics are one-way doors. Orchestrators and BI tools are not. Spend the design budget accordingly.
One is non-negotiable: reconciliation variance against the system of record must be zero. The rest are error budgets. Time-to-onboard-a-source is the best proxy for resilience to business change.
Answering with a stack. The question is about sequencing and trade-offs under ambiguity; naming tools is what candidates reach for when they have no process.
As a data architect, you are making structural choices from day zero, often before code is written or vendors are locked in. I want to understand your exact strategic execution process. Even when designing a new architecture from scratch, there is usually existing data debt, legacy systems, or messy source ecosystems to account for.
The Design Process: What are the sequential steps you take to translate high-level business goals into a conceptual blueprint while navigating existing data constraints?
The Decision Matrix: What foundational rules or trade-offs guide your early choices when deciding on data modeling patterns and the technical stack?
The Evaluation: Once the architecture begins handling production workloads, what specific, non-negotiable metrics do you use to evaluate whether the system is genuinely 'good,' scalable, and resilient against future business shifts?
Three questions, one assessment. The interviewer is not collecting three lists — they are checking whether the same spine runs through all three answers. If your process produces artifacts, your matrix decides using those artifacts, and your metrics measure what the matrix bet on, the answer holds together under probing. If the three parts are independent lists of good practice, the first follow-up will show it.
| The phrasing | What it's actually probing |
|---|---|
| "from day zero, before vendors are locked in" | Can you reason without a stack? Candidates who lead with tool names have no process underneath. |
| "exact strategic execution process" | Sequence and artifacts. "Exact" is the tell — they want steps with outputs, not principles. |
| "there is usually existing data debt" | Greenfield is a fiction. They want the coexistence and migration plan treated as part of the design, not a phase-two problem. |
| "foundational rules" | Do you have decision rules that produce an answer, or do you say "it depends" and stop? |
| "non-negotiable metrics" | Which numbers would make you roll back? Most candidates list dashboard metrics that nobody would ever act on. |
| "resilient against future business shifts" | Evolvability, not uptime. Can the architecture absorb a new product line, an acquisition, a regulation? |
A process you can name but not evidence is a philosophy. Every step below ends in a document someone can review, argue with and sign. That is what makes the answer concrete under probing — you can be asked "show me the output of step two" and have something to describe.
The design spine. Steps 1–6 are sequential; step 7 is continuous. The dashed return path is not failure — the vertical slice exists precisely to break assumptions cheaply.
1 · Decision inventory. Not "what data do you need" — that question returns everything anyone can imagine. Instead: what decisions will this change, who makes them, how often, and what does it cost to be wrong or late? Four columns, and they convert an unbounded wish list into a bounded engineering problem. In practice this is where most of the requested scope evaporates: of forty stated "real-time" needs, two or three usually have a decision behind them that actually changes if the number arrives sooner.
2 · Source forensics. Profile the sources; do not interview people about them. Documentation describes intent, and intent is not what the data does. This is the step that most often changes the architecture, and it is covered on its own in § 05.
3 · Conceptual model. Entities, grain, identity, relationships — deliberately vendor-neutral, deliberately before any stack conversation. The output is a conceptual ERD plus one sentence per fact table stating its grain. If you cannot write the grain sentence, you do not understand the process being modelled yet.
4 · Reversibility triage. The step candidates never mention, and the one that most distinguishes an architect from a senior engineer. Sort every open decision by what it costs to undo. Detailed in § 06.
5 · Contracts at the boundaries. Wherever data crosses a team boundary, publish a contract: schema, semantics, freshness SLA, owner, deprecation policy. Everything inside a boundary stays refactorable; everything crossing one becomes expensive to change. Drawing those boundaries deliberately is the architecture.
6 · Thin vertical slice, in production. One decision from the register, end to end, with real data, real authentication, real users and a real bill. Not a sandbox proof-of-concept — those validate the happy path and none of the things that actually sink programmes: identity, entitlements, network egress, the cost model at volume, and whoever owns the on-call.
7 · Coexistence. The legacy estate does not pause while you build. Treated as an afterthought, it becomes the reason the programme stalls at 70% — see § 13.
This is the deliverable that makes the rest of the answer defensible. Each row is a real decision a real person makes on a real cadence. The strictest row sets the architecture; every other row rides on infrastructure built for that one.
| Decision | Owner | Cadence | Tolerable staleness | Tolerable error | Cost of being wrong |
|---|---|---|---|---|---|
| Intraday liquidity / funding call | Treasury | Hourly, market hours | 15 min | Zero — must tie to GL | Regulatory breach, funding cost |
| Fraud hold on a transaction | Fin-crime engine | Per event | Sub-second | Tuned FP/FN rate | Loss, or a blocked good customer |
| Credit line increase offer | Risk | Daily batch | 24 hours | < 0.5% | Credit loss, mis-selling exposure |
| Regulatory position report | Finance | Daily / quarter-end | T+1 close | Zero, and reproducible | Restatement, fine, attestation risk |
| Campaign segment refresh | Marketing | Weekly | 7 days | ~5% | Wasted spend |
| Branch staffing model | Ops | Monthly | 30 days | ~10% | Service level, overtime |
The row that quietly dominates a bank's architecture is the regulatory one, and not because of latency — T+1 is generous. It is the words zero and reproducible. Reproducibility means you must be able to regenerate the report exactly as filed, months later, after the underlying data has been corrected. That single requirement rules out mutable dimension tables and rules in transaction-time history, which is a one-way door decided in week one. That is the kind of chain the interviewer is listening for: a business fact, to a modelling constraint, to a decision you cannot walk back.
"Account for existing data debt" is the part of the question most candidates gesture at and move past. Be specific instead: here are the pathologies that actually change an architecture, how to detect each one in an afternoon, and what each forces you to build.
| Pathology | How you detect it | What it forces in the design |
|---|---|---|
In-place mutation with no updated_at | Snapshot twice a day and diff row hashes; count rows that changed without any timestamp moving | Incremental extraction is impossible. Log-based CDC, or full-scan compare — a large, early cost that must be budgeted, not discovered |
| Soft deletes with an inconsistent flag | COUNT(*) WHERE is_deleted IS NULL — nulls mean the column was added later and history was never backfilled | Every downstream filter is wrong for rows older than the column. Needs an explicit "unknown" state, not a coalesce to false |
| Recycled natural keys | Group by the key, count distinct open/close date pairs — closed accounts whose numbers are reissued | The single strongest argument for surrogate keys. A natural key join silently merges two customers years apart |
| Same party, different id per product silo | Fuzzy-match a sample across cards, deposits, mortgage; measure the overlap you cannot resolve deterministically | An identity resolution layer with its own lifecycle — § 08 |
| Local timestamps with no offset | Look for a missing hour in spring and a duplicated hour in autumn | Store UTC plus the original offset. Never store local time alone — DST makes it ambiguous, permanently |
| Amounts without a currency column | Distribution of magnitudes per source system; a "1,200" that is JPY next to one that is GBP | Currency is part of the grain of every monetary fact. Retrofitting it is a full-history rewrite |
| Late-arriving backdated corrections | Compare a daily snapshot to the same day re-extracted a month later | Your "immutable" daily fact is not immutable. Either bitemporality or a restatement process — there is no third option |
| Multi-region duplicates, different collation | Case- and accent-insensitive match on name/address across regional instances | Deduplication rules become policy, not code, and need an owner who can arbitrate |
The second question asks for "foundational rules or trade-offs." The honest organising principle is not a list of dichotomies — it is a single test applied to every open choice: what does it cost to undo this after a year of production data? That test sorts decisions into two piles that deserve completely different amounts of deliberation.
The reversibility matrix. The left column is where an architect earns their title; the right column is where programmes lose months to committee.
The question asks for a conceptual blueprint, so draw one — but the value is not the boxes. Every architecture diagram has these boxes. The value is naming what changes at each boundary, and which boundaries carry contracts.
The blueprint. Note that serving is explicitly disposable: anything in it must be reproducible from integration, which is what lets you change serving technology without a migration.
Two properties of this blueprint matter more than its shape, and both are worth saying out loud. First, landing is immutable and replayable — every transformation downstream can be rebuilt from it, which converts most future mistakes from incidents into reruns. Second, serving is disposable. If the BI tool changes, or a new consumer needs a different shape, you rebuild serving from integration and nothing is lost. Those two properties together are what make the middle layer worth the effort of getting right.
In a bank, the same human being is a different customer in cards, deposits, mortgage and wealth — with different identifiers, different name spellings and different addresses, each authoritative in its own system. Every meaningful question ("what is our exposure to this client?") depends on resolving that, and the resolution changes over time. This is the hardest schema in the design.
Most warehouses track one time: when something was true in the business. Regulated reporting needs a second: when the organisation knew it. Without both, "regenerate the report exactly as it was filed on 31 March" is unanswerable — and that question is asked after every restatement.
Both answers are correct; they answer different questions. A single-clock model can produce only one of them, and silently loses the other the moment the correction lands.
The cost, stated honestly. Bitemporality roughly doubles the row count on corrected entities, makes every query carry four predicates instead of two, and is genuinely harder for analysts to use — so you expose a current-state view over it and reserve the full four-predicate form for reporting and audit. Say that trade-off out loud in the interview. A candidate who applies bitemporality to everything is as unconvincing as one who applies it to nothing: it belongs on entities that feed regulated or restated numbers, and nowhere else.
Grain is the first line of any physical design and the most expensive thing to get wrong, because detail you did not store is gone. The rule is simple to state and constantly violated: model at the finest grain the business could ever ask about, and aggregate upward. Aggregation is a query; disaggregation is a data recovery project.
Inside a boundary, refactor freely. Across a boundary, you have consumers you cannot see and cannot call. A contract makes that asymmetry explicit — and deciding where the boundaries go is most of what "architecture" means in practice.
The last comment is the point worth making aloud. Every organisation with an untrusted warehouse has the same symptom: analysts maintaining private extracts. That is not a discipline problem, it is a contract problem — nobody promised them anything, so they insured themselves. Contracts are how you end that, and they cost nothing but the willingness to be held to them.
"It depends" is not a trade-off, it is a refusal to have one. A usable rule names what it depends on and what value tips it. These are the ones worth carrying into an interview, phrased so that they decide something.
| The choice | The rule that decides it | What tips it |
|---|---|---|
| Batch vs streaming | Stream only where a registered decision has tolerable staleness below the batch cycle and acting sooner changes the outcome | No decision under ~1 hour → batch. Streaming roughly triples operational surface: ordering, state, replay, exactly-once, backfill |
| Normalize vs denormalize | Both, at different layers. 3NF where you reconcile and enforce integrity; dimensional where humans query | Not a global choice. Treating it as one is the tell of a candidate who has only worked in one layer |
| Build vs buy | Buy unless a regulator or a customer would recognise the thing as yours | Ingestion, orchestration, storage → buy. Identity resolution rules, risk logic, metric semantics → build |
| Central vs federated (mesh) | Federate only when domains have dedicated data engineers and a self-serve platform team already exists | Below roughly three staffed domains, mesh is silos with better vocabulary. The platform must precede the federation |
| ELT vs ETL | Land raw first, always. Transform after, in the warehouse, where it is versioned and re-runnable | The exception is PII that may not land unmasked — tokenize in flight, and accept the operational cost |
| Warehouse vs lakehouse | Choose the storage format first and the engine second; open table format keeps the engine a two-way door | If a vendor's proprietary storage is the only option, the vendor decision has silently become one-way (§ 06) |
| Real-time identity resolution | Resolve asynchronously; serve the last resolved state. Synchronous resolution puts a fuzzy-matching service on the critical path | Unless a decision in the register needs it inline — fraud usually does, reporting never does |
| One warehouse vs many marts | One conformed integration layer, many serving marts. Conform the dimensions, not the consumers | Marts that bypass integration are how two teams end up with two revenue numbers and no way to adjudicate |
The question explicitly says there is existing data debt and legacy systems. Programmes rarely fail at the new architecture; they fail at the switch-over, and they stall at roughly 70% — new platform live, old platform still running, nobody willing to sign the decommission. The defence is to make decommissioning a gate with numeric criteria rather than a decision someone has to be brave about.
Strangler with a reconciliation gate. The dual-run period is not waste — it is the only evidence that will let anyone sign the decommission.
The third question asks which metrics prove the system is good. The strongest framing is to answer a sharper question than the one asked: which numbers would make me roll back a release or hold a publication? Metrics that would never trigger an action are dashboard decoration, and listing them is what makes an answer sound rehearsed.
| Metric | Target | What it tells you that nothing else does |
|---|---|---|
| Reconciliation variance vs system of record | Zero. Daily. Breaks logged and aged | The only truly non-negotiable one. If warehouse revenue ≠ GL revenue, every other metric is measuring the performance of a wrong answer |
| Freshness, as an error budget | 99.5% of business days within the contracted SLA | A single threshold hides how often you miss. A budget makes reliability negotiable with a number rather than a feeling |
| Completeness of expected partitions | 100% landed, or the dataset is held back | Catches the silent failure that quality tests miss entirely — data that is correct but absent |
| Time to onboard a new source | Trending down; measured per source | The single best proxy for "resilient against future business shifts." A rigid architecture shows up here first, long before latency degrades |
| Schema-change lead time & consumer break rate | Additive change same-day; zero unplanned breaks | If adding a column takes three weeks or breaks four dashboards, the architecture is brittle no matter how fast it queries |
| Column-level lineage coverage | 100% of published regulatory metrics | Directly demanded by BCBS 239-style expectations. Also the difference between a 20-minute incident and a two-day one |
| Backfill throughput | 90 days reprocessed within one business day | Your real recovery capability. RPO/RTO on a cluster is meaningless if correcting history takes three weeks |
| Cost per million transactions processed | Flat or declining as volume grows | Absolute spend rising is fine when volume rises faster. Unit cost is the metric that reveals whether the architecture actually scales |
| Query latency p95 / p99 by consumer class | Per contract | Averages hide the tail, and the tail is what users experience and remember |
Claiming a non-negotiable metric invites the obvious follow-up: how is it enforced? Have the mechanism ready, because "we monitor it" is not an answer — monitoring tells you after someone has already used the number.
Breaks that are understood and accepted — a known timing difference, a documented in-transit account — go into a break register with an owner and an expiry date, and the gate excludes them explicitly by identifier. What it must never do is widen a tolerance to make the check pass. A tolerance that grows is a control that has been quietly switched off, and in a regulated environment that is the finding an auditor is specifically looking for.
"I design against decisions, not requirements. Step one is a decision register — who decides what, how often, how stale it can be, what being wrong costs. That converts vague goals into a latency and accuracy budget. Step two is source forensics: I profile the sources rather than interview people about them, because documentation describes intent and I need behaviour. Then the conceptual model — entities, grain, identity — deliberately before any vendor conversation."
"For trade-offs, I sort every open choice by reversibility. Grain, identity, bitemporality and metric semantics are one-way doors — undoing them means rewriting history or renegotiating with every consumer, so they get the deliberation. Orchestrators, BI tools, even the compute engine if storage is an open format, are two-way doors — I decide those quickly and move on. Most programmes invert this and spend six weeks on a tool bake-off while the grain gets decided by whoever writes the first pipeline."
"In production, one metric is non-negotiable: reconciliation variance against the system of record is zero, checked before publication, not after. Everything else is an error budget — freshness at 99.5% of business days, completeness, p95 latency per contract. But the two I watch for resilience are time to onboard a new source and schema-change lead time, because those are what tell me whether the architecture can absorb a business shift. A platform that answers today's questions in 200 milliseconds but needs a quarter to take on an acquisition has failed the actual test."
The reader who shared this question said they could answer, but not convincingly once the interviewer probed. That is the normal failure mode: the prepared answer holds for ninety seconds, and the follow-up finds the edge. These are the probes that follow this question, and what a convincing reply sounds like.