注文の JSON を Snowflake で表にすると、件数が元のシステムと合わない｜VARIANT で受け、注文と明細の2つの表で照らし合わせる

注文数が、元のシステムより少ない。クーポンを使った注文が、多すぎる。ある日から、注文が1件も出てこない。Webhook や API で届く JSON を Snowflake で表にすると、どれもエラーなしに起きることがあります。

JSON はまず形を決めずに1つの列へそのまま入れ、注文1行の表と明細1行の表に分けます。そこから元のシステムの件数・合計と照らし合わせ、日ごとの数も見れば、3つとも数字を報告に使う前に見つけられます。

図と数字は、確かめ方を説明するために、ずれが起きやすい形の注文をわざと混ぜて当社が作った、架空の注文の JSON から出したものです。

表を作る前に、元のシステムの件数と合計を6つ写しておく

JSON を表にする SQL は、項目が見つからなくてもエラーになりません。項目が無ければ NULL が入り、行が欠ければ表の行が減るだけです。誤りに気づく手がかりは、元のシステムが数えた数と並べたときの差です。

そこで表を作る前に、元のシステムの管理画面や書き出しから、6つの数を写しておきます。注文数、キャンセルの数、クーポンを使った注文の数、明細の行数、明細の合計、値引きの合計です。明細の合計は、単価×数量を足した額にします。

6つは、数える単位で選びました。数える単位とは、1行が注文か明細かの違いです。注文数・キャンセル・クーポン・値引きは注文ごとに、明細の行数と合計は明細ごとに数えます。両方の単位に数があれば、注文が抜けたのか、明細が抜けたのか、金額だけが違うのかを分けられます。

写すときは、期間の区切りを Snowflake 側とそろえます。元のシステムが日本時間で日を切り、Snowflake 側で UTC のまま日付にすると、9時間ずれます。月の境目の注文が、前後の月に入ります。注文日時に +09:00 のような時差が付いているかを見て、どちらの日付で数えるかを決めます。

届いた JSON は、VARIANT の列にそのまま入れてから、注文と明細の2つの表に分ける

届く JSON は、1行が1つの注文です。注文番号や注文日時と並んで、クーポンの中身と明細の配列が入っています。明細は1つの注文に複数あることがあるので、注文と同じ表の1行には収まりません。

まず、届いた JSON を手を加えずに入れておく表を作ります。この記事では、これを生データの表と呼びます。列は、半構造化データ（JSON など）をそのまま持てる VARIANT 型の1列と、ファイル名、入れた時刻です。

使い方が決まっていないうちは、VARIANT の列に入れておくのが公式の勧めです。dbt で層を分けているなら、生データの表が raw の層、注文と明細の表が staging の層にあたります（dbt の層の記事）。

入れるのは、ステージのファイルを表に読み込む COPY INTO です。ステージは、表に読み込むファイルや、表から書き出したファイルの置き場所です。Snowflake の中に置く内部ステージと、S3 などを指す外部ステージがあり、今回は内部ステージに置きました。ファイル形式は TYPE = JSON だけで、1行に1つの JSON が並ぶ NDJSON の形のまま入りました。

この記事の SQL は、どれも当社が実行した文と同じです。後半の一式は、< > の所に今回の値を入れて実行しました。表の名前は、記事の中の呼び方にそろえています。

CREATE OR REPLACE STAGE ORDER_FILES;
CREATE OR REPLACE TABLE ORDERS_RAW (v VARIANT, file_name STRING, loaded_at TIMESTAMP_LTZ DEFAULT CURRENT_TIMESTAMP());
COPY INTO ORDERS_RAW (v, file_name)
  FROM (SELECT $1, METADATA$FILENAME FROM @ORDER_FILES/inbound/)
  FILE_FORMAT = (TYPE = JSON);

結果は 1,000行で、エラーは0件でした。項目名の違う注文も、null のクーポンも、文字列の価格も、VARIANT の列にはそのまま入りました。取り込みでは止まらないので、ずれは、このあと表にするときに起きます。

生データの表から、注文の表 ORDERS と明細の表 ORDER_LINES を作ります。値引きやクーポンは注文の表で、明細の行数や金額は明細の表で数えます。

最初の SQL では、項目名を JSON のとおりに書きました。明細は、FLATTEN という関数の既定のまま、配列の1要素を1行に分けています。この分け方を、展開と呼びます。

クーポンは項目が空でなければ使ったとみなし、価格は TRY_TO_NUMBER で数にしました。2つの表は、どちらもエラーなく作れました。

最初の SQL で作った表は、確かめた7つの数のうち5つで元と合わなかった

元のシステムの6つの数と、2つの表から数えた数を並べました。注文数は注文の表と明細の表の両方から数えたので、比べる数は7つです。

合わなかったのは5つで、原因は4つでした。明細の表から数えた注文数には、2つの原因が重なっています。明細の行と値引きの合計の2つは、元の数と合いました。

ここからは、合わなかった数を表の上から順に見ます。

注文が300件足りないのは、9月21日の夜から項目名が OrderId に変わったから

注文の表は、1,000行の生データの表から条件を付けずに作ったので、1,000行あります。注文番号の重複なしで数えると、700件でした。残りの300行は、注文番号が NULL です。

生データの表で、項目名ごとに数えると原因が見えました。小文字の orderId がある注文が 700、大文字で始まる OrderId が 300 です。注文番号が NULL の最初の注文は、9月21日21時40分でした。

日ごとに出すと、最初の SQL では9月21日が30件で、22日からは0件です。直した SQL の注文の表で数えると、22日から29日も毎日33〜34件ありました。30日は、このデータの最後の注文が19時57分なので28件です。

列名は大文字小文字を区別しないのに、JSON の項目名は区別します。SQL と同じ感覚で書くと、見落としやすい所です。

直し方は、2つの項目名のうち値のあるほうを使うことです。COALESCE(v:orderId, v:OrderId) にすると、注文数は 1,000 に戻りました。元のシステムの更新で項目名が変わったなら、変わった時刻を、元の6つの数と同じ所に書き残しておきます。

キャンセルの40件は、明細が空のため、展開したときに行ごと消えた

明細の表に残ったキャンセルの注文は、元の40件に対して0件でした。今回のデータでは、キャンセルの注文は明細の配列が空の [] で届いています。

FLATTEN の OUTER の指定は、既定が FALSE です。公式は、展開できない入力の行を、結果から外すと書いています。要素が0個のときと、パスでたどれないときです。

orderId と OrderId のうち値のある方を注文番号にして数えると、既定では明細の行が 1,920、注文が 960 でした。outer => TRUE を付けると、行は 1,960、注文は 1,000 になりました。増えた40行は、明細の欄が空のキャンセルの注文です。

この指定を付けると、明細の表の行数は、明細の無い注文の分だけ、明細の数より多くなります。明細の行を数えるときは、COUNT(*) ではなく、COUNT(sku) のように商品コードの列で数えます。

明細の表から数えた注文が 672 だったのは、2つの原因が重なったからです。1,000件から、キャンセルの40件と、OrderId の300件のうち明細のある288件が抜けました。

照らし合わせでキャンセルを明細の表で数えたのは、明細が空の注文が明細の表から消えていないかを見るためです。消えるのはキャンセルに限らず、明細が空の注文すべてです。自社のデータでキャンセルにも明細が残るなら、明細が空になる注文の種類を元のシステムで数え、同じように比べます。

クーポンを使った注文は、数え方しだいで315件にも200件にもなった

最初の SQL では、クーポンの項目が空でない注文を、使った注文として数えました。書き方は v:coupon IS NOT NULL です。結果は315件で、元の200件より115件多くなりました。

クーポンの項目は、3通りで届いていました。中身のあるものが200件、JSON の null が115件、項目そのものが無いものが685件です。JSON の null は、JSON の中に null と書かれた値のことです。

公式によると、JSON の null は、SQL の NULL とは別の値です。項目が無いときに読むと、SQL の NULL になります。今回の結果では、IS NULL に当たったのは項目の無い685件だけでした。JSON の null の115件は、IS NOT NULL の側に入っています。

ここで、当社の見込みは外れました。COUNT(v:coupon) も、IS NOT NULL と同じく315件と出ると考えていたのです。結果は200件で、JSON の null を数えていません。

COUNT の公式のページには、JSON の null を SQL の NULL として扱う、と書かれています。同じ「空でない」を数えるつもりでも、書き方で答えが変わります。

直した SQL では、クーポンのコードを取り出して数えています。v:coupon:code::STRING を coupon_code の列にし、COUNT で数えると200件でした。数えたいのが「コードの付いたクーポン」なら、コードがあるかで数えるのが確かです。

JSON の null を SQL の NULL にしたいときは、文字列にします。::STRING で NULL になったのは800件で、項目なしの685件と、JSON の null の115件を足した数です。

カンマ入りの価格は、エラーを消すと6行が NULL になり、合計から黙って外れた

明細の価格は、1,881行が数で、39行が文字列で届いていました。文字列には、"1980" のような形と、"1,980" のようなカンマ入りがあります。

価格を ::NUMBER で数にして合計を出すと、次のエラーで止まりました。結果は0行で、合計は出ません。エラーの文の FIXED は、::NUMBER で変えようとした先の型（NUMBER）のことです。

Failed to cast variant value "1,980" to FIXED

そこで TRY_TO_NUMBER に替えると、エラーは消えました。最初の SQL の価格は、この形です。この関数は、数にできない値を NULL にします。合計は 4,292,040 で、元の 4,316,800 より 24,760 少なく、6行の価格が NULL でした。

エラーのうちは合計が出ないので、そのまま使われることはありません。エラーを消したあとの合計は、少しずれたまま使われるおそれがあります。カンマの無い “1980” は ::NUMBER のままで通ったので、NULL になったのはカンマ入りの価格だけでした。

直した SQL では、REPLACE でカンマを外してから、TRY_TO_NUMBER で数にしました。合計は 4,316,800 で元と一致し、NULL は0行でした。桁区切りの金額を文字列で受けて直す形は、日本語の CSV の記事と同じです。それでも NULL が残る行は、照らし合わせとは別に毎回数えます。

次に項目名が変わる日は、項目名の一覧を、初めて出た時刻つきで出して見つける

項目名が変わったこと自体は、届いた JSON の項目名を一覧にすれば、照らし合わせで差が出る前に分かります。

OBJECT_KEYS は、JSON の一番上の階層の項目名を、配列で返す関数です。それを FLATTEN で行にし、項目名ごとに、初めて出た注文と最後に出た注文の時刻を出しました。

一覧は8行でした。orderId は9月21日の20時57分が最後で、OrderId は21時40分からです。入れ替わった時刻が、この表で読めます。giftWrap は、26日9時53分から50件の注文に付いていました。

giftWrap はギフト包装の有無で、最初の SQL には無い項目です。知らない項目はエラーにならず、表に入らないだけです。直した SQL では gift_wrap の列を足し、項目の無い注文は FALSE にしました。

直した2つの表は、生データの表から作り直す

最初の SQL と、直した SQL です。どちらも生データの表から作ります。

-- 最初の SQL（確かめた7つの数のうち5つが合わなかった）
CREATE OR REPLACE TABLE ORDERS AS
SELECT v:orderId::STRING AS order_id, v:orderedAt::TIMESTAMP_TZ AS ordered_at, v:status::STRING AS status,
       v:customerId::STRING AS customer_id, v:coupon IS NOT NULL AS used_coupon, v:coupon:discount::NUMBER AS discount
FROM ORDERS_RAW;
CREATE OR REPLACE TABLE ORDER_LINES AS
SELECT r.v:orderId::STRING AS order_id, r.v:status::STRING AS status, i.index + 1 AS line_no,
       i.value:sku::STRING AS sku, TRY_TO_NUMBER(i.value:price::STRING) AS price, i.value:qty::NUMBER AS qty
FROM ORDERS_RAW r, LATERAL FLATTEN(input => r.v:items) i;
-- 直した SQL（7つとも合った）
CREATE OR REPLACE TABLE ORDERS AS
SELECT COALESCE(v:orderId, v:OrderId)::STRING AS order_id, v:orderedAt::TIMESTAMP_TZ AS ordered_at,
       v:status::STRING AS status, v:customerId::STRING AS customer_id,
       v:coupon:code::STRING AS coupon_code, COALESCE(v:coupon:discount::NUMBER, 0) AS discount,
       COALESCE(v:giftWrap::BOOLEAN, FALSE) AS gift_wrap
FROM ORDERS_RAW;
CREATE OR REPLACE TABLE ORDER_LINES AS
SELECT COALESCE(r.v:orderId, r.v:OrderId)::STRING AS order_id, r.v:status::STRING AS status, i.index + 1 AS line_no,
       i.value:sku::STRING AS sku, TRY_TO_NUMBER(REPLACE(i.value:price::STRING, ',', '')) AS price, i.value:qty::NUMBER AS qty
FROM ORDERS_RAW r, LATERAL FLATTEN(input => r.v:items, outer => TRUE) i;

注文の表で直したのは3か所です。注文番号は orderId と OrderId のうち値のある方を取り、クーポンは used_coupon の代わりにコードを取り出しました。値引きは、項目が無いときに0にしています。ギフト包装の gift_wrap は、直しではなく新しく足した列です。

明細の表で直したのも3か所です。注文番号を同じく値のある方から取り、展開に outer => TRUE を付け、価格はカンマを外してから数にしました。生データの表を残しておけば、直すたびにファイルを入れ直さずに済みます。

月の締めごとに、元の数と並べて差を出す

照らし合わせは、月の締めごとに1か月分で回します。Webhook で注文が1件ずつ届いていても、生データの表にたまった分を、締めた月の期間で数えます。

元の6つの数は、データの担当が、締めの翌営業日に元のシステムの管理画面か月次の集計から写します。並べて差が出た数があれば、次の3つのクエリで原因を探します。上から、項目名の大文字小文字、クーポンの数え方、項目名の一覧を見る順です。

SELECT COUNT(*) AS raw_rows, COUNT(v:orderId) AS has_orderId, COUNT(v:OrderId) AS has_OrderId,
       COUNT_IF(v:orderId::STRING IS NULL) AS orderId_null,
       MIN(IFF(v:orderId IS NULL, v:orderedAt::STRING, NULL)) AS first_null_at
FROM ORDERS_RAW;
SELECT COUNT_IF(v:coupon IS NULL) AS coupon_is_null, COUNT_IF(v:coupon IS NOT NULL) AS coupon_is_not_null,
       COUNT_IF(IS_NULL_VALUE(v:coupon)) AS json_null, COUNT(v:coupon) AS count_coupon, COUNT(v:coupon:code) AS count_code,
       COUNT_IF(v:coupon::STRING IS NULL) AS str_is_null, COUNT_IF(TYPEOF(v:coupon) = 'OBJECT') AS obj
FROM ORDERS_RAW;
SELECT k.value::STRING AS key_name, MIN(r.v:orderedAt::TIMESTAMP_TZ) AS first_seen,
       MAX(r.v:orderedAt::TIMESTAMP_TZ) AS last_seen, COUNT(*) AS orders
FROM ORDERS_RAW r, LATERAL FLATTEN(input => OBJECT_KEYS(r.v)) k
GROUP BY 1 ORDER BY 2, 1;

1つ目のクエリの別名 has_orderId と has_OrderId は、結果ではどちらも HAS_ORDERID と表示されました。SQL の列名は大文字小文字を区別しないので、結果の列でも2つを見分けられません。

その次の一式は、月の締めごとに使う形にまとめたものです。直した SQL で作った表の上で、9月の1か月分を期間にして実行しました。一式は、直した SQL の表を前提にしています。最初の SQL の表では coupon_code の列が無いので、(2) がエラーで止まりました。

< > で囲んだ所は空欄なので、自社の値を入れます。期間の日付は、2026-09-01 のような年-月-日の形です。

ほかに、今回の名前と値のまま書いてある所が4つあります。(2) の ‘cancelled’、(3) の項目名の一覧、注文日時の項目名 r.v:orderedAt、表の名前 ORDERS_RAW です。どれも自社のものに替えます。注文日時の項目名が違うと、first_seen と last_seen はエラーにならずに NULL になります。

(1) は、元のシステムの数を入れる表です。写した6つを、締めた月ごとに入れます。入れ直すときは、先に同じ期間の行を消してから入れます。(2) は、同じ期間の Snowflake 側の7つの数と、注文の表の行数 order_rows を出し、元の数と並べて差を出します。order_rows は、同じ注文が2回届くと orders より多くなります。SET の文で期間を変数に入れ、下のクエリで使っています。

(2) では、明細の表に注文日時が無いので、注文の表にある注文番号で明細を絞っています。注文番号の取り出しを誤ると、明細も注文番号で拾えずに減るので、差として表に出ます。

(3) は、これまでに届いた項目名に無い項目を、初めて出た時刻と最後に出た時刻つきで出します。文字列の比較は既定で大文字小文字を区別するので、OrderId も一覧に書いておきます。一覧にある項目が途中で止まったことは、ここには出ないので、上の項目名の一覧を出すクエリで見ます。

(4) は、商品コードがあるのに価格が NULL の明細を数えます。(5) は日ごとの注文数です。注文が1件も無い日は行そのものが出てこないので、日付が飛んでいないかも見ます。

-- (1) 元のシステムの数を入れる表。入れ直すときは、先に同じ期間の行を消す
CREATE TABLE IF NOT EXISTS SOURCE_COUNTS (
  period_start DATE, period_end DATE, metric STRING, source_value NUMBER(38, 2),
  noted_at TIMESTAMP_LTZ DEFAULT CURRENT_TIMESTAMP());
DELETE FROM SOURCE_COUNTS WHERE period_start = '<期間の初日 YYYY-MM-DD>' AND period_end = '<期間の末日 YYYY-MM-DD>';
INSERT INTO SOURCE_COUNTS (period_start, period_end, metric, source_value) VALUES
  ('<期間の初日 YYYY-MM-DD>', '<期間の末日 YYYY-MM-DD>', 'orders', <注文数>),
  ('<期間の初日 YYYY-MM-DD>', '<期間の末日 YYYY-MM-DD>', 'cancelled_orders', <キャンセルの数>),
  ('<期間の初日 YYYY-MM-DD>', '<期間の末日 YYYY-MM-DD>', 'coupon_orders', <クーポンを使った注文の数>),
  ('<期間の初日 YYYY-MM-DD>', '<期間の末日 YYYY-MM-DD>', 'item_lines', <明細の行数>),
  ('<期間の初日 YYYY-MM-DD>', '<期間の末日 YYYY-MM-DD>', 'item_amount', <明細の合計>),
  ('<期間の初日 YYYY-MM-DD>', '<期間の末日 YYYY-MM-DD>', 'discount', <値引きの合計>);
-- (2) 同じ期間の Snowflake 側の数を出し、元の数と並べる。'cancelled' は自社のキャンセルの値に替える
SET (P_START, P_END) = ('<期間の初日 YYYY-MM-DD>', '<期間の末日 YYYY-MM-DD>');
WITH o AS (
  SELECT * FROM ORDERS WHERE ordered_at::DATE BETWEEN $P_START AND $P_END),
l AS (
  SELECT * FROM ORDER_LINES WHERE order_id IN (SELECT order_id FROM o)),
sf AS (
  SELECT 'orders' AS metric, 'orders' AS src_metric, COUNT(DISTINCT order_id) AS sf_value FROM o
  UNION ALL SELECT 'order_rows', 'orders', COUNT(*) FROM o
  UNION ALL SELECT 'orders_in_lines', 'orders', COUNT(DISTINCT order_id) FROM l
  UNION ALL SELECT 'cancelled_orders', 'cancelled_orders', COUNT(DISTINCT IFF(status = 'cancelled', order_id, NULL)) FROM l
  UNION ALL SELECT 'coupon_orders', 'coupon_orders', COUNT(coupon_code) FROM o
  UNION ALL SELECT 'item_lines', 'item_lines', COUNT(sku) FROM l
  UNION ALL SELECT 'item_amount', 'item_amount', SUM(price * qty) FROM l
  UNION ALL SELECT 'discount', 'discount', SUM(discount) FROM o)
SELECT sf.metric, s.source_value, sf.sf_value, sf.sf_value - s.source_value AS diff
FROM sf LEFT JOIN SOURCE_COUNTS s
  ON s.metric = sf.src_metric AND s.period_start = $P_START AND s.period_end = $P_END
ORDER BY sf.metric;
-- (3) これまでに届いた項目名に無い項目を、初めて出た時刻と最後に出た時刻つきで出す。項目名の一覧・orderedAt・ORDERS_RAW は自社のものに替える
SELECT k.value::STRING AS key_name, MIN(r.v:orderedAt::TIMESTAMP_TZ) AS first_seen,
       MAX(r.v:orderedAt::TIMESTAMP_TZ) AS last_seen, COUNT(*) AS orders
FROM ORDERS_RAW r, LATERAL FLATTEN(input => OBJECT_KEYS(r.v)) k
WHERE k.value::STRING NOT IN ('orderId', 'OrderId', 'orderedAt', 'status', 'customerId', 'coupon', 'items', 'giftWrap')
GROUP BY 1 ORDER BY 2;
-- (4) 商品コードがあるのに、価格が NULL の明細
SELECT COUNT_IF(sku IS NOT NULL AND price IS NULL) AS price_missing FROM ORDER_LINES;
-- (5) 日ごとの注文数。注文が無い日は行が出ないので、日付が飛んでいないかも見る
SELECT ordered_at::DATE AS order_date, COUNT(DISTINCT order_id) AS orders,
       COUNT(coupon_code) AS coupon_orders, COUNT_IF(gift_wrap) AS gift_wrap_orders
FROM ORDERS
GROUP BY 1 ORDER BY 1;

今回のデータで (2) を実行すると、8つの数はどれも差が0でした。source_value は小数2桁の列なので、40.00 のように出ます。(1) を入れ直しても、元の数の表は6行のままでした。(3) は0行で、一覧から giftWrap を外すと、9月26日9時53分から50件と出ました。

(4) は、直した明細の表で0、最初の SQL の明細の表で6でした。(5) は30行で、上の日ごとの図の直した SQL の数と同じです。

明細の表だけを最初の SQL のままにして (2) を実行すると、4つの数に差が出ました。キャンセルが -40、明細の行が -576、明細の合計が -1,315,840、明細の表にある注文が -328 です。明細の行の576は、注文番号が NULL の明細が、注文番号での絞り込みから外れた分です。

diff が0でない行があれば、上の原因を探すクエリに戻ります。(4) のような NULL や件数を毎朝自動で確かめる仕組みは、データメトリック関数の記事に書いています。

7つの数が合っても、この照らし合わせでは見えない所が4つある

7つの数が合っても、表の中身がすべて正しいとは限りません。今回の照らし合わせで見えないものが、4つあります。

1つ目は、行数と金額の関係です。明細の行は最初の SQL でも 1,920 で合いましたが、数えたのは商品コードの数でした。価格が NULL になった6行も数に入っていたので、行数が合っても、金額は別に照らします。

2つ目は、明細の中の項目名です。項目名の一覧に出るのは一番上の階層だけで、明細の中の項目名が変わっても出てきません。明細の中は、items を FLATTEN してから同じように数える必要があります。

3つ目は、注文と明細を1つの表にまとめたときです。そこで値引きを足すと、明細の数だけ値引きが重なります。今回は2つの表に分けたので、重なりは数えていません。1行の単位が違う表をつなぐと合計が膨らむ話は、BigQuery の表のつなぎ方の記事にあります。

4つ目は、期間の区切りです。今回は1か月分の全件を照らし合わせたので、期間では区切っていません。元の数と並べるクエリの日付の区切り方は、元のシステムの日の切り方と合っているかを確かめてから使います。

今回のデータは、GENERATOR という関数で作った1つのファイルで、トライアルのアカウントで試しました。自社の別の形の JSON、複数のファイル、時差のそろっていない注文日時、同じ注文が2回届く場合は試していません。2回届いた注文の重複をまとめる処理も、この記事では扱っていません。取り込みの自動化と差分の更新の組み方は、取り込みの自動化の記事と差分の更新の記事にあります。

自社の生データの表で、項目名の一覧を一度出してみる

項目名ごとに、初めて出た時刻と最後に出た時刻を出すクエリを、自社の生データの表で一度実行します。注文日時の項目名と表の名前は、自社のものに替えます。

同じ項目名が大文字と小文字で2行に分かれていないか、最後に出た時刻が途中で止まっている項目が無いかを見ます。2行に分かれた項目名があれば、そこが最初に照らし合わせる所です。

生データの表から注文と明細の表を作り、元の数と照らす仕組みは、当社の Snowflake 導入支援で、dbt での変換と層の設計として組めます。