Dimension Tables

DROP TABLE IF EXISTS mart.fact_sales, mart.dim_book, mart.dim_customer, mart.dim_order_profile;
CREATE TABLE mart.dim_book (
  book_key int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,           -- surrogate key
  book_id int NOT NULL UNIQUE, title text, author text,             -- natural key
  genre text, genre_group text, list_price numeric(6,2), length_band text);
INSERT INTO mart.dim_book (book_id, title, author, genre, genre_group, list_price, length_band)
SELECT b.book_id, b.title, b.author, b.genre, g.parent, b.price,
       CASE WHEN b.pages < 250 THEN 'short' WHEN b.pages < 400 THEN 'medium' ELSE 'long' END
FROM books b JOIN mart.genre_tree g ON g.node = b.genre;
CREATE TABLE mart.dim_customer (
  customer_key int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id int NOT NULL UNIQUE, name text, country char(2), region text, signup_date date);
INSERT INTO mart.dim_customer (customer_id, name, country, region, signup_date)
SELECT customer_id, name, country, CASE WHEN country IN ('US', 'CA') THEN 'Americas'
       WHEN country IN ('GB', 'DE') THEN 'Europe' ELSE 'Asia-Pacific' END, signup_date
FROM customers ORDER BY customer_id;
CREATE TABLE mart.dim_order_profile (               -- junk dimension: low-cardinality flags
  profile_key int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  channel text, status text, coupon text, UNIQUE (channel, status, coupon));
INSERT INTO mart.dim_order_profile (channel, status, coupon)
SELECT DISTINCT channel, status, coalesce(coupon, 'none') FROM orders ORDER BY 1, 2, 3;
SELECT book_key, book_id, genre, genre_group, length_band FROM mart.dim_book ORDER BY 1;

 book_key | book_id |      genre      |  genre_group  | length_band
----------+---------+-----------------+---------------+-------------
        1 |       1 | Fiction         | Fiction & Lit | medium
        2 |       5 | Science Fiction | Fiction & Lit | medium
        3 |       3 | Cooking         | Practical     | medium
...
        6 |       4 | Travel          | Nonfiction    | short