Data Warehousing and Data Mining

Learning Objectives

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

• Distinguish OLTP systems from OLAP systems and explain why the same database design is bad at both jobs.
• Explain the four defining characteristics of a data warehouse (subject-oriented, integrated, time-variant, non-volatile) with a concrete example of each.
• Walk through an ETL pipeline (Extract, Transform, Load) for a realistic business scenario.
• Design a star schema with a fact table and multiple dimension tables, and write SQL queries against it.
• Compare star and snowflake schemas and justify when each is preferable.
• Perform and explain the five core OLAP operations: roll-up, drill-down, slice, dice, and pivot.
• Compute support, confidence, and lift for an association rule from a market-basket dataset.
• Describe the KDD process and where data mining techniques like classification and clustering fit within it.

Quick Answer

A data warehouse is a large, centralized, read-optimized repository built specifically for analysis and reporting, separate from the operational databases that run day-to-day transactions. Data flows into it through ETL (Extract, Transform, Load) pipelines that pull from multiple operational sources, clean and reshape the data, and load it into schemas — usually star or snowflake — designed for fast aggregation rather than fast single-row updates. Once the data is in the warehouse, OLAP tools let analysts roll up, drill down, slice, dice, and pivot across dimensions like time, product, and region. Data mining goes a step further: instead of just answering questions analysts already know to ask, it applies algorithms (association rules, classification, clustering) to surface patterns — like "customers who buy diapers also buy beer" — that no one thought to query for directly.

OLTP vs. OLAP: Two Different Jobs for a Database

Every organization runs two very different kinds of workload against its data, and conflating them is the single biggest reason naive "just query the production database for reports" setups fall apart.

OLTP (Online Transaction Processing) is what runs your operational systems: a customer places an order, a bank processes a transfer, a hospital updates a patient chart. These are short, frequent transactions that touch a small number of rows, need to complete in milliseconds, and must be highly normalized so that updating one fact (say, a customer's address) doesn't require touching duplicated copies scattered across many tables.

OLAP (Online Analytical Processing) is what runs your reporting and analysis: "What were total sales by region for each quarter of the last three years?" These are long-running queries that scan and aggregate millions of rows, often joining across many dimensions, and they benefit from a denormalized structure because joins are expensive at that scale.

Run analytical queries directly against an OLTP production database and two things go wrong: the heavy scans slow down or lock tables that live customers are actively using, and the normalized schema forces the analytical query into dozens of joins it doesn't need. A data warehouse exists precisely to separate these workloads — copy the data out, reshape it for analysis, and let analysts hammer it without ever touching the system that's taking real orders.

Aspect	OLTP	OLAP
Purpose	Run day-to-day operations	Support analysis and decision-making
Data	Current, detailed, frequently updated	Historical, aggregated, mostly read-only
Schema design	Highly normalized (3NF) to avoid update anomalies	Denormalized (star/snowflake) to speed up aggregation
Query pattern	Short transactions touching few rows	Complex queries scanning/aggregating millions of rows
Users	Clerks, customers, front-line applications	Analysts, executives, BI tools
Response time expectation	Milliseconds	Seconds to minutes is acceptable
Example system	A bank's core transaction database	A retailer's sales data warehouse

What Makes a Data Warehouse a Data Warehouse

Bill Inmon's classic definition names four properties that distinguish a warehouse from an ordinary database, and each one solves a specific problem that OLTP systems aren't built for.

• Subject-oriented. A warehouse is organized around business subjects — sales, customers, inventory — rather than around the applications that generate the data. An OLTP order-processing system stores data shaped by how the checkout application needs it; a warehouse reorganizes the same facts around the question "how are sales performing?", pulling in whatever fields matter for that subject regardless of which source application they came from.
• Integrated. Data arrives from multiple, inconsistent operational systems — one uses M/F for gender, another uses 1/0; one stores dates as MM/DD/YYYY, another as Unix timestamps. The warehouse resolves these inconsistencies during loading so that "customer gender" means the same thing everywhere inside the warehouse, no matter which source system it originally came from.
• Time-variant. An OLTP system typically only cares about the current state — a customer's current address, a product's current price. A warehouse deliberately keeps history: every price a product ever had, tagged with the date range it was valid, so you can ask "what was our average order value in Q2 two years ago?" and get a real answer instead of today's numbers applied retroactively.
• Non-volatile. Once a batch of data is loaded into the warehouse, it isn't updated in place the way an OLTP row is (UPDATE orders SET status = 'shipped'). Instead, new data is appended as new records, and old data is preserved. This is what makes historical reporting trustworthy — a report you ran last month still reproduces the same numbers if you ran it again today, because nobody silently overwrote yesterday's rows.

Together these four properties explain why you can't just point a BI tool at your production OLTP database and call it a data warehouse — the underlying data model is solving a fundamentally different problem.

The ETL Process: Extract, Transform, Load

ETL is the pipeline that actually builds and refreshes the warehouse. It runs on a schedule (nightly is common, though many modern warehouses now stream near-real-time) and moves data from scattered operational sources into the warehouse's analysis-ready schema.

Walk through a concrete example: a retail chain wants nightly sales figures in its warehouse.

1. Extract. Pull yesterday's transactions from the point-of-sale (POS) system, new orders from the e-commerce database, and updated customer records from the loyalty-program CSV export. Each source has its own format, naming conventions, and quirks.
2. Transform. This is where most of the real work happens:
   • Cleaning: drop duplicate transaction records, fix obviously invalid values (a sale with a negative quantity).
   • Standardizing: convert every timestamp to UTC, unify "CA" and "California" into one value, map different source-system product codes onto one master product ID.
   • Deriving: compute total_amount = quantity * unit_price - discount, or bucket transaction times into morning/afternoon/evening.
   • Conforming to dimensions: look up or create the correct product_key, customer_key, store_key, and date_key so the transformed row can be linked into the warehouse's dimension tables.
3. Load. Insert the cleaned, standardized rows into the fact table (e.g., fact_sales), and update or insert into dimension tables (e.g., a new loyalty member becomes a new row in dim_customer). Loads are usually done in bulk, off-peak hours, since they can be I/O-heavy.

A key practical detail: transformation is usually the most expensive and error-prone step, because it's where business rules and messy real-world data collide — deciding, for instance, what to do with a sale record that references a product ID no longer in the catalog.

Data Warehouse Schemas: Star and Snowflake

Warehouses are modeled very differently from OLTP databases. Instead of normalizing everything into many small tables, warehouse schemas center on one large fact table surrounded by smaller dimension tables — a structure optimized for the kind of "aggregate by category" queries analysts run.

Star Schema

A fact table holds the measurable, numeric events you want to analyze (a sale, a click, a claim) plus foreign keys pointing to the dimensions that describe who, what, where, and when about that event. Dimension tables hold the descriptive attributes, and in a star schema they are deliberately denormalized — flattened into one table each, even if that duplicates some data.

CREATE TABLE dim_product (
    product_key   INT PRIMARY KEY,
    product_name  VARCHAR(100),
    category      VARCHAR(50),
    brand         VARCHAR(50)
);

CREATE TABLE dim_time (
    time_key   INT PRIMARY KEY,
    full_date  DATE,
    quarter    INT,
    year       INT
);

CREATE TABLE fact_sales (
    sale_id      INT PRIMARY KEY,
    product_key  INT REFERENCES dim_product(product_key),
    customer_key INT REFERENCES dim_customer(customer_key),
    store_key    INT REFERENCES dim_store(store_key),
    time_key     INT REFERENCES dim_time(time_key),
    quantity     INT,
    total_amount DECIMAL(10,2)
);

-- Total sales by product category per quarter
SELECT p.category, t.quarter, t.year, SUM(f.total_amount) AS revenue
FROM fact_sales f
JOIN dim_product p ON f.product_key = p.product_key
JOIN dim_time t    ON f.time_key = t.time_key
GROUP BY p.category, t.quarter, t.year
ORDER BY t.year, t.quarter;

Notice dim_product repeats category and brand on every row for products in the same category — that's the denormalization. It's a deliberate trade-off: the query above needs only one join per dimension instead of chasing category through a separate lookup table, which matters when a fact table has hundreds of millions of rows.

Snowflake Schema

A snowflake schema normalizes the dimension tables further, splitting them into related sub-tables. dim_product might split into dim_product (name, brand) and a separate dim_category (category name, department) linked by a category_key.

CREATE TABLE dim_category (
    category_key INT PRIMARY KEY,
    category_name VARCHAR(50),
    department    VARCHAR(50)
);

CREATE TABLE dim_product (
    product_key   INT PRIMARY KEY,
    product_name  VARCHAR(100),
    brand         VARCHAR(50),
    category_key  INT REFERENCES dim_category(category_key)
);

-- Same report now needs an extra join
SELECT c.category_name, t.quarter, t.year, SUM(f.total_amount) AS revenue
FROM fact_sales f
JOIN dim_product p  ON f.product_key = p.product_key
JOIN dim_category c ON p.category_key = c.category_key
JOIN dim_time t      ON f.time_key = t.time_key
GROUP BY c.category_name, t.quarter, t.year;

The trade-off is direct: snowflaking reduces redundancy and storage, and makes updating a category name a one-row change instead of a mass update — but every extra normalized table is another join at query time, which is exactly the cost warehouses are trying to avoid. Most production warehouses default to star schemas and only snowflake a dimension when it's genuinely large or frequently changing (like a deep product-category hierarchy), not by default.

OLAP Operations: Navigating the Data Cube

Warehouse data is conceptually a cube — measures (like sales revenue) sliced along multiple dimensions (product, time, region). OLAP tools let analysts manipulate that cube interactively.

• Roll-up: aggregate data by climbing up a dimension's hierarchy. Going from "sales by city" to "sales by state" to "sales by country" is a roll-up — you're trading detail for a broader view.
• Drill-down: the reverse — descend into more detail. From "sales by quarter" to "sales by month" to "sales by day" is a drill-down.
• Slice: fix one dimension to a single value and look at the resulting sub-cube. "Show me sales for Q1 2025 only, across all products and regions" slices the time dimension down to one value.
• Dice: select a sub-cube by restricting multiple dimensions to ranges or specific values at once. "Show me sales for the Electronics and Furniture categories, in the West and South regions, for 2024 and 2025" dices along product, region, and time simultaneously.
• Pivot (rotate): reorient the cube to view it from a different angle — swapping which dimension appears in rows vs. columns of a report, e.g. turning a "region x quarter" table into a "quarter x region" table without recomputing any numbers.

A concrete walkthrough: start with a report of total revenue by region x quarter x product-category. Roll up over quarter to get region x year x category (less time granularity). Slice by fixing year = 2025 to get region x category for just that year. Dice by restricting region IN ('West','East') and category IN ('Electronics'). Pivot to flip rows and columns so categories run down the side and regions run across the top. None of this requires rewriting the underlying fact table — it's all interactive reshaping of the same cube of pre-aggregated numbers, which is why OLAP tools respond in seconds even over huge datasets.

Data Mining: Finding Patterns Nobody Asked For

Where OLAP answers questions analysts already know to ask ("what were sales by region?"), data mining applies algorithms to surface patterns the analyst didn't think to look for. It sits inside a broader process called KDD (Knowledge Discovery in Databases): selection → preprocessing → transformation → data mining → interpretation/evaluation. Data mining is just one stage of KDD — the algorithmic pattern-extraction step — sandwiched between data preparation and turning raw patterns into actionable knowledge.

Association Rule Mining (Market Basket Analysis)

The classic technique: find items that tend to be purchased together. Given a rule "if a customer buys A, they also buy B" (written A → B), three numbers decide whether the rule is useful.

Suppose a store has 1,000 transactions. 200 of them contain bread, 150 contain butter, and 120 contain both bread and butter.

• Support = fraction of all transactions containing both items = 120 / 1000 = 0.12 (12%). It measures how frequently this combination occurs at all — a rule with tiny support might just be noise.
• Confidence = P(butter | bread) = (transactions with both) / (transactions with bread) = 120 / 200 = 0.60 (60%). It measures how reliable the rule is once you know the customer bought bread.
• Lift = confidence / P(butter alone) = 0.60 / (150/1000) = 0.60 / 0.15 = 4.0. Lift compares the rule's confidence against butter's baseline popularity. A lift of 4 means customers who buy bread are four times more likely to buy butter than a random customer — a lift near 1 would mean the two items are essentially independent, and a rule like that isn't actionable even if support and confidence look decent.

This is the algorithm behind the famous (partly apocryphal, but pedagogically standard) "diapers and beer" story — a pattern with high enough support, confidence, and lift to be worth acting on (e.g., placing the two items near each other, or in a bundle promotion), even though no analyst would have thought to test that specific pair manually.

Classification

Classification assigns records to predefined categories using a model trained on labeled historical data — e.g., a decision tree trained on past loan applications (income, credit history, outcome: default / repaid) predicts whether a new applicant will default. It answers "which known category does this new record belong to?"

Clustering

Clustering groups records by similarity without predefined labels — e.g., grouping customers into segments based on purchase frequency and average spend, without knowing in advance what those segments should be called. It's used for exploratory discovery (find natural groupings) rather than prediction against a known target, which is the key difference from classification.

Key Terms

Term	Definition
OLTP	Online Transaction Processing — normalized databases optimized for fast, frequent, small transactions.
OLAP	Online Analytical Processing — denormalized structures optimized for complex aggregation queries over large volumes.
ETL	Extract, Transform, Load — the pipeline that moves data from operational sources into a data warehouse.
Fact table	The central warehouse table holding numeric measures and foreign keys to dimension tables.
Dimension table	A table holding descriptive attributes (product, customer, time, store) that a fact table's measures can be grouped by.
Star schema	A warehouse schema with one central fact table and denormalized dimension tables directly linked to it.
Snowflake schema	A warehouse schema where dimension tables are further normalized into sub-tables.
Roll-up / Drill-down	OLAP operations that aggregate up a dimension hierarchy (roll-up) or descend into more detail (drill-down).
Slice / Dice	OLAP operations that fix one dimension to a value (slice) or restrict multiple dimensions at once (dice).
Support	The fraction of all transactions containing a given item combination.
Confidence	The conditional probability that the consequent item is bought given the antecedent item is bought.
Lift	How much more likely the consequent is, given the antecedent, compared to its baseline frequency.
KDD	Knowledge Discovery in Databases — the overall process (selection, preprocessing, transformation, mining, interpretation) that data mining is one stage of.

Common Mistakes

Misconception 1: "A data warehouse is just a bigger database, so you can design it the same way you'd design an OLTP database." Why it's wrong: Applying 3NF normalization to a warehouse produces a schema with dozens of small tables, which forces analytical queries into large numbers of joins — exactly the cost a warehouse is supposed to avoid. Correct understanding: Warehouses are deliberately denormalized (star/snowflake) because their workload is read-heavy aggregation across huge row counts, not small, frequent single-row updates. Different workload, different design goals.

Misconception 2: "High confidence alone means an association rule is useful." Why it's wrong: A rule can have high confidence just because the consequent item is popular overall, not because the antecedent actually drives it. For example, if 90% of all transactions contain milk, then almost any X → milk rule will show high confidence purely by coincidence. Correct understanding: Lift corrects for this by comparing the rule's confidence to the consequent's baseline frequency. A rule needs adequate support (it happens often enough to matter), reasonable confidence (it's reliable), and lift meaningfully above 1 (the association is real, not just background popularity) to be actionable.

Misconception 3: "OLAP and data mining are the same thing, just different names for 'analyzing the warehouse.'" Why it's wrong: OLAP is about answering specific questions an analyst poses interactively (roll-up, drill-down, slice, dice on a known cube); data mining is about algorithmically discovering patterns the analyst never explicitly asked about. Correct understanding: OLAP is query-driven and exploratory-but-directed; data mining is pattern-driven and can surface associations, clusters, or classifications nobody thought to look for in advance. Both typically run against the same warehouse, but they're distinct capabilities.

Comparison and Connections

OLTP vs. OLAP (Recap)

Aspect	OLTP	OLAP
Schema	Normalized (3NF)	Denormalized (star/snowflake)
Typical query	UPDATE order SET status = 'shipped' WHERE id = 5021	SUM(revenue) GROUP BY region, quarter
Data freshness	Real-time, current state only	Periodically refreshed, includes history
Optimized for	Write throughput, low latency per transaction	Read throughput over large scans

Star Schema vs. Snowflake Schema

Aspect	Star Schema	Snowflake Schema
Dimension tables	Denormalized (flat, some redundancy)	Normalized (split into sub-tables)
Query complexity	Fewer joins, simpler and faster queries	More joins, slightly slower queries
Storage	Slightly more (redundant attribute values)	Slightly less (redundancy removed)
Maintainability	Updating a shared attribute means updating many rows	Updating a shared attribute is a single-row change
When to prefer	Default choice; dimensions are small/medium and query speed matters most	A dimension is very large or its hierarchy changes often

Practice Questions

Recall

1. What are the four defining characteristics of a data warehouse? Answer guidance: Subject-oriented, integrated, time-variant, and non-volatile.
2. Name the three steps of the ETL process and what each one does. Answer guidance: Extract (pull data from source systems), Transform (clean, standardize, and reshape it), Load (insert it into the warehouse's fact and dimension tables).

Understanding

3. Explain why a star schema's dimension tables are denormalized on purpose, even though this contradicts what you'd do in an OLTP schema design. Answer guidance: Warehouse queries are read-heavy aggregations over huge fact tables; denormalized dimensions reduce the number of joins needed per query, trading some storage redundancy and update cost for much faster analytical reads — the opposite priority from an OLTP system's frequent small writes.
4. Why can lift be more informative than confidence alone when evaluating an association rule? Answer guidance: Confidence only measures P(consequent | antecedent), which can be high just because the consequent is popular overall; lift divides that by the consequent's baseline frequency, revealing whether the antecedent actually increases the likelihood of the consequent or the high confidence is just background noise.

Application

5. A retailer has 500 transactions. 80 contain chips, 60 contain salsa, and 40 contain both. Compute the support, confidence, and lift for the rule "chips → salsa." Answer guidance: Support = 40/500 = 0.08 (8%). Confidence = 40/80 = 0.50 (50%). Lift = confidence / P(salsa) = 0.50 / (60/500) = 0.50 / 0.12 ≈ 4.17 — a strong positive association.
6. Design a star schema (list the fact table's measures/foreign keys and at least three dimension tables) for a movie theater chain tracking ticket sales. Answer guidance: fact_ticket_sales with ticket_id, movie_key, theater_key, time_key, customer_key, tickets_sold, revenue; dimension tables dim_movie (title, genre, rating), dim_theater (name, city, screens), dim_time (date, quarter, year), optionally dim_customer (loyalty tier).

Analysis

7. A company's BI team complains that their nightly sales dashboard queries against the production order-processing database are timing out and slowing down checkout for live customers. Diagnose the problem and propose a fix using concepts from this page. Answer guidance: They are running OLAP-style aggregation queries directly against an OLTP system, whose normalized schema and write-optimized design isn't built for large scans, and the load itself competes with live transactions. The fix is to build a separate data warehouse: ETL the relevant data out nightly into a star schema, and point the dashboard at the warehouse instead of production.
8. A grocery chain finds that "bread → butter" has support 0.12, confidence 0.60, and lift 4.0, while "bread → milk" has support 0.30, confidence 0.55, and lift 1.05. Which rule is more actionable for a promotional bundle, and why? Answer guidance: "Bread → butter" is more actionable despite lower support and confidence, because its lift of 4.0 shows a genuine, strong association beyond baseline popularity, while "bread → milk"'s lift near 1 suggests milk is bought with bread about as often as it's bought with anything else — the high confidence there is mostly explained by milk's general popularity, not a real link to bread.

FAQ

Q: Why can't we just run analytical reports directly on the production database instead of building a separate warehouse? A: Because analytical queries scan and aggregate large amounts of data, which is slow against a normalized OLTP schema and can lock or slow down tables that live transactions depend on. A warehouse copies and reshapes the data specifically so heavy analytical queries never compete with operational traffic.

Q: Is ELT (Extract, Load, Transform) different from ETL? A: Yes — ELT loads raw data into the warehouse first and transforms it afterward using the warehouse's own compute power, which has become popular with cloud warehouses (Snowflake, BigQuery) that have cheap, scalable processing. The underlying goals — clean, standardized, analysis-ready data — are the same; only the order and where the transformation happens differ.

Q: How is a data mart different from a data warehouse? A: A data warehouse is enterprise-wide, spanning all subjects an organization cares about. A data mart is a smaller, department- or subject-specific subset (e.g., a "sales data mart") often derived from the larger warehouse, built for a narrower audience with faster, more focused access.

Q: Do OLAP cubes have to be physically pre-computed structures, or can they be virtual? A: Both exist. MOLAP (Multidimensional OLAP) pre-computes and stores cube aggregates for very fast responses but higher storage cost. ROLAP (Relational OLAP) computes aggregates on the fly from relational star-schema tables, trading some query speed for flexibility and lower storage overhead. HOLAP hybrids combine both.

Q: Why does support matter if a rule already has high confidence and high lift? A: A rule can have high confidence and high lift purely by chance if it's based on very few transactions — 2 out of 3 transactions containing both items gives confidence 0.67, but that's not statistically meaningful. Support ensures the rule reflects a pattern that actually occurs often enough in the data to be worth acting on.

Q: Is classification supervised or unsupervised, and how does that differ from clustering? A: Classification is supervised — it trains on historical data where the correct category (label) is already known, then predicts that label for new records. Clustering is unsupervised — there are no predefined labels; the algorithm discovers groupings based purely on similarity in the data.

Quick Revision

• OLTP = fast, normalized, frequent small transactions; OLAP = read-heavy aggregation over denormalized, historical data. Don't run OLAP queries against an OLTP schema.
• A data warehouse is subject-oriented, integrated, time-variant, and non-volatile — each property solves a specific problem OLTP systems don't address.
• ETL = Extract (pull from sources) → Transform (clean, standardize, derive, conform to dimensions) → Load (insert into fact/dimension tables).
• Star schema: one fact table + denormalized dimension tables, fewer joins, faster queries, some redundancy.
• Snowflake schema: dimensions further normalized into sub-tables, less redundancy, more joins, easier to maintain shared attributes.
• Fact tables hold measures + foreign keys; dimension tables hold descriptive attributes (product, customer, time, store).
• OLAP operations: roll-up (aggregate up a hierarchy), drill-down (descend into detail), slice (fix one dimension), dice (restrict multiple dimensions), pivot (reorient rows/columns).
• Support = frequency of the item combination; confidence = P(consequent | antecedent); lift = confidence divided by baseline frequency of the consequent.
• Lift near 1 means no real association even if confidence looks high — always check lift, not just confidence.
• Classification predicts a known label from labeled training data; clustering discovers unlabeled groupings by similarity.
• Data mining is one stage inside the broader KDD process: selection → preprocessing → transformation → mining → interpretation.
• MOLAP pre-computes cubes for speed; ROLAP computes on the fly from relational tables for flexibility; HOLAP mixes both.

Related Topics

Prerequisites

• Relational Database Model (tables, keys, joins)
• Basic SQL (SELECT, GROUP BY, JOIN)
• Introduction to DBMS (schemas, data independence)

Related Topics

• Normalization and Normal Forms
• Big Data and NoSQL Systems
• Business Intelligence and Reporting Tools

Next Topics

• Machine Learning Fundamentals (classification and clustering in depth)
• Distributed and Cloud Data Warehouses (Snowflake, BigQuery, Redshift)
• Data Governance and Data Quality