Snowflake チェック制約のオーバーヘッド

1. はじめに

Snowflake に 4/3 ～ 4/8 の期間で展開されたリリース 10.12 において、チェック制約が導入されました。

Snowflake をはじめとするクラウド DWH （Redshift や BigQuery などを含む）では、以下の状態が長く続いていました。

• 主キー/一意キー制約：存在するけど実際にデータの検証はしない
• 外部キー制約：存在するけど、実際にはデータの検証はしない
• NOT NULL 制約：存在するし、データの検証も行う
• チェック制約：そもそも機能として存在しない

主キー/一意キー制約と外部キー制約に関しては、あるレコードが制約条件を満たすかチェックするために別のレコードを参照する必要があるため、データの追加・更新時にリアルタイムでチェックするのは処理時間が伸びるというデメリットが多いため、制約によってデータを検証するのではなく、後から一括で検証する必要がありました。

OracleDB などの通常のトランザクション指向の RDBMS の経験がある方なら、主キーを作成したままデータロードを行うと処理時間が一桁ぐらい延びるという経験をしたことはあると思います。

一方で、チェック制約に関しては、単一レコード内で完結して検証できるため大きな性能劣化は起きにくいはずですが、Snowflake にチェック制約が導入されたのを機に、実際にどれぐらいの性能劣化（オーバーヘッド）があるか測ってみます。

先に結論を示します。

• チェック制約なしテーブルへの INSERT： 22.2 秒
• 7 個のチェック制約ありテーブルへの INSERT：23.0 秒

結果として、わずかなオーバーヘッドはあるが、実用上はほとんど気にならない差だと思います。

2. 実測

元データテーブル（TPC-H の lineitem テーブルを元に作成）から制約なしテーブルと制約ありテーブルに INSERT ... SELECT * FROM ... を実行することでチェック制約のオーバーヘッドを確認しようと思います。

2-1. 実測環境

• Snowflake リージョン：AWS オレゴン
• エディション：Standard
• 仮想ウェアハウスタイプ：標準 Gen1 （昔に作ったものを使いまわしたため、Gen1 です）
• 仮想ウェアハウスサイズ：XSmall

2-2. データ準備

まず、TPC-H の lineitem テーブル（スケールファクター = 10）をもとに、INSERT 用のデータを生成します。後でチェック制約に利用するため一部データは WHERE 句で除外します。これにより 53,437,654 件 / 約 1.2 GB（圧縮済み） のデータが作成されます。

create or replace table lineitem
as
select * from snowflake_sample_data.tpch_sf10.lineitem
where l_quantity != 25
and   l_discount != 0.05;

次にこのテーブルと同じテーブル定義を持つ空テーブル 2 つを作成します。lineitem_w_cc はチェック制約ありテーブル（後から制約を付与）、lineitem_wo_cc は制約なしテーブルの想定です。

create or replace table lineitem_w_cc like snowflake_sample_data.tpch_sf10.lineitem;
create or replace table lineitem_wo_cc like snowflake_sample_data.tpch_sf10.lineitem;

制約ありテーブル lineitem_w_cc に対して以下の 7 つのチェック制約を付与します。Snowflake は列指向でデータを保持するため、複数列にまたがるチェック制約はよりオーバーヘッドが大きいかもと思い、単一列制約 4 つに加え、複数列にまたがる制約を 3 つ含めています。元データはこれらの制約条件をすべて満たします。

-- l_quantity が 25 以外（元データ作成時に l_quantity = 25 のレコードは除外済み）
alter table lineitem_w_cc add constraint cc_quantity check (l_quantity != 25) enable novalidate;

-- l_discount が 0.05 以外（元データ作成時に l_discount = 0.05 のレコードは除外済み）
alter table lineitem_w_cc add constraint cc_discount check (l_discount != 0.05) enable novalidate;

-- cc_returnflag は 'A' 'N' 'R' のいずれか
alter table lineitem_w_cc add constraint cc_returnflag check (l_returnflag in ('A', 'N', 'R')) enable novalidate;

-- l_linestatus は 'F' または 'O'
alter table lineitem_w_cc add constraint cc_linestatus check (l_linestatus in ('F', 'O')) enable novalidate;

-- l_shipdate（出荷日）は l_receiptdate（受取日）以前である ※複数列にまたがる制約
alter table lineitem_w_cc add constraint cc_date check(l_shipdate <= l_receiptdate) enable novalidate;

--l_extendedprice/l_quantity（つまり単価）は 900 以上 ※複数列にまたがる制約
alter table lineitem_w_cc add constraint cc_unitpricre check (l_extendedprice/l_quantity > 900) enable novalidate;

-- l_discount - l_tax（つまり税込みでの割引率）は 10% 以下 ※複数列にまたがる制約
alter table lineitem_w_cc add constraint cc_discount_tax check (l_discount - l_tax <= 0.1) enable novalidate;

ちなみに、enable novalidate オプションを付与していますが、これをつけない場合は以下のエラーになります。

ALTER TABLE ADD CHECK is not supported with ENABLE VALIDATE (you may specify ENABLE NOVALIDATE instead).

この挙動はドキュメントにも記載があります。

VALIDATE isn’t supported on existing tables.

作成したチェック制約は以下で確認することができます。

select * from information_schema.check_constraints;

また、チェック制約が正しく機能することを確認するために以下のクエリを実行しています。すべて制約違反で失敗します。

insert into lineitem_w_cc (l_quantity) values (25);
insert into lineitem_w_cc (l_discount) values (0.05);
insert into lineitem_w_cc (l_returnflag) values ('M');
insert into lineitem_w_cc (l_linestatus) values ('M');
insert into lineitem_w_cc (l_shipdate, l_receiptdate) values ('2026-04-12', '2026-04-11');
insert into lineitem_w_cc (l_extendedprice, l_quantity) values (1800, 2);
insert into lineitem_w_cc (l_discount, l_tax) values (0.12, 0.01);

これで準備を完了しました。

2-3. 測定結果

以下の 2 つの処理を、十分な間隔を空けて（仮想ウェアハウスが停止し、キャッシュが削除されるまで）、各処理を 3 回ずつ実行しました。

制約なしテーブルへの INSERT

insert into lineitem_wo_cc select * from lineitem;

制約ありテーブルへの INSERT

insert into lineitem_w_cc select * from lineitem;

それぞれの実行時間を query_history テーブル関数から取得します。

select
    query_text,
    to_char(end_time, 'yyyy-mm-dd hh24:mi') end_time,
    round(total_elapsed_time / 1000, 1) response_time_s
from
    table(information_schema.query_history(
        end_time_range_start => to_timestamp_ltz('2026-04-12 14:42', 'yyyy-mm-dd hh24:mi'),
        end_time_range_end   => to_timestamp_ltz('2026-04-12 15:30', 'yyyy-mm-dd hh24:mi')
    ))
where
    query_text like 'insert%'
order by 1,2;

3 回の実行時間の平均を求めると、本記事冒頭で示した結果になります。

• チェック制約なしテーブルへの INSERT： 22.2 秒
• 7 個のチェック制約ありテーブルへの INSERT：23.0 秒

0.8 秒の差がチェック制約によるオーバーヘッドと考えられます。誤差の範囲かもしれませんが、いずれにせよオーバーヘッドは想像通り小さいようです。

3. なぜ今までなかったのか？

オーバーヘッドが小さいのであれば、元々からチェック制約の機能はあっても良いと思うのですが、クラウド DWH では長いこと存在しませんでした。

これはチェック制約に限らず、主キー/一意キー制約や外部キー制約も含めて Write-Audit-Publish（WAP）パターンで検証するというのが、データパイプラインにおける基本方針であったため、必要ないと考えられていたのだと思います。

1. Write：制約のないテーブルに結果を書き込む
2. Audit：結果データが制約を満たしているか SELECT 文でチェックする
3. Publish：結果データが制約を満たしていることを確認できたら、ユーザーや下流パイプラインに公開する

以下でも解説しています。

チェック制約によるデータ検証と WAP パターンによるデータ検証のメリットには以下があると思います

• WAP パターンのメリット
  • 主キー/一意キー制約や外部キー制約も含めて検証しやすい
  • 非決定的なチェックが可能（チェック制約では current_timestamp など非決定的関数が利用不可）
  • 制約エラーが発生しても結果データセットは残るため、エラーの原因調査がしやすい
• チェック制約のメリット
  • 簡単に検証の仕組みを実装できる（WAP は検証のために SELECT 文を作る必要がある）
  • 即時エラーにできるため、計算リソースを節約できる
  • 複数の条件をデータ投入時にまとめてチェックできる（WAP パターンだと 1 つの検証 SQL にまとめる必要あり）
  • テーブル定義に制約条件を含められる

大半のケースでは WAP パターンで十分ともいえますが、、手段が増えた点は歓迎したいと思います。