Data Architecture Design

Every organisation has data, and most have more of it than they can use. The binding constraint is rarely storage or compute; it is that the same word means different things in different systems. Revenue computed by finance, by commercial and by the data team differ, and the meeting about which is right costs more than the systems that produced them.

Data architecture exists to make meaning explicit and reusable. It decides the canonical entities — customer, product, account, transaction, employee — and the definitions that go with them. It decides who owns each domain’s data and what they guarantee. It decides where data is stored, how it moves, and what is enforced versus requested. Those decisions determine which questions can be answered once, correctly, for everyone.

The failure to make them is expensive in a way that is hard to attribute. Dashboards proliferate, each with its own extract and its own definition; analysts spend their time reconciling rather than analysing; a regulatory request takes three weeks because nobody knows which system holds the authoritative record. None of those is a storage problem, and none is fixed by a new platform.

The warehouse holds structured, modelled data optimised for analytical queries with strong governance. The lake holds raw and semi-structured files cheaply, with weaker guarantees. The lakehouse combines open table formats with warehouse-style query and transactionality over object storage, and has become the default for new analytical platforms because it avoids copying data between two estates.

The operational side matters as much. Transactional databases serve the application; a change data capture stream carries its changes onward; the analytical estate consumes them. Where an organisation extracts data by querying production systems on a schedule, it creates load, latency and coupling — and the first symptom is a nightly job that fails when a schema changes.

Vector stores are the newest addition and are frequently over-applied. They are appropriate for semantic search and for grounding AI responses in documents. They are not a database, they do not replace structured query, and they inherit every problem of the source they index, including staleness and permissions. Placing a vector store before defining the authoritative source produces confident answers derived from a stale copy.

Data quality is usually described as a set of dimensions: completeness, accuracy, timeliness, consistency, validity and uniqueness. Useful as vocabulary, useless as a control. What changes behaviour is a published service level per data product — for example, that ninety-nine percent of records arrive within fifteen minutes and that the customer identifier is never null.

Tests must run as code, versioned with the pipeline, and fail loudly. A test that logs a warning into a dashboard nobody reads is not a control. The practical set is small: row count against expectation, null rate on critical fields, referential integrity against the canonical entity, distribution drift against history, and freshness. Five tests per product, enforced, beat a hundred aspirational rules.

Ownership completes the loop. When a test fails, someone must be accountable, and the consumer must know the data is suspect. Publishing quality status alongside the data — a visible indicator in the catalogue and in the reporting tool — prevents the most damaging outcome, which is a decision taken on data that was already known to be broken.

The question that metadata exists to answer is not “what data do we have” but “if I change this, what breaks, and if this number is wrong, where did it come from”. A catalogue that lists tables without lineage answers neither, and becomes a search engine for assets rather than an instrument of control.

Column-level lineage from source to report is the target, and it is achievable incrementally: capture lineage automatically from the transformation layer rather than asking engineers to document it. Where automation is impossible, a manual register of critical reports and their sources covers the cases that matter without pretending to completeness.

Impact analysis is the payoff. Before a schema change, the producing team can see every downstream consumer, notify them and set a date. Before a regulatory question, an analyst can trace a reported figure to its source and to the rule applied. Both are routine in organisations with lineage and near-impossible without it, and the cost of their absence is paid in delivery delay rather than in a budget line.

Data governance fails when it is a committee issuing policy. It works when classification is attached to the data at source, access is granted by policy rather than by request, and retention is executed by a job rather than remembered by a person. Those three mechanisms cover most of what any framework asks for.

Classification should be a property of a field or a dataset, set at creation and inherited downstream. That inheritance is what stops the common failure where a sensitive field is masked in one table and exposed in a derived one. Access policies then reference the classification rather than individual tables, so a new dataset with the same classification is protected automatically.

Retention and residency complete the picture. Retention needs a rule per class, an owner and a deletion job with evidence — the same discipline described for consent lifecycles. Residency needs a location per class and a check that prevents a pipeline from writing regulated data to an unapproved region. Both are more credible as automated controls than as statements in a policy.

Master data management is the discipline of agreeing what a customer, product or supplier is, and maintaining one authoritative record. It matters because every analytical question that involves a customer count depends on whether the same person is one record or three, and because regulatory reporting is judged on the accuracy of that identity.

Matching is the technical core. Deterministic matching on strong identifiers — a tax identifier, an account number — is reliable but incomplete. Probabilistic matching on name, date of birth, address and contact details catches more duplicates at the cost of false merges, which are considerably worse than missed duplicates because they combine two real people. Thresholds should be tuned to favour under-merging, with manual review for the ambiguous middle.

The golden record is a governance outcome, not a technology one. It requires agreement on which source wins for each attribute, who may change it, and how a correction propagates. Without that agreement, an MDM tool produces a fourth version of the customer that everyone argues about, and the programme quietly loses credibility.

Data estates grow without a decision. A pipeline adds a copy, a team adds an extract, a sandbox never gets deleted, and within two years the organisation holds six versions of the same transactional history. Each copy carries storage cost, compute cost, a governance obligation and an eventual deletion problem, and none of them appeared in a business case.

The useful controls are architectural rather than financial. Keep one raw copy per source and derive everything else on read, so transforming data does not duplicate it. Put a retention policy on every sandbox with an automatic expiry. Charge storage and compute back to the consuming domain so that the cost is visible where the decision is made. And measure copies per source, which is a more actionable number than total storage.

Compute is the other half. Analytical engines bill by data scanned, so an unpartitioned table and a query that reads every column cost an order of magnitude more than a well-designed one. The platform team can fix most of this centrally through partitioning standards, file sizing, clustering and query guard rails — which is again cheaper than asking hundreds of analysts to be careful.

Data migrations fail in the same three places: scope, reconciliation and the parallel run. Scope fails when the migration takes every historical record when only the live and statutory periods are needed. Reconciliation fails when the definition of done is “loaded successfully” rather than “matches the source on an agreed set of controls”. And the parallel run fails when it is not funded, so the old and new systems are compared for a fortnight instead of a quarter, and the differences surface after the old one is switched off.

A workable cutover defines four things in advance: the controls that must match and their tolerances, the period during which both systems run and who compares them, the rollback condition and how long the rollback remains available, and the decommissioning date for the source. The last is the one most often omitted, and its absence is why organisations run two systems of record for years.

Customers and downstream consumers need their own plan. Reports must move, extracts must be repointed, and reconciliations must be re-based. Migrations that succeed technically and fail commercially are usually the ones where the data moved and the ninety downstream consumers were discovered afterwards.

A retail group holds a quarterly review in which finance presents revenue from the general ledger, commercial presents a larger figure from the order system, and the leadership dashboard shows a third derived from a legacy extract. Each is defensible within its own definition, and the first forty minutes of every review are spent reconciling them.

A canonical definition exercise takes three weeks and resolves the debate. Revenue means invoiced value net of credit notes and returns, recognised in the period of delivery. The order system’s figure includes cancellations that never invoiced; the legacy extract predates a returns policy change. The definitions are written down with their sources, owners and calculation rules.

The architecture change is modest: one conformed revenue data product built from the ledger and the invoice stream, with tests for the reconciliation and with every report and dashboard migrated onto it. The legacy extract is retired. The quarterly review now starts with the business rather than with an arithmetic dispute, and the finance team reports that the hours returned exceed the cost of the work — before counting the decisions that are no longer deferred.

Roles divide broadly into modelling and semantics, platform and engineering, and governance. The people who are most valuable straddle two: someone who can model a domain and design the pipeline that delivers it, or someone who can define a policy and enforce it in code. Pure documentation roles are being absorbed into engineering.

Entry is usually from data engineering, analytics, or a domain role with heavy data exposure. The fastest development is taking one domain end to end: canonical definition, contract, pipeline, quality tests, catalogue entry and consumer migration. That experience teaches the interactions that no framework covers.

Progression runs to data architect, head of data platform, or data governance leadership. The evidence that distinguishes a candidate is a definition they resolved and retired the alternatives for, a quality regime that reduced incidents, and a migration that consumers accepted rather than worked around.

The enterprise architecture courses cover modelling, data products, lineage and governance with worked design exercises.