SnowPro Advanced: Architect 無料問題 4 / 10 ・ 分野2: Snowflake Architecture
スタースキーマの標準の表で、dim_products.product_id に PRIMARY KEY を、fact_sales.product_id に dim_products を参照する FOREIGN KEY を定義し、どちらも RELY にした。その後、読み込みの手違いで dim_products に同じ product_id の行が2つ入った。このあと SELECT p.product_id, f.units_sold FROM fact_sales f, dim_products p WHERE f.product_id = p.product_id を実行する。公式ドキュメントに合うものはどれか。
- 重複の行は入るが、主キーは既定で VALIDATE なので、書き換えの前に重複が見つかって結合が残される
- 重複の行は入るが、問い合わせの結果は NORELY のときと同じで、違いが出るのは DML と CTAS が入れるデータの側である
- 重複の行は入り、この問い合わせの結果が、同じ問い合わせを NORELY で流したときの結果と食い違う
- 重複の行を入れる INSERT が、RELY の主キーに反するとして失敗し、表は元の行の数のままになる
正解と解説を見る
正解: C
正解の理由
標準の表では、Snowflake は UNIQUE・PRIMARY KEY・FOREIGN KEY の制約を強制しないので、重複した product_id の行もそのまま入る。RELY は、NOVALIDATE の状態の制約を問い合わせの書き換えに使ってよいとオプティマイザに伝える性質で、主キーと外部キーの両方が RELY なら、この問い合わせは dim_products の列を主キーのほかに使わないので、公式の例3 のとおり dim_products との結合が外される。公式は、制約の整合を保つのは利用者の責任で、保たれていないと、RELY を付けたときの問い合わせの結果が NORELY のときと違うことがあると書いている。ここでは重複のせいで、NORELY なら結合で行が増えるところ、RELY では結合が外されて行が増えず、結果が食い違う。
違う選択肢
「重複の行を入れる INSERT が、RELY の主キーに反するとして失敗し、表は元の行の数のままになる」… 標準の表では、主キーは RELY でも強制されない。主キーなどを強制するのはハイブリッドテーブルで、標準の表では重複の行が入る。
「重複の行は入るが、主キーは既定で VALIDATE なので、書き換えの前に重複が見つかって結合が残される」… 公式の制約の性質の表では、主キーと外部キーの既定は NOVALIDATE で、VALIDATE が既定なのは CHECK の制約である。RELY は、NOVALIDATE の状態の制約を書き換えに使うかを決める性質で、書き換えの前に既存の行を確かめる仕組みではない。
「重複の行は入るが、問い合わせの結果は NORELY のときと同じで、違いが出るのは DML と CTAS が入れるデータの側である」… 公式は、RELY の制約が守られないと、DML や CTAS が誤ったデータを入れることがあると書くが、それとは別に、問い合わせの結果も NORELY のときと違うことがあると書いている。違いは SELECT の結果にも出る。
押さえどころ
RELY は、データが制約に従っていると確かめたうえで付ける。主キーと外部キーが対になるときは、両方の制約に RELY を付けると公式は例で示している。