BookNest's Star Schema

Designing BookNest's Sales Star Schema

The four steps give the star of The Star Schema: the process is order taking, the grain one order line, the dimensions date, book, customer and order profile, and the facts qty, gross_amount and unit_price. The load swaps each natural key for its surrogate with a join and computes the date key as dim_date does. Foreign keys document the star and reject a fact without a dimension row:

497-fact.sql: loading mart.fact_sales and asking a star questionSQL

DROP TABLE IF EXISTS mart.fact_sales;
CREATE TABLE mart.fact_sales (
  date_key int NOT NULL REFERENCES mart.dim_date,
  book_key int NOT NULL REFERENCES mart.dim_book,
  customer_key int NOT NULL REFERENCES mart.dim_customer,
  profile_key int NOT NULL REFERENCES mart.dim_order_profile,
  order_id int, line_no smallint, PRIMARY KEY (order_id, line_no),     -- degenerate
  qty smallint, unit_price numeric(6,2), gross_amount numeric(10,2));  -- the facts
INSERT INTO mart.fact_sales
SELECT to_char(o.order_ts AT TIME ZONE 'UTC', 'YYYYMMDD')::int, b.book_key, c.customer_key,
       p.profile_key, i.order_id, i.line_no, i.qty, i.unit_price, i.qty * i.unit_price
FROM order_items i JOIN orders o USING (order_id) JOIN mart.dim_book b USING (book_id)
JOIN mart.dim_customer c USING (customer_id)
JOIN mart.dim_order_profile p
  ON (p.channel, p.status, p.coupon) = (o.channel, o.status, coalesce(o.coupon, 'none'));
ANALYZE mart.fact_sales;
SELECT c.region, b.genre_group, sum(f.gross_amount) AS gross_2026_q2
FROM mart.fact_sales f JOIN mart.dim_date d USING (date_key)
JOIN mart.dim_customer c USING (customer_key) JOIN mart.dim_book b USING (book_key)
JOIN mart.dim_order_profile p USING (profile_key)
WHERE p.status <> 'cancelled' AND d.year = 2026 AND d.quarter = 2
GROUP BY 1, 2 ORDER BY 3 DESC LIMIT 3;
SELECT count(*) AS fact_rows, sum(gross_amount) AS gross,
       (SELECT sum(gross_amount) FROM mart.sales) AS mart_sales_gross FROM mart.fact_sales;

Output

  region  |  genre_group  | gross_2026_q2
----------+---------------+---------------
 Americas | Fiction & Lit |      92965.00
 Americas | Nonfiction    |      91305.25
 Americas | Practical     |      75629.40
 fact_rows |   gross    | mart_sales_gross
-----------+------------+------------------
    137944 | 3509959.19 |       3509959.19

The last query is the reconciliation every fact load needs: the same rows and total as the source. A customer who moves country would now rewrite history in dim_customer; Slowly Changing Dimensions fixes that.

MENU