On this page 9 sections
A data warehouse is a central database built for reporting and analysis. It collects copies of data from your other systems, such as accounting, ERP, CRM, your online shop and spreadsheets, cleans that data, keeps its history and puts it all in one trusted place for answering questions about the whole business.
Short answer
- A data warehouse is a separate database designed for reports and analysis, not for day-to-day transactions.
- It combines data from many systems, cleans it and keeps history that your other systems may overwrite.
- The main benefits are one version of the numbers, faster reports, no extra load on live systems, and a solid base for dashboards and AI.
- It's worth it when your data lives in several systems and month-end reporting means days of copying into spreadsheets.
- It's usually not worth it yet if one system already gives you the reports you need.
What is a data warehouse?
Google describes a data warehouse as a platform "used for the analysis and reporting of structured and semi-structured data from multiple data sources", such as point-of-sale, marketing and CRM systems (Google Cloud (opens in a new window)).
Your everyday systems are built to record things fast: a sale, an invoice, a stock movement. IBM compares that kind of database to a filing cabinet built for fast transaction processing, while a warehouse stores data from any number of applications (IBM (opens in a new window)). If you run an ERP system, a CRM and an online shop, each one holds part of the picture. The warehouse is where those parts meet.
How data gets into a data warehouse
Data is copied in on a schedule, often every night or every hour, using a data pipeline. There are two common patterns (AWS (opens in a new window)):
- ETL (extract, transform, load): data is pulled from each system, cleaned and reshaped, then loaded.
- ELT (extract, load, transform): raw data is loaded first and cleaned inside the warehouse, which works well when the warehouse is powerful enough to do the heavy lifting.
How the data is organised
Most warehouses arrange data around the questions people ask. A common design is the star schema: one central table of events, such as sales, surrounded by tables that describe them, such as customer, product, branch and date (IBM (opens in a new window)). That makes questions like "sales by product by region per month" quick to answer. A smaller slice built for one department, such as finance, is called a data mart.
Data warehouse vs database vs data lake
These terms get mixed up, so here's how they compare.
| Everyday database | Data warehouse | Data lake | Lakehouse | |
|---|---|---|---|---|
| Main job | Record transactions as they happen | Reporting and analysis | Store large amounts of raw data cheaply | Both lake and warehouse in one |
| Data | Current, from one system | Cleaned, historical, from many systems | Raw, any format | Raw and cleaned, in open formats |
| Structure | Fixed, per application | Defined before loading | Applied when the data is read | Layers from raw to cleaned |
| Typical users | Staff using the app | Managers, analysts, dashboards | Data engineers, data scientists | Analysts and data scientists |
| Example question | "What's on this invoice?" | "How did margin change per branch over three years?" | "What can we learn from five years of sensor logs?" | Both of the above |
A lakehouse, as offered by platforms such as Databricks, combines the low-cost storage of a lake with the reporting speed of a warehouse. If you're comparing the two, our page on Databricks consulting explains how a lakehouse works in practice.
Why use a data warehouse? The main benefits
One version of the numbers
When sales, finance and operations each pull their own reports, the totals rarely match. A warehouse applies the same rules once, such as what counts as a sale or which branch a customer belongs to, so every report starts from the same figures. IBM lists improved data quality as a key benefit (IBM (opens in a new window)).
Reports that don't slow down your live systems
Heavy reports run against your ERP or shop database can slow it down for staff and customers. AWS notes that separating analysis from the transactional system keeps it from affecting that workload (AWS (opens in a new window)).
History you'd otherwise lose
Many systems only keep the current state: today's price, today's stock level, today's customer address. A warehouse can keep snapshots over time, so you can compare this year with last year on a like-for-like basis.
Faster answers, less spreadsheet work
Once the data is in one place, tools like Power BI or Grafana can show live dashboards instead of someone rebuilding the same spreadsheet each month.
A base for AI
AI and machine learning need clean, joined-up data. IBM and Google both list support for AI as a benefit of a well-run warehouse (IBM (opens in a new window), Google Cloud (opens in a new window)). If you're planning AI solutions, sorting out your data first saves time and money later.
When to use a data warehouse
A data warehouse usually earns its place when several of these are true:
- Your data lives in three or more systems that don't talk to each other.
- Month-end or board reporting takes days of exporting and pasting.
- Different departments quote different numbers for the same thing.
- You need trends over several years, but your systems only keep current values.
- Reports are slowing down the systems your staff use every day.
- You want dashboards that update on their own.
- You're planning AI or forecasting projects.
When you don't need one yet
A warehouse is a real project with running costs, so it's not always the right first step:
- One main system: if your ERP or accounting package already reports what you need, use its reports.
- Small data, simple questions: well-built reports on your existing database, or a read-only copy of it, may be enough.
- Messy processes: if the same customer is captured three different ways, fix the capture first, or the warehouse will only report the mess faster. A sensible middle step is a small warehouse on a database you may already run, such as PostgreSQL or SQL Server, that you can move to a cloud platform later.
Cloud or on-premise? Options and costs
Most new warehouses run in the cloud, where you pay for storage and for the queries you run. Examples include Google BigQuery, Amazon Redshift, Microsoft Fabric Data Warehouse and Databricks SQL. Microsoft, for example, describes Fabric Data Warehouse as a relational warehouse on a data lake foundation, built mainly with SQL (Microsoft Learn (opens in a new window)).
To give a sense of cost, BigQuery's free tier covers the first 1 TiB of queries and 10 GiB of storage each month. After that, on-demand queries cost USD 6.25 per TiB scanned in the US multi-region (BigQuery pricing (opens in a new window)). Rand costs vary with the exchange rate, and prices differ by region.
Two points matter for South African businesses:
- Where the data lives: BigQuery, for example, offers a Johannesburg region (Google Cloud (opens in a new window)). If personal information is stored outside South Africa, POPIA section 72 sets conditions for that transfer (POPIA s72 (opens in a new window)).
- Who can see what: a warehouse puts a lot of sensitive data in one place, so access control and logging matter from day one. On-premise is still an option if you already run your own servers, but you then carry the hardware, backups and upgrades yourself. Our Cloud & DevOps team can help you weigh up the two.
Which should you choose?
| Your situation | A sensible next step |
|---|---|
| One main system, reports mostly fine | Use and improve that system's reports |
| Two or three systems, monthly spreadsheet reporting | A small warehouse on PostgreSQL or SQL Server, with scheduled loads |
| Many systems, growing data, need for dashboards | A cloud data warehouse in a South African region where possible |
| Large volumes, raw files, logs or AI plans | A lakehouse platform such as Databricks |
| Not sure | A short review of your systems and reports before choosing anything |
How Mammoth Digital can help
Our team works with Databricks, SQL Server and PostgreSQL, and includes a Databricks Certified Data Engineer Professional. We can review what you have, tell you honestly whether you need a warehouse yet, and if you do, design it, build the data pipelines and set up the dashboards.
See our Databases & Data services or contact us to talk through your reporting.
BigQuery is a trademark of Google LLC, Amazon Redshift of Amazon.com, Inc., Microsoft Fabric and SQL Server of Microsoft Corporation, and Databricks of Databricks, Inc. Mammoth Digital is independent and not a partner of these companies.
Facts checked on 27 October 2026. Sources: Google Cloud, IBM, AWS, Databrick, POPIA s72.