Every few years a junior engineer asks me why data warehousing exists at all — why can’t we just query the production database directly and be done with it. It’s a fair question, and honestly the answer isn’t obvious unless you’ve either lived through the earlier systems or someone walks you through the mess they left behind. So let’s do that properly, starting from the actual beginning rather than jumping straight to star schemas.
The mainframe era: executive information systems
Go back far enough — we’re talking punch cards, then early mainframes through the ’60s and ’70s — and the closest thing to a data warehouse was what people called an executive information system, or EIS. I didn’t live through the punch card days myself, but I’ve worked alongside DBAs who did, and the stories are consistent: you had a computer that could store, update, and retrieve data, but getting information out of it meant submitting a request for a canned report. Somebody in IT would build that report to spec, it would run — sometimes for hours, sometimes overnight — and eventually you’d get your output.
There was nothing ad hoc about any of it. If management wanted a number that wasn’t already covered by an existing report, they had to go through developers, wait for the report to be written, wait again for it to run, and only then would the answer land on someone’s desk. Batch processing, essentially, with a human approval chain bolted onto the front of it.
Decision support systems: closer, but still clunky
The next step up was the decision support system, or DSS — the direct predecessor to what we’d now call a data warehouse. DSS gave users something genuinely new: direct, ad hoc access to the data, rather than waiting on a developer to hand-build every report. You could write your own query in whatever scripting language your platform supported — Perl, PHP, Python, Ruby, straight SQL — and pull what you needed yourself.
That sounds like progress, and it was, but it came with a catch. If you didn’t actually understand how to structure that query properly, you were right back to reports that crawled for hours. The access was there. The skill required to use it well wasn’t guaranteed.
And this is where the real problem starts to surface, because DSS queries were typically running straight against operational systems — the same systems handling live transactions.
Why OLTP systems were never built for this
Operational databases are designed in third normal form for a reason: minimize redundancy, keep the schema tight, optimize for fast, small, high-frequency transactions. That’s OLTP — online transaction processing — and it’s genuinely excellent at what it’s built for. Ask an OLTP system “what’s the price of this one item” and you’ll get an answer instantly, because that’s a tiny, indexed, single-row kind of question.
Ask it something like “how many units did we move in the Southwest region in Q3 of a given fiscal year” and you’re asking a completely different kind of question — one that scans, aggregates, and joins across a lot more data than a transactional system was ever tuned to handle. It’s not that the system can’t technically do it. It’s that doing it well conflicts with the entire design philosophy the schema was built around.
There’s a second layer to this too, one people underestimate: even when you get an answer back, it might not mean what you think it means. Data sitting in systems across different locations — say one office running its own database and another office running a completely separate one — doesn’t automatically reconcile. Field naming conventions differ. Formats differ. There’s no guarantee the “customer ID” in one system lines up cleanly with whatever the other system calls the same concept. Querying an OLTP system directly for analytics doesn’t just risk performance — it risks giving you a number that looks authoritative and isn’t.
So you end up with a fairly clean split: OLTP handles day-to-day transactional questions on current data, normalized to avoid redundancy. Analytics needs integrated data pulled across sources, typically denormalized into something like a star schema, deliberately trading some redundancy for query speed and clarity.
The extract-processing workaround, and why it didn’t hold up
The obvious fix, before data warehousing matured into what we know today, was to just extract the data somewhere else — pull it out of the operational systems and into a separate environment dedicated to analytics. That did take the query load off production, which was a real win. But it introduced a new set of headaches that took a while to fully appreciate.
For one, every extract is a snapshot frozen at the moment it was pulled. Extract today, extract again in a week, and you’re not comparing apples to apples — you’re comparing two different moments in time, possibly generated by slightly different logic or aggregation levels. Was one extract rolled up daily and the other weekly? Doesn’t always match. And because there was no single defined structure for these extracts, different teams ended up building their own versions of “the truth,” using the same underlying data but arriving at numbers that didn’t quite agree.
Field-naming mismatches carried straight through into the extracts too — no coherent, enforced consistency meant one extract might be missing information the other one had, with no rule guaranteeing completeness. What you got out the other end wasn’t unusable, but it wasn’t reliable enough to build serious decision-making on top of, and it certainly didn’t support any real drill-down analysis.
Data warehousing: solving the problem at the source
This is the part where data warehousing actually earns its place. Instead of scattering extracts across ad hoc destinations, you consolidate everything into a single central repository — legacy data you’re required to retain, current operational data, external data you’re bringing in from outside — all landing in one place, structured consistently.
From there, you can carve out data marts as needed: a subset scoped to HR, another scoped to marketing, whatever slices the business actually needs for decision-making. Either you query the full warehouse directly or you work off a purpose-built mart, depending on the scope of the question you’re answering.
What actually made this practical, rather than theoretical, came down to a handful of real technology shifts. Storage got dramatically cheaper — and I mean dramatically. I remember paying real money for a 160MB external drive that felt like a luxury at the time; a 2TB drive now costs less than dinner for two. That kind of cost curve matters more than people give it credit for, because storage used to be the limiting factor on how much history you could even afford to keep.
Beyond raw storage cost, you’ve got compression that lets you pack more data into the same physical space — sometimes completely transparent to whatever’s querying it, sometimes giving you real efficiency gains at the storage-engine level, more rows per block, fewer blocks competing for memory. You’ve got indexing strategies that speed up retrieval. And you’ve got partitioning, which lets you break enormous tables into logically manageable chunks based on how the business actually consumes the data — monthly partitions if that’s the natural reporting cadence, which also makes purging old data far less painful than it would be otherwise.
Put all of that together and you get a warehousing environment with a few concrete advantages over the extract-and-hope approach: one controlled, reliable source of information instead of a dozen inconsistent copies; no duplicated effort maintaining parallel extract pipelines; consistent naming and representation across the board, so a column means the same thing everywhere it appears; and no more arguing about which time period a given report actually reflects, because the warehouse gives you a defined, drillable structure — hourly, daily, weekly, monthly, quarterly, whatever granularity the question calls for.
Where this leaves you
None of this replaces OLTP systems — they’re still exactly right for the transactional workload they were built for. What changes is that analytics finally gets an environment designed for its actual access pattern, instead of being bolted awkwardly onto a schema optimized for something else entirely.
And once you’ve got that consolidated, reliable data sitting in a warehouse, the natural next question is how you actually turn it into decisions — which is really where business intelligence comes into the picture, sitting on top of the warehouse to make that data usable for the people asking the questions in the first place. That’s worth its own conversation, but the short version is this: you can’t build good BI on top of a shaky data foundation, and everything covered here is really about how that foundation got solid enough to build on.
