Phát hiện trùng lặp kết nối

Hướng dẫn thực tế về đường ống dữ liệu Một tập dữ liệu có thể trông chính xác, các thử nghiệm có thể vượt qua và bảng điều khiển vẫn có thể bị lệch. Nguyên nhân gốc rễ thường giống nhau: một phép nối (join) âm thầm nhân đôi các hàng. Mặc dù các phép nối SQL trông đơn giản, chúng mã hóa một giả định mạnh mẽ. join_row_counts_before_after.png Tuy nhiên, nhiều đường ống dữ liệu nối hai bảng mà cả hai đều chứa các bản sao trên khóa. Do đó, phép nối trở thành nhiều-nhiều và gây ra sự bùng nổ hàng. Kết quả là, bạn nhận được số lượng bị thổi phồng, doanh thu bị tính hai lần, các sự kiện lặp lại và các tính năng mô hình sai. Bài viết này trình bày một cách thực tế để phát hiện sự trùng lặp phép nối bằng cách sử dụng hàm kiểm tra phép nối với ba kiểm tra: Tính duy nhất của khóa (trên mỗi bảng, trên mỗi khóa) Tỷ lệ bùng nổ hàng (trước và sau phép nối) Phạm vi chống nối (những gì không khớp) Sự trùng lặp phép nối trông như thế nào trong các hệ thống thực tế Các trường hợp sử dụng bị bỏ qua: Kỹ thuật tính năng: các tính năng người dùng nối với các bảng sự kiện → số lượng bị thổi phồng → hành vi giống như rò rỉ mô hình Tài chính: các giao dịch nối với tỷ giá hối đoái với ngày trùng lặp → số tiền bị nhân lên Phân tích sản phẩm: các phiên nối với lượt xem trang và sau đó nối với các chiến dịch → các chỉ số phễu bùng nổ Mặc dù các kiểu dữ liệu trông chính xác, nhưng tính đa dạng lại sai. Do đó, bạn cần các kiểm tra kiểm toán tập trung vào hành vi phép nối, không chỉ lược đồ. Bước 1 — Tạo dữ liệu mẫu sẽ phá vỡ một phép nối Chúng ta sẽ xây dựng: orders: một hàng cho mỗi đơn hàng (nên là duy nhất theo order_id) payments: hai hàng cho một đơn hàng (thanh toán một phần) customers: hai hàng cho một khách hàng (bảng chiều xấu) Điều này tạo ra tình huống nhiều-nhiều khi bạn nối không chính xác. import pandas as pd orders = pd.DataFrame({ "order_id": [101, 102, 103, 104, 105], "customer_id":[ 1, 1, 2, 3, 4], "order_total":[120, 80, 50, 200, 70] }) payments = pd.DataFrame({ "order_id": [101, 101, 102, 104, 104, 106], # 106 does not exist in orders "paid_amt": [ 60, 60, 80, 100, 100, 40], "method": ["card","card","card","bank","bank","card"] }) customers = pd.DataFrame({ "customer_id":[1, 1, 2, 3, 4], "segment": ["A","A","B","C","B"], "status": ["active","active","active","inactive","active"], # duplicate row for customer_id=1 is a classic dimension-table bug }) orders, payments, customers # Output: # +----------+-------------+-------------+ # | order_id | customer_id | order_total | # +----------+-------------+-------------+ # | 101 | 1 | 120 | # | 102 | 1 | 80 | # | 103 | 2 | 50 | # | 104 | 3 | 200 | # | 105 | 4 | 70 | # +----------+-------------+-------------+ # +----------+----------+--------+ # | order_id | paid_amt | method | # +----------+----------+--------+ # | 101 | 60 | card | # | 101 | 60 | card | # | 102 | 80 | card | # | 104 | 100 | bank | # | 104 | 100 | bank | # | 106 | 40 | card | # +----------+----------+--------+ # +-------------+---------+----------+ # | customer_id | segment | status | # +-------------+---------+----------+ # | 1 | A | active | # | 1 | A | active | # | 2 | B | active | # | 3 | C | inactive | # | 4 | B | active | # +-------------+---------+----------+ Giải thích: payments có nhiều hàng trên mỗi order_id (có thể hợp lệ). customers có nhiều hàng cho customer_id=1 (thường không hợp lệ). Do đó, một phép nối vô hại có thể nhân đôi các đơn hàng và thổi phồng tổng số. Bước 2 — Phép nối xấu (Bùng nổ hàng) bad = ( orders .merge(payments, on="order_id", how="left") .merge(customers, on="customer_id", how="left") ) print("orders rows:", len(orders)) print("bad join rows:", len(bad)) bad # Output: # orders rows: 5 # bad join rows: 10 Giải thích: Chúng ta bắt đầu với 5 đơn hàng và kết thúc với 8 dòng. Đây là một sự bùng nổ về số lượng dòng. Mặc dù 8 không phải là con số lớn ở đây, nhưng trong môi trường sản xuất, con số này có thể nhanh chóng tăng từ 50 triệu lên 400 triệu. Kết quả là, các tổng và số lượng sẽ bị thổi phồng. Bằng chứng: Tổng hiện tại bị sai in("Tổng order_total ban đầu:", orders["order_total"].sum()) in("Sau khi nối sai, tổng (order_total):", bad["order_total"].sum()) # Kết quả: # Tổng order_total ban đầu: 520 # Sau khi nối sai, tổng (order_total): 1160 Giải thích: Tổng giá trị đơn hàng không nên thay đổi sau các phép nối làm giàu dữ liệu. Tuy nhiên, phép nối đã nhân đôi các đơn hàng. Do đó, tổng giá trị đã tăng từ 520 lên 760, đây là một lỗi tài chính thầm lặng. Bước 3 – Xây dựng hàm kiểm tra phép nối Phép kiểm tra này sẽ trả lời các câu hỏi sau: Các khóa nối có duy nhất ở bên trái và bên phải không? Có bao nhiêu bản sao tồn tại theo khóa? Số lượng dòng đã mở rộng bao nhiêu sau phép nối? Những khóa nào không khớp? (phép nối ngược ở cả hai phía) import numpy as np def join_audit(left: pd.DataFrame, right: pd.DataFrame, on, how="left", left_name="left", right_name="right", sample_n=5): if isinstance(on, str): on = [on] # thống kê trùng lặp khóa left_dups = left.duplicated(subset=on).sum() right_dups = right.duplicated(subset=on).sum() left_key_counts = left.groupby(on).size().sort_values(ascending=False) right_key_counts = right.groupby(on).size().sort_values(ascending=False) # hợp nhất với chỉ báo để xem mức độ khớp merged = left.merge(right, on=on, how=how, indicator=True) row_ratio = len(merged) / max(len(left), 1) # phép nối ngược: các khóa bên trái không khớp (khi how bao gồm left) # Đối với phép nối trái, _merge == "left_only" là các dòng bên trái không khớp unmatched_left = merged.loc[merged["_merge"] == "left_only", on].drop_duplicates()