In this guide 6 sections
A reporting database is a second copy of your business data tuned for questions. A data warehouse is the same idea built for many sources and very large history. A data lake stores raw files of any kind. If your data fits in one database, start with the reporting database.
Three places to keep data for reports
Your billing software already holds data, but it was built to record invoices quickly, not to answer a question across three years of them. Reports run on it slow the cashier at the counter.
All three options solve that by keeping a separate copy. They differ in how much they hold, what shape the data takes and how much care they need once the first load is done and the consultant has left.
Size of the problem decides the choice. Fashion should not.
Reporting database
This is one ordinary database, often PostgreSQL or SQL Server, filled each night from your accounting, billing and CRM systems. Tables are tidied, dates are standardised and customer codes are matched across systems.
One developer or a part-time consultant can keep it running. Queries over a few years of invoices and stock movements come back in seconds on a modest server.
It suits a company whose total data fits comfortably in a single database. That describes most small and mid-size businesses.
- One server or one managed cloud instance
- Structured tables from a handful of systems
- Nightly or hourly refresh
- Upkeep measured in hours a month
Data warehouse
A warehouse is a reporting database designed for scale. It uses fact and dimension tables and stores the history of changes. Often it runs on a cloud service built to scan billions of rows from many sources at once.
It earns its cost when you have many systems, many analysts and a history deep enough that one database slows down. It also needs someone who understands modelling and load scheduling.
A warehouse bought too early sits idle. It still bills every month.
- Handles many sources and many users at once
- Keeps change history, such as a customer’s old address
- Needs a modelling plan before the first load
- Cost grows with data scanned and storage held
Data lake
A lake is cheap file storage holding raw material as it arrives: CSV exports, JSON from an app, PDFs, sensor logs, images. Nothing is tidied until someone needs it.
That freedom suits machine learning projects and sensor-heavy plants. It frustrates a finance head who wants last month’s receivables, because a lake answers nothing until query tools are built on top of it.
Most small companies that say they need a lake need a folder policy and a reporting database instead.
A rule of thumb for your size
Ask three questions. Does all your reporting data come from fewer than ten systems? Does it fit in one database? Do fewer than a dozen people query it? Three yeses mean a reporting database.
Move to a warehouse when nightly loads begin missing their window, when queries slow down, or when a third team needs its own model of the same data. Add a lake only when you hold files that no table can describe.
Count your source systems on one page this week, and note the size of each.
Where should our data live scorecard
Your source systems run down the page as rows, with columns for record count, kinds of data, refresh needed, number of people querying and current pain. A scoring strip at the foot adds ticks under reporting database, warehouse or lake, so the pattern shows where your company actually sits today and what would move it.
Open a blank worksheet to printQuestions owners ask
Is a reporting database the same as a backup?
No. A backup exists to restore the live system after a failure. A reporting database is reshaped for questions, with tidied tables and matched codes, and it is refreshed on a schedule.
Can we start small and move to a warehouse later?
Yes. Keep definitions on a signed sheet and loads in scripts you own, and the move is straightforward, since the tables and the business logic carry over.
Do we need a data lake for AI projects?
Only if the inputs are files such as images, documents or sensor logs. Structured business records used for forecasting usually sit well in a reporting database.
Which is cheapest to run?
The reporting database, for a small company. Its server is small and its upkeep is light, while warehouses and lakes bill by storage and processing as use grows.