Joins, Cardinality, Deduplication, and Reconciliation

The expected relationship should be declared before code is written.

One-to-one means each left record matches at most one right record, so a duplicate match is an identity or version error rather than extra data. Many-to-one lets many events enrich from one entity snapshot, chosen under a valid effective time. One-to-many expands one example into detail rows and usually needs aggregation before modeling. Many-to-many holds multiple candidates on both sides, where naive joining creates a combinatorial product.

Writing the expected relationship down converts each of these into a test. If the join returns a shape the declaration did not predict, the defect sits in the identity model, not in the query. And the query will not report it. Multiplying rows is exactly what a join is for.

A subpostmistress scanned a single £8,000 cash remittance from her core branch into her outreach branch. She scanned it once. In Horizon “the transfer replicated four times”, the system recorded receipts of £32,000, and the branch carried a phantom discrepancy of £24,000 — money the accounts said was missing and that had never existed.

The fault has a judgment behind it. Mr Justice Fraser handed down the sixth Bates v Post Office judgment, on the “Horizon Issues”, in the High Court on 16 December 2019. Among the defects the court examined was the “Dalmellington Bug”, which made Horizon replay the tail of a pouch-delivery script and issue duplicate pouch IDs. Fujitsu's own internal email trail on the error, disclosed to the Post Office Horizon IT Inquiry, records that the system “repeated the inward remittance transaction”.

Nothing about the duplicated rows was malformed. Each one was a valid transaction record, internally consistent, arithmetically well behaved. The total simply counted a real event four times.

Choosing one strategy before the domain says what counts as the same event can erase valid episodes or preserve duplicates.

Some duplicates are prevented upstream rather than removed later. Kafka has offered an idempotent producer since release 0.11.0.0, and its documentation says resending “will not result in duplicate entries in the log”. The broker assigns each producer an ID and deduplicates on a sequence number sent with every message. Confluent's documentation describes the same mechanism in the same terms. Note what the guarantee covers: retries of one write, not two genuine events that look alike.

Entity reconciliation is not the informal option of last resort. It has a formal decision rule older than most of the systems that run it. Fellegi and Sunter published A Theory for Record Linkage in 1969. Each candidate pair is scored by the likelihood ratio R = P(γ|M)/P(γ|U). Two cutoffs then split every pair into three disjoint regions: a match above Tμ, a non-match below Tλ, and between them a region that decides nothing. Winkler states the middle rule as: “If Tλ ≤ R ≤ Tμ, then designate pair as a possible match and hold for clerical review.” The cutoffs are not tuned by feel. They are set from a priori error bounds on false matches and false non-matches. The size of the ambiguous class is a stated consequence of the error rates the linkage is willing to accept.

The rule is in production. The UK Ministry of Justice's open-source Splink implements the model with m and u probabilities estimated by EM, built on “FastLink's implementation in R of an Expectation-Maximisation algorithm to estimate a Fellegi-Sunter linkage model”. It has been used to produce deduplicated and linked data for the entire criminal justice system. A pipeline that returns only matched and unmatched has not avoided the third region. It has silently assigned every pair in it to one of the other two.

Run the report for every dataset release. Compare it with the previous edition.

Step 1 and step 4 are the cheapest lines in the procedure and the most expensive to skip. On 4 October 2020 Public Health England reported that “15,841 cases between 25 September and 2 October were not included in the reported daily COVID-19 cases”. Some files containing positive test results had exceeded the maximum file size. Those rows were never transferred into daily reporting — or into contact tracing. From inside the pipeline the failure is a row count on each input that does not reconcile with the row count of the output, checked once per release.

The cost of not running that check has been estimated. Fetzer and Graeber used the gap as a natural experiment and published the result in PNAS in 2021. Each case referred late was associated with about 18.6 additional infections and 0.24 additional deaths over the following six weeks: over 125,000 additional infections and over 1,500 additional deaths in total. Every one of those records was correct where it was written. It was the transfer that lost them, and nothing downstream announced the loss.

An account can change household, a merchant can change category, and a sensor can move between assets. Joining every event to the latest relationship rewrites history.

Model the effective interval of the relationship. Then decide whether the decision should use the state at event time, scoring time, or another declared cutoff.

This is not a bespoke pattern each team has to invent. SQL:2011 added application-time period tables, in which the user sets the validity interval of each row, and system-versioned tables, in which the system maintains it. It also added period-qualified referential integrity. A child row can be forbidden unless its application-time period is contained in the period of a matching parent row, or in the union of contiguous matching parent rows. That constraint is the timeless foreign key rejected at the engine rather than caught in review. Kulkarni and Michels documented the features in 2012, and MariaDB, DB2 and SQL Server implement them. MariaDB's own documentation states the origin: “System-versioned tables were first introduced in the SQL:2011 standard.”

For slowly changing dimensions, test boundary conditions around start and end times. Overlapping intervals and gaps should be explicit errors or documented fallbacks.

A compacted log makes the same trade explicitly. Kafka's design documentation says compaction “ensures that Kafka will always retain at least the last known value for each message key within the log of data for a single topic partition”. HPE's Data Fabric documentation describes the behaviour the same way. That is a current-state store, useful for reloading caches after a restart. It is not a source for reconstructing what a key held last March.

Reconciliation metrics, ambiguity counts, deduplication policy, and examples of unresolved cases should travel with a dataset release. These artifacts explain why the population changed between versions. When a model shift appears, the team can compare join diagnostics before retraining or tuning. That shortens root-cause analysis and prevents the learner from being blamed for an identity defect. The report also supports governance: it reveals whose records are disproportionately unmatched, multiplied, or discarded.

The 2020 Census shows what that disclosure is worth. The Census Bureau's Post-Enumeration Survey, released on 10 March 2022, found no statistically significant net coverage error for the total population. Underneath that total: “The Hispanic or Latino population had a statistically significant undercount rate of 4.99%.” The Black or African American alone-or-in-combination population was undercounted by 3.30%, and American Indian or Alaska Native people living on reservations by 5.64%. The non-Hispanic White alone population was overcounted by 1.64%, and the Asian alone-or-in-combination population by 2.62%. A Government Accountability Office audit in 2024 confirmed the pattern held across two censuses: “in 2020 and 2010, Black or African American and Hispanic persons, young children, and renters were undercounted, while non-Hispanic White persons, adults over 50, and homeowners were overcounted”.

A release that published only the national figure would have published a defensible number and concealed every one of those. Sliced evidence is what makes that difference visible to someone who did not run the join.