スタースキーマでは、NULL を置いてよい列と置いてはならない列が存在する。その線引きは列のデータ型では決まらず、結合・ブラウジング・識別・計算・照合という列の役割で決めなければならない。
NULL の扱いを誤ってもエラーは出ない。NULL のまま置けば結合やフィルタで行が静かに消え、一律に埋めれば埋め値が平均や範囲フィルタに紛れ込む。壊れ方が両方向にあるからこそ、役割ごとの線引きが要る。
> [!info]
> 原典は『[[The Data Warehouse Toolkit]]』(3rd Edition) 3 章の "Null Foreign Keys, Attributes, and Facts" 項。
>
> Kimball Group の [Dealing With Nulls In The Dimensional Model](https://www.kimballgroup.com/2003/02/design-tip-43-dealing-with-nulls-in-the-dimensional-model/) テクニックとしても解説されている。
> [!note]
> Adamson の『Star Schema: The Complete Reference』は、メジャーも含む全列で NULL を禁止する立場を取るが、本ノートは NULL を許容する役割を残す Kimball に従う。
>
> Kimball は、集約値での絞り込みや行ラベルの要求に応えるため、バンド (金額帯など)・バンド前の数値・真偽値といった集約属性をディメンションテーブルに置くことを認めている。全列で NULL を禁止すると、この柔軟な列配置ができなくなるためである。
## 役割別の早見表
| テーブルタイプ | 列 | 役割 | NULL |
| ------------ | ------------------------ | --------- | ---- |
| [[ファクトテーブル]] | サロゲートキー | 識別 | ❌ 禁止 |
| ファクトテーブル | 外部キー | 結合 | ❌ 禁止 |
| ファクトテーブル | [[Degenerate Dimension]] | 識別・ブラウジング | ❌ 禁止 |
| ファクトテーブル | ファクト (メジャー) | 計算 | ✅ 許容 |
| ディメンションテーブル | サロゲートキー | 識別 | ❌ 禁止 |
| ディメンションテーブル | ナチュラルキー | 照合 | ✅ 許容 |
| ディメンションテーブル | 外部キー (アウトリガー) | 結合 | ❌ 禁止 |
| ディメンションテーブル | 記述属性 | ブラウジング | ❌ 禁止 |
| ディメンションテーブル | 日付・時刻属性 | ブラウジング | ✅ 許容 |
| ディメンションテーブル | 集約属性 | 計算 | ✅ 許容 |
## NULL を禁止する役割
### 結合 (外部キー)
結合とは、キーを突き合わせてテーブル同士を繋ぐ役割である。ファクトテーブルやディメンションテーブル (アウトリガー) の外部キーが該当する。以下、会員に紐づかないゲスト注文 `odr-004`、`odr-006` を例にする。
NULL はどの値とも等しくならないため、外部キーが NULL の行は内部結合でクエリ結果から丸ごと消え、レポート間で集計が食い違う。外部結合なら行は残るが、結果セットのディメンション列に NULL が生まれ、次節のブラウジングと同じ問題が再発する。
```
fct_order
| order_key (SK) | user_key (FK) | order_id (DD) | order_amount (FA) |
| -------------- | ------------- | ------------- | ----------------- |
| 1 | 1 | odr-001 | 1200 |
| 2 | 2 | odr-002 | 3400 |
| 3 | 1 | odr-003 | 560 |
| 4 | NULL | odr-004 | 2200 | <-- ❌
| 5 | 2 | odr-005 | 900 |
| 6 | NULL | odr-006 | 1200 | <-- ❌
dim_user
| user_key (SK) | user_id (NK) | user_name |
| ------------- | ------------ | --------- |
| 1 | usr-001 | Sato |
| 2 | usr-002 | Suzuki |
```
```sql
select
dim_user.user_name,
sum(fct_order.order_amount) as total_amount
from fct_order
inner join dim_user -- または left join
on fct_order.user_key = dim_user.user_key
group by 1
```
```
inner join の場合
| user_name | total_amount |
| --------- | ------------ |
| Sato | 1760 |
| Suzuki | 4300 |
left join の場合
| user_name | total_amount |
| --------- | ------------ |
| Sato | 1760 |
| Suzuki | 4300 |
| NULL | 3400 |
```
対策はディメンションテーブルに [[Default Row]] を設置し、参照先を持たないファクト行にはその予約キーを振ることである。
```diff
fct_order
| order_key (SK) | user_key (FK) | order_id (DD) | order_amount (FA) |
| -------------- | ------------- | ------------- | ----------------- |
| 1 | 1 | odr-001 | 1200 |
| 2 | 2 | odr-002 | 3400 |
| 3 | 1 | odr-003 | 560 |
- | 4 | NULL | odr-004 | 2200 |
+ | 4 | 0 | odr-004 | 2200 |
| 5 | 2 | odr-005 | 900 |
- | 6 | NULL | odr-006 | 1200 |
+ | 6 | 0 | odr-006 | 1200 |
dim_user
| user_key (SK) | user_id (NK) | user_name |
| ------------- | ------------ | --------- |
+ | 0 | NULL | (Unknown) |
| 1 | usr-001 | Sato |
| 2 | usr-002 | Suzuki |
```
```
inner join でも left join でも同じ結果になる
| user_name | total_amount |
| --------- | ------------ |
| Sato | 1760 |
| Suzuki | 4300 |
| (Unknown) | 3400 |
```
> [!note]
> 累積スナップショットファクトテーブルの未到達マイルストーン (未出荷の出荷日など) の日付キーも同様に NULL を禁止し、遠い未来の日付 (`9999-12-31`) を表すキーに振る。
### ブラウジング (記述属性)
ブラウジングとは、値をレポートの行見出し・フィルタ・グルーピングに使う役割である。ディメンションテーブルの記述属性 (真偽値を含む) が該当する。以下、`country_name` が未入力の `Suzuki` を例にする。
属性の NULL は、レポートでの表示がツールごとに異なり、並び順の位置 (先頭か末尾か) も DB 間で揺れる。
```
fct_order
| order_key (SK) | user_key (FK) | order_id (DD) | order_amount (FA) |
| -------------- | ------------- | ------------- | ----------------- |
| 1 | 1 | odr-001 | 1200 |
| 2 | 2 | odr-002 | 3400 |
| 3 | 1 | odr-003 | 560 |
| 4 | 0 | odr-004 | 2200 |
| 5 | 2 | odr-005 | 900 |
| 6 | 0 | odr-006 | 1200 |
dim_user
| user_key (SK) | user_id (NK) | user_name | country_name |
| ------------- | ------------ | --------- | ------------ |
| 0 | NULL | (Unknown) | (Unknown) |
| 1 | usr-001 | Sato | Japan |
| 2 | usr-002 | Suzuki | NULL | <-- ❌
```
```sql
select
dim_user.country_name,
sum(fct_order.order_amount) as total_amount
from fct_order
inner join dim_user
on fct_order.user_key = dim_user.user_key
-- where dim_user.country_name != 'Japan'
group by 1
```
```
そのまま集計した場合
| country_name | total_amount |
| ------------ | ------------ |
| Japan | 1760 |
| (Unknown) | 3400 |
| NULL | 4300 |
where を有効にした場合、三値論理により NULL も一緒に静かに消えてしまう
| country_name | total_amount |
| ------------ | ------------ |
| (Unknown) | 3400 |
```
対策は `(Unknown)` や `(Not provided)` のような記述的なラベルへの置換で、語彙は DWH 全体で統一する。置換後は `!=` のような除外条件でも行が消えず、部分集計の合計が総計と一致する。
```diff
dim_user
| user_key (SK) | user_id (NK) | user_name | country_name |
| ------------- | ------------ | --------- | -------------- |
| 0 | NULL | (Unknown) | (Unknown) |
| 1 | usr-001 | Sato | Japan |
- | 2 | usr-002 | Suzuki | NULL |
+ | 2 | usr-002 | Suzuki | (Not provided) |
```
```diff
where を有効にしても行が消えない
| country_name | total_amount |
| -------------- | ------------ |
+ | (Not provided) | 4300 |
| (Unknown) | 3400 |
```
> [!note]
> ディメンションテーブルの日付・時刻属性は例外的に NULL を許容する。ラベル置換が DATE 型では使えず、`9999-12-31` のような埋め値は範囲フィルタに紛れ込み、経過日数のような日付の計算も静かに歪めるためである。
>
> ファクトテーブルに Degenerate Dimension として置くタイムスタンプは、識別を兼ねるため引き続き NULL を禁止する。
> [!note]
> 真偽値は元の値も `true` / `false` / NULL の三値しか取らないため、真偽値型のまま持たず、`Yes` / `No` / `(Unknown)` を格納した文字列型の列として提供する。
### 識別 (キーと粒度の識別子)
識別とは、その行がどの実体・どのトランザクションの記録かを一意に決める役割である。ファクトテーブルとディメンションテーブルのサロゲートキーと、ファクトテーブルの粒度を定義する識別子 (注文番号など) が該当する。
識別列は定義上 NULL があり得ず、NULL の行は何の記録か分からないため、デフォルト値で埋める対象ではなくデータ品質の問題になる。現れたら生成処理や粒度定義の不具合を疑う。Default Row も例外ではなく、サロゲートキーには `0` のような予約値を与えて NULL にしない。
> [!note]
> クーポンコードのように一部の行にしか付かない参照コードは、NULL の Degenerate Dimension として残さず、ディメンション化して Default Row で受ける。
## NULL を許容する役割
### 計算 (メジャーと集約属性)
計算とは、集計や指標の算出に値を使う役割である。ファクトテーブルのファクト (メジャー) と、ディメンションテーブルの集約属性 (Customer lifetime value のような集約済みの派生値) が該当する。
集計関数 (`SUM` / `AVG` / `COUNT`) は NULL を正しく無視するため、そのまま NULL で置く。0 で埋めると平均や比率が静かに歪む。
### 照合 (ナチュラルキー)
照合とは、ソースシステムの値と突き合わせるためだけに列を使う役割である。ディメンションテーブルのナチュラルキーが該当する。ファクトテーブルの Degenerate Dimension (注文番号) を突き合わせに使うこともあるが、行の識別を兼ねるため禁止側の識別に分類する。
NULL がどの値とも等しくならない性質は、結合では行を消す欠点だったが、照合では「誤って一致しない」という利点になる。`N/A` のような固定の埋め値は、ソースに同じ値が来たときに誤照合するリスクがある。
Default Row はソースに対応物が無い行だから、ナチュラルキーは NULL が最も正確な表現になる。結合の例の Default Row で `user_id` を NULL にしたのはこのためである。行の識別はサロゲートキーが担うため、ナチュラルキーが NULL でも行は特定できる。
## 関連ノート
- **[深掘り]** [[Default Row の行数は NULL の原因をどこまで区別するかで決める]]: 外部キーの受け皿となる Default Row を何行用意するかの設計
- **[派生]** [[ディメンションテーブルの日付属性はプレーンな DATE をデフォルトにする]]: 例外的に NULL を許容する日付属性の、型と持ち方の設計