SQL Academy · レッスン

スター型スキーマとスノーフレークスキーマ

Q: 「スター型スキーマとスノーフレークスキーマ」で何を学びますか？

高速な分析のためにデータをモデル化します ブラウザで直接実行するハンズオンコードでSQL Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

高速な分析のためにデータをモデル化します

「スター型スキーマとスノーフレークスキーマ」はCoddyKit上の無料SQL Academyレッスンです。これはレッスン3/4です。下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。これはSQL Academy学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Academyコースには全4レッスンが含まれています。

データウェアハウススキーマとは

トランザクション（OLTP）データベースでは、冗長性を避けるためにデータを正規化します。一方、データウェアハウスでは、クエリ速度のためにストレージ容量を意図的に犠牲にして非正規化することがよくあります。データウェアハウスのテーブルを構成する代表的なパターンには、スタースキーマとスノーフレークスキーマがあります。

どちらも、中央のファクトテーブルをディメンションテーブルが取り囲む構成です。違いは、ディメンションをどこまで正規化するかにあります。

ファクトテーブルとディメンションテーブル

ファクトテーブルには、販売、クリック、出荷などの測定可能なイベントが格納されます。行数が多く、数値メジャーとディメンションへの外部キーを含みます。

ディメンションテーブルは、各イベントの文脈、つまり誰が、何を、いつ、どこで行ったかを表します。行数は少ないものの、説明用の列が豊富です。

CREATE TABLE fact_sales (
  sale_id      SERIAL PRIMARY KEY,
  date_key     INT NOT NULL,
  product_key  INT NOT NULL,
  customer_key INT NOT NULL,
  store_key    INT NOT NULL,
  quantity     INT NOT NULL,
  revenue      NUMERIC(12, 2) NOT NULL
);

CREATE TABLE dim_product (
  product_key  SERIAL PRIMARY KEY,
  product_name VARCHAR(200),
  category     VARCHAR(100),
  brand        VARCHAR(100),
  unit_price   NUMERIC(10, 2)
);

スタースキーマ

スタースキーマでは、すべてのディメンションテーブルがファクトテーブルに直接接続します。関係を紙に描くと星のように見えます。ファクトテーブルが中心で、ディメンションが頂点です。

ディメンションテーブルは完全に非正規化されています。一部の属性が行間で繰り返される場合でも、すべての説明属性を1つのテーブルに格納します。

-- Star schema: all product info in one flat dimension table
CREATE TABLE dim_product (
  product_key    SERIAL PRIMARY KEY,
  product_name   VARCHAR(200),
  category_name  VARCHAR(100),   -- denormalized
  subcategory    VARCHAR(100),   -- denormalized
  brand_name     VARCHAR(100),   -- denormalized
  brand_country  VARCHAR(100),   -- denormalized
  unit_price     NUMERIC(10, 2)
);

CREATE TABLE dim_date (
  date_key   INT PRIMARY KEY,   -- e.g. 20240315
  full_date  DATE,
  year       INT,
  quarter    INT,
  month      INT,
  month_name VARCHAR(20),
  week       INT,
  day_of_week VARCHAR(10)
);

スタースキーマクエリ

フラットなディメンションテーブルによって、クエリはシンプルになります。ファクトテーブルを1つ以上のディメンションと結合して集計するだけです。正規化されたテーブルの連鎖を経由する追加の結合はありません。

これが、スタースキーマで分析クエリを高速に実行できる理由です。結合グラフが浅いからです。

SELECT
  d.year,
  d.quarter,
  p.category_name,
  SUM(f.revenue)   AS total_revenue,
  SUM(f.quantity)  AS units_sold
FROM fact_sales f
JOIN dim_date    d ON d.date_key    = f.date_key
JOIN dim_product p ON p.product_key = f.product_key
WHERE d.year = 2024
GROUP BY d.year, d.quarter, p.category_name
ORDER BY d.quarter, total_revenue DESC;

スノーフレークスキーマ

スノーフレークスキーマでは、ディメンションテーブルをサブディメンションに分割することで、さらに正規化します。たとえば、dim_product内にcategory_nameとbrand_nameを保存する代わりに、dim_categoryテーブルとdim_brandテーブルを個別に作成します。

こうしてできる図は、関連するテーブルが枝分かれした腕のように広がる、雪の結晶のような形になります。

-- Snowflake schema: product dimension is normalized
CREATE TABLE dim_brand (
  brand_key     SERIAL PRIMARY KEY,
  brand_name    VARCHAR(100),
  brand_country VARCHAR(100)
);

CREATE TABLE dim_category (
  category_key   SERIAL PRIMARY KEY,
  category_name  VARCHAR(100),
  subcategory    VARCHAR(100)
);

CREATE TABLE dim_product (
  product_key  SERIAL PRIMARY KEY,
  product_name VARCHAR(200),
  category_key INT REFERENCES dim_category(category_key),
  brand_key    INT REFERENCES dim_brand(brand_key),
  unit_price   NUMERIC(10, 2)
);

スノーフレークスキーマへのクエリ

スノーフレークスキーマへのクエリでは、複数のテーブルに分割されたディメンションデータを再構成するため、より多くのJOINが必要になります。クエリオプティマイザーは追加された階層をたどる必要があるため、スタースキーマと比べて遅延が増える場合があります。

ただし、正規化されたディメンションはサイズが小さく、一貫性も保ちやすくなります。dim_brandの1行でブランド名を更新すれば、その変更が自動的にすべての場所に反映されます。

SELECT
  d.year,
  c.category_name,
  b.brand_name,
  SUM(f.revenue) AS total_revenue
FROM fact_sales    f
JOIN dim_date      d ON d.date_key    = f.date_key
JOIN dim_product   p ON p.product_key = f.product_key
JOIN dim_category  c ON c.category_key = p.category_key
JOIN dim_brand     b ON b.brand_key    = p.brand_key
WHERE d.year = 2024
GROUP BY d.year, c.category_name, b.brand_name
ORDER BY total_revenue DESC;

サロゲートキーとナチュラルキーの比較

ディメンションテーブルでは通常、ソースシステムのナチュラルキーではなく、データウェアハウスが生成する整数（例：SERIAL）であるサロゲートキーを使用します。

サロゲートキーはソースが変更されても安定しており、大規模なファクトテーブルで効率よく格納できます。また、履歴を追跡する必要がある緩やかに変化するディメンションにも対応できます。

-- Surrogate key (product_key) vs natural key (sku)
INSERT INTO dim_product (product_name, category_key, brand_key, unit_price)
VALUES ('Wireless Headphones', 3, 7, 89.99);
-- product_key is assigned by SERIAL -- the natural key (SKU) lives elsewhere

-- Natural key would be:
-- INSERT INTO dim_product (sku, product_name, ...)
-- VALUES ('WH-1000XM5', 'Wireless Headphones', ...);
-- Risky: SKU can be reused or reassigned by the source system

日付ディメンション

日付ディメンションは特殊です。ほぼ必ず存在し、通常は何年分もの日付をあらかじめ登録しておきます。年、四半期、月名、会計期間、休日フラグなどの派生属性をディメンションテーブルに保存しておけば、クエリ実行時に再計算する必要がありません。

-- Populate dim_date for one year using generate_series
INSERT INTO dim_date (date_key, full_date, year, quarter, month, month_name, week, day_of_week)
SELECT
  TO_CHAR(d, 'YYYYMMDD')::INT  AS date_key,
  d                             AS full_date,
  EXTRACT(YEAR    FROM d)::INT  AS year,
  EXTRACT(QUARTER FROM d)::INT  AS quarter,
  EXTRACT(MONTH   FROM d)::INT  AS month,
  TO_CHAR(d, 'Month')           AS month_name,
  EXTRACT(WEEK    FROM d)::INT  AS week,
  TO_CHAR(d, 'Day')             AS day_of_week
FROM generate_series('2024-01-01'::DATE, '2024-12-31'::DATE, '1 day') AS d;

緩やかに変化するディメンション（SCD Type 2）

顧客が別の都市へ引っ越したり、商品のカテゴリが変わったりした場合はどうすればよいでしょうか。履歴を追跡する必要があります。SCD Type 2では、変更が発生するたびに新しいディメンション行を追加し、以前の行には終了日を設定して閉じます。ファクトテーブルの行は引き続き古いディメンションキーを参照するため、過去のデータの正確性が保たれます。

-- SCD Type 2 customer dimension
CREATE TABLE dim_customer (
  customer_key  SERIAL PRIMARY KEY,
  customer_id   INT NOT NULL,
  customer_name VARCHAR(200),
  city          VARCHAR(100),
  country       VARCHAR(100),
  valid_from    DATE NOT NULL,
  valid_to      DATE,
  is_current    BOOLEAN DEFAULT TRUE
);

-- When a customer moves, close old row and insert new one:
UPDATE dim_customer
   SET valid_to = CURRENT_DATE - 1, is_current = FALSE
 WHERE customer_id = 42 AND is_current = TRUE;

INSERT INTO dim_customer (customer_id, customer_name, city, country, valid_from, is_current)
VALUES (42, 'Alice Muller', 'Berlin', 'Germany', CURRENT_DATE, TRUE);

スターとスノーフレークの比較 — トレードオフ

どちらのスキーマが常に優れているというわけではありません。優先事項に応じて選択してください。

スター — JOINが少なく、クエリが高速で、ETLがシンプルですが、ストレージコストは高くなります。読み取り中心の分析ツール（Tableau、Power BI）に適しています。
スノーフレーク — ディメンションが正規化され、冗長性が少なく、ディメンションを更新しやすい一方で、JOINが増えます。ディメンションが大規模な場合や、複数のファクトテーブルで共有される場合に適しています。

-- Checking how much storage the denormalized category column costs
-- in a large dim_product (star schema) vs a separate dim_category (snowflake)
SELECT
  COUNT(*)                               AS total_products,
  COUNT(DISTINCT category_name)          AS unique_categories,
  pg_size_pretty(
    SUM(pg_column_size(category_name))
  )                                      AS category_storage
FROM dim_product;

ギャラクシースキーマ（ファクトコンステレーション）

データウェアハウスに、ディメンションテーブルを共有する複数のファクトテーブルがある場合、その構成をギャラクシースキーマ（またはファクトコンステレーション）と呼びます。たとえば、小売業のデータウェアハウスには、売上と返品用に別々のファクトテーブルがあり、どちらも同じdim_productとdim_dateを参照することがあります。

共有ディメンションによってフィルタリングの一貫性が保たれ、異なるファクト間の比較も簡単になります。

CREATE TABLE fact_returns (
  return_id     SERIAL PRIMARY KEY,
  date_key      INT NOT NULL REFERENCES dim_date(date_key),
  product_key   INT NOT NULL REFERENCES dim_product(product_key),
  customer_key  INT NOT NULL,
  quantity      INT NOT NULL,
  refund_amount NUMERIC(12, 2) NOT NULL
);

-- Cross-fact query: net revenue = sales - refunds
SELECT
  d.year,
  d.month,
  SUM(s.revenue)       AS gross_revenue,
  SUM(r.refund_amount) AS total_refunds,
  SUM(s.revenue) - COALESCE(SUM(r.refund_amount), 0) AS net_revenue
FROM dim_date d
LEFT JOIN fact_sales   s ON s.date_key = d.date_key
LEFT JOIN fact_returns r ON r.date_key = d.date_key
WHERE d.year = 2024
GROUP BY d.year, d.month
ORDER BY d.month;

スタースキーマとスノーフレークスキーマ

スタースキーマとスノーフレークスキーマについての理解度を確認しましょう。

レッスンのまとめ

このレッスンでは、データウェアハウス設計の基本となる2つのパターンについて学びました。

スタースキーマ — 中央のファクトテーブルを、平坦で非正規化されたディメンションテーブルが取り囲む構成です。JOINが少なく、クエリが高速になる一方、ストレージはやや多く必要です。
スノーフレークスキーマ — ディメンションテーブルをサブディメンションに分割して、さらに正規化する構成です。冗長性が少なく更新しやすい一方、より多くのJOINが必要です。
ファクトテーブルには測定可能なイベントを格納し、ディメンションテーブルにはコンテキスト（誰が、何を、いつ、どこで）を格納します。
サロゲートキーによって履歴の正確性を守り、データウェアハウスをソースシステムの変更から切り離せます。
SCD Type 2では、古い行を上書きする代わりに有効期間付きの新しい行を追加することで、ディメンションの履歴を追跡します。
複数のファクトテーブルがディメンションを共有すると、その設計はギャラクシースキーマ（ファクトコンステレーション）になります。

シンプルさと速度を重視するならスターを、ディメンションが大規模で頻繁に更新される場合や、多くのファクトテーブルで共有される場合はスノーフレークを選択してください。

よくある質問