Three posts in, we’ve covered why data warehouses exist, what business intelligence and big data add on top, and what actually distinguishes a warehouse from an OLTP system on paper. What we haven’t covered yet is the part every architect eventually has to answer out loud in a design review: which architecture are you actually building, and how are you rolling it out. That’s this post.
Three flavors of architecture, and why they’re not interchangeable
There are three architectures that come up over and over: a basic data warehouse, a data warehouse with a staging area, and a data warehouse with both a staging area and data marts sitting downstream of it. Each one solves a different problem, and picking the wrong one for your situation tends to show up as pain about a year later.
The basic architecture
At its simplest, a data warehouse stores raw data pulled straight from wherever it lives — for a retailer running two locations, say one on MySQL and one on Oracle, that’s your two source systems feeding in unprocessed data. Alongside that raw data sits summary data: pre-computed aggregates, sometimes implemented as materialized views, that exist specifically so nobody has to recompute the same aggregation every time someone runs a report.
And then there’s metadata, which is easy to gloss over but does real work. You’ve got business metadata — ownership, business definitions, who’s accountable for what. Administrative metadata, tracking whether a given dataset is active, archived, or slated for purge. And technical metadata, which describes how the summary data actually got built — what granularity it’s aggregated at, what it was aggregated from. Without that layer, your summary tables are just numbers nobody can trust or explain.
An enterprise-scale warehouse built this way tends to share a few traits: it’s large in scope, genuinely covers the whole business rather than one department, spans every subject area rather than being scoped to a single application, and gets built up incrementally rather than in one shot. The upside of doing it this way is a single, synchronized source of enterprise-wide data that everything downstream, including any data marts you build later, can draw from consistently.
Adding a staging area
The basic architecture starts to strain the moment you’re pulling from genuinely messy, inconsistent sources — which, in practice, is almost always. Say your two-location retailer needs to consolidate and clean data before it ever touches the warehouse. That’s exactly what a staging-area architecture is for.
Sources feed in from wherever they actually live — operational systems in each location, legacy or historical data sitting in some older system, maybe even external data somebody emailed you that needs to be folded in. All of that lands in a staging area first, where it gets cleansed. You’ll often see an Operational Data Store, or ODS, in this layer too — think of it as a frequently-refreshed, integrated copy of the source data, sitting between the raw sources and the warehouse proper.
From staging, the transformation work happens, and only then does clean, usable data land in the warehouse’s presentation area — the layer people actually query from, using whatever access tool fits: straight SQL through something like SQL*Plus or SQL Developer, or another interface entirely. Metadata sits alongside this whole flow too, effectively acting as a roadmap to the data — what it is, where it came from, when it landed.
Staging plus data marts
Even with a clean, well-integrated warehouse, business analysts often don’t want to wade through the entire enterprise dataset just to answer a question scoped to their own product line or region. That’s the problem data marts solve, and it’s the third architecture: staging area, warehouse, and a layer of data marts sitting on top.
A data mart can be sliced by subject — HR, sales, marketing — or geographically, splitting sales data into North America, EMEA, APAC, whatever boundaries make sense for who’s accessing it. The point is scope: maybe you only want your regional sales team querying their own region’s data mart, while corporate-level users still go straight to the full warehouse for the enterprise-wide view.
It’s worth being precise about what a data mart actually is, because it’s not just a smaller warehouse. Scope-wise, a warehouse is enterprise-level; a mart is typically department- or region-level. A warehouse spans multiple subjects; a mart is usually built around one — a single line of business. A warehouse draws from many sources; a mart typically draws from few, since it’s really just a subset carved out for a specific purpose. And warehouses tend to take months to years to build fully, where standing up an individual mart is comparatively quick.
There are two real flavors of data mart worth distinguishing. A dependent data mart draws from the already-built central warehouse — which means the ETL work is largely done already; you’re mostly deciding what subset of already-clean data moves into the mart, more like copying data from one database to another than doing fresh integration work. An independent data mart, by contrast, pulls directly from operational or external sources without going through the warehouse first, which sounds convenient but usually means dealing with unclean, inconsistent data all over again.
I’ll be upfront about where I land on this: dependent data marts tend to work out better in practice, purely because the cleansing and integration headache has already been solved upstream. Independent marts are quick to stand up and genuinely useful for a fast proof of concept, but that speed comes at the cost of redoing data-quality work that a dependent mart gets for free.
Take a retailer selling software, laptops, and printers as an example. Push printer sales data into its own mart, and the team focused on printers has a lot less to filter through to find what they need — better performance, and arguably a layer of access control too, since maybe you don’t want the printer sales team browsing laptop numbers, even though someone at the corporate level still needs visibility across all of it from the warehouse itself.
How you actually build one of these
Here’s the thing about data warehouse projects that catches people off guard: the technical build usually isn’t the hard part. Deciding how you’re going to approach it is where most of the real time and friction lives — departmental politics, competing priorities about what should come first, disagreements about scope. Plan, then plan some more, and expect the plan to still need revising.
There are broadly two development strategies people reach for: the Big Bang approach, and an incremental approach, which itself splits into top-down and bottom-up variants.
Big Bang means building the entire enterprise warehouse in one continuous effort — analyzing every requirement across the business up front, building the full enterprise data model, then deciding what access layer or data marts sit on top. It’s tempting because it feels efficient, one project instead of a string of them. In practice, this is the approach I’d steer most clients away from unless they genuinely understand the full scope going in, because it carries real risk: requirements pile up faster than anyone expects, you end up constantly stepping back to re-architect something that didn’t work the way you assumed, and one late change tends to ripple into several other pieces you thought were settled. It can absolutely work, but it demands a level of upfront clarity that most organizations, honestly, don’t have yet when they start.
The incremental approach takes the opposite stance: tackle one subject area at a time. Come up with a strategy, decide what’s first, analyze it, design it, build it, put it into production — then repeat that whole cycle for the next subject. It takes more iterations, and the total timeline across all of them often runs longer than a theoretical Big Bang would, but each phase is independently validated before you move to the next, and every round gives your team more experience going into the following one. The tradeoff is that organizational pressure to “just get it all done now” doesn’t always leave room for that patience.
Within incremental, you’ve got two directions to run it. Top-down starts by analyzing requirements at the full enterprise level, building an information model, then prioritizing which subject areas to tackle first — followed by the usual analyze-design-build-implement cycle per subject. This tends to deliver a return on investment fairly quickly since you’re not waiting for the entire enterprise build to finish before anyone sees value, though there’s still real upfront cost before that return shows up, and scope boundaries matter a lot here — where you draw them shapes how the whole rollout unfolds.
Bottom-up instead starts by scoping and analyzing individual source systems, building out incrementally based on immediate business use, then figuring out architecture, metadata, and the ETL and load process from there. It tends to work well as a proof of concept and is often more comfortable for IT teams to execute against, but it comes with a real disadvantage: as business needs shift, the framework underneath can need real rework, since it wasn’t built from an enterprise-wide model in the first place.
Neither top-down nor bottom-up is objectively correct — both carry genuine tradeoffs, and which one fits depends heavily on organizational appetite for upfront planning versus getting something usable in front of stakeholders fast.
Tying it together
Architecture and rollout strategy are really two sides of the same decision. Pick a basic architecture and you’re implicitly committing to less staging discipline; add a staging area and ODS layer and you’re buying data quality at the cost of more upfront engineering; add data marts and you’re trading a single access point for scoped, faster access at the department level. Layer a development approach on top of that — Big Bang if you genuinely know the whole scope, incremental if you’d rather validate as you go — and you’ve got the real shape of a data warehouse project, well before anyone writes a line of ETL code.
