Data Deduplication: Remove Duplicate Rows

3年前、フォーチュン500に載る小売業者の分析パイプラインが停止するのを見ました。顧客データベースが847百万行に膨れ上がったのに、実際の顧客は340百万しかいなかったからです。原因は？システム統合、データ移行、人為的エラーの何年にもわたって蓄積された重複レコードでした。そのコストは？毎年230万ドルの無駄なクラウドストレージ費用と、売上報告書が同じ取引を3つの異なる顧客IDに割り当てることによって引き起こされる数え切れない分析者の混乱の時間です。

私はマーカス・チェンで、ここ12年間データエンジニアリングアーキテクトとして企業システムのデータ品質の改善を専門にしています。企業が自身のデータを信頼できずに何百万ドルも失うのを見てきましたし、体系的なデデュプリケーション戦略を実施することでそれらを回復させてきました。多くの人が気づいていないのは、重複データは単なるストレージの問題ではなく、あなたの組織のすべてのビジネス決定に連鎖する信頼の問題であるということです。

この包括的なガイドでは、データセット内の重複行を特定、削除、および防止するために学んだすべてをお伝えします。顧客のレコード、取引履歴、センサーデータを扱っている場合でも、原則は同じですが、実装の詳細が非常に重要です。

重複データの真のコストを理解する

解決策に入る前に、なぜこれが単なるストレージコストを超えて重要なのかについて話しましょう。60人以上の企業顧客と仕事をしてきた経験から、重複データは組織のあらゆる隅に影響を及ぼす波及効果を生み出します。

まず、直接的な財務的影響があります。クラウドストレージのコストは過去10年間で劇的に減少しましたが、大規模になると、重複は依然として痛手です。医療分野のクライアントは4.2ペタバイトの患者画像データを保存しており、私たちの分析では31%が異なるシステムで重複していることが明らかになりました。クラウドプロバイダーの料金が1GBあたり月0.023ドルであるため、これらの重複は彼らに毎月約31万ドル、年間370万ドルのストレージ料金がかかりました。分析作業中にその冗長データを処理するための計算コストを加えると、金額は500万ドル以上に達します。

しかし、隠れたコストは目に見えるものを超えています。マーケティングチームは異なるIDの下で同じ顧客に重複したメールを送信し、ブランドの認知を損ない、キャンペーン予算を浪費します。営業チームは、すでに顧客であるリードを追いかけて混乱を引き起こします。分析チームは、誇張された指標を含む報告書を生成し、戦略的な意思決定に悪影響を与えます。あるB2Bソフトウェア会社は、重複で混入した見込み客データベースのために、総アドレス可能市場を40%過大評価し、約束された成長目標を達成できない惨事となった資金調達ラウンドを経験しました。

コンプライアンスの影響も同様に深刻です。GDPRや類似の規制の下では、企業は特定の個人に関連付けられたすべてのデータを要求に応じて特定し削除できる必要があります。その個人がシステム内で5つの異なるレコードとして存在する場合、それはコンプライアンスの悪夢となります。ある金融サービスのクライアントは、重複するレコードを特定できなかったために、280万ユーロの罰金に直面しました。

次に、運用上の圧迫があります。複数の業界調査によれば、データサイエンティストはおおよそ60%の時間をデータのクリーンアップと準備に費やしています。その大部分が重複の処理に費やされます。チームがデータを信頼できない場合、洞察を生む代わりに、何時間も検証や照合を行います。私は、年収95,000ドルのデータアナリスト10人のチームにとって、重複データの問題が年間約285,000ドルの生産的な時間を消費する可能性があると計算しました。

重複行の解剖：なぜ発生するのか

重複がどのように発生するのかを理解することは、重複を防ぐために重要です。私の鑑定データ分析の経験から、重複レコードの主な7つの発生源を特定しており、ほとんどの組織は同時に複数の発生源に苦しんでいます。

「重複データは単なるストレージの問題ではなく、あなたの組織のすべてのビジネス決定に連鎖する信頼の問題です。」

システム統合は最大の原因です。CRM、ERPシステム、マーケティングオートメーションプラットフォームからデータを統合すると、堅牢なマッチングロジックがない限り、ほぼ確実に重複が発生します。ある製造会社と協力した際、5年間で3社の競合を買収しました。各買収が新しい顧客データベースをもたらし、彼らの統合アプローチは本質的にすべてをデータレイクに投げ込むものでした。その結果、一つの顧客が「ABC製造株式会社」、「ABC Mfg」、「A.B.C.製造株式会社」、「ABC製造」といった異なるソースシステムで表示されることとなりました。

データ移行プロジェクトも別の大きな原因です。レガシーシステムから現代のプラットフォームに移行する際、企業はしばしば移行期間中に並行システムを運行します。この期間中に作成または更新されたレコードは、両方のシステムに存在することがよくあります。クリティカルな移行が行われた際、切り替え日が曖昧で、その結果、340,000の重複レコードが中規模の保険会社に生まれることがありました。

人為的なデータ入力は本質的にエラーが発生しやすいです。営業担当者は既存のものを検索する代わりに新しい連絡先レコードを作成します。顧客サービスの担当者は「ジョン・スミス」と「ジョン・スミス」が同一人物である可能性に気づかないことがあります。異なる部門が異なる命名規則を使用します。ある通信会社では、従業員が「AT&T」をベンダーデータベースに23通りの異なる方法で入力していました。例えば、「AT&T Inc.」、「アメリカン・テレフォン・アンド・テレグラフ」、または「ATT」などです。

API統合やウェブフックは、リトライロジックを介して重複を生むことがあります。ネットワークリクエストがタイムアウトすると、多くのシステムは自動的に操作を再試行します。最初のリクエストが実際に成功したにもかかわらず確認が失われた場合、重複レコードが生成されます。ある決済処理の統合で、積極的なリトライポリシーにより、トランザクションレコードが重複して生成されるシナリオのデバッグを行ったことがあります。決済は一度完了しましたが、データベースには3回記録されました。

適切な冪等性チェックが欠如しているバッチ処理ジョブも別の一般的な原因です。ある夜間ETLジョブが途中で失敗し再実行されると、同じデータが二度ロードされる可能性があります。このプロセスは、特にジョブに適切なチェックポイントや回復メカニズムが欠如している場合、データウェアハウスに何百万もの重複を作成する原因となります。

適切なバージョニングなしの時間ベースのスナップショットは、履歴レコードを維持しようとする際に重複を生むことがあります。顧客データベースのデイリースナップショットを取得しますが、どのレコードが新規で、どのレコードが変更されたのかを適切に追跡しない場合は、同じ顧客が毎日のスナップショットに現れ、実際の顧客数が365倍に見える結果になります。

最後に、分散システムと最終的一貫性の問題があります。現代のマイクロサービスアーキテクチャでは、システムが同期される前に同じエンティティが複数のサービスで作成されることがあります。私は、顧客が秒単位で注文を発注し、プロフィールを更新し、サポートに連絡することができるEコマースプラットフォームと協力したことがあり、最終的な一貫性モデルがそれらを調整する前に、3つの異なるサービスにまたがって3つの異なる顧客レコードを生成しました。

重複を特定する：単純なマッチングを超えて

重複を見つけるための単純なアプローチは、主キーまたはユニーク識別子の完全一致を探すことです。しかし、現実の世界では、重複はあまり明白ではありません。これまでの経験を通じて、明白な完全一致から微妙なファジー重複までをキャッチする多層アプローチを開発しました。

完全一致は最初の防衛線です。これは、すべてのフィールドで同一または同じ一意の識別子を共有するレコードをキャッチします。SQLでは、これは簡単です。HAVING句のカウントが1以上であるGROUP BY句を使用して重複を見つけることができます。顧客テーブルのためには、次のような記述ができます：SELECT email, COUNT(*) as duplicate_count FROM customers GROUP BY email HAVING