Snowflake-DELETEとTRUNCATEの違い

こんにちは！日比です！
またまた、Snowflakeの検証です。
全件削除でDELETEとTRUNCATEが使えるみたいだけど
違いってあるの？と思ったことはありませんか？
今回はそんな疑問にお答えしていきたいと思います。

本記事でわかること
検証概要
1. 検証内容
検証結果
まとめ

本記事でわかること

DELETEとTRUNCATEの違い

検証概要

検証内容

検証内容は下記のとおりです。

検証結果

測度

まず、DELETEとTRUNCATEの速度差ですが、そこまで大差はありませんでした。
OracleのようにREDOログファイルなどがないため、ほとんど大差がありません。

ウェアハウス

つぎに、ウェアハウスの挙動に違いがありました。
DELETEは、ウェアハウスが稼働したのに対し
TRUNCATEは、ウェアハウスが稼働しませんでした。
メタデータの変更だけが行われたためと思われます。
コストだけ考えると、TRUNCATEの方が良さそうですね。

ロード履歴

注意すべきは、このロード履歴の扱いです。

ロード履歴とは、CSVなどからデータをCOPY INTOした履歴になります。
デフォルトでは、このロード履歴を確認し、過去にデータを投入したファイルはスキップします。

削除対象のデータを、CSVなどから再度ロードする場合、注意が必要です。

DELETEした場合、ロード履歴は保持されます。
そのため、元ファイルのCSVに変更がない場合、データをロードしません。
再ロードするには、COPY INTOでforce=TRUEオプションが必要です。

以下、削除後のCOPY INTOの挙動の例です。
■DELETEの場合

 >delete from cp_order;
+------------------------+
| number of rows deleted |
|------------------------|
|              150000000 |
+------------------------+
150000000 Row(s) produced. Time Elapsed: 0.769s

>select count(*) from cp_order;
+----------+
| COUNT(*) |
|----------|
|        0 |
+----------+
1 Row(s) produced. Time Elapsed: 0.188s

>copy into cp_order from @my_s3_stage/order_size16m;
+------------------------------------------------------+
| status                                               |
|------------------------------------------------------|
| 0 ファイルが処理された状態でコピーが実行されました。 |
+------------------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.645s
※ロード履歴があるため、再ロードされない

■TRUNCATEの場合

>truncate table cp_order;
+----------------------------------------+
| status                                 |
|----------------------------------------|
| ステートメントは正常に実行されました。 |
+----------------------------------------+
1 Row(s) produced. Time Elapsed: 0.384s

>select count(*) from cp_order;
+----------+
| COUNT(*) |
|----------|
|        0 |
+----------+
1 Row(s) produced. Time Elapsed: 0.141s

>copy into cp_order from @my_s3_stage/order_size16m;
+------------------------------------------------------+--------+-…-+
| file                                                 | status | … |
|------------------------------------------------------+--------+-…-|
| s3://xxxxx/my_stage/order_size16m/order_0_4_0.csv.gz | LOADED | …-|
　：
+------------------------------------------------------+--------+-…-+
344 Row(s) produced. Time Elapsed: 33.942s
※ロード履歴が削除されているため、再ロードされる

time travel

こちらも関心ありますよね。
結論としては、どちらもtime travelが可能です。
戻せないと心配ですよね。安心して使えますね。

以下、time travelの実行例です。
■DELETEの場合

 >delete from cp_order;
+------------------------+
| number of rows deleted |
|------------------------|
|              150000000 |
+------------------------+
150000000 Row(s) produced. Time Elapsed: 0.769s

>select count(*) from cp_order;
+----------+
| COUNT(*) |
|----------|
|        0 |
+----------+
1 Row(s) produced. Time Elapsed: 0.188s

>select count(*) from cp_order at (timestamp => '2024-05-30 07:23:32.445 +0900'::timestamp_tz);
+-----------+
|  COUNT(*) |
|-----------|
| 150000000 |
+-----------+
※削除前のデータが参照できている

■TRUNCATEの場合

>truncate table cp_order;
+----------------------------------------+
| status                                 |
|----------------------------------------|
| ステートメントは正常に実行されました。 |
+----------------------------------------+
1 Row(s) produced. Time Elapsed: 0.384s

>select count(*) from cp_order;
+----------+
| COUNT(*) |
|----------|
|        0 |
+----------+
1 Row(s) produced. Time Elapsed: 0.141s

>select count(*) from cp_order at (timestamp => '2024-05-30 07:21:30.622 +0900'::timestamp_tz);
+-----------+
|  COUNT(*) |
|-----------|
| 150000000 |
+-----------+
※削除前のデータが参照できている