Databases, Warehouses And Data Lakes Explained

Main Point

Choose a storage and processing design from the workload. A system that records a current order serves a different purpose from a system that analyzes years of orders across sources. Copies and additional layers can help, but they add freshness, lineage, access and operating responsibilities.

Key Points

• Choose stores from required reads, writes, history and formats.
• Specify consistency and freshness for each path.
• Treat backup recovery and analytical copies as separate responsibilities.

1. Match storage responsibilities to the workload

An operational database supports live application activity such as creating orders, changing status, and recording payments. An analytical warehouse organizes information for reporting across records and sources. These are common workload distinctions; actual implementations vary.

Transactions group related changes under a system’s configured guarantees. In a payment example, recording a payment and updating an associated balance should not be left half-complete after an interruption. PostgreSQL 18 documents a transaction example using begin, commit and rollback. That example illustrates one database’s behavior; verify the guarantees of the system you use.

A data lake can store many formats, often as objects in cloud storage. Flexibility does not guarantee discoverability, business meaning, validation, access control or reliable updates. Object storage also differs from a local filesystem. In Amazon S3, an object key identifies an object within a bucket; prefixes may look like folders in tools while storage still addresses objects by keys.

A lakehouse is a design approach that combines lake-oriented storage with selected table-management, analytics and governance capabilities. Product implementations differ. Before relying on the label, inspect transaction behavior, schema management, catalog, query engine, permissions and supported formats. Databricks documentation is one vendor’s example, not a universal specification.

For the orders assistant, use an authorized operational lookup for current order facts, an analytical store for historical trends, and a versioned policy retrieval path for a return question. Keep freshness and correction paths visible. Do not create every platform category unless it solves a real need.

2. Check

What workload does each store support? What is the authoritative source? How quickly must a copy refresh? Who owns corrections and permissions? What features are actually available in the selected implementation?

3. A worked architecture choice

Choose for a concrete small-store workload. Suppose staff need current order status during each customer call, a daily summary by the next morning, and versioned policy evidence for return questions. The team can operate one managed database and a controlled document store. Keep current order lookups in that database, and schedule the daily summary off-peak while monitoring its effect on calls. Use the policy store and a tested retrieval path for applicable text. That accepts a daily delay for the summary while preserving a current lookup for the caller. If history scans begin delaying those calls, move reporting to a separate analytical copy and set a measured refresh limit. A lake is justified if varied raw files need managed retention; a lakehouse needs a specific table-management or analytical requirement. Every added path brings access, correction and operating work.

4. A dated example

GitLab's February 10, 2017 postmortem describes an accidental removal from its primary database on January 31. Some recent database changes were lost, while code repositories were unaffected by that data loss. Different stores held different kinds of information, with different recovery paths. An analytical copy is not automatically a tested operational backup. For our assistant, specify both the workload a copy serves and how the authoritative service recovers.

5. Bringing the lesson together

The small store can now justify a current lookup and policy store, while deferring a separate warehouse until the reporting workload warrants it. The decision rests on delay, consistency, recovery and operating capacity. Revisit the architecture when one of those requirements changes.

Learning Checks

Attempt each answer before expanding the reasoning.

M13-L04-Q01

Why might the analyst copy operational data into an analytical system, and what responsibility does the copy introduce?

M13-L04-Q02

Which source/service roles fit a current order lookup, historical return analysis, and a dated policy question?

M13-L04-Q03

What should a team verify when a product is called a lakehouse?

M13-L04-Q04

Name needs that should influence architecture and an unnecessary layer to avoid.

Sources

• PostgreSQL Global Development Group, PostgreSQL 18 tutorial, section 3.4 “Transactions”: https://www.postgresql.org/docs/18/tutorial-transactions.html (retrieved 2026-10-29). One implementation example; check the guarantees and configuration of the actual system.
• Amazon Web Services, “Naming Amazon S3 objects”: https://docs.aws.amazon.com/AmazonS3/latest/userguide/object-keys.html (retrieved 2026-10-29). S3-specific key example; not a universal object-storage rule.
• Databricks, “What is a data lakehouse?”: https://docs.databricks.com/aws/en/lakehouse/ (retrieved 2026-10-29). Vendor architecture example; product capabilities vary and the label is not a universal specification.

• GitLab, “Postmortem of database outage of January 31”, published 2017-02-10; event/context: 2017-01-31 database outage. https://about.gitlab.com/blog/postmortem-of-database-outage-of-january-31/ (retrieved 2026-10-29). Company postmortem of historical failure; no claim warehouse caused outage or that selected teaching architecture prevents it.