Skip to content
Tutorials Center
My learning

Topics

Lesson 35 of 36 ยท Projects and Practice

Project: Sales Data Warehouse

Design a star-schema sales warehouse in Hive: dimension and fact tables, partitioned ORC loading, rollup reports, a type 2 slowly changing dimension and optimization checks.

Warehouses are where Hive earns its keep. In this project you model an online shop as a star schema: one central fact table of sales surrounded by small dimension tables. You will build it from the operational tables, run reports across it, and handle a changing customer address with a slowly changing dimension.

The Hive DDL is read-only, since Hive cannot run here. Every query in an editor runs on the shop sample database (customers, products, orders, order_items) in SQLite; the editors build the same star schema and say where HiveQL syntax differs.

The design

flowchart TD
    F["fact_sales: order_id, date_key, customer_key, product_key, quantity, revenue"]
    D1["dim_date: date_key, year, month, weekday"]
    D2["dim_customer: customer_key, name, city, country"]
    D3["dim_product: product_key, name, category"]
    F --> D1
    F --> D2
    F --> D3

Facts hold numbers you add up; dimensions hold the descriptions you group and filter by.

Design rules:

  • Grain first. One row of fact_sales is one order line (one product on one order). Everything else follows from that sentence.
  • Facts are additive numbers (quantity, revenue) plus keys to dimensions.
  • Dimensions are small and descriptive, so Hive broadcasts them in map joins (map joins).
  • Surrogate keys (customer_key) are generated integers, independent of source system ids, which is what makes history tracking possible.

Step 1: the Hive tables (read-only)

SQL
CREATE DATABASE IF NOT EXISTS dw;
USE dw;

CREATE TABLE dim_product (
  product_key BIGINT, product_id BIGINT, name STRING, category STRING
) STORED AS ORC;

CREATE TABLE dim_customer (
  customer_key BIGINT, customer_id BIGINT, name STRING, city STRING, country STRING,
  valid_from DATE, valid_to DATE, is_current BOOLEAN
) STORED AS ORC;

CREATE TABLE dim_date (
  date_key INT, full_date DATE, year INT, month INT, day INT, weekday STRING
) STORED AS ORC;

CREATE TABLE fact_sales (
  order_id BIGINT, customer_key BIGINT, product_key BIGINT,
  quantity INT, revenue DECIMAL(12,2)
)
PARTITIONED BY (date_key INT)
STORED AS ORC
TBLPROPERTIES ('orc.compress' = 'ZSTD');

The fact table is partitioned by date (monthly or daily depending on volume) so reports for one period read one partition. Dimension tables stay unpartitioned: they are small.

Step 2: build dimensions and facts

Load the dimensions from the operational data, then join the line items to the dimensions to get keys. In SQLite:

SQL Build the star schema ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

In Hive the same step is an INSERT OVERWRITE TABLE fact_sales PARTITION (date_key) SELECT ... with dynamic partitioning (see the tuning checklist). Excluding cancelled orders is a business rule: write such rules down, because they decide whether finance agrees with your numbers.

Step 3: reports

Every star query has the same shape: fact join dimensions, filter, group, aggregate.

SQL Revenue by category and country ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Subtotals with ROLLUP (Hive, read-only)

Hive supports GROUPING SETS, ROLLUP and CUBE, which produce subtotals and grand totals in one pass:

SQL
SELECT p.category, c.country, SUM(f.revenue) AS revenue
FROM fact_sales f
JOIN dim_product p  ON p.product_key = f.product_key
JOIN dim_customer c ON c.customer_key = f.customer_key
GROUP BY p.category, c.country WITH ROLLUP;     -- also GROUP BY ROLLUP(p.category, c.country)

-- GROUPING__ID (or grouping(col)) tells subtotal rows from real rows

SQLite has no ROLLUP, so the equivalent is a UNION ALL of three groupings:

SQL ROLLUP written by hand (SQLite stand-in)

Press Run (or Ctrl + Enter) to see the result here.

The NULL columns mark subtotal and grand-total rows.

Step 4: slowly changing dimension (type 2)

Customers move. If you simply overwrite the city, old sales would suddenly appear under the new city. Type 2 keeps history: close the old row (set valid_to, is_current = false) and insert a new row with a new surrogate key. New facts use the new key; old facts keep pointing at the old one.

In Hive 3 and later, one MERGE on an ACID dimension table does most of it (the dimension must be transactional, see MERGE and compaction):

SQL
-- 1. close changed rows
MERGE INTO dim_customer AS t
USING staging_customer AS s
ON t.customer_id = s.customer_id AND t.is_current = true
WHEN MATCHED AND (t.city <> s.city OR t.country <> s.country) THEN
  UPDATE SET valid_to = current_date, is_current = false;

-- 2. insert new current versions (changed and brand-new customers)
INSERT INTO dim_customer
SELECT row_number() OVER () + (SELECT max(customer_key) FROM dim_customer),
       s.customer_id, s.name, s.city, s.country, current_date, DATE '9999-12-31', true
FROM staging_customer s
LEFT JOIN dim_customer t ON t.customer_id = s.customer_id AND t.is_current = true
WHERE t.customer_id IS NULL;

After step 1, a changed customer has no current row, so step 2's anti-join inserts the new version. Try the idea:

SQL SCD type 2 in SQLite

Press Run (or Ctrl + Enter) to see the result here.

Customer 100 now has two rows (Berlin until the change, Hamburg after) and customer 200 is new. Queries on current data filter WHERE is_current = true; historical reports join on the key stored in the fact table.

Step 5: check performance

Before calling the warehouse done, verify it behaves:

  1. ANALYZE TABLE ... COMPUTE STATISTICS FOR COLUMNS on all tables.
  2. EXPLAIN a category-by-country report: the dimension tables should be map-joined (BROADCAST_EDGE), and the fact scan should list only the partitions you asked for (EXPLAIN DEPENDENCY).
  3. Check file counts per partition; if you have many small files, compact or concatenate.
  4. Consider bucketing the fact table on customer_key if you often join it with a large customer dimension.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What defines the grain of a fact table?

    Choosing the grain first makes clear which facts and dimensions the table needs.
  2. 2. Why are dimension tables good candidates for map joins?

    Small dimensions fit in memory, so the large fact table is scanned without a shuffle.
  3. 3. What does a type 2 slowly changing dimension do when an attribute changes?

    History is preserved: old facts keep the old key, new facts use the new one.
  4. 4. What does ROLLUP produce?

    ROLLUP groups by the listed columns, then successively removes the last column to give subtotals.

Summary

  • A star schema has a central fact table at a clear grain and small descriptive dimension tables with surrogate keys.
  • Partition the fact table by date, keep dimensions unpartitioned, and let Hive broadcast the dimensions.
  • Reports are fact-join-dimensions queries; ROLLUP/GROUPING SETS add subtotals.
  • Type 2 dimensions keep history with valid_from, valid_to and an is_current flag, implemented with MERGE in Hive.
  • Finish by checking statistics, plans, partitions and file counts.

Next: Hive Practice Questions and Interview Prep