Diagnose schema, join and quality failures

Understand the likely causes

• Causes include broken CSV/JSON interpretation, changed units or null defaults, boolean values accepted as integers, a missing correlation/tenant predicate, two independent child joins, or quality checks limited to supplied rows rather than the promised source population. A readable file and syntactically valid SQL do not establish correct business meaning.

Pin the failing version and expected grain

Record source and output identities, schema/transform versions, interval, expected key population and acceptance rules. Preserve a small synthetic counterexample rather than copying private production rows into a ticket. Separate decoding, parsing and type-conversion errors from downstream relationship errors. Compare actual SQL and bound parameter types, not an approximate hand-written query. Avoid relaxing a threshold or deleting a unique constraint merely to turn the job green; first identify which rule or population the current output violates.

Reject incompatible values without losing their identity

The local fixture permits only nonnegative Python int quantities and deliberately excludes bool, which is a subclass of int in Python. The text "3" is also rejected under this declared policy. Another source may permit a reviewed explicit conversion, but the rule must be stated rather than inherited from convenience truthiness or coercion. Retain rejected identity and a scoped reason under the data-retention policy; do not silently turn malformed amounts into zero.

rows = [{'id': 'e1', 'qty': 3}, {'id': 'e2', 'qty': True}, {'id': 'e3', 'qty': '3'}]
def valid(row):
    return type(row['qty']) is int and row['qty'] >= 0
print('accepted=' + str([r['id'] for r in rows if valid(r)]))
print('rejected=' + str([r['id'] for r in rows if not valid(r)]))

accepted=['e1']
rejected=['e2', 'e3']

Inspect multiplicity before correcting sums

The in-memory SQLite program joins two lines and three payments for one parent, producing six combinations and inflated sums. It demonstrates the failure rather than a recommended query. Preaggregate the two measures independently to the common parent grain before combining them. DISTINCT on amount is unsafe because legitimate equal amounts can be separate events. Also test optional parents, child-filter placement and empty input; a convenient one-child fixture can hide the defect. The program opens no disk or configured database.

import sqlite3

db = sqlite3.connect(':memory:')
db.executescript('CREATE TABLE line(parent INTEGER,amount INTEGER); CREATE TABLE payment(parent INTEGER,amount INTEGER); INSERT INTO line VALUES(1,10),(1,15); INSERT INTO payment VALUES(1,5),(1,8),(1,12);')
result = db.execute('SELECT SUM(l.amount),SUM(p.amount),COUNT(*) FROM line l JOIN payment p ON p.parent=l.parent').fetchone()
assert result == (75, 50, 6)
print(f'line_total={result[0]} payment_total={result[1]} rows={result[2]}')
db.close()

line_total=75 payment_total=50 rows=6

Reconcile the full promised population before publication

Apply the reviewed parsing, type or relationship correction to an isolated candidate and compare exact expected keys and values. Accepted-plus-rejected accounting validates supplied input only; separately check expected files, intervals or source manifests. Bind quality results to the candidate’s input and transformation versions. If the repair changes a previously published result, coordinate replacement/delta semantics with consumers and preserve rollback evidence. This guide performs no production edit, threshold change, source query or shared-data replay.

Keep it from recurring

• Version units and schema semantics, retain scoped rejects and keep grain documented at each join/group stage. Use key-level and relationship checks alongside counts, review actual reader/writer compatibility, and make critical acceptance rules visible individually instead of averaging them away.