0Pricing
SQL Interview Prep · レッスン

スタースキーマとデータウェアハウス設計

ファクトテーブルとディメンションテーブル、非正規化のトレードオフ、OLAPモデリングを学びます。

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

OLTPとOLAP

データウェアハウスに関する質問では、面接官があなたに確実に理解しておいてほしい、最初の重要な違いがあります。それがOLTPとOLAPです。

  • OLTP（トランザクション処理）：小規模な読み取りや書き込みを大量に行い、整合性を保つため高度に正規化されています。アプリケーションを支えます。
  • OLAP（分析処理）：履歴データに対して、大規模な集計読み取りを少数行い、速度のため意図的に非正規化されています。レポートやダッシュボードを支えます。

スタースキーマはOLAPの設計です。冗長性を受け入れる代わりに、高速な分析クエリを実現することが目的です。

ファクトとディメンション

スタースキーマでは、データを次の2種類のテーブルに分けます。

  • ファクトテーブル：測定可能なイベントやトランザクション（販売、クリックなど）を格納します。数値のメジャーと、ディメンションへの外部キーを保持します。
  • ディメンションテーブル：切り口として使用する説明的なコンテキスト（日付、商品、顧客、店舗など）を格納します。

ファクトテーブルを中央に置き、ディメンションテーブルが星の形の点のように周囲を取り囲むため、この名前が付いています。

ファクトテーブルの構造

ファクトテーブルは、ほとんどが外部キーと数値のメジャーで構成されます。縦長で列数が少なく、継続的に増加します。

メジャーは集計する加算可能な数値で、数量、売上、コストなどが該当します。粒度（1行が何を表すか）は明確に定義する必要があります。ここでは、1行が1回の販売における1つの商品明細を表します。

CREATE TABLE fact_sales (
  sale_id      BIGINT PRIMARY KEY,
  date_key     INT  NOT NULL,   -- FK to dim_date
  product_key  INT  NOT NULL,   -- FK to dim_product
  customer_key INT  NOT NULL,   -- FK to dim_customer
  store_key    INT  NOT NULL,   -- FK to dim_store
  quantity     INT,             -- measure
  revenue      DECIMAL(12,2),   -- measure
  cost         DECIMAL(12,2)    -- measure
);

ディメンションテーブルの構造

ディメンションは短く横に広い構造です。フィルタリングやグループ化に使用する説明用の列を多数持ちます。クエリでディメンションごとに1回だけ結合すれば済むよう、意図的に非正規化されています。

dim_productでは、categoryとbrandが別々のテーブルではなく同じ行に保持されていることに注目してください。この冗長性こそが目的です。クエリ実行時に追加の結合を避けられます。

CREATE TABLE dim_product (
  product_key  INT PRIMARY KEY,   -- surrogate key
  product_id   INT,              -- natural/business key
  product_name VARCHAR(100),
  category     VARCHAR(50),      -- denormalized
  brand        VARCHAR(50),      -- denormalized
  unit_price   DECIMAL(10,2)
);

スタースキーマのクエリ

これが、この設計によって得られる効果です。一般的な分析クエリでは、ファクトテーブルをいくつかのディメンションと結合し、フィルタリングと集計を行います。ディメンションごとに1回結合するだけで、深い結合の連鎖はありません。

面接官からは、スタースキーマに対してまさにこのようなクエリを書くよう求められます。

SELECT d.category,
       t.year,
       SUM(f.revenue) AS total_revenue
FROM fact_sales f
JOIN dim_product d ON d.product_key = f.product_key
JOIN dim_date    t ON t.date_key    = f.date_key
WHERE t.year = 2025
GROUP BY d.category, t.year
ORDER BY total_revenue DESC;

サロゲートキー

ディメンションではサロゲートキーを使用します。これは、ソースシステムのナチュラルキーとは別に、データウェアハウスが生成する意味を持たない整数の主キー（product_keyなど）です。

面接官がこの点を重視する理由は次のとおりです。

  • 変更される可能性のあるビジネスキーからデータウェアハウスを切り離せます。
  • ファクトテーブルを狭く保てます（整数による結合は高速です）。
  • 緩やかに変化するディメンションで履歴を追跡するために必要です（次のシーンで説明します）。

緩やかに変化するディメンション

データウェアハウスの面接でよく出るテーマに、ディメンションの属性が変化したとき（顧客が引っ越したときなど）の扱いがあります。これが緩やかに変化するディメンション（SCD）です。

  • Type 1：古い値を上書きします。履歴は残りません。
  • Type 2：有効日と現在フラグを付けた新しい行を追加します。完全な履歴を保持でき、サロゲートキーが必要です。
  • Type 3：「以前の値」列を保持します。履歴は限定的です。

時間の経過に伴う変更を追跡する場合、Type 2が最も一般的に期待される回答です。

-- SCD Type 2 dimension
CREATE TABLE dim_customer (
  customer_key INT PRIMARY KEY,   -- surrogate
  customer_id  INT,              -- natural key
  city         VARCHAR(50),
  valid_from   DATE,
  valid_to     DATE,
  is_current   BOOLEAN
);

スターとスノーフレーク

この比較は質問されるものと考えてください。スノーフレークスキーマでは、ディメンションをサブテーブル（product -> category -> department）に正規化します。一方、スターではフラットに保持します。

  • スター：結合が少なく読み取りが高速ですが、冗長性があります。クエリ性能を優先する場合に適しています。
  • スノーフレーク：必要なストレージが少なく、ディメンションの保守も容易ですが、クエリごとの結合が増えます。

次のように答えてください。「クエリ速度のためにデフォルトではスターを選び、ディメンションが大きく再利用される場合に限ってスノーフレークを選びます。」

日付ディメンション

ほぼすべてのスタースキーマには、単なる日付列ではなく専用の日付ディメンションがあります。年、四半期、月、曜日、祝日フラグ、会計期間などをあらかじめ計算して保持します。

これにより、分析担当者は日付関数をあちこちで使うのではなく、単純な結合だけで「会計四半期」やis_weekendによるグループ化ができます。こちらから日付ディメンションに言及すると、データウェアハウスを構築した経験があることを強く示せます。

CREATE TABLE dim_date (
  date_key   INT PRIMARY KEY,   -- e.g. 20250131
  full_date  DATE,
  year       INT,
  quarter    INT,
  month      INT,
  day_of_week VARCHAR(10),
  is_weekend BOOLEAN,
  fiscal_qtr VARCHAR(6)
);

粒度の選択

ファクトテーブルに関する最も重要な決定は、粒度、つまり1行が何を表すかです。ほかのことを決める前に定義してください。

  • 粗すぎる場合（店舗ごとに1日1行など）は、詳細が失われます。
  • 細かすぎる場合（スキャンした商品1つごとに1行など）は、テーブルが膨れ上がります。

「注文の商品明細ごとに1行」のように粒度を明確に定義すると、どのディメンションとメジャーを含めるべきかが決まります。面接官は、この規律を守っているかを見ています。

非正規化するタイミング

正規化との関係を説明しましょう。OLTPシステムは整合性のために3NFへ正規化します。一方、データウェアハウスでは読み取り速度のためにディメンションを意図的に非正規化します。

説明すべきトレードオフは次のとおりです。

  • データウェアハウスは、アプリケーションからのアドホックな書き込みではなく、管理されたETLによってロードされるため、ディメンションデータの冗長性を許容できます。
  • 結合が少ないほど、数十億行のファクトテーブルに対する集計が高速になります。

ここで差がつくのは、ルールの暗記ではなく、状況に応じた判断力です。

クイックチェック

売上データウェアハウスを設計しており、顧客が引っ越した場合にも、その顧客の都市の完全な履歴を保持する必要があります。

振り返り：スタースキーマとデータウェアハウス設計

これで、データウェアハウスのモデリングに関する質問にも対応できるようになりました。

  • OLTPは整合性のために正規化し、OLAPは読み取り速度のために非正規化します。
  • スターでは、中央のファクトテーブル（FKと数値のメジャー）を、フラットなディメンションが取り囲みます。
  • サロゲートキーと専用の日付ディメンションを使用します。
  • SCD Type 2で変更を追跡し、最初にファクトの粒度を定義します。
  • クエリ性能のために、スノーフレークよりスターを優先します。

よくある質問

「スタースキーマとデータウェアハウス設計」レッスンは無料ですか？

はい。「スタースキーマとデータウェアハウス設計」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Interview Prepコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Interview Prepコースには全4レッスンが含まれています。

「スタースキーマとデータウェアハウス設計」で何を学びますか？

ファクトテーブルとディメンションテーブル、非正規化のトレードオフ、OLAPモデリングを学びます。 ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

SQL Interview Prepを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのSQL Interview Prepは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン3/4です。

「スタースキーマとデータウェアハウス設計」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

このSQL Interview Prepレッスンでコードを書いて実行できますか？

はい。すべてのSQL Interview Prepレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

このコースのすべてのレッスン

  1. 3NFまでの正規化
  2. ERモデリングとリレーションシップのカーディナリティ
  3. スタースキーマとデータウェアハウス設計
  4. 総合模擬面接問題集
← SQL Interview Prepに戻る