5. Data Warehouses, Data Lakes and Lakehouse Architectures

Introduction and Context

Analytical repositories are the foundations on which modern data-driven organisations build reporting, business intelligence, machine learning, and strategic decision-making. Transaction-processing systems are designed to record operational events reliably: a sale, payment, delivery, login, or sensor reading. Analytical systems serve a different purpose. They integrate data from many sources, preserve historical context, support complex queries, and make information available for interpretation.

The traditional data warehouse addressed this need by storing structured, cleansed, and carefully modelled data. The data lake later emerged to accommodate the volume, variety, and speed of modern data, including semi-structured files, application logs, documents, images, and streaming events. More recently, lakehouse architectures have attempted to combine the flexibility and scalability of a lake with the governance, performance, and reliability associated with a warehouse.

These technologies are not merely storage choices. They embody different assumptions about data quality, ownership, processing, governance, performance, and organisational responsibility. A sound architecture therefore begins with business and analytical requirements rather than with the selection of a fashionable platform.

Learning Objectives

After studying this chapter, students should be able to:

1. Explain the purposes and architectural characteristics of data warehouses, data lakes, and lakehouses.

2. Design dimensional models using facts, dimensions, grain, keys, and slowly changing dimensions.

3. Distinguish star, snowflake, data vault, and related modelling approaches.

4. Evaluate data marts, semantic layers, metadata, and open table formats.

5. Compare warehouse, lake, and lakehouse patterns according to workload, governance, cost, and performance.

6. Explain the organisational implications of data mesh and domain-oriented ownership.

7. Identify risks involving quality, security, privacy, governance, and uncontrolled complexity.

8. Propose an analytical repository for a realistic organisational scenario.

From Operational Data to Analytical Repositories

An operational database is optimised for frequent, small, concurrent transactions. It generally uses normalisation to reduce redundancy and protect update consistency. An analytical repository, in contrast, is optimised for reading large volumes of historical data, joining multiple business processes, calculating aggregates, and supporting exploratory analysis.

A useful conceptual distinction is between online transaction processing (OLTP) and online analytical processing (OLAP). OLTP systems answer questions such as whether a payment was accepted or whether inventory was reduced. OLAP systems answer questions such as how profit changed by region over five years, which customer segments are declining, or how delivery delays relate to product categories.

Data normally moves from source systems through ingestion, transformation, quality validation, storage, and consumption. This movement may follow an extract-transform-load (ETL) pattern, in which data is transformed before loading, or an extract-load-transform (ELT) pattern, in which relatively raw data is loaded first and transformed within the analytical platform. ELT is especially common in scalable cloud environments because storage and compute can be separated and provisioned elastically.

The architecture should also distinguish between a system of record and a system of insight. The former records authoritative business events; the latter organises information for analysis. An analytical repository should not silently become the place where operational corrections are made unless that responsibility has been explicitly designed and governed.

The Data Warehouse and Dimensional Thinking

A data warehouse is a subject-oriented, integrated, time-variant, and relatively stable collection of data designed to support analysis. Subject orientation means that data is organised around areas such as sales, finance, customers, or supply chains rather than around individual applications. Integration means that inconsistent codes, names, currencies, and time representations are reconciled. Time variance means that historical states can be examined. Stability means that analytical records are generally loaded and queried rather than continuously updated in the manner of an operational system.

Dimensional modelling

Dimensional modelling, associated particularly with the work of Ralph Kimball, organises data around business processes. Its central structures are fact tables and dimension tables.

A fact table records measurable events or periodic states. Examples include an invoice line, a shipment, a web session, or a daily inventory balance. A dimension table provides descriptive context, such as customer, product, store, employee, or date. A fact table contains foreign keys to dimensions and numeric measures such as quantity, revenue, cost, or duration.

The most important design decision is the grain: the precise meaning of one row in a fact table. For example, the grain might be “one row per product per order line” or “one row per account per month.” Measures and keys must be consistent with that grain. A measure such as order total is additive across products but may be double-counted if it is stored on every order line. Grain ambiguity is one of the most common causes of inaccurate analytical results.

A star schema places a central fact table around relatively denormalised dimensions. It is simple for analysts, usually performs well, and maps naturally to business questions. A snowflake schema normalises some dimensions into additional related tables. This may reduce duplication, but it introduces more joins and can make self-service analysis harder. In practice, dimensional models often use a controlled degree of denormalisation for usability.

Facts may be classified as:

• Transaction facts, representing individual events such as sales or payments.
• Periodic snapshot facts, representing a condition at regular intervals, such as monthly account balances.
• Accumulating snapshot facts, representing a process whose milestones change over time, such as an order moving from placement to dispatch to delivery.
• Factless facts, recording that an event or relationship occurred without a numeric measure, such as student attendance or product promotion eligibility.

Dimensions may also be shared across fact tables. A conformed customer or date dimension gives consistent meaning to measures across sales, service, and finance. Conformed dimensions are essential to integrated enterprise reporting.

Keys and slowly changing dimensions

Warehouse dimensions commonly use surrogate keys rather than relying solely on operational identifiers. A surrogate key is an artificial key generated within the warehouse. It allows the repository to preserve historical versions and isolate analytical joins from changes in source-system keys.

A slowly changing dimension (SCD) manages changes to descriptive attributes. In Type 1 processing, the old value is overwritten, so history is not preserved. In Type 2 processing, a new row is created with effective dates, an expiration date, and frequently a current-record indicator. Type 2 is appropriate when analysts must report according to the context that existed at the time of an event. Type 3 stores limited previous values in additional columns and is useful only for selected forms of history.

For example, if a customer moves from one sales territory to another, a Type 2 customer dimension enables historical sales to remain associated with the former territory while future sales use the new territory. The choice is a business policy, not merely a technical setting.

Data Marts, Semantic Layers, and Analytical Meaning

An enterprise warehouse may be complemented by data marts, which are analytical stores focused on a department, subject, or business process. A dependent data mart is derived from an enterprise repository and supports consistent definitions. An independent data mart is built directly from source systems. Independent marts can be delivered quickly but often create conflicting measures, duplicated pipelines, and fragmented governance.

A semantic layer sits between physical data structures and analytical users. It defines business concepts such as net revenue, active customer, churn, and gross margin, and maps them to physical tables and calculations. Semantic models can provide reusable metrics, row-level security, hierarchies, and consistent naming across dashboards and analytical tools.

The semantic layer is particularly important because technical correctness does not guarantee business correctness. Two reports may both execute successfully while calculating “revenue” differently because one includes tax and the other does not. Metric definitions should therefore include calculation logic, grain, filters, ownership, refresh expectations, and permitted uses.

Data Lakes: Flexibility with Responsibility

A data lake stores large volumes of data in relatively open and flexible formats. It may contain structured tables, JSON documents, log files, text, images, audio, geospatial data, and machine-generated events. Data is often organised into zones such as raw, refined, and curated, although zone names and responsibilities vary by organisation.

The raw or landing zone preserves data close to its source form. A refined zone applies validation, standardisation, and structural improvements. A curated zone provides trusted datasets for reporting, data science, or operational use. This layered approach can preserve reproducibility while offering progressively higher levels of usability.

The main attraction of a lake is that schema need not be fully defined before ingestion. This is often called schema-on-read, although practical data lakes still require schemas, contracts, and quality controls if they are to remain useful. Without governance, a lake may become a data swamp: a repository in which users cannot determine what data exists, whether it is accurate, who owns it, or how it should be interpreted.

Data lakes also introduce issues involving small files, inconsistent partitioning, duplicated extracts, incompatible formats, and expensive scans. Object storage is inexpensive relative to many database systems, but poor design can increase compute, network, and management costs.

Lakehouse Architecture and Open Table Formats

A lakehouse aims to combine low-cost, scalable storage and broad data-type support with warehouse-like management. It usually stores data in cloud or distributed object storage while adding transactional metadata, schema enforcement, versioning, data-quality controls, and efficient query access.

The critical distinction is between a collection of files and a managed table. A file directory alone may not provide atomic updates, reliable concurrent writes, or a trustworthy record of table state. Open table formats such as Apache Iceberg, Delta Lake, and Apache Hudi add a metadata layer that can support features including:

• atomic commits and consistent table snapshots;
• schema evolution under controlled rules;
• partition evolution or management;
• time travel and historical versions;
• updates, deletes, and merge operations;
• data skipping and query-optimisation metadata.

These formats are not identical, and platform support differs. The architectural principle, however, is that openness and reliability depend on a well-defined table protocol, not simply on storing files in an open format.

Lakehouses can support business intelligence, data engineering, machine learning, and streaming workloads over common data assets. They can reduce unnecessary duplication between a warehouse and a data lake. Nevertheless, they do not remove the need for modelling, quality management, access control, workload isolation, or semantic definitions. A lakehouse is an architectural pattern, not an automatic solution to organisational disorder.

Alternative Modelling Approaches

Dimensional modelling is highly effective for governed reporting, but it is not appropriate for every layer or workload. Third normal form models reduce redundancy and can represent complex relationships accurately, though they may require many joins for analysis. Data Vault separates hubs, links, and satellites. Hubs represent core business keys, links represent relationships, and satellites preserve descriptive and historical attributes. Data Vault can support auditability and incremental integration in environments with changing sources, but a dimensional presentation layer is often still needed for business users.

A wide analytical table may be useful for a specific machine-learning feature set or dashboard, but it should not automatically become the enterprise model. The appropriate question is whether the structure supports the intended users, change patterns, quality requirements, and performance objectives.

Data Mesh and Organisational Design

Data architecture is also an organisational problem. A centrally managed platform may provide consistency, but a central team can become a bottleneck because it lacks detailed knowledge of every business domain. The data mesh approach responds by proposing four related principles: domain-oriented ownership, data as a product, self-serve data infrastructure, and federated computational governance.

Domain ownership means that teams closest to a business area are responsible for the quality and usability of its analytical data. Treating data as a product requires discoverability, documentation, reliability, support, and defined consumers. A self-serve platform reduces the technical burden of publishing and using data products. Federated governance establishes common rules while allowing domains to make appropriate local decisions.

Data mesh does not mean that every department may define critical metrics independently. Shared identifiers, privacy rules, interoperability standards, and enterprise definitions still require coordination. The approach is most effective when ownership boundaries are clear and the platform makes responsible publishing easier than informal data exchange.

A Framework for Selecting an Architecture

Architecture selection should begin by characterising the workload:

1. Business purpose: regulatory reporting, operational intelligence, exploration, machine learning, or mixed use.

2. Data characteristics: volume, velocity, variety, sensitivity, expected retention, and change frequency.

3. Performance needs: concurrent users, latency targets, query complexity, and predictable service levels.

4. Governance requirements: lineage, auditability, residency, retention, deletion, access control, and quality evidence.

5. Operating model: central platform team, domain teams, or a hybrid arrangement.

6. Economic model: storage, compute, data movement, licensing, engineering effort, and support costs.

A warehouse is often suitable when data is predominantly structured, definitions are stable, and governed BI performance is the priority. A lake is useful for exploratory, large-scale, or heterogeneous data, particularly before its business value and structure are fully understood. A lakehouse is attractive when an organisation needs shared storage for varied workloads while retaining transactional table management and analytical governance. Hybrid architectures remain common: for example, a lakehouse may provide the central data foundation while a specialised warehouse or serving layer supports highly concurrent executive dashboards.

Practical Implementation: A Controlled Repository Lifecycle

A professional implementation commonly follows a lifecycle rather than a single loading project. First, stakeholders define analytical questions, data products, service levels, and ownership. Next, source systems are profiled to discover missing values, duplicate keys, inconsistent codes, and temporal problems. The team then establishes ingestion patterns, storage zones, models, tests, and access policies.

Data quality tests should address validity, completeness, uniqueness, consistency, timeliness, and referential integrity. Critical pipelines should be observable: their status, volume, latency, failures, and quality outcomes should be visible. Lineage should identify where a report or metric originated and which transformations affected it.

Partitioning and clustering should reflect common access patterns rather than arbitrary calendar divisions. Excessively small files should be compacted, while excessive partition cardinality should be avoided. Workloads may require separate compute resources or workload management to prevent a large machine-learning job from degrading executive reporting.

Deployment should be automated through version control, testing, infrastructure-as-code where appropriate, and controlled promotion between environments. Data contracts can specify expected fields, types, meanings, quality thresholds, and change procedures between producers and consumers.

Illustrative Case: A Retailer’s Unified Analytical Platform

Background

Consider Meridian Retail, a fictional omnichannel retailer operating physical stores, an online marketplace, a loyalty programme, and regional distribution centres. Its sales reports were produced from separate departmental databases. Finance reported net revenue differently from marketing, while the online team retained clickstream data in files that analysts could not easily discover.

Situation and problem

Executives needed a reliable view of sales, margin, inventory, customer behaviour, and delivery performance. The existing environment contained duplicated extracts, inconsistent customer identifiers, and no dependable history when a product changed category or a store changed region. The organisation also wanted to develop demand-forecasting models using sales, promotions, weather, and web events.

Analysis

A pure departmental-mart strategy would deliver short-term reports but would preserve conflicting definitions. A conventional warehouse could support the main structured reports, but ingesting clickstream and semi-structured event data would require additional infrastructure. The organisation therefore selected a lakehouse foundation with governed dimensional presentation models.

Approach and solution

Source data was ingested into a raw layer with source timestamps and technical metadata. Refined tables applied data contracts, standardised currencies and product identifiers, and removed demonstrably invalid records without destroying the original evidence. Managed open tables provided versioned, transactional storage.

A conformed date, product, customer, store, and promotion dimension was created. The principal sales fact used order-line grain, while separate fact tables represented inventory snapshots and delivery milestones. Customer territory and product category changes were handled with Type 2 dimensions where historical reporting required them. A semantic layer defined net sales, gross margin, active loyalty customer, and on-time delivery.

Data products were assigned to domain teams. The commerce domain owned order and web-event products; supply chain owned inventory and delivery products; finance governed revenue and margin definitions. A central platform team provided ingestion templates, identity management, monitoring, and common quality rules.

Outcome

The resulting architecture allowed analysts to examine historical sales consistently, data scientists to access richer features, and executives to use governed metrics. It did not eliminate all complexity: reconciliation between financial systems and operational events remained necessary, and the organisation had to control compute costs and maintain product documentation.

Learning points

The case illustrates that successful architecture depends on grain, shared dimensions, semantic definitions, ownership, and operational discipline. Choosing a lakehouse alone would not have solved Meridian’s problems. The value came from combining technical capabilities with modelling and governance decisions.

Risks, Governance, and Professional Responsibilities

The flexibility of modern repositories creates significant risks. Excessive access may expose personal, financial, health, or commercially sensitive information. Data should be classified and protected through least-privilege access, encryption, masking, tokenisation, retention rules, and appropriate monitoring. Row- and column-level controls may be required for shared analytical environments.

Privacy obligations may include purpose limitation, lawful processing, data minimisation, rights of access or deletion, cross-border transfer controls, and documented retention. Deleting a person’s data from a curated table may not be sufficient if copies remain in raw zones, caches, backups, or derived features. Retention and deletion must therefore be designed across the lifecycle.

Lineage and auditability support regulatory reporting and responsible analysis. Analysts should understand whether data is sampled, delayed, imputed, or subject to known bias. A model trained on poorly governed historical data may reproduce discriminatory patterns even if the repository itself is technically secure. Governance should include human accountability for definitions, exceptions, access decisions, and remediation.

Common mistakes include loading every available source without a purpose, treating raw data as trusted data, ignoring grain, creating independent departmental metrics, over-partitioning tables, retaining sensitive data indefinitely, and assuming that a platform’s catalogue automatically produces meaningful metadata. Documentation must be maintained as part of delivery, not added only after failure.

Current Developments and Best Practices

Modern platforms increasingly support streaming ingestion, real-time or near-real-time tables, vector and geospatial data, and integrated machine-learning workflows. Open table formats and interoperable catalogues are reducing dependence on a single processing engine, although practical interoperability still requires testing. Data observability tools increasingly monitor freshness, distribution changes, schema drift, and downstream impact.

Generative AI is also being integrated into catalogue search, natural-language querying, pipeline assistance, and documentation. These uses require controls against fabricated explanations, unauthorised data exposure, insecure generated code, and ambiguous metric interpretation. Natural-language access should be grounded in governed semantic models rather than unrestricted access to poorly documented tables.

The following practices provide a dependable foundation:

• define grain and business purpose before building tables;
• assign accountable owners to important datasets and metrics;
• separate raw evidence from trusted analytical products;
• use conformed dimensions and a governed semantic layer;
• automate quality tests, lineage, deployment, and monitoring;
• classify sensitive data and enforce least privilege;
• design for schema evolution, retention, and deletion;
• measure total cost, including compute, movement, engineering, and support;
• publish data products with documentation, service expectations, and contact points;
• review architecture periodically as workloads and organisational boundaries change.

Chapter Summary

Data warehouses, data lakes, and lakehouses represent different but overlapping approaches to analytical data management. Warehouses emphasise integrated, historical, governed data and are particularly effective for structured BI. Lakes provide scalable, flexible storage for diverse data but require strong metadata, quality, and ownership practices. Lakehouses use managed table protocols and open formats to combine lake flexibility with transactional reliability and analytical governance.

Dimensional modelling remains central to usable analytics. Facts must have a clearly defined grain, dimensions provide context, surrogate keys support controlled history, and conformed dimensions create enterprise consistency. Data marts and semantic layers translate physical structures into reliable departmental and business concepts. Data mesh extends the discussion into organisational design by assigning domain ownership while retaining federated governance.

No architecture is automatically superior. The appropriate design depends on workload, data characteristics, performance, governance, operating model, and total cost. Effective repositories combine technology with modelling discipline, metadata, security, quality engineering, accountable ownership, and continuous operational management.

Key Terms

Analytical repository: A system that stores and organises data for reporting, analysis, and decision-making.

Data warehouse: An integrated, historical repository designed primarily for structured analytical workloads.

Data lake: A scalable repository that can store structured, semi-structured, and unstructured data in flexible forms.

Lakehouse: An architecture combining lake-style storage flexibility with managed tables, governance, and warehouse-like analytical capabilities.

OLTP: Online transaction processing systems designed for reliable operational transactions.

OLAP: Online analytical processing designed for complex queries over historical and integrated data.

Fact table: A table recording measurable events, states, or relationships at a defined grain.

Dimension table: A table containing descriptive attributes used to analyse facts.

Grain: The precise business meaning represented by one row in a fact table.

Star schema: A dimensional model with a central fact table connected to relatively denormalised dimensions.

Conformed dimension: A shared dimension with consistent meaning and keys across multiple analytical processes.

Surrogate key: A warehouse-generated identifier used independently of a source-system key.

Slowly changing dimension: A method for managing changes to dimension attributes over time.

Data mart: An analytical store focused on a department, subject, or business process.

Semantic layer: A governed abstraction that defines business metrics and maps them to physical data.

Metadata: Data describing the meaning, structure, origin, quality, ownership, and use of other data.

Open table format: A table-management protocol that adds metadata and transactional behaviour to files in distributed storage.

Data mesh: An organisational and architectural approach based on domain ownership, data products, self-service infrastructure, and federated governance.

Data contract: An explicit agreement describing the schema, meaning, quality, and change expectations of a data product.

Review Questions

Short Answer Questions

1. Why is defining the grain essential when designing a fact table?

2. Distinguish between ETL and ELT.

3. What is the purpose of a semantic layer?

4. How does a Type 2 slowly changing dimension preserve history?

5. Why can an unmanaged data lake become a data swamp?

Long Answer Questions

1. Compare the architectural characteristics, strengths, and limitations of a data warehouse, data lake, and lakehouse.

2. Explain dimensional modelling and discuss facts, dimensions, conformed dimensions, surrogate keys, and star schemas.

3. Discuss how open table formats improve the reliability of data stored in object storage.

4. Explain the data mesh model and evaluate its implications for centralised and domain-oriented governance.

5. Develop a framework for selecting an analytical repository architecture for a large organisation.

Analytical / Application Questions

1. A bank has structured transaction data, semi-structured mobile-event data, strict retention obligations, and highly concurrent regulatory dashboards. Propose an architecture and justify the roles of its warehouse, lakehouse, semantic layer, and governance controls.

2. A sales fact table stores one row per order line but includes the full order total on every row. Identify the analytical risk and redesign the model or calculation approach.

3. An organisation has five departmental definitions of active customer and duplicated extracts across independent data marts. Design a remediation plan using conformed dimensions, a semantic layer, ownership, and quality controls.

22. Multiple Choice Questions with Answers

Question 1

A sales fact table contains one row for each product on an order. What does this statement define?

A. The table’s partitioning strategy

B. The table’s grain

C. The customer dimension type

D. The semantic-layer hierarchy

Correct Answer: B. The table’s grain

Question 2

Which design best preserves a customer’s historical sales territory after the customer moves?

A. Overwrite the territory in the existing dimension row

B. Delete all previous sales and reload them

C. Create a new dimension version with effective dates

D. Store territory only in the sales dashboard

Correct Answer: C. Create a new dimension version with effective dates

Question 3

What is the principal purpose of a conformed dimension?

A. To eliminate all historical data

B. To ensure consistent analytical meaning across fact tables

C. To convert every table into third normal form

D. To prevent data from being used by departments

Correct Answer: B. To ensure consistent analytical meaning across fact tables

Question 4

Which capability most directly distinguishes a managed open table from an unmanaged collection of files?

A. The ability to store only structured data

B. The absence of metadata

C. Transactional commits and consistent table snapshots

D. The requirement that all data be transformed before ingestion

Correct Answer: C. Transactional commits and consistent table snapshots

Question 5

A data lake contains files, but users cannot determine their owners, meanings, quality, or update times. This condition is best described as:

A. A conformed warehouse

B. A data swamp

C. A Type 1 dimension

D. A semantic model

Correct Answer: B. A data swamp

Question 6

Which situation most strongly supports the use of a semantic layer?

A. Different reports calculate net revenue using incompatible definitions

B. A source system needs faster transaction commits

C. A raw zone needs additional object storage

D. A dimension requires a surrogate key

Correct Answer: A. Different reports calculate net revenue using incompatible definitions

Question 7

Which statement best describes a data mesh?

A. A single central database containing all organisational data

B. A storage format used only for streaming data

C. An approach combining domain ownership, data products, self-service infrastructure, and federated governance

D. A replacement for all data-quality testing

Correct Answer: C. An approach combining domain ownership, data products, self-service infrastructure, and federated governance

Question 8

Why might a lakehouse be selected instead of a collection of independent data marts?

A. It guarantees that business definitions never require governance

B. It can provide shared, managed data assets for multiple workloads

C. It prevents departments from owning data products

D. It requires all data to be discarded after transformation

Correct Answer: B. It can provide shared, managed data assets for multiple workloads

Question 9

A retailer wants to analyse inventory levels at the end of each month. Which fact type is most appropriate?

A. Periodic snapshot fact

B. Factless event fact only

C. Accumulating snapshot for every customer

D. Type 1 dimension

Correct Answer: A. Periodic snapshot fact

Question 10

Which practice most directly reduces the risk of exposing sensitive customer data in an analytical repository?

A. Retaining every raw copy indefinitely

B. Using least-privilege access with classification and masking controls

C. Removing all metadata from the catalogue

D. Allowing every domain unrestricted access

Correct Answer: B. Using least-privilege access with classification and masking controls