Data Warehousing, OLAP and Lakehouse Platforms

Introduction and Context

Modern organizations collect data from enterprise resource planning systems, customer applications, websites, sensors, financial platforms, social media, and external data providers. Operational databases are designed primarily to record current transactions accurately and efficiently. They are not usually optimized for long-running analytical queries, historical comparisons, multidimensional exploration, or executive reporting. Data warehousing addresses this gap by organizing integrated, historical, and analysis-oriented data for decision-making.

A contemporary analytical environment may include a traditional enterprise data warehouse (EDW), departmental data marts, an online analytical processing (OLAP) engine, a semantic layer, a data lake, and increasingly a lakehouse. These components are not mutually exclusive. They represent different design choices concerning data structure, governance, performance, cost, flexibility, and organizational capability.

The central design problem is not simply where to store data. It is how to convert heterogeneous operational data into trustworthy information while preserving meaning, lineage, security, and appropriate performance. This requires dimensional modeling, carefully designed extraction and transformation processes, suitable analytical architectures, and governance mechanisms.

Learning Objectives

After studying this chapter, the reader should be able to:

• distinguish operational processing from analytical processing;
• design fact tables, dimension tables, star schemas, and snowflake schemas;
• define grain, measures, dimensions, keys, and slowly changing dimensions;
• compare ETL and ELT pipelines and select an appropriate approach;
• explain OLAP cubes and operations such as roll-up, drill-down, slice, dice, and pivot;
• evaluate data warehouses, data marts, data lakes, and lakehouse platforms;
• design semantic layers for consistent business interpretation;
• analyze performance, governance, security, quality, and cost considerations; and
• apply these concepts to managerial and technical decision-making.

From Operational Systems to Analytical Platforms

Online transaction processing (OLTP) systems support frequent inserts, updates, and short transactions. Their schemas are commonly normalized to reduce redundancy and protect update integrity. A banking system, for example, must record an account transfer reliably and atomically.

Online analytical processing (OLAP) systems support complex queries over large volumes of historical data. Typical questions include: Which product categories generated the highest margin in each region during the last three years? Which customer segments are becoming less profitable? How did inventory turnover change after a pricing intervention?

The principal differences are summarized as follows:

| Characteristic | OLTP | OLAP |

|---|---|---|

| Primary purpose | Execute business transactions | Analyze business activity |

| Data orientation | Current operational state | Historical and integrated data |

| Schema tendency | Normalized | Dimensional or denormalized |

| Workload | Short, concurrent transactions | Complex scans and aggregations |

| Users | Clerks, applications, operational staff | Analysts, managers, data scientists |

| Optimization | Write consistency and transaction latency | Read performance and analytical flexibility |

A data warehouse is a subject-oriented, integrated, time-variant, and relatively non-volatile collection of data used to support management decisions. “Non-volatile” does not mean that a warehouse never changes; it means that analytical data is generally loaded and managed as historical records rather than continuously updated in the same manner as operational records.

Dimensional Modeling

Dimensional modeling organizes information around measurable business events and the descriptive context required to interpret them. Its most important structures are fact tables and dimension tables.

Grain: the Foundation of a Fact Table

The grain states exactly what one row in a fact table represents. Examples include:

• one row per product sold on an invoice line;
• one row per account per day;
• one row per aircraft maintenance event; or
• one row per website session.

Grain must be declared before selecting measures. A fact table that mixes daily account balances with individual transactions has ambiguous meaning and can produce incorrect aggregates. Every measure and foreign key must be compatible with the declared grain.

Fact Tables

A fact table records business events or periodic states. It normally contains foreign keys to dimensions and numerical measures. For a retail sales model, a fact table may contain DateKey, ProductKey, StoreKey, CustomerKey, Quantity, SalesAmount, DiscountAmount, and CostAmount.

Measures may be classified as:

• additive: safely summed across all relevant dimensions, such as sales revenue;
• semi-additive: summable across some dimensions but not others, such as account balance, which can be summed across branches but not across time; and
• non-additive: not meaningfully summed, such as a percentage, ratio, or unit price.

Ratios should usually be calculated from additive components. For example, margin percentage should be computed as total margin divided by total sales, rather than by averaging row-level margin percentages. If margin is revenue minus cost, then for a selected set of rows:

Margin percentage = (Σ Revenue − Σ Cost) / Σ Revenue × 100

This expression assumes that revenue is nonzero and that the numerator and denominator use compatible filters and currency units.

Common fact-table types include:

1. Transaction fact: one row per business event.

2. Periodic snapshot fact: one row per entity per regular period, such as daily inventory.

3. Accumulating snapshot fact: one row that is updated as a process moves through milestones, such as an order lifecycle.

4. Factless fact: records that an event or relationship occurred without a numerical measure, such as student attendance or product eligibility.

Dimension Tables

Dimensions provide the descriptive context for facts. Typical dimensions include Date, Product, Customer, Supplier, Employee, Store, and Geography. A dimension can contain attributes used for filtering, grouping, labeling, and navigation.

A date dimension should generally be prepopulated and include attributes such as calendar date, week, month, quarter, fiscal period, holiday indicator, and working-day indicator. Storing these attributes avoids repeated calendar logic and supports consistent reporting.

Dimensions normally use surrogate keys rather than relying exclusively on operational natural keys. A surrogate key is a warehouse-generated identifier. It permits multiple historical versions of the same business entity, isolates the warehouse from source-system key changes, and supports unknown or not-yet-identified members. An “Unknown Product” row, for example, may receive a designated surrogate key so that fact loading is not blocked by a late-arriving dimension record.

Star and Snowflake Schemas

A star schema contains a central fact table connected directly to relatively wide dimension tables. It is simple for users, usually efficient for analytical queries, and well suited to semantic modeling.

A snowflake schema normalizes some dimensions into additional related tables. It can reduce redundancy and improve control over hierarchies, but it increases joins and may be less intuitive for business users. Neither pattern is universally superior. Star schemas are often preferable for reporting simplicity and query performance; snowflake structures may be appropriate where dimensions are very large, shared hierarchies are complex, or governance requires normalization.

Slowly Changing Dimensions

A slowly changing dimension (SCD) records changes in descriptive attributes. Common approaches include:

• Type 0: retain the original value permanently;
• Type 1: overwrite the old value, losing history;
• Type 2: create a new dimension row with effective dates and a current-row indicator;
• Type 3: retain limited previous values in additional columns.

Type 2 is appropriate when historical reporting must reflect the context that existed when a fact occurred. For example, if a customer moves from Region East to Region West, historical sales may need to remain associated with Region East. A Type 2 dimension commonly contains SurrogateKey, CustomerID, Region, EffectiveFrom, EffectiveTo, and IsCurrent. The date intervals must be managed so that a customer has no unintended overlapping current records.

Conformed Dimensions and Bus Architecture

A conformed dimension has consistent meaning and keys across fact tables or data marts. A common Date dimension and Customer dimension enable valid comparisons between sales, returns, service, and marketing facts. Without conformed dimensions, an enterprise may produce incompatible definitions of “customer,” “month,” or “revenue.”

The dimensional bus architecture uses shared conformed dimensions to integrate subject-area marts incrementally. It balances enterprise consistency with phased implementation. It is especially useful where an organization cannot build the entire warehouse at once.

Data Warehouse Layers and Data Marts

A practical warehouse commonly contains several logical layers:

1. Source layer: operational systems, files, APIs, and external feeds.

2. Landing or raw layer: minimally altered copies of source data.

3. Staging layer: temporary structures used for validation and transformation.

4. Integration layer: standardized, reconciled, and historically managed data.

5. Presentation layer: dimensional models, aggregates, and business-facing tables.

6. Semantic layer: governed metrics, relationships, security rules, and analytical definitions.

A data mart is a subject-oriented analytical store serving a department or business process. A dependent data mart is supplied from an enterprise warehouse and generally supports consistency. An independent data mart is built directly from operational sources; it may be faster to deliver but risks duplicated logic and conflicting definitions.

The semantic layer is the interface between physical data structures and consumers. It defines terms such as “net sales,” “active customer,” and “gross margin,” maps them to physical columns, applies relationships and security policies, and may provide reusable measures. It is essential because technical column names alone do not guarantee consistent business interpretation.

ETL and ELT Engineering

Extract, transform, load (ETL) transforms data before loading it into the target warehouse. Extract, load, transform (ELT) loads raw or lightly processed data into a scalable analytical platform and performs transformations there. Cloud warehouses and lakehouse engines have made ELT increasingly common because they provide elastic compute and inexpensive storage.

ETL may be advantageous when target systems require strongly controlled structures, when sensitive data must be reduced before loading, or when transformation logic is independent of the target platform. ELT is attractive when retaining raw data is important, transformations must be reproducible, and the target engine can scale computational work.

A robust pipeline includes:

• source extraction and change detection;
• schema and data-type validation;
• deduplication and standardization;
• key resolution and dimension management;
• fact loading at a declared grain;
• reconciliation and quality checks;
• error quarantine and restartability;
• audit logging and lineage; and
• monitoring of freshness, volume, failures, and distribution changes.

Incremental loading is commonly based on change data capture (CDC), timestamps, log positions, version numbers, or source-system change flags. A full refresh is simpler but may be expensive and may complicate history. Incremental processes require careful treatment of late-arriving facts, late-arriving dimensions, deletions, corrections, and out-of-order events.

Data quality should be tested using rules such as uniqueness, referential integrity, accepted-value constraints, completeness thresholds, range checks, and reconciliation with source totals. A pipeline that completes technically but loads incorrect measures is not successful.

OLAP and Multidimensional Analysis

An OLAP model represents measures across dimensions and hierarchies. A sales cube might contain measures such as revenue, units, and margin, and dimensions such as time, product, geography, and channel.

Important OLAP operations include:

• Roll-up: aggregate to a higher level, such as city to region or month to quarter.
• Drill-down: move to greater detail, such as year to quarter to month.
• Slice: select one dimension value, such as sales for 2025.
• Dice: select a subcube using multiple dimension filters, such as two regions and three product groups.
• Pivot: rotate dimensions to present the same data from another perspective.
• Drill-through: move from an aggregate result to underlying detailed records.

Hierarchies must be valid. A geography hierarchy may be Country–State–City, while a time hierarchy may be Year–Quarter–Month–Day. Ragged or many-to-many hierarchies require special modeling and may make aggregation ambiguous.

OLAP implementations may be relational (ROLAP), multidimensional (MOLAP), or hybrid (HOLAP). ROLAP relies on relational tables and SQL; MOLAP stores precomputed multidimensional structures; HOLAP combines relational detail with multidimensional aggregates. Modern columnar warehouses and semantic engines often provide OLAP-like behavior without a traditional proprietary cube.

Pre-aggregation can improve response time, but it introduces storage, refresh, and consistency costs. Aggregations should be selected according to workload patterns rather than created indiscriminately.

Data Lakes and Lakehouse Platforms

A data lake stores data in its original or near-original form and can accommodate structured, semi-structured, and unstructured data. It commonly uses object storage and separates storage from compute. A lake supports exploratory analytics, machine learning, archival, and ingestion of data whose structure may not yet be fully known.

A lake can become a “data swamp” when data lacks ownership, cataloging, quality controls, discoverability, or lifecycle management. Raw storage alone does not create analytical value.

A lakehouse seeks to combine the flexibility and economical storage of a lake with warehouse-style reliability and management. Common capabilities include:

• transactional consistency for concurrent reads and writes;
• schema enforcement and controlled schema evolution;
• versioning and time-travel queries;
• metadata-driven table management;
• batch and streaming support;
• open or interoperable storage formats; and
• unified support for BI, data engineering, and machine learning.

A lakehouse is an architectural pattern rather than a single universal product. Its success depends on the quality of the table format, processing engine, catalog, governance, security model, and operational practices.

The choice among warehouse, lake, and lakehouse should be based on workload and constraints. A warehouse may be strongest for governed executive reporting and predictable SQL performance. A lake may be suitable for raw multimodal data and experimentation. A lakehouse may be suitable where an organization wants shared storage and stronger reliability across BI and data science. Hybrid architectures are common and can be rational when different workloads have genuinely different requirements.

Performance and Physical Design

Analytical performance depends on data volume, query patterns, concurrency, data distribution, and platform design. Important techniques include:

• columnar storage, which reads only required columns;
• partitioning, which limits the data scanned for common filters;
• clustering or sorting, which improves locality;
• compression and encoding;
• materialized views and aggregates;
• workload management and resource isolation;
• appropriate join strategies; and
• statistics and query-plan monitoring.

Partitioning by a high-cardinality attribute such as customer ID can create excessive small partitions. Partitioning should generally use attributes frequently used for pruning, such as event date, while avoiding highly fragmented layouts. Small files in object storage can create metadata and scheduling overhead; compaction is therefore an operational concern.

A useful performance measure is not only query latency but also cost per useful analytical result, freshness, concurrency, and reliability. Optimizing a single query while degrading maintainability or cost is not necessarily an improvement.

Governance, Security, and Responsible Use

Analytical platforms aggregate data and can amplify privacy risks. Governance should address:

• ownership and stewardship;
• cataloging and business glossary management;
• lineage from source to report;
• data classification and retention;
• role-based or attribute-based access control;
• masking, tokenization, and encryption;
• separation of duties;
• consent and lawful processing;
• auditability and incident response; and
• regulatory obligations such as data-protection, financial, health, or sector-specific requirements.

Security must apply to raw, curated, and semantic layers. Row-level security may restrict users to a region, while column-level controls may protect salary or personally identifiable information. Copying sensitive data into a lake without equivalent controls is a serious architectural failure.

Metric governance is also an ethical issue. A dashboard can be technically accurate yet misleading if definitions change silently, excluded populations are not disclosed, or historical corrections are hidden. Managers should know data freshness, known limitations, confidence boundaries, and the consequences of automated decisions.

Illustrative Case: Regional Retail Analytics Modernization

Background

A fictional national retailer operates stores, an online channel, a loyalty program, and a distribution network. Its finance, marketing, and operations teams use separate spreadsheets and departmental reports. “Net sales” is defined differently by finance and marketing, and regional managers cannot compare store and online performance reliably.

Problem

The retailer needs daily sales and inventory reporting, monthly profitability analysis, and customer segmentation. Source systems contain different product identifiers, customer records, time zones, and return treatments. The organization also wants to retain clickstream data for future recommendation models.

Analysis

The team identifies sales-line events as the grain of the transaction fact. It creates conformed Date, Product, Customer, Store, Channel, and Promotion dimensions. Inventory is modeled separately as a daily periodic snapshot because inventory balances are semi-additive across time. Returns are represented either as negative quantities and amounts under an agreed policy or in a separate return fact, provided the policy is consistently applied.

Product category changes require Type 2 history because management wants prior sales to remain associated with the category applicable at the time. Customer address changes also use Type 2 history for regional analysis, subject to privacy controls. A governed semantic measure defines net sales as gross sales less approved discounts and returns, with currency conversion rules documented.

Approach and Solution

The architecture uses object storage as a raw landing area, a lakehouse table layer for validated and historized data, and a warehouse-style presentation model for governed BI. CDC extracts operational changes. Data-quality tests check product-key coverage, duplicate transaction identifiers, currency validity, and reconciliation to source totals. Clickstream events remain in the lakehouse, while curated session and campaign facts support marketing dashboards.

The semantic layer exposes certified measures and row-level security by region. Finance, marketing, and operations participate in a metric council that approves definitions and change control. Data lineage links executive dashboards to source tables and transformation versions.

Outcome

The retailer obtains a shared definition of net sales, daily inventory visibility, and historical regional reporting. Analysts can explore clickstream data without placing raw event workloads on the reporting warehouse. However, the organization recognizes that platform implementation alone does not solve ownership, data-quality, or adoption problems. Continuous stewardship and monitoring remain necessary.

Learning Points

The case demonstrates the importance of declaring grain, using conformed dimensions, separating transaction and snapshot facts, managing history explicitly, and treating the semantic layer as a governed product. It also illustrates why a hybrid lakehouse–warehouse design may be more practical than forcing every workload into one physical system.

Contemporary Developments and AI

Streaming ingestion increasingly supports near-real-time dashboards, fraud detection, and operational intelligence. Event-time processing is important because data may arrive later than the time at which an event occurred. Data contracts are emerging as agreements between producers and consumers concerning schemas, meanings, quality, and change compatibility.

Data mesh emphasizes domain ownership, data as a product, self-service platform capabilities, and federated computational governance. It is an organizational and architectural approach, not a replacement for dimensional modeling or data quality engineering. It can improve accountability but may increase coordination demands.

AI assistants can help generate SQL, document lineage, detect anomalies, and translate natural-language questions into analytical queries. They can also produce insecure queries, misinterpret metrics, expose sensitive data, or create unsupported conclusions. AI-generated analytics therefore requires governed semantic models, permission-aware execution, testable outputs, human review, and clear disclosure of limitations.

Best-Practice Design Principles

1. Begin with business processes and declare fact-table grain.

2. Use conformed dimensions and governed definitions for cross-functional analysis.

3. Distinguish additive, semi-additive, and non-additive measures.

4. Preserve raw data when justified, but do not confuse retention with governance.

5. Make pipelines observable, restartable, testable, and lineage-aware.

6. Select ETL, ELT, warehouse, lake, or lakehouse patterns according to workload and constraints.

7. Design security and privacy before copying data into analytical platforms.

8. Treat semantic models and metrics as managed products.

9. Optimize for total value, including cost, freshness, reliability, and maintainability.

10. Establish ownership, stewardship, change control, and user training.

Chapter Summary

Data warehousing transforms integrated historical data into a foundation for analytical and managerial decision-making. Dimensional modeling centers on clearly defined fact-table grain, measurable business events, descriptive dimensions, surrogate keys, conformed dimensions, and appropriate historical techniques such as slowly changing dimensions. Star schemas favor simplicity, while snowflake schemas may provide additional normalization and hierarchy control.

ETL and ELT are alternative pipeline strategies, and both require validation, incremental processing, reconciliation, lineage, and monitoring. OLAP enables roll-up, drill-down, slicing, dicing, pivoting, and drill-through across dimensions and hierarchies. Data marts provide focused analytical access, while semantic layers establish shared business meaning and security.

Data lakes provide flexible storage for diverse data, whereas lakehouses seek to combine lake flexibility with warehouse reliability, governance, and analytical performance. Architecture selection should reflect workload, cost, freshness, governance, and organizational capability. Responsible platforms require privacy protection, access control, quality management, lineage, metric governance, and human oversight of AI-assisted analytics.

Key Terms

Data warehouse: Integrated, historical data store designed for analytical decision support.

OLTP: Processing optimized for operational transactions.

OLAP: Processing optimized for multidimensional analysis.

Fact table: Table recording business events, states, or measurable activity.

Dimension table: Table providing descriptive context for facts.

Grain: Precise meaning represented by one fact-table row.

Additive measure: Measure that can be summed across relevant dimensions.

Semi-additive measure: Measure summable across some dimensions but not others.

Star schema: Fact table directly connected to denormalized dimensions.

Snowflake schema: Dimensional schema with normalized dimension structures.

Surrogate key: Warehouse-generated identifier for a dimension member.

Slowly changing dimension: Method for managing changes in dimension attributes.

Conformed dimension: Shared dimension with consistent meaning across models.

Data mart: Subject-oriented analytical store for a department or process.

Semantic layer: Governed business-facing layer defining metrics and relationships.

ETL: Extract, transform, and load processing pattern.

ELT: Extract, load, and transform processing pattern.

Data lake: Flexible store for structured, semi-structured, and unstructured data.

Lakehouse: Platform pattern combining lake flexibility with warehouse capabilities.

Change data capture: Technique for identifying source-system changes incrementally.

Review Questions

Short Answer Questions

1. Why must the grain of a fact table be declared before selecting measures?

2. Distinguish additive, semi-additive, and non-additive measures.

3. What is the purpose of a conformed dimension?

4. Differentiate ETL from ELT.

5. What problem does a semantic layer solve?

Long Answer Questions

1. Explain the architecture and design principles of a dimensional data warehouse.

2. Compare star schemas and snowflake schemas, including their advantages and limitations.

3. Explain transaction, periodic snapshot, accumulating snapshot, and factless fact tables.

4. Compare data warehouses, data lakes, and lakehouses in terms of structure, governance, performance, and use cases.

5. Discuss the role of governance, privacy, security, and lineage in analytical platforms.

Analytical / Application Questions

1. Design a dimensional model for a university course-registration system. State the grain, facts, dimensions, and treatment of enrollment cancellations.

2. A company reports inventory balances by summing daily snapshots across months. Diagnose the error and propose an appropriate analytical method.

3. An organization has governed financial reporting, rapidly changing IoT data, and machine-learning workloads. Recommend an architecture and justify whether a warehouse, lake, lakehouse, or hybrid approach is most suitable.

Multiple Choice Questions with Answers

Question 1

What is the primary purpose of declaring the grain of a fact table?

A. To specify exactly what one row represents

B. To determine the database vendor

C. To eliminate all dimension tables

D. To guarantee that every measure is additive

Correct Answer: A. To specify exactly what one row represents

Question 2

Which measure is typically semi-additive?

A. Sales revenue

B. Units sold

C. Account balance

D. Transaction count

Correct Answer: C. Account balance

Question 3

Which slowly changing dimension technique preserves historical versions by creating new rows?

A. Type 0

B. Type 1

C. Type 2

D. Type 3

Correct Answer: C. Type 2

Question 4

Which OLAP operation moves from year-level data to month-level data?

A. Roll-up

B. Drill-down

C. Slice

D. Pivot

Correct Answer: B. Drill-down

Question 5

What is a principal advantage of a conformed dimension?

A. It prevents all source-system changes

B. It enables consistent analysis across fact tables

C. It removes the need for data quality checks

D. It stores only unstructured data

Correct Answer: B. It enables consistent analysis across fact tables

Question 6

In ELT, where are transformations commonly performed?

A. Only in the source OLTP system

B. After loading, within the analytical platform

C. Only inside user spreadsheets

D. Before extraction begins

Correct Answer: B. After loading, within the analytical platform

Question 7

Which characteristic is most associated with a data lake?

A. Support only for normalized transaction tables

B. Storage of diverse data in relatively raw form

C. Mandatory use of proprietary multidimensional cubes

D. Absence of metadata and governance by definition

Correct Answer: B. Storage of diverse data in relatively raw form

Question 8

Why should margin percentage generally be calculated from aggregated revenue and cost?

A. Percentages are always additive

B. It avoids giving inappropriate equal weight to row-level percentages

C. It removes the need for currency consistency

D. It guarantees that revenue is nonzero

Correct Answer: B. It avoids giving inappropriate equal weight to row-level percentages

Question 9

Which design is most appropriate for recording inventory at the end of each day?

A. Periodic snapshot fact

B. Factless fact only

C. Static dimension table

D. Accumulating snapshot for every product change

Correct Answer: A. Periodic snapshot fact

Question 10

What is a central responsibility of a semantic layer?

A. Defining governed metrics and business relationships

B. Replacing all operational applications

C. Storing only raw unprocessed events

D. Removing access-control requirements

Correct Answer: A. Defining governed metrics and business relationships