Two answers to the same problem
The databases that run day-to-day applications are built for many small, quick actions: add an order, update an address, look up a ticket. Analysis asks a different kind of question: how did sales change across all regions over three years? Running heavy questions like that against a live application database can slow the application down, and the data it needs is often spread across several systems.
Organizations therefore copy data into separate places built for analysis. Two common designs are the data warehouse and the data lake.
What a data warehouse is
A data warehouse is a central store of cleaned, organized data from many sources, structured for reporting and analysis. Data is loaded in after being checked, reshaped and made consistent. The shape of the data is decided before it is stored, so the approach is called schema-on-write.
Typical features:
- Data is organized into tables, often in a pattern with central “fact” tables (events such as sales) surrounded by descriptive “dimension” tables (such as product, date and region).
- It is queried with SQL.
- It holds history, so trends over time can be examined.
- The data has been prepared, so people in different departments can usually get consistent answers from it.
Warehouses are closely tied to business intelligence, where analysts and managers rely on trusted figures for reports and interactive dashboards.
What a data lake is
A data lake is a large store that keeps data in its original, raw form, whatever the format: spreadsheets, log files, images, documents, sensor readings, exports from other systems. Data is stored first, and structure is applied later when someone reads it. This is called schema-on-read.
Typical features:
- It accepts structured, semi-structured and unstructured data.
- Storage is usually inexpensive per unit, often built on cloud storage that grows as needed.
- It is flexible: you do not have to decide up front how the data will be used.
- It suits data science and exploratory work, including large volumes sometimes described as big data.
Main differences
| Aspect | Data warehouse | Data lake |
|---|---|---|
| Data form | Cleaned, structured tables | Raw, any format |
| When structure is applied | Before storing (schema-on-write) | When reading (schema-on-read) |
| Typical users | Analysts, report writers, managers | Data engineers, data scientists |
| Strength | Consistent, trusted, quick reporting | Flexibility and low-cost storage of everything |
| Main risk | Slow to change; effort to model data | Becoming disorganized |
| Query tools | Mostly SQL | SQL and other processing tools |
The “data swamp” problem
A lake without care can turn into a “data swamp”: heaps of files that nobody can find, understand or trust. Avoiding this takes effort:
- A catalog or inventory describing what each dataset is, where it came from and who owns it.
- Naming conventions and folder structure.
- Basic data cleaning for datasets that will be shared.
- Clear ownership and review.
Without those habits, the flexibility of a lake becomes a liability, because freedom to store anything easily becomes freedom to store things nobody can use.
Governance, security and privacy
Both designs concentrate a lot of information in one place, which raises the stakes for protection. Practical points include:
- Access should be limited by need, using ideas such as least privilege, and reviewed periodically.
- Data should be labeled with a sensitivity level using a scheme for data classification, so that confidential material is handled accordingly.
- Personal data copied into an analytical store is still personal data, and the rules that apply to the original system generally follow it. Data retention periods should apply to copies too, which is easy to forget.
- Encryption of stored data and data in transit is standard good practice.
- Analytical stores need backups or a clear way to rebuild them from source, and the rebuilding process should be tested.
ETL and ELT
Getting data in involves a pipeline. In ETL (extract, transform, load), data is pulled from sources, reshaped, and only then loaded into the warehouse. In ELT, data is loaded first and transformed inside the destination, which fits lakes and modern warehouses with plenty of processing power. Both approaches need scheduling and monitoring, and task automation with scripts is often part of keeping them running.
The lakehouse and mixing the two
The line between the two has blurred. Many organizations keep raw data in a lake and then build cleaned, structured layers on top, sometimes in a separate warehouse. A design that tries to offer warehouse-style tables and rules on top of lake-style storage is often called a lakehouse. The label is used loosely, so check what a particular product actually provides.
Choosing between them
Questions that help:
- Who will use the data, and for what? Standard reporting suggests a warehouse. Exploration of varied data suggests a lake.
- How varied is the data? Mostly tables from business systems fit a warehouse. Many file types point to a lake.
- How much consistency do you need? Finance and compliance reporting usually needs the agreed definitions a warehouse enforces.
- What skills does the team have? A lake demands more engineering skill to keep orderly.
- What will it cost over time? Cloud services typically charge for storage and processing separately, and cloud costs can grow in surprising ways if queries and stored data are not watched.
Smaller organizations often do not need either at first. A well-run operational database or a few well-kept reporting tables may be enough, and it can make sense to add a warehouse only once reporting begins to strain the systems or sources multiply.
Not a replacement for operational databases
Neither design replaces the transactional database behind an application. Those databases still handle live activity, and the warehouse or lake receives copies. Understanding how that operational side works, covered in database basics, makes the analytical side easier to follow.