Data Warehouse vs Reporting Database: A UK Business Guide to Choosing the Right Architecture for Business Intelligence in 2026

Most UK businesses do not choose a data warehouse architecture deliberately. They arrive at one by accident, usually at the point where a monthly management pack has grown into fourteen linked spreadsheets, three of which only one person understands, and the finance director has started asking why the revenue figure in the board pack disagrees with the revenue figure in the CRM. That moment is a genuine architectural fork, and the two roads leading away from it are not the same road at different budgets.

This guide sets out how to tell them apart. It covers what a reporting database actually is and why it is often the right answer; what a data warehouse adds and what that addition costs; how ETL and ELT pipelines differ in practice rather than in vendor diagrams; when normalisation helps reporting and when it quietly destroys query performance; and how to size the infrastructure to the volume and complexity of data a business actually has, rather than the volume it imagines it will have in five years. The recurring theme is that the expensive mistake is rarely choosing the cheaper option. It is choosing the more elaborate one before the business has a problem elaborate enough to need it.

Reporting database and data warehouse - two different problems

The terms are used interchangeably in sales conversations, which is unhelpful, because they describe solutions to problems that only superficially resemble each other. Getting the distinction straight is most of the decision.

A reporting database is a copy of an operational system’s data, restructured so that reading from it is fast and so that heavy queries do not slow down the system that the business runs on. The defining characteristic is that it has one primary source. If your reporting pain is “our Sage queries lock the accounts system”, or “the dashboard times out because it is hitting the live order table”, or “we cannot let the analyst near production”, then the problem is contention and query shape, and a reporting database solves it. Typical implementations run from a read replica with a handful of pre-aggregated views through to a properly modelled denormalised copy refreshed on a schedule. Reporting database design at this level is a matter of weeks, not quarters.

A data warehouse is a different animal. Its defining characteristic is that it integrates multiple sources that disagree with each other, and that it holds history the source systems do not keep. The problem it solves is not slowness, it is reconciliation: the CRM calls it an account, the finance system calls it a customer, the support desk calls it an organisation, and no two of them agree on which records are the same entity or what a “closed” one means. A warehouse imposes a single set of definitions - conformed dimensions, in the standard terminology - and keeps a record of how things looked at each point in time, so that a report run today for last March returns what it would have returned last March.

That second property is the one most often overlooked and most often the actual reason a business needs a warehouse. Operational systems overwrite. When a customer moves from the Midlands region to the South East, the CRM updates the field, and every historical report that groups by region silently rewrites the past. If nobody has ever complained about that, a reporting database is very likely sufficient. If someone has complained about it, or if the business is regulated in a way that makes it matter, that is a warehouse requirement and no amount of clever view-writing over a live replica will substitute for it.

There is a third pattern worth naming because a lot of UK mid-market businesses land on it without realising: the reporting database that quietly became a warehouse. It started as a copy of one system, then someone added a monthly spreadsheet of marketing spend, then the HR headcount extract, then a table of manually-maintained product categories, and it now integrates five sources with no conformed definitions, no history and no documentation. This is the most expensive architecture of the three, because it carries all of a warehouse’s complexity and none of its discipline. Recognising it is the first step to fixing it, and the fix is usually cheaper than people fear.

The numbers behind the decision

The figures below describe the shape of the choice for UK small and mid-sized businesses. They are indicative ranges drawn from typical delivery work rather than published benchmarks, and the useful thing about them is not the precise values but the ratios between them - specifically, how large the gap is between the two options and how often the larger option is bought for a problem the smaller one would have solved.

The last figure is the one that reshapes budgets when people take it seriously. The platform decision - Azure SQL versus Snowflake versus BigQuery versus Postgres - absorbs most of the attention in early conversations and almost none of the eventual effort. What consumes the project is discovering that the product code in the order system has three formats depending on which year the record was created, that 4% of customer records are duplicates, and that nobody can produce a written definition of “active customer” that two departments will both sign. None of that changes based on which database you bought.

The 6-in-10 figure deserves the same attention from the other direction. It is not an argument that warehouses are unnecessary; it is an observation that the word gets used as a generic term for “better reporting”, and that a scoping conversation which asks specifically about sources, history and disagreement between systems will frequently land somewhere much cheaper than the initial enquiry assumed.

Reporting database versus data warehouse - a direct comparison

The table below sets the two architectures against each other on the dimensions that actually determine which one fits. Read it as a diagnostic rather than a scorecard: the right answer is whichever column describes the problem you have, and the highlighted column is not the recommended option in general, only the one most UK businesses reach for prematurely.

The two failure modes at the bottom are worth dwelling on, because they are the outcomes that actually happen rather than the ones discussed in planning. A reporting database fails gracefully: it keeps doing its job for the one system it covers while a second system’s data gets bolted on badly, and the degradation is visible and gradual. A warehouse fails abruptly and expensively: the pipelines are built, the model is sound, and then six months after go-live nobody has updated the definition of a sales region following a reorganisation, one number in the board pack goes wrong, trust evaporates, and the business returns to spreadsheets while continuing to pay the platform bill.

That asymmetry argues for a specific sequencing bias. Where both options plausibly fit, starting with the reporting database and treating it as a stage rather than a destination costs relatively little if you later need the warehouse - the source profiling, the definitions work and the BI layer all carry forward. Starting with the warehouse and discovering the business only needed one system’s data faster is a far harder position to unwind, because the organisation has by then committed to a platform, a toolchain and usually a headcount.

None of which means the warehouse is the wrong answer when the conditions are genuinely met. If three systems disagree, if history matters, and if there is an identified person whose job includes owning data definitions, the reporting database will not get you there and building one first is simply a delay. The question is whether those three conditions are actually true today, or aspirational.

What actually triggers the move off spreadsheets

The chart below shows how often each trigger appears as the stated reason a UK business starts looking at reporting infrastructure. Most organisations report several at once, which is why the figures sum well beyond 100%. The pattern that matters is which triggers point towards which architecture.

Read against the previous section, the triggers split cleanly. Slow reports, locked operational systems and key-person dependency are all single-source problems and all solvable with a reporting database. Systems disagreeing, like-for-like comparison across years, and an acquisition bringing a second finance system are integration and history problems, and those are warehouse territory.

The top trigger - manual consolidation eating days every month - is ambiguous, and that ambiguity is where a lot of over-buying originates. Days of manual work can mean one person exporting a single system and reformatting it, which automation over a reporting database eliminates entirely for a few thousand pounds. It can equally mean someone reconciling four exports by hand because no two agree on customer identity, in which case automating the export changes nothing; the manual effort was the reconciliation, and only a model with conformed definitions removes it. Those two situations look identical on a symptom list and cost an order of magnitude apart to fix.

The bottom trigger is small in frequency but disproportionate in consequence. When a lender, auditor or acquirer asks for evidenced figures with a documented lineage from source, spreadsheet-based reporting cannot comply in any defensible way, and the timeline is usually external and short. Businesses anticipating a transaction, refinancing or a first external audit within eighteen months should factor that in now, because retrofitting lineage to a live warehouse under deal pressure is the most expensive version of this project.

Readiness scoring - where most UK businesses sit before they start

The three cards below triage an organisation against the conditions that actually determine whether a warehouse will succeed. The badges are deliberately blunt: red marks a condition that will cause a problem if it is not addressed before build, amber marks something that needs a named plan, and green marks a condition already met. Score honestly. The most common cause of an abandoned warehouse is not technical, it is that two or three red rows were treated as details to sort out later.

Two rows on that grid carry more weight than the rest. The first is a named owner for definitions. A data model encodes decisions about what things mean, and those decisions go stale the moment the business reorganises, launches a product line, or changes how it books revenue. Without someone whose job description includes keeping them current, the model drifts from the business within about two quarters and the numbers stop being trusted. This is an organisational appointment, not a technical one, and it does not need to be a full-time role - but it does need to be someone’s named responsibility rather than a shared assumption.

The second is budget for ongoing change. Reporting infrastructure is not a capital project that completes; it is closer to a product with a long life. A reasonable planning figure is 15–25% of the build cost per year for model changes, new sources, and the work of keeping tests and documentation current. Projects funded as one-off builds with no change budget are the ones that quietly become legacy within eighteen months, at which point the business pays twice: once for the platform it no longer trusts and again for the spreadsheets that came back.

If the grid returns mostly red in the definitions column but the data column clearly indicates a warehouse, the sequencing answer is usually to do the definitions work as a discrete, short engagement first - a few weeks producing a written metric dictionary and an agreed customer-matching rule - and only then scope the build. That work is needed either way, it is cheap relative to the build, and doing it first converts the largest source of project risk into a known quantity. The same principle governs any substantial technology commitment, and it is covered more generally in our guide to building an IT roadmap that aligns technology spend with strategy.

What it costs - platforms, pipelines and the parts nobody quotes for

The table below sets out realistic UK cost bands for the common shapes of this project. Platform figures assume UK or Ireland region hosting and typical mid-market data volumes - tens of millions of rows rather than billions. Build figures assume external delivery at standard UK consultancy rates; internal delivery trades cash cost for opportunity cost and rarely comes out cheaper unless the capability already exists.

Three cost lines are routinely absent from proposals and routinely material. The first is source data remediation. Nobody can quote accurately for fixing duplicate customer records or inconsistent product codes before profiling the data, so it is either excluded or given a token figure, and it is frequently the largest single line in the eventual total. A short paid profiling exercise before the main scope is agreed converts this from an unknown into a number, and is worth insisting on.

The second is the BI layer and its licences. Power BI Pro at roughly £8 per user per month looks trivial until it is 90 users, and Power BI Premium per-user or capacity pricing changes the arithmetic sharply above a certain scale. Looker Studio is free but pushes query cost back onto the warehouse. Metabase and other open-source options remove licence cost and add hosting and maintenance. None of these is wrong; the error is modelling a five-year total cost of ownership without any of them in it.

The third is egress and compute behaviour under real use. Consumption-priced platforms - BigQuery, Snowflake, Fabric capacities - bill for what queries actually do, and a dashboard that auto-refreshes every fifteen minutes for 60 users behaves very differently from the same dashboard in a demo. The mitigation is not to avoid consumption pricing but to set a budget alert on day one and to review the first full month’s actual usage against the estimate before rolling the platform out more widely.

ETL or ELT - how the data actually gets there

Every architecture above needs a way of moving data from source to destination, and the two dominant patterns are distinguished by one thing: where the transformation happens. In ETL, data is extracted, transformed in a dedicated processing tier, and only the finished result is loaded. In ELT, raw data is loaded into the destination first and transformed there using the destination’s own compute. The ordering of two letters conceals a substantial difference in cost profile, debuggability and who can maintain it.

ELT has become the default for good reasons. Loading raw data first means the raw layer is always available for reprocessing, so when a transformation turns out to be wrong - and it will - you fix the logic and rebuild rather than re-extracting from a source system that may have overwritten the evidence. Transformation logic written in SQL and version-controlled with a tool such as dbt is readable by anyone who can read SQL, which in most UK mid-market businesses is a considerably larger group than those who can maintain a graphical ETL package. And cloud warehouse compute is cheap enough that pushing the work into the destination is usually cheaper than running a separate transformation tier.

ETL retains real advantages in specific circumstances. Where data cannot land in raw form for regulatory reasons - special category personal data under UK GDPR that must be pseudonymised or tokenised before it reaches an analytics platform - transformation has to happen in transit, and that is ETL by definition. Where the destination charges by compute and the transformations are heavy and repetitive, a dedicated tier can be cheaper. Where an existing investment in SSIS or a similar platform is working, maintained and understood, replacing it purely to follow a pattern is a poor use of budget.

The extraction layer itself is a separate decision. Managed connector services such as Fivetran or Stitch remove the work of writing and maintaining extraction code, and price by data volume - convenient, and prone to unpleasant surprises when a source system starts emitting more rows. Open-source Airbyte trades licence cost for hosting and operational effort. Hand-written extraction is entirely reasonable for two or three stable sources with good APIs, and unreasonable at fifteen. The honest test is whether anyone will still be maintaining the extraction code in two years, and connectors for well-known SaaS platforms almost always fail that test in favour of buying.

Whichever pattern is used, three practices separate pipelines that stay trustworthy from those that do not: idempotent loads, so a re-run produces the same result rather than duplicating rows; tests that assert row counts, uniqueness and referential integrity on every refresh; and alerting on failure that reaches a person rather than a mailbox nobody reads. A pipeline that silently fails on a Tuesday and is noticed on the following Monday has done more damage to organisational trust in the numbers than one that fails loudly and often. This is the same operational discipline that underpins any monitored infrastructure, as covered in our guide to proactive network monitoring for UK businesses.

A score in the low forties is typical and is not an indictment of the people who built the pipelines. It reflects the fact that pipelines are usually built under delivery pressure to get the first dashboard live, and the operational hardening is deferred to a phase that never gets funded. The practical response is to insist that testing and alerting are in the original scope rather than a later improvement, because they cost a fraction of the build when included and considerably more to retrofit across a dozen existing pipelines.

What a real implementation looks like, week by week

The timeline below describes a two-source integrated mart for a UK business of around 80 staff - finance system plus CRM, roughly 20 million rows of history, delivered to a first trusted subject area. Larger warehouse programmes follow the same sequence with longer phases and more iterations, not a different shape. The single most useful thing about laying it out this way is that it shows how late the dashboard appears and how much of the work precedes it.

The parallel run in weeks 11–12 is the step most often compressed under delivery pressure, and it is the one that determines whether the organisation trusts the output. Discovering in a controlled reconciliation that the old spreadsheet double-counted credit notes is a good outcome; discovering it because the board spotted an odd number in the first month of live reporting is not, even though the underlying finding is identical.

Normalisation - when it helps reporting and when it hurts

Operational databases are normalised for a reason. Third normal form removes redundancy, which means a fact is stored once and updated once, which is exactly what you want when hundreds of users are writing to the system concurrently and correctness under update matters more than anything. Applying that same design to a reporting workload is where a great deal of avoidable slowness comes from, because reporting has the opposite characteristics: few writers, many readers, and queries that sweep large ranges rather than fetching single records.

The practical consequence is join count. A normalised operational schema might require eight or ten joins to answer “revenue by product category by region by month”, and each join is work the database must do on every execution for every user. Database normalisation for reporting is therefore best understood as something to partially undo: a star schema deliberately reintroduces redundancy, storing the product category on the product dimension and the region on the customer dimension, so that the same question becomes a fact table joined to two or three dimensions. Storage is cheap; repeated join work at query time is not.

That said, denormalisation is not a free win and the common failure is going too far. A single enormous flat table containing every attribute of every entity is fast for the queries it was designed for and unusable for anything else, because there is no longer any structure describing how things relate. It also makes attribute changes painful: updating a product category means rewriting every historical row that mentions that product, which both destroys point-in-time accuracy and takes a long time. The star schema is the middle position precisely because it keeps the dimensional structure while removing the join depth, and for the overwhelming majority of UK mid-market reporting workloads it remains the right default.

There is a further modern wrinkle. Columnar analytical engines - BigQuery, Snowflake, Synapse, ClickHouse, DuckDB - change the arithmetic, because they read only the columns a query touches and compress aggressively. Joins are cheaper on these platforms than on a traditional row-store, which means the penalty for keeping a more normalised model is smaller than it used to be. It is not zero, and the clarity argument for dimensional modelling stands regardless of engine, but teams should be sceptical of performance advice written for row-store SQL Server in 2012 and applied unchanged to a columnar platform in 2026.

The ordering of that list carries a message worth stating directly: structural changes beat hardware, by a wide margin and at a fraction of the recurring cost. Scaling up compute is at the bottom, delivers the least, and is the only item on the list that increases the monthly bill permanently. It is also, reliably, the first thing suggested when reports are slow, because it requires no analysis and can be done in an afternoon. A query that takes ninety seconds because it joins eleven normalised tables and computes a running total row by row will still take thirty seconds on quadruple the compute, and the business will pay for that compute every month thereafter.

The last-but-one item is a reminder that scope is an optimisation too. Most operational dashboards are looked at for recent performance, and defaulting them to thirteen months - enough for a year-on-year comparison - while leaving full history available on request removes a large fraction of the work without removing any capability anyone actually uses.

How often the architecture chosen matches the problem

The figure below reflects how frequently, at the point of first review, the architecture a UK business has in place is a reasonable match for the reporting problem it actually has. The remainder split between over-building - a warehouse where a reporting database would serve - and under-building, where multiple sources are being reconciled by hand on top of infrastructure that was never designed to integrate them.

Of the 61% that do not match, the split is roughly two-to-one towards over-building. That ratio surprises people who assume the common failure is under-investment, and it has a straightforward explanation: the decision is usually made at a moment of acute frustration, the vendors present at that moment sell warehouses, and nobody in the room is incentivised to ask whether a cheaper architecture would resolve the specific complaint. Under-building is the less common but more painful failure, because it manifests as people continuing to do reconciliation by hand while believing the problem has been solved.

The corrective is unglamorous and works: write down the questions first, map each to its sources, and let the mapping choose the architecture. A list where twelve of fifteen questions draw on one system is telling you something specific, and it is worth more than any vendor assessment.

A worked example - when the cheaper answer was the right one

A Leeds-based distribution business of 64 staff approached the problem convinced it needed a data warehouse. The symptoms were familiar: the monthly management pack took the finance manager most of four days, two people held the knowledge of how it was assembled, and the order system slowed noticeably whenever anyone ran the stock ageing report. A vendor had quoted a little over £90,000 for a warehouse build with a twelve-month roadmap, and the board had approved it in principle pending a second opinion.

The scoping exercise took eight days and started with the questions rather than the systems. Twenty-two questions were on the management pack. Nineteen of them drew entirely on the ERP. Two needed marketing spend, which arrived as a monthly CSV from the agency. One needed headcount from the payroll system for a revenue-per-head figure. There was no third system, no meaningful disagreement between sources on customer identity, and no requirement for point-in-time reporting of anything except stock valuation, which the ERP already snapshotted monthly for its own purposes.

What they had was a contention problem and a modelling problem wearing a warehouse costume. The delivered solution was a reporting database: a nightly-refreshed copy of the ERP restructured into a small star schema with five dimensions and three fact tables, the marketing CSV loaded into a governed reference table with a simple upload process, and the headcount figure maintained as a twelve-row table updated quarterly. Power BI sat on top. The build came to £21,400, the platform runs at around £180 a month, and the change budget was set at £5,000 a year.

The outcomes worth reporting are specific rather than sweeping. The management pack moved from four days to a review of an already-populated report. The stock ageing report stopped touching the production database. The parallel run found three errors in the old spreadsheets, of which one had been understating a product line’s margin for at least two years. What the business did not get is also worth stating: no point-in-time customer history, no ability to reconcile a second finance system, and no capacity to absorb an acquisition without further work. Those were accepted as known limits rather than discovered as failures.

We were about to spend ninety thousand pounds to solve a problem we had never actually written down. The eight days spent listing the questions was the most valuable part of the project, and it cost less than one day of the build we nearly bought.

Two years on, the business acquired a competitor with its own finance system, and the conversation about a warehouse became a real one rather than a premature one. The reporting database is now the first source feeding it, the star schema carried across largely intact, and the metric dictionary written during the original scoping is the reference document for the integration work. That is the sequencing argument in practice: the earlier, cheaper build was not wasted effort when the larger requirement finally arrived, it was the foundation of it.

The decision checklist - twelve questions before you commit

Work through these in order, in writing, before evaluating a single platform. The list takes an afternoon with the right people in the room and reliably changes the shape of the project.

1. List the questions. Write the ten to twenty questions the business most needs answered on a recurring basis. Not report titles - actual questions, phrased as someone would ask them out loud.
2. Map each question to its sources. For every question, name the systems its answer depends on. Count how many questions need more than one system. This count, more than anything else, determines the architecture.
3. Test for history. For each question, ask whether the answer must reflect how things looked at the time, or how they look now. If several need point-in-time accuracy, a warehouse is indicated regardless of source count.
4. Define the contested terms. Write definitions for active customer, recognised revenue, completed order, sales region and anything else two departments might dispute. Circulate them and collect the disagreements. The disagreements are the finding.
5. Profile the source data before quoting. Measure duplicate rates on candidate keys, null rates on required fields, and format consistency across record age. Insist any proposal is priced after this, not before.
6. Establish whether an entity-matching rule exists. If two systems must be joined on customer, determine now what makes two records the same customer and what happens when the rule is ambiguous. Do not let this default silently.
7. Name the definitions owner. Identify the individual accountable for keeping metric definitions current. If nobody can be named, resolve that before building - it is the strongest single predictor of whether the result is still trusted in two years.
8. Set the refresh requirement honestly. Ask what decision changes if data is four hours old rather than five minutes old. Real-time requirements are expensive and much rarer than they are claimed to be; nightly serves most management reporting.
9. Confirm data residency and lawful basis. Establish where the platform stores data, whether UK or EU region hosting is required, and the lawful basis for processing personal data in an analytics context under UK GDPR. Determine whether a DPIA is needed before build, not after.
10. Budget for change, not just build. Allocate 15–25% of the build cost annually for model changes, new sources and maintenance. A build-only budget produces a system that ages out within two years.
11. Decide who maintains it. Establish whether the model will be maintained internally, by a partner, or jointly, and confirm the named people have the time. Choose tooling that matches that capability rather than the theoretical best tool.
12. Plan the parallel run. Commit to running old and new reporting side by side for one full reporting cycle and reconciling every variance in writing. Put it in the plan at the start so it cannot be compressed away at the end.

Two of these deserve a note on how they interact with UK obligations. Question 9 matters more than it appears: an analytics copy of operational data frequently contains personal data that the source system holds under a narrower purpose, and the lawful basis for analysing it is not automatically inherited. Where output materially affects individuals - credit decisions, employment decisions, service prioritisation - the ICO’s guidance points firmly towards a DPIA, and doing that at design time is straightforward where retrofitting it is not. Access control to the reporting layer is the other half of the same question, and the permission-boundary thinking set out in our guide to controlling what Microsoft 365 Copilot can see applies almost unchanged: a reporting platform, like an AI assistant, is only as safe as the narrowest permission set it can be made to respect.

Common mistakes to avoid

The failures below account for most of the reporting projects that end up abandoned, distrusted or costing several times their estimate. None of them is a technology failure.

• Choosing the platform before defining the questions. The platform decision absorbs disproportionate attention and determines relatively little. Businesses that select Snowflake, Fabric or BigQuery in month one and then start asking what the reports need to show have inverted the dependency, and will discover the requirements through rework.
• Treating a warehouse as a fix for data quality. Integrating four sources with inconsistent product codes produces an integrated set of inconsistent product codes, delivered faster and with more authority. Remediation is a separate workstream with its own cost, and it belongs in the plan explicitly.
• Building everything before delivering anything. Multi-year programmes that model the entire estate before publishing a single report lose sponsor support, because eighteen months of expenditure has produced nothing anyone outside the project can see. One trusted subject area in three months earns the runway for the next one.
• Copying the operational schema and calling it a reporting database. A replica solves contention but not query shape. If the reports were slow because they join eleven normalised tables, they will be slow on the replica too - just without affecting the live system. The modelling work is the point.
• Specifying real-time when nightly would do. Streaming pipelines cost several times more to build and considerably more to operate, and add failure modes that batch pipelines do not have. The test is whether a decision would genuinely be made differently on fresher data, and for management reporting the answer is almost always no.
• Leaving reference data in spreadsheets. Product hierarchies, regional mappings and target figures maintained in a file on somebody’s desktop become the weakest link in an otherwise governed pipeline. They need a governed home with version history and a controlled update process, even if that home is a simple table with an upload form.
• No tests and no alerting. A pipeline without row-count, uniqueness and referential integrity tests will eventually load wrong data silently, and the damage is measured in lost trust rather than downtime. Tests are inexpensive at build time and tedious to retrofit across an established estate.
• Skipping the parallel run. The reconciliation between old and new is what converts a technically complete system into one the finance director will sign off. Compressing it to save two weeks routinely costs six months of credibility.

One further pattern deserves a mention because it is easy to miss during procurement. Analytics platforms accumulate access quietly: a dashboard gets shared, a workspace permission gets widened for convenience, an export ends up in a shared drive. A reporting layer often concentrates data that is deliberately fragmented across operational systems, which makes it a higher-value target than the individual sources it draws from. Treating it as in-scope for the organisation’s normal access review and technical controls - rather than as a reporting tool exempt from them - is the correct posture, and the verification habits described in our Cyber Essentials Plus technical audit guide apply to it directly.

Data warehouse versus reporting database at a glance

The key facts and figures from this guide, collected for reference.

How Cloudswitched approaches database reporting

Cloudswitched scopes reporting work by starting with the questions rather than the platform. The first engagement is a fixed-scope discovery exercise: the recurring questions the business needs answered, the systems each one depends on, the definitions that are contested between departments, and a profile of the source data measuring duplicate rates, null rates and format consistency. The output is a written recommendation with a costed architecture, and it is frequently a smaller architecture than the enquiry assumed.

Delivery follows the sequence set out above - definitions, profiling, landing zone, conformed dimensions, facts and history, tests and alerting, BI layer, parallel run - with a first trusted subject area in production before the next one is scoped. Models are version-controlled, pipelines carry data quality tests that run on every refresh, and handover includes the metric dictionary and runbook rather than just credentials. Where the right answer is a reporting database rather than a warehouse, that is what gets built, and it is built so that it extends cleanly if a second real source later appears.

Frequently Asked Questions

What is the difference between a data warehouse and a reporting database?

A reporting database is a copy of one operational system’s data, restructured so reports run quickly and do not slow the live system. Its defining trait is a single primary source. A data warehouse integrates several systems that disagree with each other, imposes one set of agreed definitions across them, and keeps history the source systems overwrite, so a report run today for last March returns what it would have returned last March. The practical test is to list your recurring questions and count how many need more than one system and how many need point-in-time accuracy. If most need neither, the reporting database is the right answer and is roughly a fifth of the cost.

How much does a data warehouse cost for a UK SME?

A first trusted subject area in a multi-source warehouse typically costs £45,000 to £110,000 to build over three to nine months, with platform costs of £400 to £1,500 a month at mid-market volumes and an annual change budget of £10,000 to £25,000. A full-estate warehouse for a group structure runs from £110,000 upwards. By comparison, a modelled reporting database over a single system is £8,000 to £25,000 with platform costs under £400 a month. The figure most often missing from quotes is source data remediation, which cannot be priced accurately until the data has been profiled and is frequently the largest single line in the eventual total.

Should we use ETL or ELT?

ELT - load raw data into the destination first, then transform it there - is the sensible default for most UK businesses. Keeping the raw layer means transformations can be corrected and rebuilt without re-extracting from source systems that may have overwritten the evidence, and transformation logic written in version-controlled SQL is maintainable by a much larger group of people than a graphical ETL package. ETL still wins in specific cases: where personal data must be pseudonymised before it reaches the analytics platform, where destination compute pricing makes heavy repeated transformation expensive, or where an existing SSIS estate is working and well understood. The pattern matters less than whether the pipeline is idempotent, tested and alerting on failure.

Does normalisation make reporting slower?

Usually, yes, though the effect is smaller than it used to be. Normalised operational schemas avoid redundancy so that updates are safe and cheap, which is right for transactional workloads but means analytical queries traverse many joins on every execution. A star schema deliberately reintroduces some redundancy - storing category on the product dimension, region on the customer dimension - so the same question needs two or three joins instead of ten. Columnar analytical engines have narrowed the gap by making joins cheaper, so performance advice written for row-store SQL Server a decade ago should be applied carefully. The clarity argument for dimensional modelling holds regardless of engine.

Can we start with a reporting database and move to a warehouse later?

Yes, and where both options plausibly fit this is usually the better sequencing. The work that carries forward is substantial: the source profiling, the written metric definitions, the star schema itself and the BI layer all remain useful, and the reporting database typically becomes the first source feeding the warehouse. The asymmetry matters - starting small and growing costs relatively little if the larger requirement arrives, whereas starting with a warehouse and discovering the business only needed one system’s data faster is hard to unwind once a platform, toolchain and often headcount have been committed. The exception is when three sources genuinely disagree today and history is already required, in which case the intermediate step is just a delay.

How long does a data warehouse take to build?

Plan three to nine months to a first trusted subject area for a multi-source warehouse, and two to six weeks for a single-source reporting database. The timeline is driven far more by data quality and definitional disagreement than by engineering. A useful shape for a two-source integrated mart is roughly two weeks on questions and definitions, one to two weeks profiling sources, one week on platform and landing zone, two to three weeks on conformed dimensions, two weeks on facts and history, one week on tests and orchestration, two weeks on the BI layer and two weeks of parallel running. Programmes that model the entire estate before publishing anything tend to lose sponsor support before they deliver.

Do we need real-time data in our reporting?

Rarely, for management reporting. The honest test is whether a decision would genuinely be taken differently on data that is five minutes old rather than four hours old. Streaming pipelines cost several times more to build, considerably more to operate, and introduce failure modes that batch pipelines do not have. Genuine real-time requirements exist - operational dashboards driving live dispatch, fraud detection, stock availability on a trading website - and they are worth paying for where they exist. What is usually being described when someone asks for real time is a frustration with data being a week old, and a nightly refresh resolves that completely.

Where should a UK business host its data warehouse?

The major platforms all offer UK South or EU regions, and for most UK businesses hosting in a UK or EU region is the path of least resistance under UK GDPR, particularly where the data includes personal data or where customers ask about residency in due diligence. Beyond residency, the relevant questions are the lawful basis for processing operational personal data in an analytics context, whether a DPIA is needed because outputs materially affect individuals, retention periods for the analytics copy, and who has access. Deciding these at design time is straightforward; retrofitting them to a live platform with dashboards already in circulation is considerably harder.

What is a conformed dimension and why does it matter?

A conformed dimension is a single agreed description of an entity - customer, product, date, employee - that every fact table in the warehouse joins to in the same way. It matters because it is the mechanism by which integration actually happens. Without it, revenue from the finance system and opportunities from the CRM cannot be reported side by side, because there is no shared definition of the customer they both refer to. Building conformed dimensions is where most of the analytical judgement in a warehouse project sits, and it is why entity matching - deciding when a CRM account and a finance customer are the same organisation - needs an explicit rule and an exception list rather than a silent default.

Who should own the data warehouse after it goes live?

Two distinct roles need naming. Someone must own metric definitions - the business meaning of active customer, recognised revenue, sales region - and keep them current as the organisation changes. This is a business role, does not need to be full time, but does need to be a named responsibility rather than a shared assumption, because definitions go stale within about two quarters of a reorganisation. Separately, someone must own the pipelines operationally: receiving failure alerts, running the restore process, reviewing platform spend. That can sit internally or with a partner. Projects where neither is named are the ones that quietly stop being trusted.

Will a data warehouse fix our data quality problems?

No, and expecting it to is one of the more expensive assumptions in this area. Integrating four sources with inconsistent product codes and duplicate customer records produces an integrated set of the same problems, delivered faster and with more apparent authority. Remediation is a separate workstream with its own cost and its own owner, and it should appear in the plan explicitly rather than being absorbed into the build. What a warehouse does contribute is visibility: data quality tests running on every refresh make problems measurable and trackable rather than anecdotal, which is usually the first time an organisation can see the true scale of them.

What happens if we do nothing?

Spreadsheet-based reporting does not fail suddenly, it degrades. The consolidation takes a little longer each quarter, the key-person dependency deepens, and the number of figures nobody can fully explain grows. For many small businesses this remains a reasonable trade for years. It becomes untenable at three specific moments: when an acquisition adds a parallel finance system, when headcount growth makes the manual effort unsustainable, and when an external party - a lender, auditor or acquirer - asks for figures with documented lineage back to source. The third is the one worth planning for, because the timeline is set externally and is usually short.

Related reading

Further guides covering the planning, governance and delivery context around reporting infrastructure.

• Building an IT Roadmap: Aligning Technology Spend with Strategy
• Microsoft 365 Copilot Data Security: Controlling What Copilot Can See
• Network Monitoring: Spotting Problems Before Your Users Do
• Cyber Essentials Plus: What the Technical Audit Actually Checks
• AI Feature Prototyping: Validating Ideas Before Committing Budget

Start with the questions, not the platform

The choice between a reporting database and a data warehouse is not a choice between a cheap option and a good one. They solve different problems, and the cost difference between them reflects a genuine difference in what they do rather than a difference in quality. Writing down the recurring questions the business needs answered, mapping each to the systems it depends on, and testing which of them need history will settle the decision in an afternoon and before any platform has been evaluated.

What determines whether the result is still trusted two years later has little to do with which platform was chosen. It is whether someone owns the definitions, whether the pipelines are tested and alerting, whether there is a budget for change rather than only for build, and whether the parallel run was done properly. Those are the parts worth being uncompromising about.