2つの大規模データセットを自動で比較する方法

今月の請求書合計額がERPに実際に計上された内容と一致しているという確信がどれほどあるかを買掛金担当のアナリストに尋ねてみれば、答える前に一瞬言葉に詰まる様子が見られるはずです。正直なところ、ほとんどの月において、まったく十分な確信を持てていないというのが実情です。その証拠となるのが、まさにこの失敗のパターンです。先月の表計算ソフトを再度開き、何かおかしい箇所が見つかるまでスクロールするというのは、比較照合プロセスではなく、単に不確実性に甘んじる習慣にすぎません。

2つのデータセットが一致するはずのあらゆる箇所で、このパターンが繰り返されます。ベンダー請求書の抽出データとERPサブ元帳、POSのエクスポートデータとウェアハウスシステム、レガシープラットフォームとそれを置き換えるクラウドシステムなどです。これらがきれいに一致することはめったになく、行数が増えるたびに原因の特定により多くの時間がかかるようになります。

この記事では、誰が実行しても、その日にどれほど疲れていても、1回目の実行と同じ結果を10回目でも出すように構築された比較の実際の仕組みについて詳しく説明します。

信頼性の高い比較では毎回同じ結果が得られます — 一度限りのチェックではそうはいきません

目指すべき成果は、10回目の実行でも1回目と同じ結果を生み出す比較です。一度だけ正しく完了した比較が目標ではありません。一度だけなら、誰でも運よく成功することがあります。

ここでの「信頼性」には特定の限定的な意味があります。各サイクルにおいて同じように適用される、同一の照合キー、同一の標準化ルール、そして同一の照合ロジックです。実行するたびに、それらが誰かのメモリから再導出されるようなことはありません。表計算ソフトによる手動チェックが答えるのは「今日これらは一致したか」であり、これは「今後も一致し続けるか」という問いとは異なりますが、この2つは常に混同されています。

リスクは現実のものであり、仮定の話ではありません。Gartnerの調査によると、データ品質の低さが原因で生じるコストは、それを負担する組織にとって年間平均1,290万ドルに上ります。同じ担当者が毎月、記憶を頼りに同じ方法で構築した場合にしか機能しない照合プロセスは、その数値の要因の一つとなっています。

比較を一過性のものではなく信頼性の高いものにする3つの要素があります。具体的には、両方のシステムで同じ意味を持つ照合キー、比較を行う前に両側で標準化されたフォーマット、そして担当者が割り当てられた例外を伴う全件照合です。次の3つのセクションでは、それぞれについて順番に説明します。

この文脈における「自動的」とは、サイクルごとに人がゼロから作り直すことなくロジックが同じように実行されることを意味しており、人間の判断が不要になるわけではありません。何が本当の差異に該当するかを判断するのは、依然として人間です。照合作業自体が、決算週の午後11時に担当者がすべてのルールを正確に覚えていることに依存しなくなるだけです。

何かを自動化する前に、現在の比較プロセスが現在実際に何をチェックしているのかを正確に書き出しておくことが重要です。その一覧は、ほとんどの場合、想定よりも短くなります。

ステップ1 — 一致キーは、両方のシステムで同じ意味を持つ場合にのみ機能します

「失敗」する比較の多くは、実際には照合で失敗しているわけではありません。それらはもっと前のキー定義の段階で失敗していました。そもそも、同じレコードをどのように識別するかについて、2つのシステム間で合意できていなかったためです。

利用可能な照合キーには、一意性、両方のシステム間での安定性、一貫した表現という3つの要素が必要です。同じヘッダー名を持つ2つの列が自動的に同じキーになるわけではありません。その思い込みこそが、こうした問題の多くが発生する原因です。

両方のデータセットが請求書番号、トランザクションID、口座番号などの真の固有識別子を共有している場合は、そのキーでの直接結合で十分です。それ以上に複雑なものは必要なく、ここで複雑さを加えると、通常はプロセスで問題が発生する箇所が増えるだけです。

氏名やアドレスの表記揺れ、個別に生成されたIDなど、識別子がシステム間で異なっていたり存在しなかったりする場合、完全一致による結合は機能しません。その代わりに、一般にレコードリンケージやファジーマッチングとして知られる類似度ベースのマッチングでそのギャップを埋める必要があります。2つの値が完全に一致しているかどうかではなく、どの程度近いかを比較します。

複合キーには特に注意が必要です。ベンダー、請求書番号、日付から作成されたキーは、これらのフィールドのいずれかで書式のずれが生じるまではユニークに見えます。その時点で「ユニークな」キーは気付かれないうちにユニークではなくなり、照合結果の数値に整合性がなくなるまで誰も気付きません。正しく選択されたキーであっても正しく一致させるには、両方のシステムで値の書式が同じである必要があります。この問題については次に説明します。

完全一致するキーが存在する場合

複数のフィールドから再構築するのではなく、共有の固有識別子を直接使用してください。信頼する前に、両方のソースシステムで実際にユニークであることを確認してください。フィールドは一方のシステムでユニークであっても、移行やマージ後に他方のシステムではユニークではなくなる可能性があります。一部のERPはエクスポート時に識別子を切り捨てたり再エンコードしたりするため、キーがシステム境界を越えても変更されずに保持されるかどうかを確認してください。

そうでない場合

類似性の基準として使用できる最も適切なフィールド（通常は名前、アドレス、説明など）を特定します。結果を確認する前に、あらかじめ類似度の閾値を決定してください。出力を確認した後に選ばれた閾値は、誰かが望んだ結果が出るように知らず知らずのうちに調整されてしまう可能性があります。類似性に基づくマッチング設定を行うと、1行ずつ目視で確認する代わりに、ファイル全体に対してそのような閾値を一貫して適用できます。閾値とその根拠を書き留めて記録してください。文書化されていない閾値は、設定した担当者の退職とともに組織から失われてしまうノウハウの典型です。

ステップ2 — 実際にあるように見える差異のほとんどは、型とフォーマットの不一致によって生じます

フラグが設定された「不一致」のうち、実際の違いではなく単なる書式の相違である割合が増えるほど、その比較に対する信頼は失われていきます。結合の実行後にフィルターをかけるのではなく、実行前に標準化を行うことこそが、プロセス全体の信頼性を保つ鍵となります。

よくある原因は予測可能です。あるシステムで先頭のゼロが削除され、別のシステムでは保持されていること、末尾の空白、大文字・小文字の表記ゆれ、Nullと空白と「N/A」の違い、日付形式の不一致、通貨の小数点以下の精度の不整合などです。これらはいずれも、データセット間の実際の不一致を表すものではありません。

照合前にフィールドレベルでこれを修正する方が、後から結果から偽陽性を除外するよりも効果的です。後からフィルタリングしてもすべてを検出できるわけではなく、サイクルごとに毎回手作業でやり直す必要があります。これに対して、パイプライン自体に組み込まれた標準化は、繰り返し発生する手作業ではなく、データソースごとに一度だけ行う設定作業です。

この作業カテゴリにはデータクレンジングという名前があり、比較を行う前にNull、句読点、大文字小文字を標準化します。これは、数値に異常が見つかったときに誰かが行うアドホックなクリーンアップ処理ではなく、個別のステップとしてプロセスに組み込むべきものです。

キーを定義し、双方でフォーマットを標準化すれば、クリーンな2つのデータセットから実際の照合を行う作業は比較的機械的であり、まさにこれが次のステップです。

ステップ3 — 2つのデータセットの照合とは、1つの合否判定ではなく、3つの出力を生成することを意味します

有用な比較を行うと、一致したレコード、データセットAにのみ存在するレコード、データセットBにのみ存在するレコードの3つのグループが生成されます。単一の合格/不合格判定はあまり役に立たず、そもそも例外が見落とされる原因になります。

機械的には、検証済みのキーに基づいて2つのデータセットを結合し、その結果を一致したレコードと2つのグループの不一致レコードに分離することを意味します。一致、左側のみ、右側のみという3つの個別の出力を生成する結合メカニズムは、このステップに必要な構造そのものであり、それらの出力を再結合するユニオンステップによって完全な照合ビューが構築されます。

極めて大規模な抽出データ、数百万行のテーブル、通年の取引履歴などの場合、データが既に存在するデータベースや処理エンジン内で比較を実行することで、表計算ソフトや単純なスクリプトが最終的に直面するメモリ上限を回避できます。

Aにのみ存在する行やBにのみ存在する行はすべて、担当者を割り当て、事前に合意した重要性の閾値を設定する必要があります。これは、決算週の最中に見つけ出すのではなく、開始前に決定しておくべきです。文書化された閾値がないと、チームはわずか数セントの端数差異をすべて追いかけるか、すべてをそのまま通過させてしまうかのどちらかになり、いずれの結果も照合を行う本来の目的を損なってしまいます。

3方向の出力と所有権ロジックを手作業で維持することは、数千行程度のところでスケールしなくなります。そこが、再現性のあるプロセスと表計算ソフトによるチェックが大きく分かれるポイントであり、チームが通常、それを実行するための別の手段を探し始めるポイントです。

チームが実際にこれを構築する3つの方法 — そしてそれぞれが機能しなくなるポイント

チームが実際にこの比較を構築する方法は、手作業による表計算ソフト、カスタムのSQLまたはPythonスクリプト、専用のデータ準備・自動化プラットフォームの3つのいずれかです。どれが適切であるかは、データ量、発生頻度、そして誰がメンテナンスを行うかによって決まるものであり、たまたますでにインストールされているツールによって決まるわけではありません。

小さなファイルで、繰り返しのない真に一度限りの比較であれば、表計算ソフトで十分です。その業務に追加の仕組みは一切必要ありません。カスタムSQLやPythonは、専任のデータエンジニアリングサポートがあり、要件が安定していて、スケジューリング基盤がすでに整っているチームに適しています。カスタムロジックの構築は、それを保守する担当者がいる場合にのみ価値があります。

意思決定のルールはシンプルです。誰かのデスクトップにすでにあるものではなく、比較を実行する必要がある頻度やデータセットの規模に合わせてアプローチを選択することです。

実際にどこで問題が生じるのか — そしてその修正方法とはどのようなものかを見てみましょう

ある中規模の産業用流通業者は、15年間使用してきたオンプレミスERPから新しいクラウドERPへのベンダーおよび請求書レコードの移行を進めている途上でした。毎月、そして特に本番カットオーバー期間中は、2つのシステム間でベンダーの請求書を照合する必要がありました。

3人の担当者がそれぞれ異なる部分を担当していました。AP照合アナリストのDanaが、月次チェックを担当していました。IT移行リードのMarcusが、カットオーバー時のデータ整合性を担当していました。コントローラーは決算期日を担当するとともに、実際に追跡する価値のある不一致とは何かについての暗黙のルールを定めていました。

移行が検討される前から、ベースラインはすでに手作業でした。約35,000行のベンダー請求書エクスポートデータを、ExcelでVLOOKUPを使ってERPのAPサブ台帳と照合しており、Danaは毎月末に丸2営業日を費やしていました。

そして、カットオーバーの月が訪れました。レガシーシステムで「0003347」のように先頭にゼロが付いたテキストとして保存されていた請求書番号は、新しいERPではそれらのゼロが削除され、数値フィールドとして取り込まれました。請求書番号がたまたまゼロで始まっていたすべてのベンダーについて、請求書番号の完全一致照合が警告なしに失敗し、どちらのシステムでもこの事象はフラグ付けされませんでした。

2つ目の問題が、1つ目の問題をさらに深刻化させていました。$ 250という重要性の閾値（それを下回る差異は日常的にそのまま通されていました）は、コントローラーのメモリの中にしか存在しませんでした。それはどの照合チェックリストにも記載されていなかったため、フラグが付けられた行を確認しても、どれが実際に重要でどれがノイズなのかをすぐに判断できる人はいませんでした。

数字によってコストが具体的になりました。35,000行のうち約1,200行が、純粋に先頭のゼロの書式設定による影響だけで、誤った「不一致」の結果として返されました。Ventana Researchのベンチマークによると、目標を達成している企業における一般的な決算処理期間は約6営業日とされていますが、今回のケースでは、DanaとMarcusがフラグの付いた何千行ものデータを手作業で確認して実際の不一致と書式設定のノイズを切り分けたため、さらに4日長くかかりました。

この修正では、上記で構築した同じ手法を使用しました。照合を行う前に、請求書番号フィールドを両側で同じテキスト表現に正規化し、修正したキーで結合し、結果を「一致（matched）」、「レガシーのみ（only-in-legacy）」、「新規ERPのみ（only-in-new-ERP）」に分割して、閾値を超えた文書化された例外のみを担当者にルーティングしてください。

締め処理は、次のサイクルで通常の所要時間に戻りました。$250の閾値は、特定の担当者の頭の中だけに存在するものではなく、明文化されて適用されるルールとなり、すべての例外判断について、誰がどのような理由で対応したかの記録が残るようになりました。

例外の担当者と重要性の閾値が現在誰かのメモリにしかない場合、照合例外解消スターターキットは、まさにそのステップのために構築された、無料ですぐに適応できるワークフローです。

実運用に移されるとどのようになるか

一度構築すれば、比較は独自のスケジュールに従って実行され、毎回すべての行数を返すのではなく、実際に確認する必要があるものだけを返します。すべてを確認するのではなく例外のみを確認するように移行することこそが、このように構築する最大の目的です。

定期的な実行は、誰かが手動でチェックを開始するのを思い出したときではなく、毎日、毎週、毎月、または会計カレンダーに連動して行われます。例外キューが唯一の実質的な手作業の接点となります。アナリストは、35,000行のファイルを1行ずつスキャンする代わりに、閾値を超えた未解決項目の短いリストを確認します。

非常に大きなテーブルの場合、両方のテーブルがすでに存在するデータベース内で比較を処理することで、すべてのデータをアプリケーションメモリに読み込むのを回避できます。同じワークフローが独自の定期スケジュールに従って自動的に実行され続け、1回限りの確認作業を定常的なプロセスへと変えます。

社内向けの提案を作成しているアナリティクスリーダーにとって、毎回同じように実行されるワークフローは、個人の表計算ソフトの使い方に依存するよりも監査が容易になります。人間の判断を完全に排除するのではなく、手作業による照合作業を削減するのに役立ち、何がいつ実行されたかの記録も残ります。この目的でプラットフォームを評価するITステークホルダーは、通常、承認する前にそのセキュリティおよびコンプライアンスに関するドキュメントを確認したいと考えます。

今後の展望に関する留意点（明確な前提事項）：AIによるスキーマ適合やデータ準備のサジェストは、初期セットアップの手順、特に2つのシステム間でフィールドをマッピングする煩雑な作業を迅速化するのに役立ちます。これはセットアップ時の支援を意味するものであり、AIが自らデータセットを比較するという主張ではありません。

照合プログラム全体ではなく、1つの比較から始めましょう。

これが機能することを証明する最短の方法は、誰も実際に機能する様子を確認していない段階で照合プログラム全体のスコープを定めることではなく、既存の手作業による比較作業を1つ完全に再構築することです。

すでに繰り返し負担となっている比較作業を選んでください。年次ではなく月次の照合を選ぶことで、1年後ではなく1～2サイクル以内に効果を確認できます。構築中に、照合キー、形式の標準化ルール、重要性の閾値を明確に文書化してください。上記のシナリオにおけるコントローラーの$250の閾値のように、何も文書化されないままにしないでください。

手作業のプロセスを廃止する前に、新しいプロセスを既存の手作業プロセスと並行して1サイクル完全に実行します。この重複期間により、誰もが出力に完全に依存する前にその信頼性を高めることができ、新しいバージョンで見逃した事項があれば、Danaまたは確認を担当する人が検出する機会にもなります。

アナリティクスリーダーにとって、1つの比較と測定可能な1つの導入前後の変化を示すこのストーリーは、社内で提案を行う際にIT部門や財務部門のステークホルダーに説明する最も分かりやすい方法でもあります。これは具体的で対象範囲が明確であり、実際に動作を確認する前にプラットフォームを信頼するよう誰かに求めることもありません。

カスタムスクリプトを保守することなくこれを構築したいアナリストは、Alteryx Oneの無料トライアルを開始し、上記で説明したのと同じ照合・突合ワークフローを設定できます。