Databases, Data Warehouses, and Data Lakes

They Look Alike, But They Play Different Roles

Part 4 of 12 in "Databases for Non-Developers". This part covers how three "data stores" — operational databases, data warehouses, and data lakes — differ in what they're for.

---

Imagine a customer waiting at a checkout counter while the employee starts a five-year sales analysis on that same register. Checkout slows down and the line grows. Digital services face the same split: processing today's orders and analyzing years of orders are fundamentally different jobs.

That difference is exactly why three kinds of stores exist.

Operational Databases: Handling What's Happening Right Now

An operational database processes current work quickly and accurately — things like sign-ups, orders, payments, reservations, and inventory changes. It reads and updates relatively small numbers of records, frequently.

If a customer edits their address, that one record needs to change fast. This kind of work is also called online transaction processing (OLTP). The relational databases from Part 3 are the classic example.

Key traits:

• A small amount of data, at very high speed, for many users at once
• What matters is the accurate value right now (current stock, current balance)
• If it stops, revenue and service stop immediately

Data Warehouses: Gathering Data from Many Systems for Analysis

A data warehouse is a store where data from multiple business systems is collected and organized for reports and analysis. It suits questions that read large amounts of data at once, like "how have sales and return rates by region changed over the past three years?" This kind of work is called online analytical processing (OLAP).

Because different operational systems may name customers or products differently, standardizing formats and clarifying meaning as data moves into the warehouse is a critical step. This transformation work is called ETL (extract, transform, load). A well-organized warehouse helps an organization look at the same numbers and have one conversation.

Representative examples: Snowflake, Google BigQuery, Amazon Redshift, Microsoft Fabric family

Data Lakes: Storing Diverse Data Close to Its Original Form

A data lake stores not just tidy table data but all kinds of material — logs, images, audio, documents — kept relatively close to its original form. Its strength is the flexibility of "we don't know what analysis this will feed yet, but let's keep it." It's often used to gather raw material for machine learning training.

Like a lake, it can hold a lot. But if you pile up data with no documentation and no owner, it becomes a 'data swamp' where no one knows what's what. Without managing a data catalog, sources, quality, and retention periods, the accumulated data becomes a security risk and a cost rather than an asset.

Lakehouse: An Attempt to Combine Both Approaches

A data lakehouse is an approach that adds warehouse-style management and analytics to the lake's flexible storage. That said, the boundary varies by product and design, so you shouldn't judge by the name alone. When someone says "it's a lakehouse," a good follow-up question is: "Which management features does it actually provide?"

The Three Stores Compared

Why Dashboard Numbers Don't Match

A sales report may say 100,000 customers while a marketing screen says 120,000. That doesn't necessarily mean one is wrong. It depends on how "customer" is defined — someone who has purchased, or anyone who registered an email. When cancelled orders are excluded from revenue also creates differences.

Building a good analytics system doesn't end with copying data into one place. You have to agree on what each metric means, how it's calculated, when it's refreshed, and who owns it. That's exactly the point that leads into data governance, the topic of Part 7.

Do You Have to Separate Operations and Analytics?

For a small service, a single relational database may handle both operations and simple reporting just fine. Review a separate analytics store when data grows and complex analysis starts to slow the service down.

Building a giant data platform from day one can make cost and management burden outweigh business value. On the other hand, if heavy reports run against the operational database and customers feel the slowdown, you're paying the price for delaying separation. The practical signal for when to separate is: "is analysis starting to slow the service down?"

Key takeaways Operational databases process today's transactions, data warehouses consolidate data from multiple systems for analysis, and data lakes store diverse raw material broadly. Whichever store you use, if the meaning of metrics and the owners aren't defined, the same data leads to different conclusions. And a lake accumulated without management isn't a lake — it's a swamp.

Questions to Think About

• What exactly do "active users," "customers," and "revenue" mean in our dashboards?
• Who approves those definitions, and where are they documented?
• Has analysis work ever slowed down the customer-facing service?
• Do we have stored data whose source and owner nobody knows?

Next: questions to ask before database performance when choosing a database product.

---

Series Contents

1. What Is a Database — How Is It Different from an Excel File?
2. How Databases Protect Accuracy
3. What Kinds of Databases Are There?
4. Databases, Data Warehouses, and Data Lakes ← You are here
5. Why Choosing the Right Database Matters
6. Data Models and Data Quality
7. What Is Data Governance?
8. Collect Less Personal Data — Only What's Needed
9. The Basics of Data Security
10. Why Data Breaches Happen, and What to Do When One Does
11. Cloud and Serverless Databases
12. Databases in the AI Era