Data Warehouse Tutorial

Data Warehouse Tutorial for Beginners

A data warehouse is a central data platform designed for reporting, historical analysis, and business decision-making. It combines data from operational systems such as sales applications, finance software, customer platforms, and inventory databases, then organizes that data so analysts can query it consistently.

This data warehouse tutorial introduces the essential concepts in a practical learning order: architecture, ETL and ELT, data marts, dimensional modelling, fact and dimension tables, schemas, and common dimension types.

What is a data warehouse?

A data warehouse is a subject-oriented, integrated, time-variant, and non-volatile collection of data prepared for analysis. These characteristics distinguish it from an operational database:

• Subject-oriented: Data is organized around business subjects such as customers, products, orders, or revenue.
• Integrated: Data from multiple sources is standardized into consistent names, formats, codes, and units.
• Time-variant: Historical records are retained so users can compare performance across dates and reporting periods.
• Non-volatile: Loaded data is generally read and analyzed rather than continuously overwritten by individual business transactions.

A data warehouse may use a relational database, a cloud-native analytical engine, or another column-oriented platform. SQL is commonly used to query a warehouse, but SQL itself is a language rather than a data warehouse.

Operational Database vs Data Warehouse

Area	Operational database	Data warehouse
Primary purpose	Run day-to-day transactions	Support reporting and analysis
Typical workload	Frequent inserts and updates	Large scans, aggregations, and comparisons
Data scope	Current data for one application	Integrated current and historical data
Common design	Normalized transaction tables	Dimensional models or analytical tables
Typical users	Applications and operational teams	Analysts, managers, and reporting tools

Core Components of a Data Warehouse

Terminology varies between platforms, but a data warehouse solution commonly contains four functional components:

1. Source systems: Transactional databases, business applications, files, event streams, and external services that produce data.
2. Data integration layer: ETL or ELT pipelines that extract, validate, standardize, and load source data.
3. Warehouse storage: Integrated historical data arranged for efficient analytical queries.
4. Consumption layer: Reports, dashboards, semantic models, ad hoc SQL queries, and analytical applications.

Production systems may also include staging storage, metadata management, orchestration, data-quality monitoring, security controls, lineage, and separate data marts.

Data Warehouse Architecture and Data Flow

A typical data warehouse architecture moves information through a controlled sequence:

1. Extract data from each source system.
2. Land the extracted data in a staging or raw area.
3. Validate data types, required fields, duplicates, and business keys.
4. Standardize values and apply agreed business rules.
5. Load integrated warehouse tables.
6. Publish dimensional models, data marts, or semantic models for reporting.
7. Monitor pipeline runs, rejected records, freshness, and query performance.

What do L1, L2, and L3 mean in a data warehouse?

L1, L2, and L3 are not universal data warehouse standards. Some teams use them as local names for processing layers. A common interpretation is:

Layer	Common purpose	Typical contents
L1	Raw or landing layer	Source data retained with minimal changes
L2	Cleaned and integrated layer	Validated, standardized, deduplicated data
L3	Business or presentation layer	Facts, dimensions, aggregates, and reporting models

Other organizations may assign different meanings to these labels. Always check the architecture documentation and data contracts for the system being used.

ETL and ELT in Data Warehousing

ETL means Extract, Transform, and Load. Data is collected from source systems, transformed in a processing layer, and then loaded into the target warehouse. Transformations can include correcting data types, standardizing dates and currencies, removing duplicates, joining records, and calculating business measures.

ELT means Extract, Load, and Transform. Source data is first loaded into the target analytical platform, and transformations are performed there. ELT is frequently used when the warehouse platform provides enough scalable processing capacity to transform data after ingestion.

Method	Processing order	Transformation location
ETL	Extract → Transform → Load	Before data enters the warehouse tables
ELT	Extract → Load → Transform	Inside the target analytical platform

Data Marts and Enterprise Data Warehouses

An enterprise data warehouse integrates information across multiple business functions. A data mart is a smaller analytical data set focused on a particular department, subject, or use case, such as sales, finance, or customer support.

A data mart may be populated from the enterprise warehouse to preserve shared definitions, or it may be created directly from source systems for a limited requirement. Learn more in What is Data Mart?

Dimensional Modelling: Facts, Dimensions, and Grain

Dimensional modelling structures data around measurable business events. Before creating tables, define the grain: exactly what one row in a fact table represents. For example, the grain might be one product line on one customer order.

• Fact table: Stores measurable events and foreign keys to related dimensions. Examples include quantity sold, discount amount, and sales value.
• Dimension table: Stores descriptive context used to filter, group, and label facts. Examples include customer, product, store, and date.
• Measure: A numeric value that can be aggregated, subject to its business meaning.
• Surrogate key: A warehouse-generated identifier that separates a dimension record from changing source-system keys.

Star Schema and Snowflake Schema

In a star schema, a central fact table connects directly to denormalized dimension tables. This arrangement is straightforward for reporting tools and users to query.

In a snowflake schema, dimensions are normalized into additional related tables. This can reduce repeated attributes, but it introduces more joins and makes the model less direct for report authors. The appropriate schema depends on governance, maintainability, performance, and reporting requirements.

Common Types of Data Warehouse Dimensions

• Conformed dimension: A shared dimension with consistent meaning across multiple fact tables or data marts.
• Junk dimension: A combined dimension for low-cardinality flags and indicators that do not justify separate dimensions.
• Slowly changing dimension: A dimension designed to handle changes to descriptive attributes over time.
• Degenerate dimension: A business identifier, such as an order number, stored in a fact table without a separate dimension table.
• Role-playing dimension: One physical dimension used in several logical roles, such as order date, shipment date, and payment date.

How to Build a Small Data Warehouse Example

Consider a retailer that wants a monthly sales report. Order data comes from an ecommerce application, product details come from a catalogue database, and store information comes from a separate administration system.

1. Define the business question: Measure sales quantity and revenue by month, product category, store, and customer region.
2. Declare the grain: One row in the sales fact table represents one product line on a completed order.
3. Identify dimensions: Date, product, store, and customer.
4. Identify facts: Quantity, gross amount, discount amount, and net amount.
5. Map source fields: Record the source and transformation rule for every warehouse column.
6. Load dimensions first: Resolve business keys and assign warehouse surrogate keys.
7. Load the fact table: Connect each sales event to the correct dimension records.
8. Validate the result: Reconcile row counts and financial totals with approved source reports.
9. Publish the model: Provide governed reporting views or a semantic model for analysts.

Data Warehouse Tutorial Learning Path

Use the following sequence to progress from basic data warehouse concepts to dimensional design:

1. What is Data Warehouse?
2. Data Warehouse Architecture?
3. What is Data Mart?
4. What is Data Modelling?
5. What is Dimensional Table & Fact Tables?
6. Schemas in Dimensional Modelling.
   1. What is Star Schema?
   2. What is Snow Flake Schema?
7. Different types of Dimensions.
   1. Confirmed Dimension.
   2. Junk Dimension.
   3. Slowly changing Dimension.
   4. Degenerate Dimension.
   5. Role-playing Dimension.

Data Warehouse Design Checks Before Reporting

• Confirm that the grain of every fact table is stated clearly and enforced consistently.
• Verify that measures use documented formulas, currencies, units, and aggregation rules.
• Reconcile warehouse totals and record counts with authoritative source-system results.
• Test how late-arriving facts, missing dimension records, duplicate events, and source corrections are handled.
• Confirm that historical dimension changes produce the intended reporting result.
• Check data freshness, pipeline failures, rejected records, and alert ownership.
• Restrict sensitive columns and verify that report users receive only authorized data.
• Document source-to-target mappings, business definitions, lineage, and retention rules.

Data Warehouse Frequently Asked Questions

What is ETL in data warehousing?

ETL is the process of extracting data from source systems, transforming it into a consistent and validated form, and loading it into a data warehouse. It is used to integrate data and apply business rules before analytical reporting.

Is SQL a data warehouse?

No. SQL is a language used to define, transform, and query data. Many data warehouses support SQL, but the warehouse is the complete analytical system that includes storage, integration pipelines, metadata, security, and reporting access.

What are the four components of a data warehouse?

A practical four-part description includes source systems, a data integration layer, warehouse storage, and a consumption or presentation layer. Some architecture models divide these components differently or list supporting capabilities such as metadata separately.

What do L1, L2, and L3 mean in a data warehouse?

These labels depend on the organization. They often refer to raw data, cleaned and integrated data, and business-ready presentation data, respectively. Because there is no universal definition, the project documentation should be treated as the authority.

What is the difference between a data warehouse and a data mart?

A data warehouse generally integrates data across an organization, while a data mart serves a narrower department, subject, or reporting use case. A governed data mart can be derived from the enterprise warehouse so that it retains shared definitions.