Data Deduplication: Remove Duplicate Rows

Ba năm trước, tôi đã chứng kiến một chuỗi phân tích của một nhà bán lẻ trong danh sách Fortune 500 dừng lại vì cơ sở dữ liệu khách hàng của họ đã tăng lên 847 triệu hàng—trong khi họ chỉ có 340 triệu khách hàng thực tế. Thủ phạm? Các bản ghi trùng lặp đã tích lũy như mảng bám kỹ thuật số qua nhiều năm tích hợp hệ thống, di chuyển dữ liệu và lỗi của con người. Chi phí? 2,3 triệu đô la lãng phí cho việc lưu trữ đám mây hàng năm, cùng với vô số giờ phân tích nhầm lẫn khi các báo cáo doanh số cho thấy cùng một giao dịch được gán cho ba ID khách hàng khác nhau.

Tôi là Marcus Chen, và tôi đã dành 12 năm qua với tư cách là một kiến trúc sư kỹ thuật dữ liệu chuyên về khắc phục chất lượng dữ liệu cho các hệ thống doanh nghiệp. Tôi đã thấy các công ty mất hàng triệu vì họ không thể tin tưởng dữ liệu của chính mình, và tôi đã giúp họ phục hồi bằng cách thực hiện các chiến lược loại bỏ trùng lặp có hệ thống. Điều mà hầu hết mọi người không nhận ra là dữ liệu trùng lặp không chỉ là một vấn đề lưu trữ—đó là một vấn đề tin tưởng ảnh hưởng đến mọi quyết định kinh doanh mà tổ chức của bạn đưa ra.

Trong hướng dẫn toàn diện này, tôi sẽ hướng dẫn bạn qua mọi thứ tôi đã học về việc xác định, loại bỏ và ngăn ngừa các hàng trùng lặp trong tập dữ liệu của bạn. Dù bạn đang làm việc với hồ sơ khách hàng, nhật ký giao dịch, hay dữ liệu cảm biến, các nguyên tắc vẫn giữ nguyên, nhưng các chi tiết thực hiện thì rất quan trọng.

Hiểu Chi Phí Thực Sự của Dữ Liệu Trùng Lặp

Trước khi chúng ta đi vào các giải pháp, hãy nói về lý do điều này quan trọng hơn những chi phí lưu trữ hiển nhiên. Trong kinh nghiệm của tôi khi làm việc với hơn 60 khách hàng doanh nghiệp, dữ liệu trùng lặp tạo ra một hiệu ứng gợn sóng ảnh hưởng đến mọi góc độ trong tổ chức của bạn.

Đầu tiên, có tác động tài chính trực tiếp. Chi phí lưu trữ đám mây đã giảm đáng kể trong thập kỷ qua, nhưng ở quy mô lớn, dữ liệu trùng lặp vẫn gây thiệt hại. Một khách hàng trong lĩnh vực chăm sóc sức khỏe đã lưu trữ 4,2 petabyte dữ liệu hình ảnh bệnh nhân, và phân tích của chúng tôi cho thấy rằng 31% trong số đó bị trùng lặp trong các hệ thống khác nhau. Với mức giá $0.023 mỗi GB mỗi tháng từ nhà cung cấp đám mây của họ, những bản sao này đã tiêu tốn khoảng 310.000 đô la hàng tháng—3,7 triệu đô la hàng năm—chỉ từ phí lưu trữ. Thêm vào đó là chi phí tính toán để xử lý dữ liệu thừa trong các công việc phân tích, và con số đã tăng lên hơn 5 triệu đô la.

Nhưng các chi phí ẩn thì lớn hơn nhiều so với những cái nhìn thấy được. Các đội ngũ marketing gửi mail điện tử trùng lặp đến cùng một khách hàng dưới các ID khác nhau, làm tổn hại đến hình ảnh thương hiệu và lãng phí ngân sách chiến dịch. Các đội bán hàng theo đuổi những khách hàng tiềm năng đã trở thành khách hàng, tạo ra sự khó chịu và nhầm lẫn. Các nhóm phân tích sản xuất báo cáo với các chỉ số phình to dẫn đến các quyết định chiến lược kém. Tôi đã thấy một công ty phần mềm B2B ước lượng thị trường tổng thể của họ cao hơn 40% vì cơ sở dữ liệu khách hàng tiềm năng của họ đầy các bản ghi trùng lặp, dẫn đến một vòng gọi vốn thảm hại khi họ không thể đạt được những mục tiêu tăng trưởng đã hứa hẹn.

Các vấn đề tuân thủ cũng nghiêm trọng không kém. Theo GDPR và các quy định tương tự, các công ty phải có khả năng xác định và xóa tất cả dữ liệu liên quan đến một cá nhân cụ thể khi có yêu cầu. Nếu cá nhân đó tồn tại dưới năm bản ghi khác nhau trong hệ thống của bạn, bạn sẽ gặp phải cơn ác mộng tuân thủ. Một khách hàng trong lĩnh vực dịch vụ tài chính đã phải đối mặt với khoản phạt 2,8 triệu euro một phần vì họ không thể hoàn toàn tuân thủ các yêu cầu xóa do các bản ghi trùng lặp chưa được xác định.

Tiếp theo là vấn đề tồn đọng hoạt động. Các nhà khoa học dữ liệu ước tính rằng họ phải dành 60% thời gian cho việc dọn dẹp và chuẩn bị dữ liệu, theo nhiều khảo sát ngành mà tôi đã xem xét. Một phần lớn thời gian đó phải dành cho việc xử lý các bản ghi trùng lặp. Khi nhóm của bạn không thể tin tưởng vào dữ liệu, họ sẽ phải dành hàng giờ để xác thực và kiểm tra chéo thay vì tạo ra những thông tin chi tiết. Tôi đã tính toán rằng đối với một nhóm mười nhà phân tích dữ liệu với mức lương trung bình 95.000 đô la mỗi năm, các vấn đề về dữ liệu trùng lặp có thể tiêu tốn khoảng 285.000 đô la giá trị thời gian sản xuất mỗi năm.

Cấu Trúc của Các Hàng Trùng Lặp: Tại Sao Chúng Xuất Hiện

Hiểu cách mà các bản ghi trùng lặp phát sinh là rất quan trọng để ngăn chặn chúng. Trong nhiều năm phân tích dữ liệu điều tra của mình, tôi đã xác định được bảy nguồn chính gây ra các bản ghi trùng lặp, và hầu hết các tổ chức đều phải chịu ảnh hưởng của nhiều nguồn cùng lúc.

"Dữ liệu trùng lặp không chỉ là một vấn đề lưu trữ—đó là một vấn đề tin tưởng ảnh hưởng đến mọi quyết định kinh doanh mà tổ chức của bạn đưa ra."

Các tích hợp hệ thống là thủ phạm số một. Khi bạn kết hợp dữ liệu từ một CRM, một hệ thống ERP và một nền tảng tự động hóa tiếp thị, bạn gần như chắc chắn sẽ tạo ra các bản ghi trùng lặp trừ khi bạn có logic khớp mạnh mẽ. Tôi đã làm việc với một công ty sản xuất đã mua lại ba đối thủ trong vòng năm năm. Mỗi lần mua lại mang đến một cơ sở dữ liệu khách hàng mới, và cách tiếp cận tích hợp của họ chủ yếu là đổ tất cả vào một hồ dữ liệu. Kết quả? Một khách hàng có thể xuất hiện với tên gọi "ABC Manufacturing Inc.", "ABC Mfg", "A.B.C. Manufacturing Incorporated", và "ABC Manufacturing" trên các hệ thống nguồn khác nhau.

Các dự án di chuyển dữ liệu là một nguồn chính khác. Khi chuyển từ các hệ thống cũ sang các nền tảng hiện đại, các công ty thường chạy song song các hệ thống trong thời gian chuyển đổi. Các bản ghi được tạo hoặc cập nhật trong khoảng thời gian này thường xuất hiện trong cả hai hệ thống. Tôi đã thấy những đợt di chuyển mà ngày chuyển đổi bị mờ, dẫn đến một khoảng thời gian chồng chéo hai tuần tạo ra 340.000 bản ghi trùng lặp cho một công ty bảo hiểm vừa.

Việc nhập dữ liệu bằng tay là điều vốn có nhiều lỗi. Các đại diện bán hàng tạo ra các bản ghi liên lạc mới thay vì tìm kiếm các bản ghi hiện có vì nhanh hơn. Các nhân viên dịch vụ khách hàng không nhận ra rằng "John Smith" và "Jon Smith" có thể là cùng một người. Các bộ phận khác nhau sử dụng các quy tắc đặt tên khác nhau. Một khách hàng trong lĩnh vực viễn thông đã có 23 cách khác nhau mà nhân viên đã nhập "AT&T" vào cơ sở dữ liệu nhà cung cấp của họ, từ "AT&T Inc." đến "American Telephone & Telegraph" đến "ATT" không có dấu cách.

Các tích hợp API và webhook có thể tạo ra các bản ghi trùng lặp thông qua logic thử lại. Khi một yêu cầu mạng bị lỗi thời gian, nhiều hệ thống tự động thử lại thao tác. Nếu yêu cầu đầu tiên thực sự thành công nhưng thông báo xác nhận bị mất, bạn sẽ gặp phải các bản ghi trùng lặp. Tôi đã gỡ lỗi các kịch bản mà một tích hợp xử lý thanh toán đã tạo ra các bản ghi giao dịch trùng lặp vì chính sách thử lại tích cực—thanh toán đã được thực hiện một lần, nhưng cơ sở dữ liệu đã ghi lại nó ba lần.

Các công việc xử lý theo lô thiếu kiểm tra idempotency thích hợp là một nguồn phổ biến khác. Nếu một công việc ETL hàng đêm thất bại giữa chừng và bị chạy lại, bạn có thể tải cùng một dữ liệu hai lần. Tôi đã thấy điều này tạo ra hàng triệu bản ghi trùng lặp trong các kho dữ liệu, đặc biệt khi các công việc thiếu các cơ chế kiểm tra và phục hồi thích hợp.

Các bản chụp theo thời gian mà không có phiên bản hóa phù hợp tạo ra các bản ghi trùng lặp khi bạn cố gắng duy trì các bản ghi lịch sử. Nếu bạn thực hiện các bản chụp hàng ngày cơ sở dữ liệu khách hàng của mình nhưng không theo dõi đúng cách bản ghi nào là mới và bản ghi nào đã được sửa đổi, bạn sẽ gặp phải cùng một khách hàng xuất hiện trong mỗi bản chụp hàng ngày, khiến nó trông như bạn có 365 lần số khách hàng thực tế mà bạn có.

Cuối cùng, còn vấn đề của các hệ thống phân tán và tính nhất quán cuối cùng. Trong các kiến trúc microservices hiện đại, cùng một thực thể có thể được tạo ra trong nhiều dịch vụ trước khi các hệ thống đồng bộ hóa. Tôi đã làm việc với các nền tảng thương mại điện tử nơi một khách hàng có thể đặt hàng, cập nhật hồ sơ của họ, và liên hệ với hỗ trợ trong vài giây, tạo ra ba bản ghi khách hàng khác nhau trên ba dịch vụ khác nhau trước khi mô hình tính nhất quán cuối cùng điều chỉnh chúng.

Xác Định Các Bản Ghi Trùng Lặp: Vượt Ra Ngoài So Sánh Đơn Giản

Cách tiếp cận ngây thơ để tìm các bản ghi trùng lặp là tìm kiếm các khớp chính xác trên một khóa chính hoặc định danh duy nhất. Nhưng trong thế giới thực, các bản ghi trùng lặp hiếm khi rõ ràng như vậy. Qua nhiều năm, tôi đã phát triển một phương pháp phát hiện trùng lặp đa tầng bắt mọi thứ từ các khớp chính xác hiển nhiên đến các bản ghi trùng lặp mờ nhạt.

Khớp chính xác là hàng phòng thủ đầu tiên của bạn. Điều này bắt những trái cây dễ hái—các bản ghi giống nhau trên tất cả các trường hoặc chia sẻ cùng một định danh duy nhất. Trong SQL, điều này khá đơn giản. Bạn có thể sử dụng mệnh đề GROUP BY với COUNT lớn hơn một để tìm các bản ghi trùng lặp. Đối với một bảng khách hàng, bạn có thể viết gì đó như: SELECT email, COUNT(*) as duplicate_count FROM customers GROUP BY email HAVING