はじめに
SnowflakeなどのDWH製品はSQLで操作できるため、一見すると一般的なRDBと同じ感覚で使えるように見えます。
しかし、実際には目的や設計思想が異なるため、RDBの前提をそのまま持ち込むと違和感を覚えることがあります。
SQL ServerなどのRDB製品のあとに、Snowflakeをさわってみて感じた、RDBとDWHの違いを整理しました。
以前にも似たような記事を執筆しましたが、今回はより実践よりの内容にしてみました。
参考：業務システムと分析システムの違いについて #データ分析 - Qiita
前提として、用語は次のように定義します。（厳密にはDWHもRDBの一部では？と突っ込まれるかもですが、本記事ではこのように区分けすることにします。）
- RDB（Relational Database）
- 日々の業務データを正確に保存・更新するためのデータベース
- 整合性・正確性が最優先
- OLTPの基盤として利用されることが多い
- 例：SQL Server、MySQL、PostgreSQL、Oracle Databaseなど
- DWH（Data Warehouse）
- 企業内の大量データを集約し、分析しやすい形で蓄積するためのデータ基盤
- 分析効率を重視
- OLAPの基盤として利用されることが多い
- 例：Snowflake、Amazon Redshift、BigQuery、Databricksなど
主キーは存在するが、制約としては機能しない
RDBでは主キー（PK）は一意性を保証する重要な制約ですが、DWHにおけるPKは「論理的な定義」に近く、制約としては強制されません。
定義しても重複データはそのまま登録できてしまいます。
DWH製品であるSnowflakeの公式ページには次のように書かれています。
Snowflakeは、制約の定義と維持をサポートしていますが、常に強制される NOT NULL 制約以外については強制しません。
（中略）
標準テーブルの制約は、主にデータモデリングの目的と他のデータベースとの互換性を持たせるため、および制約を利用するクライアントツールをサポートするために提供されます。
参考：制約 | Snowflake Documentation
互換性やメタデータ用途のために設定はできますが、NOT NULLを除き制約としては強制されません、ということのようです。
Snowflakeでは、制約によってデータを守るというより、ELTや運用ルールで整合性を担保する考え方になります。
ちなみにSnowflakeには標準テーブルとは別に「ハイブリッドテーブル」という特殊なテーブルがあります。このハイブリッドテーブルは、Snowflakeの中でもOLTP寄りの用途を想定した特殊なテーブルで、PKを制約として設定できるようです。
参考：ハイブリッドテーブル | Snowflake Documentation
回避例１：INSERT時に GROUP BY で重複を潰す
INSERTの段階で同一キーをまとめてしまう方法です。
INSERT INTO users_latest
SELECT
user_id
,MAX(updated_at) AS updated_at
,ANY_VALUE(user_name) AS user_name
FROM
users
GROUP BY
user_id
;
同一 user_id が複数あっても1行に集約します。
回避例２：INSERT後に重複チェックして例外を投げる
チェック用クエリを別途実行し、重複があったら処理自体を失敗する方法です。
SELECT
user_id
,COUNT(*) AS cnt
FROM
users
GROUP BY
user_id
HAVING
COUNT(*) > 1
;
このクエリが1件でも返った場合は異常と判断します。
- 運用イメージ
- 上記SQLを実行
- 結果が0件 → 正常終了
- 結果が1件以上 → 例外をスローしロールバック、ジョブ失敗の通知を出す
インデックスが存在しない
RDBではインデックス設計がパフォーマンスに直結しますが、DWH製品であるSnowflakeにはRDBのようにユーザーが明示的に設計・管理するインデックスは存在せず、代わりに自動的に最適化が行われます。
そのため、事前に細かくチューニングするより、スケールさせて処理する設計が前提となります。
PKと同様、ハイブリッドテーブルであればインデックスは設定できるようです。
参考：ハイブリッドテーブルのインデックス作成 | Snowflake Documentation
RDBでインデックスを貼る例
たとえば users テーブルから user_id = 123 を検索したい場合です。
SELECT *
FROM users
WHERE user_id = 123
;
このテーブルに何千万件ものレコードが存在する場合、通常はつぎのようにインデックスを作成します。
CREATE INDEX idx_users_user_id
ON users(user_id)
;
このインデックスがないと、テーブル全体をスキャンするため遅くなります。
Snowflakeの場合
Snowflakeではさまざまな仕組みによりクエリを高速化しています。
-
マイクロパーティションで不要なデータを読まない
- Snowflakeではデータをパーティションに分けて保存し、各パーティションのmin/max値・NULL率・分布を自動で記録している
- WHEREの範囲に該当しないパーティションは最初から読まないため速い
-
列指向ストレージで必要な列だけ読む
- Snowflakeは列ごとにデータを保存する列指向
- 必要な列だけを読み取り、不要な列は読まないため速い
-
Warehouseのスケールで速くする
- SnowflakeはWarehouseサイズを変えることで、CPUやメモリなどの計算リソースを増やせる
- インデックスで速くするのではなく、計算リソースで速くするという思想
-
キャッシュが効く
- 同じクエリを再実行すると結果をそのまま返すため爆速（結果キャッシュ）
- 一度読んだデータはWarehouseのメモリに残り、同じパーティションを読むクエリは速い（データキャッシュ）
GROUP BY ALL が使える
DWH製品であるSnowflakeでは、GROUP BY ALL が利用でき、SELECT句に書いた非集約列を自動的に GROUP BY 対象にできます。
これはとても便利ですが、他のDBでは使えないケースが多く、SQLの移植性は下がります。
そのため、 GROUP BY ALL は使いどころを意識する必要があります。
通常の GROUP BY の例
すべての非集約列を明示的に GROUP BY に書く必要があります。
SELECT
category
,sub_category
,COUNT(*) AS item_count
FROM
items
GROUP BY
category
,sub_category
;
GROUP BY ALL の例
SELECT句に書いた非集約列を、Snowflakeが自動で GROUP BY に含めます。
SELECT
category
,sub_category
,COUNT(*) AS item_count
FROM
items
GROUP BY ALL
;
SELECT句内で定義した列エイリアスを同じSELECT内で使える
Snowflakeでは、SELECT句で定義したエイリアスを、同じSELECT句の別の式で参照できます。（Lateral Column Alias）
これは多くのRDBではできません。
Snowflakeは、SELECT句を上から順に評価するため可能になっています。
一般的なRDBでの書き方
amount_with_tax で amount は参照できないので、 price * quantity を繰り返し記述する必要があります。
SELECT
price
,quantity
,price * quantity AS amount
,(price * quantity) * 1.1 AS amount_with_tax -- amountを参照できない
FROM
sales
;
Snowflakeの例
amount_with_tax で amount を参照できるので、 price * quantity を繰り返し記述する必要がありません。
SELECT
price
,quantity
,price * quantity AS amount
,amount * 1.1 AS amount_with_tax -- amountを参照できる
FROM
sales
;
ただし、Snowflakeでもつぎの場合はエラーとなります。
SELECT
price
,quantity
,amount * 1.1 AS amount_with_tax -- amountが先に定義されていないためエラー
,price * quantity AS amount
FROM
sales
;
おまけ：他製品との比較
RDB系製品とDWH系製品をいくつかピックアップして比較をしてみました。
実際の環境での検証は行っていないため、情報が最新でない場合や解釈の誤りを含む可能性があります。参考程度に読んでいただければ幸いです。
PK定義
整合性・正確性を優先するRDB系はPKが設定できますが、分析向けに使用するDWH系はPKが定義できても機能しないものが多いようです。
| 分類 | 製品名 | PK定義 | 強制 | 参考リンク |
|---|---|---|---|---|
| RDB | SQL Server | できる | される | SQL Server で主キーを作成する - SQL Server - Microsoft Learn |
| RDB | MySQL | できる | される | MySQL :: MySQL 8.0 リファレンスマニュアル :: 24.6.1 パーティショニングキー、主キー、および一意キー |
| RDB | PostgreSQL | できる | される | PostgreSQL 17.6文書 - 5.5. 制約 |
| RDB | Oracle Database | できる | される | Oracle AI Database 26ai - データ整合性 |
| DWH | Snowflake | できる | されない | 制約 - Snowflake Documentation |
| DWH | Amazon Redshift | できる | されない | プライマリキーおよび外部キーの制約の定義 - Amazon Redshift |
| DWH | BigQuery | できる | されない | 主キーと外部キーを使用する - BigQuery - Google Cloud Documentation |
| DWH | Databricks | できる | されない | Databricksの制約 - Databricks on Google Cloud |
調べる中で、数年前まではDWH系ではそもそもPKを定義できないものが多かったようですが、ここ数年でPK定義はできる（が機能しない）のが主流になっていったような感じがしました。
インデックス
RDB系ではユーザー側でインデックスを設計する必要があり、設計次第で性能が大きく変わります。
一方DWH系ではユーザー側でインデックスを設定する必要はなく、自動で最適化されるものが多いようです。
| 分類 | 製品名 | インデックス | 参考リンク |
|---|---|---|---|
| RDB | SQL Server | あり | Indexes - SQL Server - Microsoft Learn |
| RDB | MySQL | あり | MySQL :: MySQL 8.0 リファレンスマニュアル :: 8.3 最適化とインデックス |
| RDB | PostgreSQL | あり | PostgreSQL 17.6文書 - 第11章 インデックス |
| RDB | Oracle Database | あり | Oracle AI Database 26ai - 索引の管理 |
| DWH | Snowflake | なし | ただしハイブリッドテーブルであれば設定可能 |
| DWH | Amazon Redshift | なし | - |
| DWH | BigQuery | なし | RDBのようなインデックスはないが、検索インデックス、ベクトルインデックスというものはある |
| DWH | Databricks | なし | - |
GROUP BY ALL
RDB系では使えない製品が多く、DWH系では使える製品が多いようです。
| 分類 | 製品名 | GROUP BY ALL | 参考リンク |
|---|---|---|---|
| RDB | SQL Server | 使えない | グループ別(Transact-SQL) - SQL Server - Microsoft Learn |
| RDB | MySQL | 使えない | MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.2.10 SELECT ステートメント |
| RDB | PostgreSQL | 使えない | PostgreSQL 17.6文書 - SELECT |
| RDB | Oracle Database | 使える | GROUP BY ALL in Oracle Database 23ai/26ai - ORACLE-BASE |
| DWH | Snowflake | 使える | GROUP BY - Snowflake Documentation |
| DWH | Amazon Redshift | 使える | GROUP BY 句 - Amazon Redshift |
| DWH | BigQuery | 使える | Query syntax - BigQuery - Google Cloud Documentation |
| DWH | Databricks | 使える | GROUP BY clause - Databricks on AWS |
ただしRDB系でも徐々に使えるような流れになっていそうでした。
PostgreSQLは現時点では未サポートで、将来バージョン（PostgreSQL 19以降）での対応が検討されています。
参考：Waiting for PostgreSQL 19 – Add GROUP BY ALL. – select * from depesz;
SQL Serverに GROUP BY ALL の記述はありますが、Snowflakeなどとは異なる挙動らしく、現在は非推奨（将来のバージョンで削除予定）の構文のようです。
-- For backward compatibility only.
-- Non-ISO-Compliant Syntax for SQL Server and Azure SQL DatabaseGROUP BY {
ALL column-expression [ ,...n ]
| column-expression [ ,...n ] WITH { CUBE | ROLLUP }
}
Lateral Column Alias（LCA）
これに関しては公式ドキュメントでの明確な情報が少ないため、実検証や公開情報をもとに整理しています。
| 分類 | 製品名 | LCA | 参考リンク |
|---|---|---|---|
| RDB | SQL Server | できない | sql - Lateral aliasing in databases - Stack Overflow ※2022年8月時点 |
| RDB | MySQL | できない | mysql - Using Column Alias in Same SELECT Clause - Stack Overflow ※2015年3月時点 |
| RDB | PostgreSQL | できない | postgresql - Reference column alias in same SELECT list - Database Administrators Stack Exchange ※2015年3月時点 |
| RDB | Oracle Database | できない | なし（Copilot、ChatGPT、Gemini） |
| DWH | Snowflake | できる | 実際にSnowflakeで検証 |
| DWH | Amazon Redshift | できる | Redshift Lateral Column Alias - Derived Column - DWgeek.com |
| DWH | BigQuery | できない | [Feature] Referencing by alias in the same SELECT statement - Google Cloud / Data Analytics - Google Developer forums ※2024年12月時点 |
| DWH | Databricks | できる | Introducing the Support of Lateral Column Alias - Databricks Blog |
ちょっと情報は怪しいですが、こちらもざっくりとRDB系はできないものが多く、DWH系はできるものが多そうです。
いくつかのAIではBigQueryはできるようになっていると回答がありましたが、さっと探したところ公式でそれらしき記載は見つけられませんでした。
おわりに
SnowflakeなどのDWH製品は「RDBの代替」ではなく、分析に特化したDWHとして設計されています。
この前提を意識するだけで、設計や運用の違いが分かるのではないでしょうか。