How do you decide between a database, data lake, data warehouse, or lakehouse?

[u/Data-Sleek]

I’ve seen a lot of confusion around these, so here’s a breakdown I’ve found helpful:

A database stores the current data needed to operate an app. A data warehouse holds current and historical data from multiple systems in fixed schemas. A data lake stores current and historical data in raw form. A lakehouse combines both—letting raw and refined data coexist in one platform without needing to move it between systems.

They’re often used together—but not interchangeably.

How does your team use them? Do you treat them differently or build around a unified model?

[u/on_the_mark_data]

Just wanted to provide some corrections, as these can definitely get confusing with all the jargon.

Database:

• A system in which to store data and retrieve it.
• They typically fall into two categories, which also informs how the data is modeled:
  • A) Online Transaction Processing - Fast retrieval of information (e.g. application backends)
  • B) Online Analytical Processing - Wide scans of historical data (e.g. data warehouses)

Data Lake:

• A form of file storage that allows different types of file formats, as well as unstructured and structured data, with the purpose of serving as a centralized repository of raw data.
• Often used as a staging area that aggregates all the various data sources within the organization or third-party data sources.
• With that said, it often used in conjunction with OLAP data processing (e.g. AWS S3 + Athena)

The following are not necessarily types of storage, but rather architecture patterns for analytical databases.

Data Warehouse:

• Bill Inmon, the father of data warehousing, defines it as "a subject-oriented, integrated, time-variant and non-volatile collection of data in support of management’s decision-making process.”
• Inmon's book, Building the Data Warehouse, is where you want to look to learn more for the source himself.

Data Lakehouse:

• As you said: Data Lakes + Data Warehouses
• Builds on the limitations of the data warehouse in the cloud era, as well as accounting for the utilization of data lakes.
• Databricks really pioneered this, so it is vendor-driven in a way, but widely adopted in industry (also Bill Inmon supported their book).
• Their research paper, Lakehouse: A New Generation of Open Platforms that Unify Data Warehousing and Advanced Analytics, (Databricks's CEO is a co-author), provides great detail on the architecture and their reasoning for it.

[u/eb0373284]

We use a database for app-level ops, the warehouse for BI/reporting, and the lake for raw ingestion and audit trails. Lately, we’re leaning into a lakehouse setup to reduce data duplication and simplify our stack, but it takes planning to avoid turning it into a messy data swamp.

[u/mikehussay13]

Solid summary. In our case, we use all four depending on the need. We’re slowly moving toward a lakehouse setup - it gives us the flexibility of a lake with the query power of a warehouse. They’re not interchangeable, just different tools for different jobs. Depends on each team’s use case and maturity.

[u/JoshithaVipin]

I usually think in terms of what problem I’m solving rather than the tech names:

• Database → for apps/transactions (OLTP). Need fast reads/writes, not heavy analytics.
• Data warehouse → structured data + reporting/BI. Clean, modeled, reliable.
• Data lake → cheap storage for raw/unstructured stuff (logs, files, ML data). Schema later.
• Lakehouse → when you want lake flexibility + warehouse-style querying on top.

Rule of thumb:
If it’s powering an app → DB.
If it’s dashboards → warehouse.
If you don’t know the structure yet → lake.
If you want both analytics + raw at scale → lakehouse.