Data Storage Architectures: Warehouse, Lake, Lakehouse
This lesson compares the architectural pattern behind your storage — separate from any specific vendor — on description, trade-offs, and where each fits.
- Distinguish an operational database (OLTP) from analytical storage patterns
- Compare warehouse, lake, and lakehouse on schema handling, cost, and consistency guarantees
- Explain where data marts and data mesh fit relative to a warehouse or lakehouse
- Map the HIC medallion layers onto the warehouse storage pattern
Where each pattern sits
Source systems feed the operational database that runs the application. Analytical copies of that data then flow into one of three analytical stores, depending on how much structure you apply and when.
Operational Database (OLTP)
The live transactional system of record — for example, the database DHIS2 itself runs on.
| Pros | Cons |
|---|---|
| Optimized for fast, reliable reads/writes of individual records | Not designed for heavy aggregations or large scans |
| Strong consistency (ACID transactions) | Running analytics directly on it risks slowing down the live application |
| Already exists — nothing new to stand up |
Data Warehouse
A structured, schema-first store built specifically for analytics — data is cleaned and modeled before it lands. Examples: Snowflake, BigQuery, Redshift, Postgres.
| Pros | Cons |
|---|---|
| Fast, reliable queries — data is pre-modeled (star schemas, dims/facts) | Rigid — schema must be defined before loading (“schema-on-write”) |
| Strong consistency, governance, and access control | Struggles with unstructured data (images, free text, logs) |
| Mature BI tool support | Can get expensive at scale depending on pricing model |
Data Lake
A large, low-cost store that holds raw data in its native format — structured, semi-structured, or unstructured. Examples: Amazon S3, Azure Data Lake, Google Cloud Storage.
| Pros | Cons |
|---|---|
| Cheap storage, scales to massive volumes | Easy to end up with a “data swamp” — unorganized, undocumented, hard to trust |
| ”Schema-on-read” — load first, structure later, keep everything | No native transactions or strong consistency guarantees |
| Handles any file type: JSON, images, logs, video, DHIS2 API dumps | Analysts usually can’t query it directly without extra tooling |
Lakehouse
Combines lake-style cheap storage with warehouse-style transactions, schema enforcement, and fast SQL — one platform for both. Examples: Databricks, Snowflake (via Iceberg), MotherDuck + DuckLake.
| Pros | Cons |
|---|---|
| One copy of data serves both BI and ML/AI workloads — no separate lake + warehouse to keep in sync | More moving parts — table format, catalog, compute engine — to understand |
| Open table formats (Delta Lake, Apache Iceberg) avoid vendor lock-in | Can be complex to manage for teams that only need interactive SQL, not ML |
| Supports ACID transactions on top of cheap object storage |
Data Mart
A small, focused subset of a warehouse built for one team or one subject area — for
example, your gold/aggregate and gold/tracker folders from the DHIS2 demo.
| Pros | Cons |
|---|---|
| Fast, simple, purpose-built for a specific audience (e.g. “malaria program mart”) | Can duplicate logic across if not built carefully on shared models |
| Easier for non-technical users to navigate than a full warehouse | Not a full solution on its own — sits downstream of a warehouse or lakehouse |
Data Mesh
An organizational pattern (not a technology) where each domain team owns and publishes its own data as a product.
| Pros | Cons |
|---|---|
| Scales well across large organizations with many independent teams/programs | Requires strong governance and standards to avoid fragmentation |
| Domain teams closest to the data own its quality | Overkill for small teams — needs organizational maturity, not just tooling |
At a glance
| Architecture | Coding / setup | User-friendliness | Best for |
|---|---|---|---|
| Operational database (OLTP) | Medium — usually already managed by IT/DBAs | High for simple queries, poor for analytics | Powering the application itself, not reporting |
| Data warehouse | Medium — SQL-centric, dbt-friendly | High for analysts once modeled | Routine reporting and BI on structured, well-understood data — exactly what your Gold layer is |
| Data lake | High — needs engineering to keep it organized | Low for end users, higher for engineers | Landing raw exports (e.g. DHIS2 API JSON pulls) cheaply before they’re modeled — a natural home for your Bronze layer if using object storage |
| Lakehouse | High — more concepts: catalogs, table formats, engines | Medium — powerful but more to learn | Teams that need both BI dashboards and ML/AI on the same DHIS2 + other-source data, without duplicating pipelines |
| Data mart | Low — just more dbt/SQL models on top | Very high for the target audience | Handing a specific program team (e.g. immunization, ANC) exactly the Gold-layer tables they need, nothing more |
| Data mesh | High — organizational + technical complexity | Low–medium — depends entirely on team maturity | Large, multi-country or multi-program DHIS2 deployments where each program manages its own pipelines |
Plotting user-friendliness against coding and setup effort makes the trade-off visible:
How this maps to the course. The HIC warehouse follows the data warehouse pattern with medallion layers — Bronze mirrors what a data lake gives you (raw, schema-on-read), while Silver and Gold apply the structure and modeling that make it a warehouse. See Lab 3: Medallion dbt Modeling.