Learning Objectives

By the end of this lesson, learners should be able to:

  • Explain the purpose of a data warehouse.
  • Describe ETL processes.
  • Distinguish operational databases from analytical databases.
  • Understand data marts and cloud data warehouses.

Operational Systems Vs Analytical Systems

Operational Systems (OLTP)

Designed for daily transactions.

Examples:

  • Sales transactions,
  • ATM withdrawals,
  • Online orders.

Analytical Systems (OLAP)

Designed for analysis and reporting.

Examples:

  • Sales trend analysis,
  • Customer segmentation,
  • Profitability analysis.

Operational systems prioritize speed of transactions, while analytical systems prioritize complex querying and historical analysis.

What Is A Data Warehouse?

A data warehouse is a centralized repository that stores integrated, historical, and subject-oriented data for analysis.

Key Characteristics

  • Integrated,
  • Historical,
  • Non-volatile,
  • Subject-oriented.

Data Warehouse Architecture

Typical layers:

  1. Data sources,
  2. ETL layer,
  3. Data warehouse,
  4. Data marts,
  5. BI tools.

ETL Process

Extract

Collect data from source systems.

Transform

Clean, standardize, validate, and enrich data.

Load

Store transformed data in the warehouse.

Example

A multinational retailer extracts sales data from stores in Toronto, London, Dubai, Singapore, and Sydney, converts all currencies to USD, standardizes product codes, and loads the data into a global warehouse.

Data Cleansing Activities

  • Removing duplicates,
  • Correcting invalid dates,
  • Standardizing country names,
  • Handling missing values,
  • Validating business rules.

Poor cleansing leads to unreliable reports.

Data Marts

A data mart is a smaller repository focused on a specific department such as finance, marketing, or HR.

Data marts improve performance and simplify access for departmental users.

Cloud Data Warehousing

Modern organizations increasingly use cloud platforms such as Snowflake, BigQuery, Redshift, and Synapse.

Advantages

  • Scalability,
  • Lower infrastructure management,
  • Faster deployment,
  • Global accessibility.

International Case Study

A global fashion retailer consolidated data from 18 countries into a cloud data warehouse. Executives gained a single view of inventory and sales, reducing stockouts and excess inventory.

Common ETL Challenges

  • Inconsistent source data,
  • Different currencies,
  • Different time zones,
  • Duplicate customer records,
  • Changing business definitions.

Strong data governance is essential.

Practical Activity

Draw a simple ETL diagram showing data flowing from sales systems, CRM, and website analytics into a data warehouse and then into a dashboard.

Learning Materials / Reference Materials

  • Kimball & Ross. The Data Warehouse Toolkit.
  • Inmon. Building the Data Warehouse.
  • Snowflake and BigQuery documentation.

Lesson Summary

Data warehouses and ETL processes provide the integrated, high-quality data foundation required for reliable business intelligence and analytics.

PreviousNext