概要
Snowflake にて、２つのテーブルが同等であること（一致すること）を確認する方法の手順を共有します。
ETL 処理の検証やデータ移行時の整合性確認など、テーブル間の差分を確認したい場面で活用できます。
２つのテーブルの同等性を確認するための方針
２つのテーブルの同等性を確認するため、下記の３つのチェックを実施することとしました。
- 件数の一致を確認すること
- キー項目ごとに重複がないことを確認すること
- EXCEPT によりレコードの差分がないことを確認すること
２．のキー項目重複チェックを追加した理由
下記のようなデータでは、１．の件数チェックと３．の EXCEPT による差分チェックだけでは差異を検出できません。両テーブルとも件数は 4 件で一致しており、また EXCEPT 演算子は重複レコードを差分として取得しないためです。
なお、片方のテーブルで重複がないことを確認するだけでも差異の検出は可能です。しかし、既存のバグ等により両テーブルで重複が発生している可能性を考慮し、両方のテーブルでチェックを行うこととしました。
３．の EXCEPT による差分チェックの目的
EXCEPT による差分チェックの主な目的は、差分レコードの特定です。通常の運用では「２つのテーブルに差分がある」という事実だけでは不十分で、どのレコードに問題があるのかを特定する必要があります。
EXCEPT (MINUS) 演算子は比較的負荷の高い処理ですが、差分の特定には必要な処理であると考えました。
実施手順の事前準備
データベースオブジェクトの作成
検証用のデータベースとスキーマを作成します。
CREATE DATABASE IF NOT EXISTS DATA_CHECK_01;
CREATE SCHEMA IF NOT EXISTS DATA_CHECK_01.SCHEMA_01;
USE DATABASE DATA_CHECK_01;
USE SCHEMA DATA_CHECK_01.SCHEMA_01;
続いて、比較対象となる２つのテーブルを作成します。
CREATE OR REPLACE TABLE DATA_CHECK_01.SCHEMA_01.ACTUAL_TABLE_01 (
ID NUMBER,
NAME STRING,
AMOUNT NUMBER(10,2),
STATUS STRING,
UPDATED_AT TIMESTAMP_NTZ
);
CREATE OR REPLACE TABLE DATA_CHECK_01.SCHEMA_01.EXPECTED_TABLE_01 (
ID NUMBER,
NAME STRING,
AMOUNT NUMBER(10,2),
STATUS STRING,
UPDATED_AT TIMESTAMP_NTZ
);
テーブルの同等性確認用 SQL ファイルの作成
SQL ファイルを作成し、下記のコードを貼り付けます。
コードが長いため、こちらを選択
-- =========================================================
-- 比較するテーブルの情報をセット
-- =========================================================
-- 比較対象テーブルをセット
SET ACTUAL_TABLE_NAME = 'DATA_CHECK_01.SCHEMA_01.ACTUAL_TABLE_01';
SET EXPECTED_TABLE_NAME = 'DATA_CHECK_01.SCHEMA_01.EXPECTED_TABLE_01';
-- キー項目をセット
SET KEY_COLS = 'ID';
-- 比較対象カラムをセット
-- セッション変数の最大サイズが 256 bytes であるため一時テーブルに格納
CREATE OR REPLACE TEMPORARY TABLE WK_EXCEPT_CHECK_COLS AS
SELECT
'ID, NAME, AMOUNT, STATUS' AS COLS;
-- =========================================================
-- 1. 件数一致チェック
-- =========================================================
-- 1-1. 件数確認の処理を実行
CREATE OR REPLACE TEMPORARY TABLE WK_ROW_COUNT_CHECK AS
SELECT
'ROW_COUNT_CHECK' AS CHECK_NAME,
$ACTUAL_TABLE_NAME AS ACTUAL_TABLE_NAME,
$EXPECTED_TABLE_NAME AS EXPECTED_TABLE_NAME,
A.CNT AS ACTUAL_TABLE_COUNT,
B.CNT AS EXPECTED_TABLE_COUNT,
A.CNT - B.CNT AS COUNT_DIFF,
CASE
WHEN A.CNT = B.CNT THEN 'OK'
ELSE 'NG'
END AS RESULT
FROM
(
SELECT COUNT(*) AS CNT
FROM IDENTIFIER($ACTUAL_TABLE_NAME)
) A
CROSS JOIN
(
SELECT COUNT(*) AS CNT
FROM IDENTIFIER($EXPECTED_TABLE_NAME)
) B;
-- 1-2. データの確認
SELECT * FROM WK_ROW_COUNT_CHECK;
-- 1-3. 結果確認
DECLARE
V_NG_COUNT NUMBER;
E_ROW_COUNT_MISMATCH EXCEPTION (
-20001,
'ROW_COUNT_CHECK failed. See WK_ROW_COUNT_CHECK for details.'
);
BEGIN
SELECT COUNT(*)
INTO :V_NG_COUNT
FROM WK_ROW_COUNT_CHECK
WHERE RESULT = 'NG';
IF (V_NG_COUNT > 0) THEN
RAISE E_ROW_COUNT_MISMATCH;
END IF;
RETURN 'ROW_COUNT_CHECK passed';
END;
-- =========================================================
-- 2. キーごとの重複チェック
-- =========================================================
-- =========================================================
-- 2. キーごとの重複チェック
-- =========================================================
-- 2-1. キーごとに重複しているレコードを一時テーブルに格納
EXECUTE IMMEDIATE $$
DECLARE
SQL_TEXT STRING;
-- キー項目
KEY_COLS STRING;
-- 比較対象カラム
COLS STRING;
ACTUAL_TABLE_NAME STRING;
EXPECTED_TABLE_NAME STRING;
BEGIN
KEY_COLS := $KEY_COLS;
SELECT COLS
INTO :COLS
FROM WK_EXCEPT_CHECK_COLS;
ACTUAL_TABLE_NAME := $ACTUAL_TABLE_NAME;
EXPECTED_TABLE_NAME := $EXPECTED_TABLE_NAME;
SQL_TEXT := '
CREATE OR REPLACE TEMPORARY TABLE WK_DUPLICATE_KEY_CHECK AS
SELECT DISTINCT
CHECK_NAME,
TABLE_NAME,
DUPLICATE_COUNT,
' || COLS || '
FROM (
SELECT
''DUPLICATE_KEY_CHECK'' AS CHECK_NAME,
''' || ACTUAL_TABLE_NAME || ''' AS TABLE_NAME,
COUNT(*) OVER (
PARTITION BY ' || KEY_COLS || '
) AS DUPLICATE_COUNT,
' || COLS || '
FROM ' || ACTUAL_TABLE_NAME || ' T
)
WHERE DUPLICATE_COUNT > 1
UNION ALL
SELECT DISTINCT
CHECK_NAME,
TABLE_NAME,
DUPLICATE_COUNT,
' || COLS || '
FROM (
SELECT
''DUPLICATE_KEY_CHECK'' AS CHECK_NAME,
''' || EXPECTED_TABLE_NAME || ''' AS TABLE_NAME,
COUNT(*) OVER (
PARTITION BY ' || KEY_COLS || '
) AS DUPLICATE_COUNT,
' || COLS || '
FROM ' || EXPECTED_TABLE_NAME || ' T
)
WHERE DUPLICATE_COUNT > 1
';
EXECUTE IMMEDIATE SQL_TEXT;
END;
$$;
-- 2-2. データの確認
SELECT *
FROM WK_DUPLICATE_KEY_CHECK
ORDER BY TABLE_NAME;
-- 2-3. 結果確認
DECLARE
V_NG_COUNT NUMBER;
E_DUPLICATE_KEY_FOUND EXCEPTION (
-20003,
'DUPLICATE_KEY_CHECK failed. See WK_DUPLICATE_KEY_CHECK for details.'
);
BEGIN
SELECT COUNT(*)
INTO :V_NG_COUNT
FROM WK_DUPLICATE_KEY_CHECK;
IF (V_NG_COUNT > 0) THEN
RAISE E_DUPLICATE_KEY_FOUND;
END IF;
RETURN 'DUPLICATE_KEY_CHECK passed';
END;
-- =========================================================
-- 3. EXCEPT によりレコードの差分がないこと
-- =========================================================
-- 3-1. データ一致確認の処理を実行
DECLARE
-- 比較対象カラム
cols STRING;
-- セッション変数から受けるローカル変数
actual_table_name STRING;
expected_table_name STRING;
sql_text STRING;
BEGIN
actual_table_name := $ACTUAL_TABLE_NAME;
expected_table_name := $EXPECTED_TABLE_NAME;
SELECT COLS
INTO :cols
FROM WK_EXCEPT_CHECK_COLS;
sql_text := '
CREATE OR REPLACE TEMPORARY TABLE WK_EXCEPT_CHECK AS
SELECT
''EXCEPT_CHECK'' AS CHECK_NAME,
''' || actual_table_name || '_MINUS_' || expected_table_name || ''' AS DIFF_TYPE,
' || cols || '
FROM (
SELECT
' || cols || '
FROM ' || actual_table_name || '
EXCEPT
SELECT
' || cols || '
FROM ' || expected_table_name || '
)
UNION ALL
SELECT
''EXCEPT_CHECK'' AS CHECK_NAME,
''' || expected_table_name || '_MINUS_' || actual_table_name || ''' AS DIFF_TYPE,
' || cols || '
FROM (
SELECT
' || cols || '
FROM ' || expected_table_name || '
EXCEPT
SELECT
' || cols || '
FROM ' || actual_table_name || '
)';
EXECUTE IMMEDIATE sql_text;
END;
-- 3-2. データの確認
SELECT * FROM WK_EXCEPT_CHECK;
-- 3-3. 結果確認
DECLARE
V_NG_COUNT NUMBER;
E_EXCEPT_MISMATCH EXCEPTION (
-20002,
'EXCEPT_CHECK failed. See WK_EXCEPT_CHECK for details.'
);
BEGIN
SELECT COUNT(*)
INTO :V_NG_COUNT
FROM WK_EXCEPT_CHECK;
IF (V_NG_COUNT > 0) THEN
RAISE E_EXCEPT_MISMATCH;
END IF;
RETURN 'EXCEPT_CHECK passed';
END;
-- =========================================================
-- 最終処理
-- =========================================================
SELECT 'ALL OK' AS Message;
テーブル情報の設定
比較するテーブル名、比較対象のカラム、キー項目をセットします。
比較対象のカラムを明示的に指定する理由は、監査列のように動的な値がセットされる列（本記事の例では最終更新日列である UPDATED_AT）を比較対象から除外するためです。
-- =========================================================
-- 比較するテーブルの情報をセット
-- =========================================================
-- 比較対象テーブルをセット
SET ACTUAL_TABLE_NAME = 'DATA_CHECK_01.SCHEMA_01.ACTUAL_TABLE_01';
SET EXPECTED_TABLE_NAME = 'DATA_CHECK_01.SCHEMA_01.EXPECTED_TABLE_01';
-- キー項目をセット
SET KEY_COLS = 'ID';
-- 比較対象カラムをセット
-- セッション変数の最大サイズが 256 bytes であるため一時テーブルに格納
CREATE OR REPLACE TEMPORARY TABLE WK_EXCEPT_CHECK_COLS AS
SELECT
'ID, NAME, AMOUNT, STATUS' AS COLS;
想定利用手順
テーブル同等性確認用の SQL ファイルを Run all で実行し、正常終了することを確認します。
エラーが発生した場合は、Query History からエラーとなった処理の１つ下を選択することで原因を確認できます。目視での確認となりますが、下記の例では NAME 列の値が一致していないことを特定できます。
ケース別の動作確認
各チェックがどのように差分を検出するかを、４つのケースで確認します。
ケース1: ＜異常系＞件数が一致しないケース
件数が一致しないデータで上書きします。
-- =========================================================
-- 件数が一致しないケース
-- =========================================================
INSERT OVERWRITE INTO ACTUAL_TABLE_01
(ID, NAME, AMOUNT, STATUS, UPDATED_AT)
VALUES
(1, 'Alice', 100.00, 'ACTIVE', '2025-12-01 10:00:00'),
(2, 'Bob', 200.00, 'ACTIVE', '2025-12-01 11:00:00');
INSERT OVERWRITE INTO EXPECTED_TABLE_01
(ID, NAME, AMOUNT, STATUS, UPDATED_AT)
VALUES
(1, 'Alice', 100.00, 'ACTIVE', '2026-04-01 10:00:00'),
(2, 'Bob', 200.00, 'ACTIVE', '2026-04-01 11:00:00'),
(3, 'Carol', 300.00, 'INACTIVE', '2026-04-01 12:00:00');
SQL ファイルを実行すると、1. 件数一致チェック の 1-3. 結果確認 でエラーが発生します。
Query History からエラーとなった処理の１つ下の内容を確認すると、ACTUAL_TABLE_NAME の件数が 2 であるのに対し、EXPECTED_TABLE_NAME の件数が 3 であり、差分があることを確認できます。
ケース2: ＜異常系＞件数は一致するが、キー項目に重複があるケース
件数は一致するが、キー項目に重複があるデータで上書きします。
-- =========================================================
-- 件数は一致するが、キー項目が一致しないケース
-- =========================================================
INSERT OVERWRITE INTO ACTUAL_TABLE_01
(ID, NAME, AMOUNT, STATUS, UPDATED_AT)
VALUES
(1, 'Alice', 100.00, 'ACTIVE', '2025-12-01 10:00:00'),
(2, 'Bob', 200.00, 'ACTIVE', '2026-04-01 11:00:00'),
(2, 'Bob', 200.00, 'ACTIVE', '2026-04-01 11:00:00'),
(3, 'Carol', 300.00, 'INACTIVE', '2026-04-01 12:00:00');
INSERT OVERWRITE INTO EXPECTED_TABLE_01
(ID, NAME, AMOUNT, STATUS, UPDATED_AT)
VALUES
(1, 'Alice', 100.00, 'ACTIVE', '2026-04-01 10:00:00'),
(2, 'Bob', 200.00, 'ACTIVE', '2026-04-01 11:00:00'),
(3, 'Carol', 300.00, 'INACTIVE', '2026-04-01 12:00:00'),
(3, 'Carol', 300.00, 'INACTIVE', '2026-04-01 12:00:00');
SQL ファイルを実行すると、2. キー項目重複チェック の結果確認でエラーが発生します。
Query History からエラーとなった処理の１つ下の内容を確認すると、重複しているキーを特定できます。
ケース3: ＜異常系＞件数とキー項目は問題ないが、項目値が一致しないケース
件数とキー項目は問題ないが、項目値が一致しないデータで上書きします。
-- =========================================================
-- 件数とキー項目が OK だが項目値が一致しないケース
-- =========================================================
INSERT OVERWRITE INTO ACTUAL_TABLE_01
(ID, NAME, AMOUNT, STATUS, UPDATED_AT)
VALUES
(1, 'Alice', 100.00, 'ACTIVE', '2025-12-01 10:00:00'),
(2, 'Bob', 200.00, 'ACTIVE', '2025-12-01 11:00:00'),
(3, 'XXXXX', 300.00, 'INACTIVE', '2025-12-01 12:00:00');
INSERT OVERWRITE INTO EXPECTED_TABLE_01
(ID, NAME, AMOUNT, STATUS, UPDATED_AT)
VALUES
(1, 'Alice', 100.00, 'ACTIVE', '2026-04-01 10:00:00'),
(2, 'Bob', 200.00, 'ACTIVE', '2026-04-01 11:00:00'),
(3, 'Carol', 300.00, 'INACTIVE', '2026-04-01 12:00:00');
SQL ファイルを実行すると、3. EXCEPT による差分チェック の 3-3. 結果確認 でエラーが発生します。
Query History からエラーとなった処理の１つ下の内容を確認すると、目視での確認とはなりますが、NAME 列の値が一致していないことを特定できます。
差分データの抽出が不要な場合には、 HASH_AGG 関数による処理を実行した方が高速に処理できる可能性が高いです。本記事では、テスト時に利用することを想定していたため、差分抽出を実施しています。
WITH ACTUAL_BY_KEY AS (
SELECT
ID,
COUNT(*) AS CNT,
HASH_AGG(NAME, AMOUNT, STATUS) AS HASH_VALUE
FROM DATA_CHECK_01.SCHEMA_01.ACTUAL_TABLE_01
GROUP BY ID
),
EXPECTED_BY_KEY AS (
SELECT
ID,
COUNT(*) AS CNT,
HASH_AGG(NAME, AMOUNT, STATUS) AS HASH_VALUE
FROM DATA_CHECK_01.SCHEMA_01.EXPECTED_TABLE_01
GROUP BY ID
),
DIFF_KEYS AS (
SELECT
COALESCE(A.ID, E.ID) AS ID,
A.CNT AS ACTUAL_COUNT,
E.CNT AS EXPECTED_COUNT,
A.HASH_VALUE AS ACTUAL_HASH_VALUE,
E.HASH_VALUE AS EXPECTED_HASH_VALUE
FROM ACTUAL_BY_KEY A
FULL OUTER JOIN EXPECTED_BY_KEY E
ON A.ID = E.ID
WHERE
A.ID IS NULL
OR E.ID IS NULL
OR A.CNT <> E.CNT
OR A.HASH_VALUE <> E.HASH_VALUE
)
SELECT *
FROM DIFF_KEYS
ORDER BY ID;
| ID | ACTUAL_COUNT | EXPECTED_COUNT | ACTUAL_HASH_VALUE | EXPECTED_HASH_VALUE |
|---|---|---|---|---|
| 3 | 1 | 1 | 5833180850030802282 | -3474571889050824196 |
ケース4: ＜正常系＞すべてのチェックが OK のケース
すべてのチェックが通るデータで上書きします。
-- =========================================================
-- ALL OK のケース
-- =========================================================
INSERT OVERWRITE INTO ACTUAL_TABLE_01
(ID, NAME, AMOUNT, STATUS, UPDATED_AT)
VALUES
(1, 'Alice', 100.00, 'ACTIVE', '2025-12-01 10:00:00'),
(2, 'Bob', 200.00, 'ACTIVE', '2025-12-01 11:00:00'),
(3, 'Carol', 300.00, 'INACTIVE', '2025-12-01 12:00:00');
INSERT OVERWRITE INTO EXPECTED_TABLE_01
(ID, NAME, AMOUNT, STATUS, UPDATED_AT)
VALUES
(1, 'Alice', 100.00, 'ACTIVE', '2026-04-01 10:00:00'),
(2, 'Bob', 200.00, 'ACTIVE', '2026-04-01 11:00:00'),
(3, 'Carol', 300.00, 'INACTIVE', '2026-04-01 12:00:00');
SQL ファイルを実行すると正常終了します。
補足: 採用しなかった方法
Snowpark でのデータ比較
Snowpark には Testing モジュールが存在しますが、内部的には Python 変数で差分確認を行っているコードであるため、データ量が多くなった際の性能問題を懸念して採用しませんでした。
出所: Testing | Snowflake Documentation
出所: snowpark-python/src/snowflake/snowpark/testing.py at v1.48.0 · snowflakedb/snowpark-python