Đối soát số liệu nguồn và đích sau khi nạp
So số dòng, số giá trị được báo cáo, số lượng và định danh trong đúng phạm vi để tìm lỗi bù trừ, thay đổi null và thiếu sót chung.
Một lần nạp đã chép bốn bản ghi và đích vẫn chứa bốn bản ghi. Tổng số lượng tại đích cũng bằng nguồn. Kết quả có vẻ đáng yên tâm cho tới khi số lượng của một khách hàng tăng một đơn vị còn của khách khác giảm một đơn vị. Hai lỗi triệt tiêu trong tổng nhưng vẫn sai với cả hai khách hàng.
Đối soát xem những thuộc tính nào của một tập nguồn xác định đã được giữ qua lần nạp. Số dòng, tổng, số giá trị thiếu, kiểm tra khóa và so sánh từng dòng trả lời các câu hỏi khác nhau. Bài này xây dựng các kiểm tra ấy cho một lô nhỏ và chỉ ra điều mà ngay cả kết quả khớp hoàn toàn cũng không chứng minh được về tập dữ liệu bạn đã chọn.
Bài dành cho người đã hiểu phép gom nhóm và nối cơ bản trong SQL. Python chỉ tạo bộ dữ liệu SQLite và thu kết quả truy vấn. Dùng Python từ 3.12 trở lên; lần chạy được ghi nhận dùng Python 3.12.3 và SQLite 3.45.1. Mọi số lượng đều là số nguyên có giới hạn hoặc NULL do bài đặt ra, không phải dữ liệu khách hàng thật hay phép đo dấu phẩy động.
Xác định tập dữ liệu trước khi so sánh
Nguồn và đích đều chứa lô load-17, cùng một dòng không liên quan thuộc load-18. Phạm vi đối soát chỉ là load-17. So toàn bộ nguồn với một lô tại đích sẽ báo lỗi do phạm vi khác nhau; ngược lại, so hai bộ lọc cùng vô tình thiếu dữ liệu có thể che một thiếu sót thật.
Hợp đồng sao chép giữ mã định danh nguyên không null và số lượng nguyên có thể null cho từng dòng được chọn. Mỗi mã chỉ được có một dòng trong lô. Nếu pipeline thực tế có chủ ý gom nhóm, lọc hoặc đổi đơn vị, trước tiên cần biểu diễn quan hệ đích kỳ vọng từ ảnh chụp nguồn đã chọn. So trực tiếp các dòng thô qua phép biến đổi như vậy sẽ áp đặt sai hợp đồng.
Đặt bốn khối Python lần lượt vào một tệp mã. Chạy khi không có bên ghi khác; giao dịch cục bộ tạo ranh giới đọc cơ sở dữ liệu nhất quán, nhưng ví dụ không có nguồn từ xa hay tiến trình nạp dữ liệu sản xuất.
import hashlib, json, sqlite3
db = sqlite3.connect(':memory:', autocommit=sqlite3.LEGACY_TRANSACTION_CONTROL)
db.executescript("""CREATE TABLE source(batch TEXT, id INTEGER NOT NULL, qty INTEGER);
CREATE TABLE target(batch TEXT, id INTEGER NOT NULL, qty INTEGER);""")
BATCH = 'load-17'
original = [(101, 10), (102, 20), (103, None), (104, 0)]
with db:
db.executemany('INSERT INTO source VALUES (?, ?, ?)',
[(BATCH, key, qty) for key, qty in original])
db.execute("INSERT INTO source VALUES ('load-18', 999, 500)")
db.execute("INSERT INTO target VALUES ('load-18', 999, 500)")
def replace_target(rows):
with db:
db.execute('DELETE FROM target WHERE batch = ?', (BATCH,))
db.executemany('INSERT INTO target VALUES (?, ?, ?)',
[(BATCH, key, qty) for key, qty in rows])
def fingerprint(rows):
raw = json.dumps(rows, separators=(',', ':'), ensure_ascii=True).encode()
return hashlib.sha256(raw).hexdigest()Bốn dòng nguồn được chọn có số lượng 10, 20, NULL và 0. NULL nghĩa là chưa có giá trị được báo cáo. Giá trị 0 là một số lượng đã được báo cáo. Lược đồ chủ ý cho phép mã định danh trùng để phép đối chứng sau chứng minh vì sao phải kiểm tra chúng trước. Dữ liệu thử đáng tin cậy; bảng này không phải bộ xác thực đầu vào tổng quát cho mọi kiểu giá trị SQLite.
Dùng số liệu tổng hợp để tìm vấn đề
Tài liệu hàm tổng hợp của SQLite định nghĩa COUNT(*) là số dòng và COUNT(qty) là số lượng giá trị không null. SUM(qty) cộng các giá trị không null. Vì vậy, bộ số liệu nguồn là (4, 3, 30); nó không khẳng định đã biết đủ số lượng của cả bốn dòng.
Số dòng khác nhau gợi ý có dòng thiếu hoặc thừa. Số giá trị không null khác nhau có thể cho thấy giá trị bị mất hoặc một giá trị thay thế được tự thêm. Tổng khác nhau cho biết có sai khác số học trong phạm vi này. Cả ba số khớp nhau vẫn hữu ích, nhưng không chứng minh mỗi mã định danh đang nhận đúng giá trị của nó.
Hàm sau chạy mọi truy vấn trong một giao dịch đọc được mở rõ ràng. Tên bảng lấy từ bộ giá trị nội bộ cố định; giá trị lô được truyền bằng tham số SQL. Hàm yêu cầu kết nối đang rảnh và chỉ kết thúc giao dịch mà chính nó đã mở.
def reconcile():
if db.in_transaction:
raise ValueError('reconciliation owns an idle connection')
db.execute('BEGIN')
try:
stats, duplicate_ids, rows = {}, {}, {}
for table in ('source', 'target'): # Fixed internal table names.
stats[table] = db.execute(
f'SELECT COUNT(*), COUNT(qty), SUM(qty) FROM {table} WHERE batch = ?',
(BATCH,)).fetchone()
duplicate_ids[table] = db.execute(
f'SELECT id, COUNT(*) FROM {table} WHERE batch = ? GROUP BY id HAVING COUNT(*) > 1',
(BATCH,)).fetchall()
rows[table] = db.execute(
f'SELECT id, qty FROM {table} WHERE batch = ? ORDER BY id, qty',
(BATCH,)).fetchall()
if any(duplicate_ids.values()):
return {'status': 'invalid keys', 'stats': stats, 'duplicates': duplicate_ids}
differences = db.execute("""
WITH s AS (SELECT id, qty FROM source WHERE batch = ?),
t AS (SELECT id, qty FROM target WHERE batch = ?)
SELECT s.id, CASE WHEN t.id IS NULL THEN 'missing' ELSE 'changed' END,
s.qty, t.qty
FROM s LEFT JOIN t ON t.id = s.id
WHERE t.id IS NULL OR s.qty IS NOT t.qty
UNION ALL
SELECT t.id, 'unexpected', NULL, t.qty
FROM t LEFT JOIN s ON s.id = t.id WHERE s.id IS NULL
ORDER BY 1, 2
""", (BATCH, BATCH)).fetchall()
return {'status': 'match' if not differences else 'mismatch',
'stats': stats, 'differences': differences,
'fingerprints': {table: fingerprint(values) for table, values in rows.items()}}
finally:
db.rollback() # End this read-only transaction; no business writes occur.Trước khi ghép dòng, hàm kiểm tra tính duy nhất riêng ở từng phía. Khóa trùng làm phép so sánh một-một dự định trở nên không xác định, ngay cả khi số dòng và tổng vẫn khớp. Tài liệu kiểm thử dữ liệu của dbt cũng tách tính duy nhất, yêu cầu không null, tập giá trị chấp nhận và quan hệ tham chiếu thành các điều kiện riêng. Đạt một điều kiện không thay thế các điều kiện khác; ví dụ này không chạy dbt.
Phép nối đầu tìm các mã nguồn thiếu dòng tương ứng tại đích hoặc có số lượng thay đổi. Phép nối thứ hai tìm mã tại đích không có trong nguồn. Nhãn missing và unexpected có ý nghĩa vì chỉ nhìn một ô số lượng NULL không đủ biết dòng có tồn tại hay không. Ta dùng mã định danh bắt buộc không null để xác định sự hiện diện của dòng.
Với giá trị, toán tử IS NOT của SQLite thực hiện phép so sánh có xử lý null cần thiết: NULL với NULL là không đổi, còn NULL với 0 là thay đổi. Biểu thức s.qty != t.qty thông thường cho kết quả chưa xác định khi một toán hạng là NULL và có thể âm thầm loại sai khác ấy khỏi mệnh đề WHERE. Các hệ khác thường viết điều kiện tương ứng bằng IS DISTINCT FROM; cần kiểm tra ngữ nghĩa của hệ đang dùng trước khi chuyển truy vấn.
Khung dễ đọc này được dựng từ đúng câu truy vấn cục bộ và kết quả đã thu. Hai truy vấn dùng cùng lô bốn dòng; đây không phải ảnh chụp giao diện một ứng dụng cơ sở dữ liệu gốc.
Đọc các trường hợp đối chứng theo mã định danh
cases = {
'reordered': list(reversed(original)),
'balanced error': [(101, 11), (102, 19), (103, None), (104, 0)],
'null replaced by zero': [(101, 10), (102, 20), (103, 0), (104, 0)],
'wrong zero-quantity key': [(101, 10), (102, 20), (103, None), (105, 0)],
'duplicate key': [(101, 10), (101, 20), (103, None), (104, 0)],
}
for label, candidate in cases.items():
replace_target(candidate)
report = reconcile()
print(label, report['status'], report['stats'],
report.get('differences', report.get('duplicates')))Bản sao đảo thứ tự vẫn khớp vì thứ tự dòng không thuộc hợp đồng này. Trường hợp lỗi bù trừ giữ nguyên (4, 3, 30) nhưng báo mã 101 đổi từ 10 thành 11 và mã 102 đổi từ 20 thành 19. Một tổng không chỉ ra được cặp sai số trái dấu nào đã giữ cho nó không đổi.
Thay NULL bằng 0 giữ tổng ở 30 nhưng làm số giá trị không null tăng từ ba lên bốn. Kết quả chi tiết chỉ ra mã 103. Thay mã 104 bằng 105 cũng giữ nguyên cả bộ số tổng hợp; kết quả báo thiếu 104 và thừa 105. Ngay cả bản ghi có số lượng bằng không vẫn mang định danh và có thể quan trọng với độ đầy đủ.
Trường hợp khóa trùng tiếp tục có cùng bộ số tổng hợp. Đích chứa mã 101 hai lần, nên hàm trả invalid keys thay vì đưa ra báo cáo sai khác từng dòng có thể gây hiểu nhầm. Hãy xử lý vi phạm cấp độ duy nhất của dữ liệu này trước khi coi kết quả nối là một phép so sánh cho mỗi thực thể nghiệp vụ.
Bộ số liệu nguồn luôn là bốn dòng, ba số lượng đã báo cáo và tổng 30. Mọi trường hợp được dựng để làm rõ phạm vi của từng phép kiểm tra.
Mã băm của các dòng đã sắp xếp là phép so sánh gọn bổ sung. Nó chứa cả định danh lẫn giá trị null tường minh của JSON, nên đổi thứ tự dòng giữ cùng mã băm còn lỗi bù trừ thì đổi mã. Cách kiểm tra chỉ ghép các số lượng sẽ bỏ qua định danh; cộng các mã băm dòng lại đưa vào quy tắc biểu diễn và kết hợp riêng.
Ở đây, biểu diễn byte có phạm vi hẹp: các cặp đã sắp thứ tự gồm định danh nguyên và số lượng nguyên hoặc null, được ghi thành JSON với dấu phân cách cố định. Mở rộng sang số thập phân, thời điểm, NaN dấu phẩy động hay chuẩn hóa văn bản đòi hỏi cách biểu diễn chuẩn được xác định rõ. Mã băm không xác thực nguồn, và so mã băm không thay thế các sai khác chi tiết vốn đã có trong bộ thử nhỏ. Va chạm mã băm mật mã vẫn có thể xảy ra; quyết định khớp của hàm dùng phép so sánh quan hệ thật, không dựa trên việc hai mã băm bằng nhau.
Phân biệt hai phía đồng ý với nguồn đầy đủ
replace_target(original)
assert reconcile()['status'] == 'match'
with db:
db.execute('DELETE FROM source WHERE batch = ? AND id = 104', (BATCH,))
db.execute('DELETE FROM target WHERE batch = ? AND id = 104', (BATCH,))
report = reconcile()
assert report['status'] == 'match'
assert report['stats']['source'][0] == 3
print('same omission on both sides:', report['status'])
# The external expectation of four identities was not part of this comparison.Phép đối chứng cuối xóa định danh 104 ở cả hai phía. Đối soát báo khớp vì hai quan hệ được cung cấp giống nhau hoàn toàn. Kỳ vọng từ bên ngoài rằng lô phải chứa bốn định danh chưa hề được đưa cho hàm. Tính số dòng kỳ vọng từ chính lần trích xuất đang thiếu dữ liệu cũng không tạo ra bằng chứng độc lập.
Với một lần nạp thực tế, cần giữ mốc cắt hoặc mã ảnh chụp nguồn, phạm vi nghiệp vụ đã chọn và tập đối tượng kỳ vọng được xác lập độc lập nếu có. Một bản danh mục xuất nguồn có thể khai báo số dòng; một sổ riêng có thể liệt kê khóa dự kiến. Mỗi nguồn bằng chứng có giả định về độ tin cậy và tính hiện hành riêng. Chỉ một số dòng vẫn không xác định được đúng tập đối tượng, như trường hợp sai khóa đã cho thấy.
Thiếu sót làm tập được chọn giảm từ bốn định danh xuống ba nhưng vẫn giữ tổng số lượng ở 30. Hình không khẳng định hàm cục bộ có thể tự phát hiện một kỳ vọng nguồn chưa được cung cấp.
Giao dịch đọc cũng chỉ có mục đích giới hạn. Nó giữ các truy vấn cục bộ trên một cách nhìn nhất quán theo quy tắc cô lập giao dịch của SQLite. Nó không đồng bộ nguồn từ xa đang thay đổi độc lập với đích được lấy mẫu lúc khác, không chứng minh một lần xuất nhiều trang đã hoàn tất và không ngăn đích bị sửa sau đó. Lưu phiên bản nguồn và đích đã kiểm tra cùng kết quả để người đọc về sau biết báo cáo bao phủ trạng thái nào.
Xử lý đầu vào rỗng và lớn một cách có chủ ý
Khi không có dòng, bộ số liệu là (0, 0, None) vì SUM của SQLite trả NULL khi không có giá trị không null. Bốn dòng đều có số lượng NULL cho (4, 0, None). Một dòng có số lượng 0 cho (1, 1, 0). Giữ số đếm cạnh tổng bảo toàn các phân biệt này, thay vì đổi mọi tổng thiếu thành 0.
Số lượng nguyên nhỏ và tổng trong bộ thử đều vừa miền số nguyên có dấu của SQLite. Hợp đồng sản xuất phải giới hạn cả tổng lẫn từng dòng; SUM nguyên có thể tràn dù mọi dòng riêng lẻ đều vừa miền. Chuyển sang TOTAL dấu phẩy động làm đổi hợp đồng số học, không phải cách sửa chính xác cho vấn đề đó.
Cách cài đặt này đưa cả hai tập dòng đã sắp xếp vào bộ nhớ để tạo mã băm và trả về mọi sai khác. Nó phục vụ việc hiểu cơ chế, không phải tuyên bố về khả năng mở rộng. Đối soát lớn hơn cần chiến lược giới hạn báo cáo và cách so sánh theo phân vùng hoặc luồng mà vẫn giữ các quy tắc về khóa, null, biểu diễn và ảnh chụp. Lưu sai khác đại diện mà không đưa ra các trường nhạy cảm không cần thiết.
Trong bài tập, đặt lại cả hai phía về bốn dòng ban đầu trước mỗi trường hợp. Thêm mã 106 với số lượng 0 vào đích; kỳ vọng đích có năm dòng, tổng vẫn 30 và một kết quả unexpected. Sau đó đổi số lượng nguồn của 103 thành 5 trong khi đích vẫn NULL; phải có kết quả changed dù có null. Cuối cùng so hai lô rỗng trong phạm vi và hai lô có toàn giá trị null. Ghi cả những gì khớp lẫn phát biểu về độ đầy đủ bên ngoài vẫn chưa được kiểm tra.