Data Warehousing and Data Integration

Introduction

Business organisations generate data through sales transactions, websites, mobile applications, accounting systems, customer relationship management platforms, supply-chain systems, sensors and external data services. Although this abundance of data creates analytical opportunity, operational systems are usually designed to process individual transactions rather than support historical analysis. A sales system may efficiently record an order, but it may not efficiently answer questions such as: Which customer segments have become less profitable over three years? Which products are frequently purchased together? How does performance differ across regions and channels?

Data warehousing and data integration provide the architectural and methodological foundations for answering such questions. They bring data from multiple sources into environments where it can be organised, governed, analysed and reported consistently. This chapter examines data warehouses, data marts, data lakes and lakehouses; compares extract, transform, load (ETL) with extract, load, transform (ELT); and develops the central dimensional-modelling concepts used in business intelligence.

Learning Objectives

By the end of this chapter, you should be able to:

1. Explain the purposes and characteristics of data warehouses, data marts, data lakes and lakehouses.
2. Describe the stages and design considerations of data integration.
3. Compare ETL and ELT and select an appropriate approach for a business context.
4. Define facts, dimensions, measures, grain and keys in dimensional modelling.
5. Distinguish star schemas from snowflake schemas.
6. Explain and apply slowly changing dimension techniques.
7. Identify data-quality, governance, security and ethical issues in analytical data platforms.

1. From Operational Data to Analytical Information

An operational database supports online transaction processing (OLTP). It is optimised for inserting, updating and retrieving current records with strong transaction consistency. For example, an order-processing system must ensure that an order, its payment and its inventory adjustment are recorded reliably.

An analytical environment supports online analytical processing (OLAP). It is optimised for scanning large volumes of historical data, aggregating measures and comparing results across multiple perspectives. OLAP users commonly analyse data by time, product, customer, location, channel and organisation.

The two environments have different priorities:

A data warehouse is not merely a large database. It is a subject-oriented, integrated, time-variant and relatively non-volatile collection of data designed to support management analysis. “Subject-oriented” means that data is organised around subjects such as sales or customers rather than around individual applications. “Integrated” means that formats, codes, definitions and identifiers are reconciled. “Time-variant” means that historical states and periods are retained. “Non-volatile” means that data is generally loaded and read for analysis rather than continually updated by front-line transactions.

2. Analytical Data Architectures

2.1 Data warehouse

A data warehouse stores curated, structured data for reporting, business intelligence and analytical applications. Data is extracted from source systems, validated, transformed and loaded according to defined business rules. A warehouse normally contains historical snapshots or transaction-level records and provides consistent definitions for measures such as revenue, units sold and gross margin.

A warehouse may be hosted on-premises, in a cloud platform or in a hybrid environment. Modern cloud warehouses commonly separate storage from computing, allowing organisations to scale analytical processing independently from data storage.

2.2 Data mart

A data mart is a smaller analytical store focused on a department, process or subject area, such as finance, marketing or human resources. A dependent data mart is populated from an enterprise warehouse and normally follows its standards. An independent data mart is built directly from operational or external sources. Independent marts can be developed quickly, but they may create inconsistent definitions, duplicated data and competing versions of the truth.

Data marts are useful when a department has specific performance, security or usability requirements. However, they should be governed as part of an enterprise architecture rather than developed as isolated spreadsheets or databases.

2.3 Data lake

A data lake stores large quantities of raw, semi-structured and unstructured data. Examples include JSON event logs, documents, images, audio, sensor readings and application extracts. A lake delays some interpretation until data is needed, a characteristic often described as schema-on-read. This flexibility is valuable for data science, exploration and machine learning.

A poorly governed lake can become a “data swamp”. Common causes include missing metadata, duplicated files, unclear ownership, obsolete records, weak access controls and the inability to determine whether two datasets have compatible meanings.

2.4 Data lakehouse

A lakehouse combines the economical and flexible storage associated with a lake with warehouse-like features such as transactional reliability, structured metadata, governance and efficient analytical querying. It commonly supports both structured business reporting and advanced analytics on a shared platform.

The term does not guarantee a particular product architecture. In practice, the quality of a lakehouse depends on storage formats, metadata management, access controls, data-quality processes, workload isolation and governance. A lakehouse can reduce duplication, but it does not remove the need for clear business definitions and modelling.

Figure: A simplified flow from operational and external sources to integrated analytical platforms and users.

2.5 Comparison of analytical platforms

3. Data Integration and the ETL–ELT Decision

Data integration combines data from different sources so that it can be used consistently. It involves source discovery, extraction or capture, profiling, cleansing, transformation, matching, loading, monitoring and documentation.

3.1 ETL

In ETL, data is extracted from sources, transformed in a separate processing layer, and then loaded into the target warehouse. Transformations may include changing data types, standardising dates, mapping source codes, removing duplicates, validating relationships and calculating derived fields.

ETL is useful when:

• the target warehouse requires only validated and structured data;
• sensitive data must be filtered before entering the target;
• transformation logic is complex or must be centrally controlled;
• source systems should be protected from analytical processing; or
• the target platform has limited transformation capacity.

3.2 ELT

In ELT, data is extracted and loaded into a scalable lake or warehouse before transformation occurs within the target platform. ELT is well suited to cloud platforms with elastic processing and low-cost storage. It preserves raw data, which can support auditing, reprocessing and new analytical requirements.

ELT requires careful access control because raw data may contain personal or commercially sensitive information. It also requires reliable metadata, workload management and controls to prevent users from treating unvalidated raw data as authoritative.

3.3 ETL and ELT comparison

The choice is not always exclusive. Organisations often use a hybrid approach: minimal transformations are applied before loading to protect systems or remove prohibited data, while business-specific transformations occur in the warehouse or lakehouse.

3.4 Integration patterns

Batch integration moves data at scheduled intervals. It is appropriate for daily financial reporting or periodic inventory analysis. Streaming integration moves events continuously or with very low latency. It is appropriate for fraud detection, real-time recommendations and operational monitoring. Change data capture (CDC) identifies inserts, updates and deletions in a source so that only changes need to be transferred. CDC can reduce processing volume but requires reliable source logs and careful treatment of updates and deletes.

A robust pipeline should be idempotent where possible: rerunning the same input should not create unintended duplicate results. It should also record operational metadata such as run time, source extract version, row counts, rejected records and error details.

4. Dimensional Modelling

Dimensional modelling organises analytical data into measurable business events and the contexts in which those events occur. It is widely associated with star schemas and is designed to be understandable to business users and efficient for analytical queries.

4.1 Business process and grain

A model begins by identifying the business process: for example, retail sales, shipment, payment, service call or employee absence. The next and most important decision is the grain. Grain states exactly what one row in a fact table represents.

Examples of grain include:

• one row per product on a customer order;
• one row per daily inventory balance per product and store;
• one row per flight booking; or
• one row per monthly account balance.

The grain must be declared before selecting facts and dimensions. Mixing different grains in one fact table causes incorrect aggregation and ambiguous interpretation. A daily inventory fact and an individual sales-line fact should normally be separate fact tables because they represent different events.

4.2 Fact tables

A fact table records business events or states at the selected grain. It usually contains foreign keys to dimensions and numeric measures. For a sales-line fact, keys may identify date, product, customer, store and promotion. Measures may include quantity, gross sales amount, discount amount, tax amount and cost amount.

A fact table often has a surrogate key, although the combination of dimensional keys may also identify the event. Degenerate dimensions are transaction identifiers, such as an invoice number, stored directly in the fact table when no separate descriptive dimension is needed.

4.3 Measures

A measure is a value used for analysis. Measures are commonly classified as:

• Additive: can be summed across all relevant dimensions, such as units sold or sales amount.
• Semi-additive: can be summed across some dimensions but not others, such as an account balance, which may be summed across accounts but not across time.
• Non-additive: should not be summed, such as a percentage, ratio or unit price. These are usually recalculated from additive components.

For example, a weighted average selling price should generally be calculated as:

Here, total sales amount is the numerator and total units sold is the denominator. This calculation is appropriate when both values refer to the same population, period and level of aggregation. Averaging product-level prices can produce a misleading result when products have different sales volumes.

4.4 Dimension tables

Dimensions describe the who, what, where, when and how of a business event. Typical dimensions include Date, Product, Customer, Store, Employee, Supplier and Promotion. A Date dimension may contain day, week, month, quarter, financial period, holiday indicator and season. A Product dimension may contain product name, brand, category, package size and department.

Dimensions should use consistent definitions and often contain descriptive attributes that make reports easier to understand. A conformed dimension has the same meaning and structure across multiple fact tables. A conformed Date or Customer dimension allows sales, returns and customer-service facts to be analysed together.

4.5 Keys and surrogate keys

A natural key originates in a source system, such as a customer number. A surrogate key is an warehouse-generated identifier, often an integer, used to identify a dimension record. Surrogate keys are valuable because source identifiers may differ between systems and because multiple historical versions of one business entity may need separate records.

A reliable loading process should maintain mappings between source keys and surrogate keys, handle unknown or not-yet-identified members, and prevent accidental reuse of a key for a different historical meaning.

4.6 Star and snowflake schemas

A star schema has one central fact table connected directly to relatively denormalised dimension tables. It is easy for analysts to understand and commonly supports efficient BI queries.

A snowflake schema normalises one or more dimensions into related tables. For example, Product may link to Brand, which links to Category. Snowflaking can reduce repeated descriptive data and improve control over certain hierarchies, but it increases joins and may be less intuitive for business users.

Neither design is universally superior. The choice depends on platform performance, data volume, hierarchy complexity, governance and the skills of users.

5. Slowly Changing Dimensions

A slowly changing dimension (SCD) is a dimension whose descriptive attributes change over time. A customer may move to a new region, a product may change category, or an employee may change department. The design must decide whether analysis should show the current value, the historical value, or both.

Type 0: retain the original value

The original value is never changed. This is appropriate for attributes that should remain fixed, such as an original registration date or birth date, subject to data-correction policies.

Type 1: overwrite

The old value is replaced by the new value. This is simple and appropriate when history is not required or when correcting an error. However, historical reports may change after an overwrite.

Type 2: create a new version

A new dimension row is created for each significant change. The row commonly includes a surrogate key, effective-from date, effective-to date and current-row indicator. Existing fact rows continue to point to the historical version that was valid when the event occurred.

For example, if customer C104 moves from North to South on 1 July, the North row may have an effective-to date of 30 June and current indicator “N”; a new South row begins on 1 July and has current indicator “Y”. Facts dated before July use the North surrogate key, while later facts use the South key.

Type 3: retain limited previous history

The current value and one or more previous values are stored in columns in the same row. This is useful when the business only needs a limited comparison, but it does not preserve an unlimited history.

Type 2 provides the richest historical interpretation but increases dimension size and loading complexity. The correct choice depends on reporting requirements, regulatory needs, correction policies and the meaning of the attribute.

6. Worked Modelling Example

A retailer wants to analyse sales by date, product, customer, store and promotion. The business process is the sale of a product line. The declared grain is: one row per product line on a completed customer transaction.

The fact table, FactSales, may contain DateKey, ProductKey, CustomerKey, StoreKey, PromotionKey, TransactionNumber, Quantity, SalesAmount, DiscountAmount and CostAmount. The dimensions may contain:

• DimDate: calendar date, month, quarter and financial year;
• DimProduct: product name, brand, category and supplier;
• DimCustomer: customer segment, region and loyalty status;
• DimStore: store name, city, region and format; and
• DimPromotion: campaign, promotion type and funding source.

Suppose a line records 4 units with sales amount 80 currency units, discount 8 and cost 48. The net sales amount after discount is:

If gross margin is defined as net sales minus cost, then:

These calculations assume that sales, discount and cost are measured on the same line, currency basis and tax treatment. If tax, returns or shipping costs are handled separately, the business definition must state whether they are included.

The grain prevents an incorrect design such as storing one row per transaction while also recording product-line quantities. If a transaction contains several products, a transaction-level row cannot accurately represent each product without repeating or losing information.

7. Applied Case: Northstar Retail Group

This is an illustrative case.

Background

Northstar Retail Group operates physical stores and an online channel. Its point-of-sale system, e-commerce platform, loyalty application and finance system use different product codes, customer identifiers and definitions of sales. Executives receive weekly spreadsheets, but regional totals do not agree with finance totals.

Problem

The organisation cannot reliably compare store and online performance. A product may appear under several names, customer regions are inconsistent, and historical reports change whenever a product is reclassified. Analysts spend much of their time reconciling data rather than interpreting it.

Analysis

The project team profiles the sources and identifies three major causes: duplicated product records, inconsistent calendar and promotion definitions, and a failure to declare the grain of existing reports. The team also discovers that online returns are recorded on a different date from the original sale.

Approach and solution

Northstar establishes a governed warehouse with a sales-line fact table and conformed Date, Product, Customer, Store and Promotion dimensions. Product and customer identifiers are mapped through cross-reference tables. Product category changes use SCD Type 2 because management needs historical reporting according to the category that was valid when the sale occurred. Corrected spelling errors use Type 1 treatment.

Daily batch loads are used for finance and store sales. CDC is used for selected high-volume operational systems. Raw extracts are retained in a controlled lake area for audit and reprocessing, while validated dimensional data is loaded into the warehouse. Data-quality checks compare source and target row counts, detect unknown product keys and reconcile sales totals to finance within an agreed tolerance.

Outcome

Managers can compare channels using common definitions. Historical reports remain stable after product reclassification, while current product reports can still show the latest category. The organisation reduces manual reconciliation and gains a foundation for customer segmentation and inventory analysis.

Learning points

The case demonstrates that technology alone does not solve integration problems. Agreement on business definitions, grain, ownership, historical treatment and data-quality thresholds is essential. It also illustrates why a hybrid architecture can be practical: a lake preserves source history, while a warehouse provides governed analytical structures.

8. Governance, Quality, Security and Ethics

Data integration can amplify errors. If a source system contains duplicate customers or incorrect prices, the warehouse can distribute those errors to many reports. Important quality dimensions include accuracy, completeness, consistency, timeliness, validity, uniqueness and integrity.

Governance should assign data owners and custodians, define critical data elements, document lineage, establish retention periods and specify approved business definitions. A data catalogue should help users discover datasets, understand their meaning, identify owners and assess quality.

Personal data requires special protection. Organisations should apply purpose limitation, data minimisation, access control, encryption, retention management and appropriate legal or regulatory requirements. Sensitive attributes should not be copied into analytical environments without a justified purpose. Row-level security, masking, tokenisation and role-based access may be appropriate controls.

Ethically, analysts must consider whether combining datasets creates unexpected surveillance or unfair profiling. A customer may consent to a loyalty programme without expecting their behaviour to be combined with external data for high-impact decisions. Data governance therefore includes transparency, accountability, fairness and the ability to investigate how a decision was supported.

9. Contemporary Developments and Best Practices

Modern platforms increasingly support real-time ingestion, event streaming, data contracts, semantic layers, DataOps and automated quality testing. A semantic layer can provide governed definitions for measures such as revenue and active customer, reducing inconsistency between dashboards. Data contracts specify expected schemas, meanings and quality obligations between producers and consumers.

AI can assist with anomaly detection, schema matching, metadata generation and natural-language querying. However, AI-generated mappings and summaries require human review. A plausible but incorrect automated transformation can spread silently through critical reports.

Practical best practices include:

1. Begin with business processes and decisions, not with available technology.
2. Declare the grain before designing a fact table.
3. Establish conformed dimensions and shared definitions.
4. Preserve appropriate raw data, but govern it rigorously.
5. Separate technical metadata, business metadata and operational monitoring.
6. Design for late-arriving data, corrections, duplicates and rejected records.
7. Test transformations with reconciliations and representative edge cases.
8. Document SCD policies for each changing attribute.
9. Protect sensitive data throughout extraction, storage and analysis.
10. Monitor cost, performance, freshness, quality and user adoption.

Challenges and Limitations

Warehousing projects can be expensive and organisationally difficult. Source systems may lack reliable keys, historical records or consistent definitions. Integration pipelines can fail because of schema changes, network interruptions, late files or unexpected null values. A warehouse can also become a bottleneck if every analytical requirement is forced into one centrally controlled release process.

Conversely, excessive decentralisation can produce duplicated pipelines and conflicting metrics. A lakehouse may reduce physical duplication but can still suffer from semantic fragmentation. Dimensional models simplify common reporting, but they may require additional structures for graph analysis, highly nested data, unstructured content or rapidly changing event streams. Design should therefore reflect analytical purpose rather than follow a fashionable architecture.

Chapter Summary

Data warehouses provide integrated, historical and governed data for analytical decision-making. Data marts focus on departments or subjects, data lakes preserve flexible raw and semi-structured data, and lakehouses seek to combine lake flexibility with warehouse governance and performance. Data integration may use ETL, ELT, batch processing, streaming or change data capture; the appropriate choice depends on latency, scale, governance and platform capabilities.

Dimensional modelling begins with a business process and an explicit grain. Fact tables record events or states, while dimensions describe their context. Measures may be additive, semi-additive or non-additive, and ratios should generally be recalculated from appropriate additive components. Star schemas favour simplicity and usability, whereas snowflake schemas normalise dimensions and may reduce redundancy. Slowly changing dimensions determine how historical attribute changes are represented, with Types 0, 1, 2 and 3 serving different purposes.

Successful analytical platforms require more than storage and transformation. Data quality, metadata, lineage, security, privacy, ethical use, ownership and clear business definitions are essential to trustworthy decision support.

Key Terms

Data warehouse: A governed, integrated and historical analytical data store.

Data mart: A focused analytical store for a department, subject or business process.

Data lake: A scalable store for raw structured, semi-structured and unstructured data.

Lakehouse: An architecture combining lake flexibility with warehouse-style analytical and governance capabilities.

ETL: Extract, transform and load data into a target platform.

ELT: Extract and load data before transforming it within the target platform.

Change data capture: A method of identifying source inserts, updates and deletes for integration.

Fact table: A table recording business events or states at a defined grain.

Dimension table: A table describing the context of facts.

Measure: A value used for quantification and analysis.

Grain: The precise meaning of one row in a fact table.

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

Conformed dimension: A shared dimension with consistent meaning across fact tables.

Surrogate key: A warehouse-generated identifier for a record, especially a dimension version.

Star schema: A fact table directly connected to denormalised dimensions.

Snowflake schema: A dimensional schema with one or more normalised dimensions.

Slowly changing dimension: A dimension whose descriptive attributes change over time.

Data lineage: Documentation of data origins, transformations and destinations.

Schema-on-read: Interpreting structure when data is accessed rather than enforcing all structure at ingestion.

Review Questions

Short Answer Questions

1. What is the main difference between an OLTP system and an analytical system?
2. Define the grain of a fact table and explain why it matters.
3. What is a conformed dimension?
4. Distinguish an additive measure from a semi-additive measure.
5. State one important difference between ETL and ELT.

Long Answer Questions

1. Compare data warehouses, data marts, data lakes and lakehouses in terms of purpose, data structure, strengths and risks.
2. Explain the stages of a reliable data integration pipeline and the role of metadata and monitoring.
3. Describe star and snowflake schemas and evaluate when each may be appropriate.
4. Explain SCD Types 0, 1, 2 and 3, including an example of a business situation for each.
5. Discuss how governance, privacy, security and ethics influence the design of an analytical data platform.

Analytical / Application Questions

1. A retailer stores sales, returns and inventory balances in one fact table. Identify the modelling problem and propose an improved design, including the grain of each fact table.
2. A bank needs real-time fraud alerts but monthly regulatory reporting. Recommend an integration and storage architecture, explaining where streaming, batch processing, ETL or ELT may be used.
3. A product changes category in July, and management wants historical sales reported using the category applicable at the time of each sale. Select an SCD strategy and describe the dimension records and fact-key behaviour required.

Multiple Choice Questions with Answers

Question 1

Which characteristic most directly distinguishes a data warehouse from an operational database?

A. It is designed primarily for current transaction entry.

B. It stores integrated historical data for analysis.

C. It must contain only unstructured files.

D. It cannot contain numerical measures.

Correct Answer: B. It stores integrated historical data for analysis.

Question 2

In an ELT process, where are transformations generally performed?

A. Only in the source application

B. Before extraction from the source

C. Inside the target warehouse or lakehouse

D. Only by end users in spreadsheets

Correct Answer: C. Inside the target warehouse or lakehouse

Question 3

What does the grain of a fact table specify?

A. The encryption method used for the table

B. The precise meaning of one row

C. The number of dimensions permitted

D. The physical storage location

Correct Answer: B. The precise meaning of one row

Question 4

Which is usually an additive measure?

A. Unit price

B. Percentage margin

C. Units sold

D. Customer satisfaction rating

Correct Answer: C. Units sold

Question 5

A fact table records one row per product line on a completed transaction. Which design choice is this?

A. A declared grain

B. A Type 1 dimension

C. A snowflake hierarchy

D. A data-lake partition

Correct Answer: A. A declared grain

Question 6

What is a principal feature of a star schema?

A. All dimensions are excluded from the model.

B. The fact table connects directly to generally denormalised dimensions.

C. Every measure is stored in a separate database.

D. It can store only streaming data.

Correct Answer: B. The fact table connects directly to generally denormalised dimensions.

Question 7

A customer’s regional value is overwritten, and previous values are not retained. Which SCD technique is being used?

A. Type 0

B. Type 1

C. Type 2

D. Type 3

Correct Answer: B. Type 1

Question 8

Which SCD technique creates a new dimension row when an attribute changes?

A. Type 0

B. Type 1

C. Type 2

D. Type 3

Correct Answer: C. Type 2

Question 9

Why should a ratio such as average selling price generally be recalculated from totals?

A. Ratios are always stored as text.

B. A simple average can ignore differences in sales volumes.

C. Totals cannot be stored in fact tables.

D. Dimensions cannot contain products.

Correct Answer: B. A simple average can ignore differences in sales volumes.

Question 10

Which issue is a common risk of an uncontrolled data lake?

A. It cannot store raw data.

B. It automatically removes duplicate records.

C. It may become a data swamp with poor metadata and governance.

D. It prevents all machine-learning activities.

Correct Answer: C. It may become a data swamp with poor metadata and governance.