ResearchPractical AIProjectsOpen SourceAbout
Projects/Finding and Fixing Silent Data Loss in a Live Lead System
Data EngineeringData QualityDagsterdbtDelta Lake

Finding and Fixing Silent Data Loss in a Live Lead System

A system that collects about 6,400 businesses twice a day, opened with a formal check of its own data quality. It found two serious problems that never raised an error: one quietly merged 545 different businesses into others, and another threw away 57.8% of one collector's results. I then rebuilt it with 197+ automated checks so the same kind of bug can't hide again.

Published 2026-09-07 · Last modified 2026-10-07

Measured results

  • ▲545 different businesses recovered after a faulty duplicate check had quietly merged them into others (8.5% of the table)
  • ▲57.8% of one collector's results recovered from a settings bug that looked exactly like a legitimate empty answer
  • ▲A repair script caught in review before it ran: it would have added up to 6,378 duplicate rows every time it ran
  • ▲A faulty join caught multiplying one table's rows by 28, while every build report still said everything was fine
  • ▲197+ automated checks now guard every stage of the system
  • ▲The rebuilt system runs end to end on a live schedule, with its full test build clean at 36 passed, 0 warnings, 0 errors

The short version

I run a live system that collects about 6,400 businesses in the Philippines twice a day, uses an AI model to score how well each fits a loan company's ideal customer, and hands about 2,000 qualified leads to a sales team. I'm a software engineer, not a trained data engineer. I built it using tools I already knew.

Before rebuilding it, I checked its data quality against the real, live data. It turned up 13 problems. Two were serious, and both were silent: neither caused an error, a failed run or an alert. The numbers just quietly came out wrong.

The lesson: the most dangerous bugs in an automated system are the ones that look like success. A dashboard that says "all green" can be wrong for months. The fix is not more dashboards. It's checks that test whether the output is actually right.

The duplicate check that erased 545 businesses

The system decides whether two records are the same business by comparing a Google Maps ID, and if that's missing, their website. The ID was never actually being collected, so every run quietly used the website instead. That quietly assumed "two businesses with the same website are the same business."

For small Philippine businesses, that is badly wrong. Many list their Facebook page as their website. 231 websites were shared by more than one business. Re-running the corrected rule over all 6,378 records showed that 545 different businesses had been wrongly merged away: 8.5% of the table, on every run, for as long as the bug existed.

The setting that erased 57.8% of one collector's results

Two collectors shared the same logic for labelling each business by category, but differed in one setting: how long the AI's reply was allowed to be (300 vs 40,000 "tokens", the small chunks AI models count text in). The model's real replies run about 775 tokens, so at 300 more than half of every reply was cut off mid-sentence. The system caught the broken reply and filed it as "no categories apply", which is indistinguishable from a legitimate empty result.

So over half of that collector's output was never actually categorised, and nothing reported it, because a failure and a genuine "nothing" looked identical further down the line.

The repair script that would have made it worse

Fixing the setting didn't repair the damaged records, so I built a repair script by copying the original collector and rewiring it to read old records instead of collecting new ones. The reading side was rewired correctly. The writing side wasn't: it still added records instead of updating them. Run once, it would have created up to 6,378 duplicate rows. On its 4-hour schedule, it would have kept going, and nothing would have stopped it, because the safety net that existed in the original didn't apply to the copy.

I caught this in review, before it ever ran, by asking one question: does a safeguard that made sense in the original still do anything once the code is copied somewhere new? The fix updates each record by its ID, so running it again is harmless. 33 automated checks now watch for exactly this kind of bug, a safeguard that looks like it's doing something but isn't.

The join that multiplied one table by 28

In the reporting layer, a placeholder (with a comment saying "location extracted from address during ingestion") joined every scoring event to every one of 28 locations. Each event was counted 28 times: 52,696 rows for 1,882 real events. Every count, rate and average downstream was wrong by exactly 28x.

The build tool reported everything clean, because that reporting layer had no checks at all. A row-multiplication bug doesn't break any "this field must not be empty" rule. Only a check that each event appears exactly once would have caught it.

What I built so this kind of bug is harder to repeat

  • One written rulebook. Instead of five scattered definitions of "what counts as a hot lead" (in a prompt, a parser, two scripts and a reporting file), there is one rule set. The code and documentation are generated from it and checked automatically.
  • A holding area instead of silent deletion. Records that break the rules go to a quarantine table with a reason. They are never thrown away and never quietly "fixed". An automatic check watches the quarantine rate against its historical 11%.
  • 197+ automated checks in four layers. These test the code in small pieces, the assembled workflows, whether two workflows agree with each other, and the live AI model. The workflow tool stores its logic as a block of text with no built-in checking, so I moved that logic into ordinary files that can be tested and inserted when the system is built.
  • A safe way to deploy changes to the workflow tool itself. The existing sync tool claimed two safety features (automatic backup and checking after a push) that had never actually been built.
  • A live, running schedule. I learned the hard way that switching a setting to "running" only describes what a scheduler would do if one exists. It doesn't create the scheduler. The fix was an explicit configuration and a startup script that checks the scheduler is really alive.

Closing the loop, September 2026

Two findings were left open on purpose after the first rebuild. The Google Maps ID was never collected at the source (the root cause of the first bug), and 89 duplicate businesses were still in the table from earlier runs. Both have since moved.

The collector now reads Google's stable ID straight from each listing's own link. Nothing else in the system had to change, because everything downstream was already set up to receive that value and had just never been given one. The 89-record cleanup is built to merge safely: it moves each lead's scoring history to the surviving record before removing the duplicate, so deleting one never wipes out a lead's history. It is tested without needing any database.

When I tried to run that cleanup against the live database, a third problem surfaced that I want to name honestly. The production database stopped responding altogether mid-fix. That's a different failure from anything the original check was looking for, and a gap in its own right: nothing in the system's freshness checks tells "the data is stale" apart from "the data source is gone." I recorded it and added it to the to-do list rather than smoothing it over, in keeping with the rest of this project.

Tools

n8n, Supabase, Delta Lake, DuckDB, dbt, Dagster, Python and LM Studio.

Limitations

  • I checked this against my own data. There has been no independent review.
  • The first attempt to run the 89-record cleanup against the live database hit an outage mid-fix. That is recorded as an open problem, not a solved one.
  • The freshness checks cannot yet tell stale data apart from a source that has gone away.

What this shows

  • Designing the rules, not just the code: one authoritative rulebook instead of five drifting copies.
  • Owning the design: every fix traces back to a design decision, not a typo.
  • Finding bugs before they cost anything: the repair-script bug was caught in review, before a single run.
  • Testing a tool that resists testing: the workflow tool has no built-in checking, so I built the missing scaffolding.
  • Honest failure modes: every "success" signal in this system (a green build, a passing test suite, a "running" flag) was, at some point, found to be reporting success for work it hadn't actually done.

This is personal, self-directed work, not a client or employer deliverable, and it is not sold or licensed. It is built on my own existing lead data and deliberately shaped like a modern "medallion" data architecture (raw, cleaned and ready-to-use layers). No employer code, data or confidential material was used.