How to Build a Data Warehouse Architecture Layer by Layer

A warehouse is a production line, not a storage room

In most companies data never sits in one place. Orders live in the e-commerce backend, invoices in the accounting package, quotes in a folder of spreadsheets, and customer context in someone's inbox. The moment you decide to answer a single question from all of them at once, the real design problem appears, and it is worth stating plainly: how to build a data warehouse architecture layer by layer, so that each stage does one job and hands its output to the next. The answer is not a product you buy. It is the path your data takes from the source system to the number a manager reads on a Monday morning.

Layers matter because they make failure legible. When a figure looks wrong, it is wrong in the source, in the ingestion step, in the transformation, or in the definition of the metric itself. In a data warehouse architecture built without layers those four possibilities blur together, and every discussion ends with someone saying they do not trust the report. The sections below walk through what each layer does, which decisions belong to it, and where builds usually get stuck.

The source layer you do not control

The first layer is not yours. Sources are systems that exist for their own reasons: the ERP, the online store, the CRM, the payment provider, the marketplace dashboards, and, inevitably, spreadsheets. The opening task of any data warehouse architecture is an inventory of them.

For each source, record four things: how the data can be reached (database connection, API, file drop, manual export), how often it changes, which field uniquely identifies a record, and who owns the system. The fourth one looks like an administrative detail and saves the most time. Only the person who uses a system daily can tell you what a column actually means when its name says something else.

• Access method determines how ingestion has to be built.
• Rate of change determines refresh frequency; data that is fine once a day should not share a pipeline with data that has to be current.
• Unique key determines whether two sources can be joined at all. Customer matching is where most projects stall.
• Ownership determines who settles a definition dispute.

Do not choose tools before this inventory exists. Spreadsheets belong in it too: we looked at where spreadsheet-based tracking breaks down in an earlier piece, and in a warehouse build those files are simply another source to be read, not something to be ignored.

Ingestion: full loads, incremental loads and change capture

The ingestion layer moves data from sources into the warehouse. Three approaches cover almost every case, and the choice drives your running cost.

Full load. Pull the whole table every time. Easiest to build, most expensive to run, and entirely reasonable for small, slow-moving tables such as product categories, branch lists or the staff roster.

Incremental load. Pull only rows changed since the last run. This requires a trustworthy updated-at timestamp or a monotonically increasing key in the source. If the source application does not refresh that timestamp on every update, incremental loading will quietly lose changes, which is why the first build should always be reconciled against a full load before you rely on it.

Change data capture. Read inserts, updates and deletes from the database's own transaction log. It is the most faithful option because deletions are visible, but it needs configuration and privileges on the source database that not every business is willing to grant.

The second decision in this layer is whether to transform data in flight or after landing it. The classic ETL pipeline transforms on the way in: data leaves the source, is cleaned in an intermediate step, and arrives already shaped. The now common alternative reverses the last two stages, loading raw data first and transforming it inside the warehouse. The second approach is markedly kinder to small teams, because when a business rule changes you can rebuild history instead of arguing about it.

The raw layer: store it exactly as it arrived

The raw layer keeps source data untouched. Odd column names stay odd, dates that arrive as text stay text, and a customer spelled three different ways stays spelled three different ways. The purpose is to hold a faithful copy of what the source said at a given moment.

This layer looks redundant, it is the first thing people try to cut, and cutting it is the most expensive saving in a data warehouse architecture. Transformation rules change. Six months in, when finance decides that refunded orders should no longer count toward revenue, you can only restate last year if the raw data is still there. Without it you are left with numbers computed under a rule nobody uses anymore.

Two habits pay for themselves here: stamp every row with the time it was loaded, and tag every load with a batch identifier. When figures shift unexpectedly, those two fields are what let you point at the load that caused it instead of guessing.

Transformation: cleaning, matching and business rules

This is where the effort goes. The transformation layer turns raw data into something usable and encodes how the business counts things. In practice that means:

• Casting: text dates and amounts become real dates and numbers.
• Deduplication: rows captured twice by a retried load are collapsed.
• Standardisation: city names, unit codes and currencies are reduced to one spelling.
• Entity matching: the same customer across three systems is given one internal identifier.
• Business rules: cancellations, refunds, discounts, commission and tax are defined into or out of revenue.

That last item is a commercial decision, not a technical one, and an engineering team cannot make it alone. Most builds slow down exactly here, because two departments have been counting revenue differently and the data warehouse architecture is the first thing that makes the gap visible. That is not a defect in the project. It is the first benefit it delivers.

Build transformations as version-controlled, repeatable steps rather than scripts someone runs by hand. A step that produces the same result when run twice is the only practical way to recover from a load that failed halfway through the night.

The modelling layer: facts, dimensions and history

Clean data is still organised the way the source application needed it. The modelling layer reorganises it the way questions are asked. Dimensional modelling remains the most durable approach: events you measure go into fact tables, and the attributes that describe those events go into dimension tables. Arranged that way, a fact table surrounded by its dimensions forms the star schema that most reporting tools are built to read efficiently.

A sales fact table holds keys for date, customer, product and channel alongside measures such as quantity, amount and discount. The customer's name, industry and city belong to the customer dimension. Anyone writing a report can then ask which products sold in which cities with a single join, instead of reconstructing the source system's logic each time.

Deciding what happens when an attribute changes

Dimension attributes change: customers relocate, product categories get renamed, account managers are reassigned. You have to make a deliberate choice. Overwrite the attribute and past reports will show today's version of reality; keep the old value as a separate dated row and history stays as it was on the day. Both are defensible, but reading a report without knowing which rule applies is misleading. Most builds track history for a small set of dimensions and overwrite the rest.

This layer also owns the date dimension. Fiscal periods, week numbers, public holidays and working-day counts held in one table remove a surprising amount of duplicated logic from every report that follows.

The presentation layer and one definition per metric

The presentation layer is what business users actually touch. The hard part is not performance, it is definition. If "active customer" means one thing in sales and another in finance, two dashboards will produce two numbers from identical data and trust evaporates. Metrics therefore get defined once, with a name, a formula and an owner, and reporting tools read that definition rather than inventing their own.

In a small setup this layer may be a handful of views. As it grows it splits into departmental marts for sales, finance and operations. Split on definitional conflict rather than on user count: when two departments genuinely define the same measure differently, two clear views beat one ambiguous one.

Access Control Belongs in the Data Warehouse Architecture

Centralising data centralises risk, which is why access is a layer rather than an afterthought. Three separations cover most needs: row-level access so a regional manager sees only their region, column-level masking so personal data and salary fields are limited to specific roles, and a strict read-write split so report consumers never hold write privileges.

Retention rules live here too. A warehouse will happily carry a record for years after it was deleted in the source system, so deletion and masking rules have to be written deliberately rather than assumed.

Three vertical concerns that cut across every layer

The layers above describe horizontal flow. Three more concerns run vertically through the whole data warehouse architecture, and because they are usually considered last, they are usually where operations hurt.

Orchestration governs what runs after what and what happens when a step fails. Chains built on plain scheduled jobs fall apart as dependencies grow; you need something that understands the dependency, not just the clock.

Data quality tests run automatically after each load. Is a field that must never be null suddenly null, has a key that must be unique started repeating, has today's row count moved in a way that makes no sense. Without tests, the person who finds the error is the person reading the report, and confidence is hard to rebuild after that.

Metadata and lineage record where each field came from, what transformed it and which reports depend on it. When a source column changes, lineage is the only way to know what breaks before it breaks.

How to Build a Data Warehouse Architecture Layer by Layer Without the Four Usual Mistakes

Reviewing builds after the fact, the same four errors keep appearing. First, skipping the raw layer and cleaning data on the way in, which makes history unrecoverable when a rule changes. Second, burying metric definitions inside a dashboard tool, so the definitions leave when the tool does. Third, deferring quality tests until later, because later never arrives. Fourth, and the costliest, modelling without the source system owner in the room, so a misunderstood column is baked into the bottom of the model where nobody thinks to look.

A build order that actually finishes

Trying to stand up every layer of a data warehouse architecture at once is the shortest route to a project that ends before it produces a report. A sequence that works looks like this:

• Pick one narrow business question, something as specific and uncontested as monthly revenue split by channel.
• Connect only the sources that question needs, which is usually two.
• Build the raw layer and the ingestion jobs, then watch the load logs for a few days before trusting them.
• Write the transformation and a single fact table with two or three dimensions beside it.
• Produce the first report and reconcile it against the source system's own screen. If they differ, stop until you know why.
• Add quality tests, then move to the second question.

Each step leaves something that works. By the time the third question arrives the layers already exist and you are only adding a source and a model. Whether the investment is justified at all is a separate question, and we covered the thresholds that make it worthwhile in our piece on when a smaller company actually needs a warehouse. The build order here assumes you have already crossed them.

What ownership looks like after go-live

A warehouse is never finished. Source systems get upgraded, field names change, a new marketplace is added, a metric definition is revised. So the final step of a build is naming an owner: who investigates a failed load, who handles a request for a new source, who approves a change to a metric definition.

Smaller teams usually buy that role rather than hire it, and what matters is that the role is not vacant. An unowned warehouse ages quietly and starts producing wrong numbers before anyone notices. A layered build at least keeps maintenance proportional: you replace the layer that broke instead of rebuilding everything around it.

If you want to see where your own setup currently sits, our data warehouse consulting and business intelligence service page sets out the scope we work to, and you can request a quote based on your source count and reporting needs. Where a source system has no off-the-shelf answer, custom development can be built into the same data warehouse architecture rather than bolted onto the side of it.