スタースキーマ図：ファクトテーブルとディメンションテーブル（売上の例つき）

プロダクトマネージャーが、前四半期のカテゴリ別・地域別の売上を、前年同期と比べて見たいと言ってきます。注文データベースでは、5 つのテーブルを結合し、カレンダーをその場で作り、顧客が地域をまたいで引っ越すことを覚えておく必要があります。質問が増えるたびに結合が増え、2 人のアナリストが 2 つの違う数字を出します。

スタースキーマは、データを 1 つのビジネスプロセスを中心に組み替えることでこれを解決します。中央に測定値のファクトテーブルを置き、その周囲にそれを説明するディメンションテーブルを置きます。この記事では、粒度から SQL まで、売上のスタースキーマを順に設計します。緩やかに変化するディメンション、スター対スノーフレーク、列指向のウェアハウスも扱います。

スタースキーマとは

スタースキーマには 2 種類のテーブルがあります。

• ファクトテーブルは、注文明細のようなビジネスイベントの測定値を記録します。ディメンションへの外部キーと数値の指標を持ち、列が少なく行が多いテーブルです。
• ディメンションテーブルは、誰が、何を、どこで、いつといった文脈を持ちます。カテゴリや地域のようなテキスト属性を持つ、列が多く平らで行の少ないテーブルです。

これらの手法の多くに名前を付けた Kimball Group は、スタースキーマを、主キーと外部キーでディメンションテーブルにつながるファクトテーブルだと説明しています。Microsoft の Power BI ガイダンスは、その魅力を簡潔に述べています。ディメンションテーブルは絞り込みとグループ化を、ファクトテーブルは集計を可能にします。

4 ステップで設計する

Kimball の4 ステップのディメンショナル設計プロセスは、順番に行う 4 つの判断です。

1. ビジネスプロセスを選ぶ：店舗と Web ショップでの小売販売。
2. 粒度を宣言する：注文明細 1 行につき 1 行、つまり 1 件の注文の 1 つの商品です。Kimball は粒度を拘束力のある契約と呼び、ディメンションやファクトより先に宣言します。
3. ディメンションを特定する：日付、商品、店舗、顧客、プロモーション。いずれも注文明細 1 行につき 1 つの値を持ちます。
4. ファクトを特定する：数量、単価、割引、純額、原価。いずれもその粒度で成り立ちます。

ソースが捉える最も低い水準である原子的な粒度から始めます。集計は原子的な行から作るもので、その逆はありません。粒度は決して混ぜないでください。注文単位で課金されるのに全明細に保存された送料は、明細の数だけ数えられます。Kimball が勧めるように業務ルールで明細に配分するか、専用のファクトテーブルを用意します。

例のテーブル

ディメンションは意図的に平らにしています。dim_product はブランド、サブカテゴリ、カテゴリを 1 行に持ち、dim_store は Web ショップを channel = 'online' のもう 1 つの店舗として扱います。order_number は縮退ディメンションです。ヘッダーの文脈は他のディメンションにあるため番号だけが残り、買い物かごのグループ化には引き続き役立ちます。

単価は典型的な落とし穴です。明細をまたいで合計しても意味がないため、SUM(net_amount) / SUM(quantity) のように加算可能な部分を割ります。

サロゲートキーと日付ディメンション

各ディメンションには、1 から順に振る意味のない整数であるサロゲートキーを付けます。履歴を持つディメンションは自然キー 1 つにつき複数の行を持ち、ソースごとにキーが食い違うことも多いためです。自然キー（sku、store_code、customer_id）は、行をソースまでたどれるよう属性として残します。

日付ディメンションは例外です。安定していて予測できるため、キーに 20261120 のような意味を持たせられ、パーティション分割にも役立ちます。絞り込みやグループ化は、キーに対する演算ではなく属性（year、quarter、is_holiday）で行います。20 年分でも約 7,305 行にすぎません（20 × 365.25）。

ファクトの外部キーは null にしてはいけません。null のキーは内部結合から行を落とし、合計が気づかないうちに小さくなります。不明または該当なしの値には、既定のディメンション行を使います。この例では promotion_key の 0 が「プロモーションなし」を意味します。

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

緩やかに変化するディメンション（SCD）のタイプが、属性が変わった後にレポートが何を表示するかを決めます。

Kimball のタイプ 4 から 7 は、ミニディメンションとこれらの組み合わせを加えたものです。

タイプ 2 が主力です。顧客 C-1042 が 2026 年 11 月 20 日に Austin から Denver へ引っ越します。

以前の売上はキー 1001 のまま Austin に残り、以後の売上には 2417 が付きます。ファクトのロードでは、範囲が注文日を含む行を選びます（valid_from <= order_date < valid_to）。Kimball のタイプ 2 の定義では、少なくとも有効開始日、有効終了日、現在行の指標が必要です。null ではなく遠い未来の valid_to を使うと、範囲フィルターが単純になります。dbt のスナップショットはタイプ 2 を代わりに実装してくれます。

すべての履歴を現在の都市で集計するには、同じ customer_id の現在の行に結合します。変更データキャプチャは、更新がすべてイベントとして届くため、タイプ 2 とよく合います。change data capture の図を参照してください。

スタースキーマ、スノーフレークスキーマ、ワイドテーブルの比較

Kimball はスノーフレーク化したディメンションを勧めていません。業務ユーザーには操作しにくく、クエリ性能を損なうことがあり、平らにしたディメンションはまったく同じ情報を持つからです。Power BI のガイダンスも同じ見解です。

ワイドテーブルは結合が不要で、列指向の圧縮により繰り返しの値も安く保てます。代償は保守です。修正は書き直しに、新しい属性は再構築になるため、スターから生成してください。

モデルが簡単にするクエリ

SELECT d.year_month,
       p.category,
       SUM(f.net_amount)                                AS revenue,
       SUM(f.net_amount - f.cost_amount)                AS gross_margin,
       SUM(f.net_amount) / NULLIF(SUM(f.quantity), 0)   AS avg_selling_price
FROM fact_sales AS f
JOIN dim_date    AS d ON d.date_key    = f.date_key
JOIN dim_product AS p ON p.product_key = f.product_key
JOIN dim_store   AS s ON s.store_key   = f.store_key
WHERE d.year = 2026
  AND s.channel = 'online'
GROUP BY d.year_month, p.category
ORDER BY d.year_month, revenue DESC;

結合はすべてファクトから 1 つのディメンションへ、1 つの整数キーで行われます。絞り込みはディメンションの属性に、合計はファクトの列にかかります。新しい質問で変わるのは WHERE と GROUP BY で、結合は変わりません。BI ツールがこの種のクエリを確実に生成できるのはそのためです。

列指向ウェアハウスでの性能

列指向ウェアハウスはクエリが使う列だけを読むため、列の少ないファクトテーブルに向いており、小さなディメンションは安く結合できます。ブロードキャスト結合では、BigQuery が小さいほうのテーブルを全ワーカーに送ります。INT64 のキーと数量が論理 8 バイト、NUMERIC の金額が 16 バイトの場合、上のクエリはファクトから注文明細 1 行あたり 64 バイトを読み、5 億行で約 32 GB です。テーブルの幅がどれだけ広くても変わりません。

• 日付で枝刈りする。 ファクトを date_key でパーティション分割、クラスタリング、またはソートします。BigQuery は、パーティション列そのものに定数の絞り込みがあるときにパーティションを枝刈りします。大きなスキャンでは dim_date.year だけでなく、date_key にも定数で絞り込みを付けてください。
• 強制されなくてもキーを宣言する。 BigQuery は主キーと外部キーを NOT ENFORCED として宣言すると、結合の削除と並べ替えに使います。Snowflake は RELY を付けた制約で結合を削除します。どちらもデータは検査せず、制約が破られると誤った結果を返すことがあるため、キーはパイプラインでテストしてください。
• Redshift ではデータを同じ場所に置く。 Redshift のガイダンスは、ファクトと 1 つのディメンションを共通のキーで分散し、他のディメンションには ALL 分散を使うか、AUTO に任せることです。

スタースキーマは通常、メダリオンアーキテクチャのゴールド層に置かれます。データがそこに届くまではデータパイプラインのアーキテクチャ図で、変換がどこで動くかは ETL と ELT の比較で説明しています。

売上スタースキーマの図

図の読み方は次のとおりです。

• 中央の fact_sales は、5 つの外部キー、縮退ディメンションの order_number、line_number、5 つの指標を持ちます。
• すべての線に鳥の足：一方の端はファクト行がゼロ個以上、もう一方の端はディメンション行がちょうど 1 つです。ラベルは結合キーを示します。
• ティールのカードがディメンションで、dim_date が上、残りが左右にあります。
• アンバーのカードの dim_customer は、SCD タイプ 2 で履歴を保持します。
• Markdown ブロックには、粒度と、合計してよい指標が書かれています。

スタースキーマ図を分かりやすく描く

• 粒度を図に書く。 レビュアーが最初に尋ねるところで、粒度を読み違えると二重集計の原因になります。
• ファクトを中央に置き、すべてのディメンションを 1 ホップの距離に置く。 ディメンション同士をつなぐ線はスノーフレークです。意図して描くか、平らにしてください。
• キーの役割を示す：PK、FK、自然キーは NK、縮退ディメンションは DD。
• SCD タイプを示す：履歴を持つディメンションに付けます。
• 省く：ロードジョブ、ステージングテーブル、誰も絞り込みに使わない列。
• 1 つの図に 1 つのビジネスプロセスを描く。 複数のファクトテーブルが共通のコンフォームドディメンションを共有する場合は、バスマトリクスを加えます。行がプロセス、列がディメンションです。

鳥の足記法については ER 図の例を参照してください。

Archboard で作る

1. Google または GitHub でサインインし、ダイアグラムを作成します。
2. テーブルがすでにあるなら、CREATE TABLE 文を SQL から ER 図へのツールに、DBML を DBML から ER 図へのツールに貼り付けて、最初の下書きを作ります。
3. ER 図パックから fact_sales という名前のエンティティを追加し、1 行に 1 属性で書きます。FK を付けた 5 つのキー、DD を付けた order_number、line_number、続いて指標です。アクセントは青にします。
4. エンティティノードをさらに 5 つ追加します。ファクトの上に dim_date、左に dim_customer と dim_promotion、右に dim_product と dim_store です。サロゲートキーには PK、自然キーには NK を付け、アクセントはティール、dim_customer だけアンバーにします。
5. fact_sales を各ディメンションに鳥の足でつなぎます。ファクト側はゼロ個以上、ディメンション側はちょうど 1 つで、各線にキーのラベルを付けます。
6. 基本パックのテキストノートを追加します。キーの凡例、SCD タイプ 2 のルール、スマートな日付キー、既定のプロモーション行、Web ショップについてです。
7. ファクトの下に、基本パックの Markdown ブロックを追加し、粒度と加算可否のルールを書きます。

この例で使うダイアグラムノードは 7 個で、Free プランの範囲に収まります。

よくある質問

スタースキーマとは、簡単に言うと何ですか？ 売上金額のような測定値を持つ中央のファクトテーブルが、日付、商品、顧客のようにそれを説明するディメンションテーブルにつながったものです。描くと星の形に見えます。

ファクトテーブルとディメンションテーブルの違いは何ですか？ ファクトテーブルは、宣言した粒度でのイベントの測定値と外部キーを保存します。ディメンションテーブルは、カテゴリや地域のように、絞り込みやグループ化に使う属性を保存します。

スタースキーマとスノーフレークスキーマの違いは何ですか？ スターは各ディメンションを平らなテーブル 1 つに保ち、スノーフレークは商品、サブカテゴリ、カテゴリのような連鎖に正規化します。スターのほうが結合が少なく、使いやすくなります。

スタースキーマは正規化されていますか？ 完全には正規化されていません。ディメンションテーブルは意図的に非正規化されており、カテゴリ名のような値を各商品行に繰り返し持つ一方、ファクトテーブルは列を絞ったままです。

列指向ウェアハウスでもスタースキーマは重要ですか？ はい。列指向エンジンではワイドテーブルを安くスキャンできますが、スターには、属性を直す場所が 1 つであること、緩やかに変化するディメンションによる履歴、レポート間で一貫した答えという利点が残ります。

無料で始める — 1 シート、クレジットカード不要。