データウェアハウス（DWH）の設計を始めようとしたとき、「スタースキーマにするか、スノーフレークスキーマにするか」という問いに直面したことはないだろうか。オンプレのRDBMS設計では正規化こそが正義とされてきたが、クラウドDWHの世界ではその常識が覆ることがある。
この記事では、スタースキーマとスノーフレークスキーマの構造的な違いから、AWS RedshiftとGoogle BigQueryそれぞれでどちらを選ぶべきかの実務判断基準まで、オンプレ経験のあるエンジニア向けに解説する。
オンプレの「正規化神話」がクラウドDWHで通じない理由
オンプレのOLTPデータベース設計では、3正規形（3NF）に基づいてテーブルを分割するのが標準だった。更新異常を防ぎ、ストレージを節約し、データの一貫性を保つためだ。
ところがDWHの目的はOLTPとは根本的に異なる。DWHは「大量のデータを高速に集計・分析する」ことが主目的であり、更新よりも参照（SELECT）が圧倒的に多い。さらにAmazon RedshiftやGoogle BigQueryに代表されるクラウドDWHは列指向（カラムナー）ストレージを採用しており、JOIN回数の多い複雑なクエリはパフォーマンス上のペナルティを受けやすい。
OLTP vs OLAPの違いで詳しく解説しているように、分析系ワークロードでは「データをどう読み出すか」が設計の主軸になる。その観点からスキーマ選択を考えると、オンプレとは異なる判断軸が見えてくる。
スタースキーマとは何か
スタースキーマは、中心にファクトテーブル（事実テーブル）を置き、その周囲にディメンションテーブル（次元テーブル）を配置する構造だ。ER図にすると星（スター）の形に見えることが名前の由来である。
基本構造
・ファクトテーブル: 売上金額・件数・数量など「測定値」を保持する。各ディメンションの外部キーを持ち、テーブルは縦に非常に長くなる
・ディメンションテーブル: 日付・商品・顧客・店舗など「分析の切り口」を保持する。非正規化されていて1テーブルで完結している
想定例として、ECサイトの売上分析を考えると次のような構造になる。
| テーブル種別 | テーブル名（例） | 主なカラム（例） |
|---|---|---|
| ファクト | fact_sales | sale_amount, quantity, date_id, product_id, customer_id, store_id |
| ディメンション | dim_date | date_id, year, quarter, month, week, day_of_week, is_holiday |
| ディメンション | dim_product | product_id, product_name, category, sub_category, brand, unit_price |
| ディメンション | dim_customer | customer_id, customer_name, age_group, prefecture, registration_date |
スタースキーマのポイントはディメンションテーブルが非正規化されている点だ。たとえばdim_productには「category（大分類）」と「sub_category（小分類）」が同一テーブル内に並んでいる。OLTPの設計者なら「カテゴリは別テーブルに切り出すべき」と感じるが、スタースキーマではこれが意図的な設計だ。
スノーフレークスキーマとは何か
スノーフレークスキーマは、スタースキーマのディメンションテーブルをさらに正規化し、階層構造を複数のテーブルに分割した構造だ。ER図が雪の結晶（スノーフレーク）のように枝分かれして見えることが名前の由来である。
基本構造
先ほどのdim_productをスノーフレーク化すると次のようになる。
| テーブル名（例） | 主なカラム（例） | 参照先 |
|---|---|---|
| dim_product | product_id, product_name, sub_category_id, brand_id, unit_price | dim_sub_category, dim_brand |
| dim_sub_category | sub_category_id, sub_category_name, category_id | dim_category |
| dim_category | category_id, category_name | — |
| dim_brand | brand_id, brand_name, manufacturer | — |
カテゴリ階層を独立テーブルに分離することで、カテゴリ名を変更するときはdim_categoryを1行修正するだけで済む。スタースキーマではdim_productの全行を更新しなければならない。
スタースキーマ vs スノーフレークスキーマ：比較表
| 観点 | スタースキーマ | スノーフレークスキーマ |
|---|---|---|
| テーブル数 | 少ない（ディメンションが1階層） | 多い（ディメンションが複数階層） |
| クエリのJOIN数 | 少ない（ファクトとディメンションの直接結合のみ） | 多い（ディメンション間のJOINが必要） |
| クエリ速度 | 一般的に速い | JOIN増加によりやや遅くなりやすい |
| ストレージ容量 | 多い（非正規化による重複データあり） | 少ない（正規化による重複排除） |
| データ更新のしやすさ | 変更箇所が多くなりがち | 変更箇所が少なく一貫性を保ちやすい |
| クエリ設計の複雑さ | シンプル（アナリストが書きやすい） | 複雑（JOIN経路を把握する必要あり） |
| BIツールとの相性 | 良い（Tableau・Looker等が自動認識しやすい） | やや複雑（JOIN定義を手動設定することも） |
Amazon Redshiftではどちらを選ぶか
Amazon Redshiftは列指向の分散型DWHであり、スキーマ設計の選択がパフォーマンスに直結する。一般的な判断基準は次のとおりだ。
Redshiftにおける設計判断の軸
・クエリ速度を優先する場合 → スタースキーマ: Redshiftはノード間でデータを分散して並列処理する。JOIN回数が増えるとノード間のデータ転送（ネットワーク転送）が増え、スループットが低下しやすい。集計クエリが主体の分析DWHでは、JOIN数を減らすスタースキーマの方がスキャン効率が良い
・ディメンション更新が頻繁 → スノーフレークも検討: カテゴリ階層の変更が多い場合、スノーフレークスキーマの方がメンテナンスコストを下げられる。ただしクエリ速度とのトレードオフを必ず計測すること
・ディストリビューションキーの設計が重要: どちらのスキーマを選んだとしても、ファクトテーブルのDIST KEYを適切に設定し、JOIN時のデータ移動を最小化することがRedshiftでの最重要設計事項だ
Redshiftで意識すべき3つのキー
| キー種別 | 役割 | スタースキーマでの設定例 |
|---|---|---|
| DIST KEY | データの分散方式を決める | ファクトテーブルとよくJOINするディメンションのIDを一致させる |
| SORT KEY | クエリのスキャン範囲を絞る | date_idをSORT KEYにして日付フィルタを効かせる |
| ENCODE | 列ごとの圧縮方式 | ANALYZE COMPRESSIONで自動判定させる |
Google BigQueryではどちらを選ぶか
BigQueryはサーバーレスの列指向DWHで、スキャンしたデータ量に応じた従量課金が基本だ（スロット予約モデルも選択可）。BigQueryではRedshiftと異なる設計の自由度がある。
BigQueryにおける設計判断の軸
・ネストと繰り返しフィールド（STRUCT/ARRAY）を活用する選択肢がある: BigQueryはJSONライクなネスト構造をネイティブサポートしており、スノーフレークスキーマが解決しようとする「ディメンションの階層表現」をテーブル分割なしに1テーブル内で実現できる。カテゴリと小カテゴリをSTRUCT型でまとめるアプローチだ
・スキャンコストの観点では非正規化（スタースキーマ寄り）が有利: BigQueryは全テーブルのフルスキャンに課金されるため、JOINで参照するテーブルが増えるほどコストが上がりやすい。頻繁に参照するディメンション情報はファクトテーブルに埋め込むか、マテリアライズドビューで事前集計することが多い
・パーティション設計と組み合わせる: BigQueryではdim_dateのような日付ディメンションをJOINするより、ファクトテーブル自体をDATE型列でパーティション分割し、WHERE条件でスキャン範囲を絞る方がコスト・速度の両面で有利なケースが多い
ETLパイプラインとの関係
スキーマ選択はデータ投入の設計とも密接に関わる。ETLとELTの違いで解説しているように、クラウドDWHではDWH側で変換するELTパターンが増えている。
スタースキーマを採用する場合、ソースデータのフラット化（非正規化）をETL/ELTの変換ステップで行う必要がある。AWS Glueやdbt（data build tool）を使い、正規化されたソースデータをDWH向けのスタースキーマ形式に変換するパターンが一般的だ。
スノーフレークスキーマを採用する場合は、ソースの正規化構造をそのまま保持しやすいが、分析クエリの複雑化とJOINコストに注意が必要だ。
実務での選び方：判断フロー
| 優先するもの | 推奨スキーマ | 補足 |
|---|---|---|
| クエリ速度・BIツール連携 | スタースキーマ | アナリストが書きやすく、Tableau/Looker等との相性も良い |
| ディメンション更新頻度が高い | スノーフレークスキーマ | カテゴリ階層の変更が多い業務DWHに向く |
| ストレージコスト削減 | スノーフレークスキーマ | ただしクラウドでのストレージ単価は年々低下しており、判断の優先度は下がりつつある |
| BigQueryのスキャンコスト削減 | 非正規化 or ネスト型 | スタースキーマに近い発想でBigQuery固有機能を活用する |
| Redshiftの集計速度重視 | スタースキーマ | JOIN数を減らしDIST KEY・SORT KEYを最適化する |
よくあるつまずき
・「正規化＝正しい設計」という思い込みの持ち込み: オンプレのOLTP設計経験が長いエンジニアが、DWHにスノーフレークスキーマを選びがちだ。クエリが複雑になり、BIツールのセルフサービス利用が困難になるケースが見られる
・スキーマ選択後にボトルネックに気づく: スノーフレークスキーマで構築後、JOIN数の多いクエリが遅くてスタースキーマへ作り直すことになるのは一般的なつまずきだ。設計前に代表的な分析クエリのパターンを洗い出し、JOINの深さを確認しておくことが重要
・RedshiftとBigQueryを混同した設計: BigQueryはSTRUCT/ARRAY型があるためスノーフレーク的な設計をテーブル内で実現できる。一方でRedshiftはこの機能を持たないため、同じ設計思想をそのまま移植するとJOIN地獄になる
・ディメンションテーブルのサイズ見積もりミス: スノーフレークで分割したテーブルが小さすぎると、JOIN時のオーバーヘッドに対してストレージ節約のメリットがほとんど出ない
本記事のまとめ
・スタースキーマ: ファクトテーブル＋非正規化ディメンションテーブル。JOIN数が少なく、クラウドDWHのクエリ速度・BIツール連携に有利。Redshiftでは特に推奨される
・スノーフレークスキーマ: ディメンションをさらに正規化した多階層構造。ストレージ効率・更新容易性が高いが、JOIN増加によるクエリコスト上昇に注意
・BigQueryはSTRUCT/ARRAY型やパーティション設計を活用した「非正規化寄りのスタースキーマ」がコスト・速度の両面で有効
・Redshiftはスタースキーマ＋DIST KEY/SORT KEYの最適化がパフォーマンスの決め手
・スキーマ選択の前に「どんな分析クエリが走るか」「ディメンションの更新頻度はどれくらいか」を先に確認することが現場での失敗を防ぐポイントだ
データレイク・DWH・データマートの違いも併せて読むと、データ基盤全体の中でのDWHの位置づけが整理しやすくなるだろう。
PR
AWSを使ったクラウドアーキテクチャの設計原則・実践パターンを体系的に学べる一冊。DWH設計を含むデータ基盤の設計判断を深める際の参考書として活用できる。
