Data Warehousing and Analytical SQL

Introduction

Operational databases are designed to record transactions efficiently: an order is entered, a payment is posted, or a customer address is changed. Business intelligence, however, asks different questions. Managers want to compare sales across regions, identify profitable customer segments, measure inventory performance, and evaluate trends over time. These questions require integrated, historical, consistent, and analysis-oriented data.

A data warehouse is a subject-oriented, integrated, time-variant, and relatively nonvolatile repository designed to support analysis and decision-making. It commonly receives data from enterprise resource planning systems, customer relationship management systems, web applications, spreadsheets, and external data providers. Data is extracted, transformed, validated, and loaded through an ETL process, or extracted, loaded, and transformed through an ELT process.

This chapter explains dimensional modeling, fact and dimension tables, star and snowflake schemas, slowly changing dimensions, OLAP structures, KPI datasets, and SQL techniques for reporting. The emphasis is practical: a well-designed warehouse should make important business questions easier to answer while preserving accuracy, traceability, and appropriate access control.

Learning Objectives

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

1. Explain the purpose and architecture of a data warehouse.
2. Design a dimensional model using facts, dimensions, grains, and keys.
3. Compare star and snowflake schemas.
4. Select and implement appropriate slowly changing dimension strategies.
5. Use analytical SQL for aggregation, ranking, time comparison, and KPI reporting.
6. Explain OLAP concepts and distinguish them from relational reporting.
7. Evaluate data quality, governance, security, and performance issues in warehouse projects.

1. From Operational Data to Analytical Data

An operational system is optimized for current transactions. It often uses normalized tables to minimize duplication and preserve update consistency. A warehouse is optimized for reading, aggregation, historical analysis, and integration.

Important differences include:

A warehouse does not replace operational systems. Instead, it provides a governed analytical layer. A common architecture contains source systems, a staging area, transformation pipelines, warehouse storage, semantic or presentation models, and reporting tools.

ETL and ELT

In ETL, data is extracted from sources, transformed in a processing layer, and loaded into the warehouse. In ELT, raw or lightly processed data is loaded first and transformed using the warehouse engine. ELT is common in cloud platforms because scalable storage and compute can be separated.

A robust pipeline should include source identification, schema validation, type conversion, duplicate detection, business-rule validation, error handling, reconciliation, and audit logging. A pipeline that loads rows successfully but silently changes their meaning is not reliable.

2. Dimensional Modeling

Dimensional modeling organizes data around business processes. Its central ideas are the fact table, dimension table, grain, surrogate key, and measure.

Grain: the Most Important Design Decision

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

• One row per order line.
• One row per product per store per day.
• One row per account per month.
• One row per website session.

The grain must be declared before choosing measures. If a fact table has one row per order line, order-level shipping cost cannot safely be summed across rows unless it has been allocated or stored in a separate fact table. Mixing grains creates double counting.

Fact Tables

A fact table records measurable events or periodic states. It normally contains foreign keys to dimensions and numeric measures.

For a retail sales fact at order-line grain, columns might include:

Measures are additive, semi-additive, or non-additive. Additive measures can be summed across all relevant dimensions, such as sales revenue. Semi-additive measures can be summed across some dimensions but not time; account balance is a typical example. Non-additive measures, such as percentages and ratios, should generally be recalculated from their components rather than summed.

A common revenue calculation is:

The equation assumes that all components use the same currency, period, tax treatment, and sign convention. If returns are stored as negative sales in the fact table, adding them may already produce net revenue; the reporting logic must not subtract them a second time.

Dimension Tables

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

A date dimension is particularly valuable because it can contain calendar date, month, quarter, fiscal period, weekday, holiday indicator, and reporting-year attributes. It also avoids relying on database-specific date functions in every report.

Surrogate Keys

A surrogate key is a warehouse-generated identifier, usually an integer or other compact value. It is distinct from a source-system business key. Surrogate keys support historical tracking, accommodate multiple source systems, and prevent changes in source identifiers from breaking warehouse relationships.

Unknown, not applicable, and error conditions should be represented deliberately, often through reserved dimension rows such as 0 = Unknown. This is preferable to broken foreign keys or unexplained nulls, although the chosen convention must be documented.

3. Star and Snowflake Schemas

A star schema has a central fact table connected directly to denormalized dimension tables. It is easy for analysts to understand and usually requires fewer joins.

A snowflake schema normalizes some dimensions into additional related tables. For example, Product may reference Brand, Category, and Department tables. Snowflaking can reduce repeated descriptive data and support controlled hierarchies, but it increases joins and may make self-service analysis more difficult.

Neither design is universally superior. A star schema is often appropriate for a presentation layer, while normalized structures may be useful in the integration layer. The decision should consider query patterns, data volume, hierarchy stability, storage cost, and user skill.

4. Slowly Changing Dimensions

A slowly changing dimension (SCD) manages changes to descriptive attributes over time. For example, a customer may move from one region to another, or a product may be reassigned to a category. The correct strategy depends on whether historical reports should preserve the old value.

Type 0: Retain the Original Value

The original value is never changed. This is suitable for immutable attributes such as a birth date or original acquisition channel, assuming the source value is trusted.

Type 1: Overwrite

The dimension row is updated with the new value. This is simple and appropriate when corrections should replace history or historical changes are not analytically relevant. It does not preserve the previous value.

Type 2: Add a New Version

A new dimension row is inserted for each significant change. The old row is closed using an effective end date or current-row indicator. Facts are linked to the dimension version that was valid when the event occurred.

Typical Type 2 columns include:

• Surrogate key
• Business key
• Attribute values
• Effective start date
• Effective end date
• Current-row indicator
• Record creation and update timestamps

A Type 2 dimension permits a report such as “sales by the customer’s region at the time of purchase.” It requires careful loading logic. A change-detection process should compare tracked attributes, expire the old row, and insert the new version. Effective-date boundaries must avoid overlaps and gaps.

Type 3: Add a Previous-Value Column

A current value and a limited previous value are stored in the same row. This supports a narrow comparison, such as current and prior sales territory, but does not preserve an unlimited history.

Choosing an SCD Strategy

The decision should be based on the business question. If management needs historical attribution, Type 2 is usually required. If the requirement is data correction, Type 1 is often better. Different attributes in the same dimension can use different strategies, but this must be documented and tested.

5. OLAP and Analytical Structures

Online Analytical Processing (OLAP) supports multidimensional analysis. A business user may view sales by product, geography, channel, and time, then move from yearly totals to quarterly, monthly, and daily detail.

Important OLAP operations include:

• Roll-up: aggregate to a higher level, such as city to region.
• Drill-down: move to a more detailed level, such as year to month.
• Slice: select one dimension value, such as one fiscal year.
• Dice: select a subcube using multiple conditions.
• Pivot: rotate dimensions to change the presentation.

An OLAP cube may be materialized physically or represented logically through relational tables and semantic models. MOLAP stores multidimensional structures; ROLAP uses relational storage; HOLAP combines approaches. Modern cloud warehouses often implement OLAP behavior through columnar storage, partitions, aggregate tables, caching, and semantic layers rather than traditional proprietary cubes.

A hierarchy must be valid before roll-up is meaningful. If every store belongs to exactly one region, store-to-region aggregation is straightforward. If products can belong to multiple categories, a bridge table or allocation rule may be necessary to avoid double counting.

6. KPI Datasets and Metric Design

A key performance indicator (KPI) is a measurable value tied to a business objective, target, and time frame. A technically correct query can still produce a poor KPI if its definition is vague.

A KPI specification should state:

1. Business meaning.
2. Numerator and denominator.
3. Grain and population.
4. Inclusion and exclusion rules.
5. Time zone and reporting calendar.
6. Currency and unit conventions.
7. Refresh frequency.
8. Target and threshold logic.
9. Owner and approval process.

For example, conversion rate may be defined as:

The formula assumes that sessions and orders are attributed consistently, that the denominator is not zero, and that the observation period and population definitions match. Counting orders divided by users would be a different KPI.

A KPI dataset should generally expose numerator, denominator, result, period, dimensional keys, target, status, and data-quality indicators. Storing only a rounded percentage makes auditing difficult. Percentages should usually be calculated from aggregated components, not averaged across arbitrary subgroups.

For example, the correct overall conversion rate is:

where i indexes reporting groups. This is not necessarily equal to the simple average of group conversion rates because groups may have different denominators.

7. Analytical SQL Patterns

The following examples use broadly portable SQL. Syntax varies among database systems, especially for date functions and materialized views.

Aggregation and Grouping

SELECT
    d.calendar_year,
    p.category,
    SUM(f.net_amount) AS revenue,
    SUM(f.quantity) AS units_sold
FROM fact_sales AS f
JOIN dim_date AS d ON f.date_key = d.date_key
JOIN dim_product AS p ON f.product_key = p.product_key
GROUP BY d.calendar_year, p.category
ORDER BY d.calendar_year, revenue DESC;

Every selected non-aggregated column is included in the GROUP BY clause. The query assumes that the fact table grain and product dimension relationship do not duplicate fact rows.

Conditional Aggregation

SELECT
    d.calendar_month,
    SUM(CASE WHEN p.category = 'Accessories'
             THEN f.net_amount ELSE 0 END) AS accessory_revenue,
    SUM(f.net_amount) AS total_revenue
FROM fact_sales AS f
JOIN dim_date AS d ON f.date_key = d.date_key
JOIN dim_product AS p ON f.product_key = p.product_key
GROUP BY d.calendar_month;

Conditional aggregation is useful for compact KPI datasets, provided that categories are mutually understood and the query handles nulls intentionally.

Window Functions

Window functions calculate across related rows without collapsing them. A twelve-month trend might use:

SELECT
    month_start,
    revenue,
    LAG(revenue, 12) OVER (ORDER BY month_start) AS revenue_prior_year,
    revenue - LAG(revenue, 12) OVER (ORDER BY month_start) AS year_over_year_change
FROM monthly_revenue;

The first twelve months may have no prior-year comparison. A percentage change requires a nonzero prior value:

(revenue - revenue_prior_year) / NULLIF(revenue_prior_year, 0) * 100.0

NULLIF prevents division-by-zero errors. The interpretation of a null result should be defined in the reporting specification.

Ranking

SELECT
    product_key,
    category,
    revenue,
    DENSE_RANK() OVER (
        PARTITION BY category
        ORDER BY revenue DESC
    ) AS category_rank
FROM product_revenue;

DENSE_RANK gives equal values the same rank and does not leave gaps after ties. ROW_NUMBER always produces a unique sequence, while RANK leaves gaps after ties. The choice should match the business meaning.

Common Table Expressions

Common table expressions (CTEs) improve readability and separate stages of logic:

WITH monthly AS (
    SELECT d.month_start, SUM(f.net_amount) AS revenue
    FROM fact_sales AS f
    JOIN dim_date AS d ON f.date_key = d.date_key
    GROUP BY d.month_start
), compared AS (
    SELECT month_start,
           revenue,
           LAG(revenue) OVER (ORDER BY month_start) AS prior_month
    FROM monthly
)
SELECT month_start,
       revenue,
       prior_month,
       revenue - prior_month AS change_amount
FROM compared
ORDER BY month_start;

CTEs improve organization, but they are not automatically materialized. Performance must be tested using the database optimizer and execution plan.

Cohort and Retention Analysis

Cohort analysis groups customers by a defining event, such as first purchase month, and follows behavior over later periods. A reliable implementation requires a clearly defined first-event date, a date spine or calendar table, and an explicit denominator. Retention can mean returning customers, active accounts, revenue retention, or another measure; the label alone is insufficient.

8. Worked Example: Designing a Sales Mart

Assume a retailer asks: “What were monthly net sales and gross margin by region and product category, using the customer’s region at the time of purchase?”

Grain and Measures

The chosen grain is one row per order line. Measures include quantity, gross sales, discount, return amount, cost, and net sales. Gross margin is derived as:

The units are monetary and must share currency and tax conventions. Margin percentage is:

It should be calculated after aggregation when reporting across multiple order lines.

Model

The fact table links to Date, Product, Customer Version, Store, and Promotion dimensions. Customer Version is a Type 2 dimension. When a customer changes region, a new surrogate key is created. A sale on 10 March links to the version valid on 10 March, even if the customer later moves.

Report Query

SELECT
    d.month_start,
    c.region,
    p.category,
    SUM(f.net_sales) AS net_sales,
    SUM(f.cost_amount) AS cost_of_goods_sold,
    SUM(f.net_sales - f.cost_amount) AS gross_margin,
    100.0 * SUM(f.net_sales - f.cost_amount)
          / NULLIF(SUM(f.net_sales), 0) AS gross_margin_pct
FROM fact_sales AS f
JOIN dim_date AS d ON f.date_key = d.date_key
JOIN dim_customer AS c ON f.customer_key = c.customer_key
JOIN dim_product AS p ON f.product_key = p.product_key
GROUP BY d.month_start, c.region, p.category;

The query calculates margin percentage from total sales and total cost. It does not average line-level percentages, which could distort the result. Validation should reconcile total net sales to the approved financial source and check for duplicate dimension matches.

9. Illustrative Case: Northstar Retail

Background

Northstar Retail operates stores and an online channel. Executives receive sales reports from separate systems, and regional totals differ between finance and marketing dashboards.

Problem

Investigation identifies three causes: product categories are maintained differently by channel, customer regions are overwritten in the CRM, and returns are treated inconsistently. Reports therefore use different definitions and cannot reproduce historical results.

Analysis and Solution

The analytics team defines an order-line fact grain and creates conformed Date, Product, Customer, Channel, and Store dimensions. Product category is governed centrally. Customer region uses Type 2 history. Returns are represented using a documented negative-value convention. A KPI layer publishes net sales, units, gross margin, return rate, and conversion rate with numerator and denominator fields.

Data-quality tests check uniqueness of dimension keys, referential integrity, accepted category values, nonnegative quantities where appropriate, valid effective-date ranges, and reconciliation to finance. Dashboard users receive a metric glossary and data lineage information.

Outcome

The organization can compare channels using consistent definitions and can answer both “sales by current region” and “sales by region at time of purchase” by choosing the appropriate dimension logic. Differences are not eliminated by hiding them; they are explained through governed definitions and traceable transformations.

Learning Points

The case demonstrates that warehouse success is primarily a modeling and governance problem, not merely a SQL problem. Grain, history, conformed dimensions, metric definitions, and reconciliation must be designed together.

10. Performance, Quality, and Governance

Performance Practices

Partition large fact tables by a commonly filtered date attribute when supported by the platform. Use columnar storage for analytical workloads, but do not assume that indexes, clustering, or partitioning always improve performance. Examine execution plans. Avoid selecting unused columns, filter early when appropriate, and pre-aggregate stable, frequently requested results. Performance design must preserve correctness; an incorrect aggregate table is worse than a slow query.

Data Quality Controls

Useful controls include completeness, uniqueness, validity, consistency, timeliness, and reconciliation checks. A daily load may compare source and warehouse row counts, monetary totals, maximum event timestamps, and rejected-record counts. Thresholds should be risk-based rather than arbitrary.

Security and Privacy

Warehouse governance must address least-privilege access, role-based permissions, encryption, audit logs, retention, masking, and row-level security. Customer identifiers and sensitive attributes should not be exposed unnecessarily. Regulations such as the GDPR or local privacy laws may require lawful processing, purpose limitation, retention controls, access rights, and deletion procedures. Deletion in a warehouse is complicated when historical facts are needed for legal or financial reporting; policies and legal advice are essential.

AI and Modern Data Platforms

AI assistants can generate SQL, summarize dashboards, and detect anomalies, but generated code requires review. Risks include incorrect joins, leakage of confidential data into prompts, fabricated explanations, and silent changes to metric definitions. A governed semantic layer, approved datasets, metadata, and query testing reduce these risks. AI should assist accountable professionals rather than replace data ownership.

11. Best Practices and Common Failure Modes

Best practices include declaring grain before implementation, separating business keys from surrogate keys, using conformed dimensions, documenting KPI definitions, preserving source lineage, testing historical logic, and reconciling outputs to trusted systems.

Common failures include mixing transaction and snapshot grains, summing non-additive ratios, overwriting history unintentionally, joining dimensions with nonunique keys, treating null as zero without justification, and allowing every dashboard author to define revenue independently. Another failure is building a technically elegant warehouse without understanding decisions users need to make.

Chapter Summary

Data warehouses transform integrated operational data into a historical and analytical resource. Dimensional modeling begins with grain and uses fact tables for measurable events and dimensions for descriptive context. Star schemas emphasize usability; snowflake schemas normalize hierarchies and may reduce redundancy. Surrogate keys and slowly changing dimensions preserve reliable relationships and history.

OLAP supports roll-up, drill-down, slicing, dicing, and pivoting across valid dimensions and hierarchies. KPI datasets must define numerators, denominators, populations, calendars, units, targets, and ownership. Analytical SQL uses aggregation, conditional expressions, window functions, ranking, CTEs, and careful null and zero handling. Performance, data quality, privacy, governance, and reconciliation are essential to trustworthy business intelligence.

Key Terms

Data warehouse: An integrated, historical repository designed for analysis and decision support.

Dimensional modeling: A modeling approach organized around business processes, facts, and dimensions.

Fact table: A table recording measurable events or periodic states.

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

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

Measure: A numeric value recorded or derived for analysis.

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

Semi-additive measure: A measure that can be summed across some dimensions but not all, often not time.

Surrogate key: A warehouse-generated identifier for a dimension or fact row.

Conformed dimension: A dimension shared consistently across multiple facts or subject areas.

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

Snowflake schema: A dimensional schema with normalized dimension structures.

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

Type 1 SCD: A strategy that overwrites a previous dimension value.

Type 2 SCD: A strategy that inserts a new dimension version and preserves history.

OLAP: Online Analytical Processing for multidimensional analysis.

KPI: A defined performance measure linked to an objective and target.

ETL: Extract, transform, and load.

ELT: Extract, load, and transform.

Data lineage: Traceability showing where data originated and how it was transformed.

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 principal purpose of a surrogate key?
4. How does a Type 2 slowly changing dimension preserve history?
5. Name three common OLAP operations.

Long Answer Questions

1. Compare star and snowflake schemas and explain circumstances in which each may be appropriate.
2. Explain how a data warehouse differs from an operational database in purpose, design, and workload.
3. Discuss the design of a KPI dataset, including numerators, denominators, time, ownership, and auditability.
4. Explain why averaging subgroup percentages can produce a misleading overall KPI.
5. Describe a governance framework for protecting sensitive data in an analytical warehouse.

Analytical / Application Questions

1. A fact table contains one row per order line, but shipping cost is repeated on every line. Explain the risk and propose two modeling or allocation solutions.
2. A customer changes region on 1 July. Design the Type 2 dimension records and explain which record should be used for purchases made before and after that date.
3. Write an analytical SQL query that calculates monthly revenue, prior-month revenue, and month-over-month change while preventing division-by-zero errors.

Multiple Choice Questions with Answers

Question 1

What does the grain of a fact table define?

A. The database server size

B. The meaning of one row in the fact table

C. The number of dimensions in a schema

D. The frequency of dashboard refreshes

Correct Answer: B. The meaning of one row in the fact table

Question 2

Which measure is generally semi-additive?

A. Order-line quantity

B. Gross sales

C. Account balance

D. Number of returned units

Correct Answer: C. Account balance

Question 3

What is the defining feature of a Type 2 slowly changing dimension?

A. It permanently rejects attribute changes

B. It overwrites the old value

C. It stores a new version when a tracked value changes

D. It stores only the previous value

Correct Answer: C. It stores a new version when a tracked value changes

Question 4

Which schema usually places denormalized dimensions directly around a central fact table?

A. Star schema

B. Network schema

C. Document schema

D. Snowflake-only schema

Correct Answer: A. Star schema

Question 5

Which SQL function is commonly used to access the previous row’s value in an ordered window?

A. SUM

B. LAG

C. COUNT

D. COALESCE

Correct Answer: B. LAG

Question 6

Why is NULLIF useful in KPI calculations?

A. It converts every null to zero

B. It prevents division by zero when used as a denominator guard

C. It sorts rows alphabetically

D. It removes duplicate dimension rows

Correct Answer: B. It prevents division by zero when used as a denominator guard

Question 7

Which OLAP operation moves from yearly totals to monthly detail?

A. Roll-up

B. Drill-down

C. Slice

D. Pivot

Correct Answer: B. Drill-down

Question 8

A KPI is calculated for three groups with orders of 10, 20, and 30 and sessions of 100, 100, and 300. What is the overall conversion rate?

A. 20.0%

B. 25.0%

C. 15.0%

D. 30.0%

Correct Answer: A. 20.0%

Question 9

What is a major purpose of a conformed dimension?

A. To ensure consistent analysis across fact tables

B. To eliminate all fact measures

C. To store only current transactions

D. To prevent historical reporting

Correct Answer: A. To ensure consistent analysis across fact tables

Question 10

Which practice best protects the correctness of a gross margin percentage across groups?

A. Average all group percentages without weights

B. Sum the percentages

C. Calculate total margin divided by total sales

D. Use the largest group’s percentage

Correct Answer: C. Calculate total margin divided by total sales