Data Warehouses vs. Data Lakes (and Lakehouses)

Purpose of This Lesson

This lesson establishes how data warehouses, data lakes, and data lakehouses differ in storage model, schema handling, processing workflow, cost, flexibility, and common AWS use cases. The central skill is selecting the architecture that matches the data format and analytical requirement rather than treating these platforms as interchangeable.

Key Concepts

Data warehouse

A data warehouse is a centralized repository optimized for analysis and read-heavy workloads. Data from multiple sources is cleaned, transformed, and loaded into a predefined structured format, commonly using an ETL process:

1. Extract data from its sources.
2. Transform it into the target schema.
3. Load the transformed data into the warehouse.

Warehouses commonly use star or snowflake schemas and are designed for complex, predictable queries. Amazon Redshift is the primary AWS example.

Data lake

A data lake is a large storage repository for raw data in its native format. It can contain structured, semi-structured, and unstructured data, with little or no preprocessing before storage. Amazon S3 is commonly used as the underlying AWS data lake storage layer.

A data lake generally follows ELT rather than ETL:

1. Extract data from its sources.
2. Load it into raw storage such as S3.
3. Transform it later when a particular use case requires transformation.

The same raw data can support different transformations for business intelligence, exploration, advanced analytics, or machine learning.

Data lakehouse

A data lakehouse is a hybrid architecture intended to combine data-lake flexibility, scale, and lower-cost storage with data-warehouse performance and reliability. It can support structured and unstructured data, schema-on-write and schema-on-read patterns, detailed analytics, and machine-learning workloads.

An AWS example is AWS Lake Formation used with S3 and Redshift Spectrum. Data remains in S3 while Redshift Spectrum provides a warehouse-like way to query it. The storage layer is still the lake rather than a conventional relational database copy.

Data Architecture and Processing Context

Schema-on-write versus schema-on-read

A warehouse uses schema-on-write. The structure is known before data is written, so incoming data is transformed to match the planned schema. This makes it possible to design indexes and other query optimizations in advance, but changing the schema later can require a substantial migration effort.

A lake uses schema-on-read. Data is stored first in its raw form, and its structure is interpreted when it is read or processed. This postpones modeling decisions and makes it easier to ingest new data sources or revisit the same data for different purposes.

AWS data lake workflow

A common AWS pattern is:

• Store raw objects in Amazon S3.
• Use AWS Glue to extract or define structure and schema information and maintain a data catalog.
• Use Amazon Athena or another compatible query service to use the Glue Data Catalog when querying the data.

Glue and Athena provide structure and query capability, but the underlying data can remain stored as-is in S3. The lake does not have to replicate every dataset into a relational database.

Typical warehouse domains and views

A warehouse may integrate clickstream data, purchase data, and catalog data so that analysts can relate user behavior, orders, and product information. Different consumers may use different views or data marts:

• Accounting may need financial and purchase-oriented information.
• Customer-behavior analysts may need clickstream and catalog relationships.
• Machine-learning teams may need large extracts of behavioral data for a recommender system.

This illustrates why one warehouse can expose multiple purpose-built views rather than serving every query through exactly the same representation.

Cost and agility tradeoffs

Warehouses generally require more upfront modeling, transformation, and query optimization. They can be more expensive because the data must be structured for efficient complex queries and the supporting tools and engineering effort can be substantial.

A lake can be more cost-effective and flexible for very large volumes of data because raw objects can be retained in services such as S3 and transformed only when needed. Storage costs can still accumulate at scale, and a lake does not eliminate the need for governance, cataloging, or effective query design.

Exam- or Assessment-Relevant Takeaways

• Choose a data warehouse when data is structured, the schema is known upfront, fast and complex queries matter, and business intelligence or traditional analytics is the primary workload.
• Choose a data lake when data may be structured, semi-structured, or unstructured; the future use is uncertain; or flexibility and large-scale raw-data storage are priorities.
• Associate ETL and schema-on-write with the warehouse.
• Associate ELT and schema-on-read with the lake.
• Amazon Redshift is the AWS data warehouse example emphasized in this lesson.
• Amazon S3 is the AWS storage foundation commonly used for data lakes.
• AWS Glue can provide schema and catalog information for raw S3 data, while Amazon Athena can use that catalog to query the data.
• A data lake does not necessarily require copying raw data into a relational database.
• A lake and warehouse can coexist: retain raw data in S3 for flexibility and load selected, transformed data into Redshift for BI and optimized analytics.
• For the AWS lakehouse pattern discussed here, remember the combination of AWS Lake Formation, S3, and Redshift Spectrum.

Tool / Feature Decision Guide

Requirement	Better fit or pattern	Decisive reason
Predictable BI dashboards over structured, integrated data	Amazon Redshift data warehouse	Predefined schema and query optimization support complex read-heavy analysis
Store logs, raw behavior data, or mixed-format sources before their future use is known	Amazon S3 data lake	Raw data can be retained without defining a complete schema first
Discover structure in raw S3 data and make it queryable	AWS Glue with Amazon Athena	Glue supplies catalog/schema information and Athena can use it for queries
Train machine-learning models from large volumes of raw behavior data	Data lake, potentially with downstream transformations	Flexible raw storage supports feature extraction and multiple analytical treatments
Query S3-resident data with a warehouse-like interface	S3 with Redshift Spectrum, within the Lake Formation lakehouse example	Computation/query behavior is warehouse-like while storage remains in S3
Need both raw-data flexibility and optimized BI datasets	Use both a data lake and a data warehouse	Each layer serves a different workload rather than forcing one system to do everything

Common Traps / Misconceptions

• A data lake is not limited to unstructured data. It can hold structured, semi-structured, and unstructured data.
• Schema-on-read does not mean there is never a schema. It means the structure is interpreted when data is read or processed rather than being fully imposed before storage.
• Using Glue does not turn S3 into a relational warehouse. Glue catalogs structure while the data can remain raw in S3.
• A data lake is not automatically cheaper in every situation. S3 can be cost-effective, but large retained datasets and inefficient processing can still become expensive.
• A lake and a warehouse are not mutually exclusive. The same source data may be retained raw in a lake and represented in transformed form in a warehouse.
• A lakehouse is not simply a warehouse with a different name. It is a hybrid architecture intended to combine lake storage flexibility with warehouse-like performance and reliability.
• ETL and ELT are not interchangeable labels in this comparison. The placement of the transformation step is the key distinction.

Real-World Engineer / Analyst Notes

• Start architecture decisions with the workload and data characteristics: format diversity, schema stability, query latency, analytical complexity, and expected future uses.
• Keeping raw data in a lake preserves the ability to apply multiple later transformations. This is useful when requirements are still evolving.
• A warehouse is valuable when repeated users and applications need consistent, curated, quickly queryable datasets rather than repeatedly interpreting raw objects.
• In a combined architecture, define which layer is authoritative for raw retention and which datasets are curated for BI. Otherwise, teams may create inconsistent copies and conflicting definitions.
• Cataloging and query access are separate concerns from physical storage. In the AWS pattern discussed, S3 stores the objects, Glue describes them, and Athena or Redshift Spectrum provides query access.
• The lakehouse concept reduces the need to choose a single platform for every workload, but it does not remove the need to understand the tradeoff between raw flexibility and curated query performance.

Quick Reference Summary

Dimension	Data warehouse	Data lake	Data lakehouse
Primary storage model	Structured, transformed data	Raw data in native format	Lake-oriented storage with warehouse-like capabilities
Schema approach	Schema-on-write	Schema-on-read	Can support both
Typical pipeline	ETL	ELT	Can combine both patterns
Data types	Primarily structured	Structured, semi-structured, unstructured	Structured and unstructured
Main strengths	Fast complex queries, BI, consistent analytics	Flexibility, scale, raw retention, discovery, ML	Flexibility plus performance and reliability
AWS examples	Amazon Redshift	Amazon S3, often with Glue and Athena	AWS Lake Formation with S3 and Redshift Spectrum
Main tradeoff	Less agile and potentially more expensive to change	Requires later interpretation, governance, and processing	More capable hybrid design but still requires deliberate architecture

Flashcards

Q: A team has stable structured sources and needs fast, complex BI queries. Which architecture is the strongest default choice, and why?

A: A data warehouse such as Amazon Redshift, because the known schema can be optimized before loading and the workload is read-heavy analytical querying.

Q: When would you choose a data lake instead of a warehouse for incoming data?

A: Choose a data lake when the data includes mixed formats, its future uses are uncertain, or raw retention and flexible downstream processing are more important than immediate curated query performance.

Q: What is the decisive processing-order difference between ETL and ELT in this lesson?

A: ETL transforms data before loading it into the warehouse. ELT loads raw data first, as in S3, and transforms it later for a particular use case.

Q: A source produces raw logs, JSON-like records, and other files whose structure may evolve. What AWS storage pattern fits best?

A: Store the data in Amazon S3 as a data lake, then use AWS Glue and a query service such as Amazon Athena to catalog and interpret its structure when needed.

Q: How do schema-on-write and schema-on-read differ operationally?

A: Schema-on-write requires the target structure to be defined and applied before storage. Schema-on-read stores data first and determines or applies its structure when the data is accessed.

Q: What is the role of AWS Glue in the S3 data-lake workflow described here?

A: Glue extracts or defines schema information and maintains a data catalog that query tools can use to understand raw S3 data.

Q: What is the role of Amazon Athena in this workflow?

A: Athena can use the AWS Glue Data Catalog to determine how to query data that remains stored in S3.

Q: Does adding a Glue catalog mean the raw data has been converted into a relational warehouse? Why or why not?

A: No. The catalog describes the data for access, while the objects can remain in their raw form in S3 without being replicated into a relational database.

Q: An organization wants raw data for machine learning and curated datasets for dashboards. Must it choose either a lake or a warehouse?

A: No. It can retain raw data in a lake and load transformed, query-optimized representations into a warehouse for BI and analytics.

Q: What AWS combination represents the lakehouse example emphasized in the lesson?

A: AWS Lake Formation used with Amazon S3 and Redshift Spectrum, where data remains in S3 but can be queried in a warehouse-like manner.

Q: Why is a warehouse generally less agile when requirements change?

A: Its predefined schema and query-oriented organization may require substantial schema changes or data migration when analytical requirements evolve.

Q: What kinds of data can a data lake contain?

A: Structured, semi-structured, and unstructured data, generally retained in its native raw format.

Q: What is the main architectural idea behind a data lakehouse?

A: It attempts to combine the flexibility, scale, and storage economics of a data lake with the performance, reliability, and analytical capabilities associated with a warehouse.

Practice Questions

Question 1

An analytics team receives structured purchase records, catalog data, and clickstream data. The schema is well understood, and the team needs consistently fast, complex queries for BI dashboards. Which choice best fits?

A. Store everything as raw S3 objects and defer all modeling indefinitely

B. Load curated data into Amazon Redshift using an ETL process

C. Use AWS Glue only, without a query or storage layer

D. Use Redshift Spectrum solely because the data is structured

Correct answer: B

Explanation: The stable structured schema, integrated sources, and fast complex BI queries point to a data warehouse and an ETL workflow. Amazon Redshift is the AWS warehouse example in the lesson.

Question 2

A company is collecting application logs and user-behavior files in several formats, but it does not yet know which analyses will be needed next year. It wants to preserve the raw data and avoid redesigning a warehouse for every new question. What is the best initial approach?

A. Transform every file immediately into one fixed relational schema

B. Store the raw data in Amazon S3 as a data lake

C. Load only aggregated records into Amazon Redshift

D. Discard formats that do not fit a predefined schema

Correct answer: B

Explanation: Mixed formats and uncertain future requirements favor raw, flexible storage in an S3 data lake. Transformations can be applied later for specific analytical needs.

Question 3

Raw data is already stored in S3. Analysts need a discoverable schema and want to query the data without first copying it into a relational database. Which pattern matches the lecture?

A. AWS Glue cataloging with Amazon Athena querying the cataloged data

B. Amazon Redshift loading all data before any schema is defined

C. Replacing S3 with a star schema immediately

D. Using only Amazon S3, because a lake cannot be cataloged

Correct answer: A

Explanation: Glue can provide schema and catalog information, and Athena can use that catalog to query data that remains stored in S3.

Question 4

A business wants S3 to remain the underlying storage for a large data collection but also wants warehouse-like querying of that data. Which AWS lakehouse pattern is most relevant?

A. Amazon Redshift used without external storage

B. AWS Lake Formation with S3 and Redshift Spectrum

C. Amazon Athena replaced by an Oracle database

D. AWS Glue used as the only persistent storage system

Correct answer: B

Explanation: The lesson specifically identifies AWS Lake Formation with S3 and Redshift Spectrum as a lakehouse example: storage remains in S3 while Spectrum provides warehouse-like querying.

WordPress Metadata

Suggested Slug:
aws-data-warehouses-data-lakes-lakehouses

Meta Description:
Compare AWS data warehouses, data lakes, and lakehouses, including ETL versus ELT, schema handling, S3, Glue, Athena, Redshift, and Redshift Spectrum.

Tags:
AWS, data engineering, data warehouses, data lakes, data lakehouse, Amazon Redshift, Amazon S3, AWS Glue, Amazon Athena, Redshift Spectrum, ETL, ELT, schema-on-write, schema-on-read