What 83 broken government files taught us about extracting structured data

Before writing a general-purpose pipeline for extracting structured data from Vietnamese documents, this project tested the underlying approach on something concrete and unglamorous: 83 real PDF and Excel files published by the Hanoi Department of Construction (Sở Xây dựng Hà Nội), listing foreign-buyer apartment purchases. Property data is not the product here — it's the proving ground. Government registries, no consistent format between files, and the kind of defects that only show up once you actually try to parse a few hundred of them: corrupted ZIP containers inside .xlsx files, shifted and merged spreadsheet cells, mixed date formats, and in a few PDFs, two overlapping text layers on the same page.

This post is about what that process actually looked like — the specific bugs, why they got past the usual tooling, and the parts that are still unresolved on purpose rather than quietly smoothed over.

Where it landed

• 35 projects, 4,343 active transactions (is_current = true) — append-only history underneath, so a correction never overwrites or deletes a record, it supersedes it.
• Field accuracy: 96.8%, up from 84.8% before a round of fixes — measured by cropping a row image straight from the source document, reading it blind, and only then checking it against the database, on a fixed random sample of 50 records (seed 42, measured 2026-08-24).
• Extraction completeness: 79.17%, measured by comparing extracted row counts against the row-numbering ("STT") the source documents print themselves — a direct comparison, not an estimate. 58 file/sheet segments are still below 95% completeness by that same check.
• 1,053 of 4,343 records aren't linked to a specific project (project_slug is null) — kept in the dataset and counted openly, not dropped from the aggregates.
• 15 records are missing a buyer nationality value outright, and a further small, fully enumerated set (see below) has nationality values that are known-corrupted rather than simply missing.

Classes of problems that actually came up

Corrupted .xlsx archives. A .xlsx file is a ZIP container. Some of the source files had one damaged internal entry out of six, and standard libraries (openpyxl/pandas) refused to open the whole file over that single bad entry, even though the other five sheets were physically intact. The fix was to read the ZIP entries directly and salvage what was readable, logging honestly — including which sheet — what wasn't recoverable.

Shifted table structure. Some publications shift the row-number column over by one or two columns (an extra "Tòa"/building-block column gets inserted), or put the project address in a merged header cell instead of repeating it per row. A naive, global fix for this once dropped extracted records from 1,452 to 234 — it only became safe once it was rewritten as an opt-in path that triggers strictly on a zero-result case on the normal parse, with a full regression run against a reference file set after every subsequent change.

Mixed Vietnamese/English nationality labels across files from different sources — some files are developer CRM exports already in English ("South Korea", "China", "Taiwan"), others are in Vietnamese. Solved with an explicit mapping table, not a heuristic.

Substring collisions in nationality matching. Short dictionary keys matched inside ordinary Vietnamese words — "hàn" (South Korea) inside "thành" (city), "áo" (Austria) inside "báo cáo" (report), "trung" (China, without "quốc") inside "Trung tâm" (shopping center). The same class of bug showed up three separate times, in three different files, before it was fixed properly: a closed dictionary with word-boundary checks, plus a dedicated trap fixture that must match exactly zero times on any future change.

Two overlapping text layers in some PDFs. Six nationality values in the database look like scrambled noise — for example "S Ii Nfguanpogr)E" and "Hvàinn Qauốc" (from the same source file), or "H À N Q U Ố C" from another. That's consistent with two text runs overlaid on the same PDF cell, not random corruption — one row had at least three overlapping lines of text inside a single cell boundary. An "every other character" decode hypothesis was tested against all 6 and does not hold as a single safe rule — most of them don't recover to an unambiguous word that way. Nothing was auto-corrected: the rule applied here is that one unclear case is enough to leave the whole class alone rather than guess, so all 6 stay recorded as "unrecognized" nationality, carved out of the strict nationality-dictionary check by their exact (source file, value) pair rather than fixed.

One identifier doing two jobs. A content hash was used both to identify a record and to detect whether it had changed — so correcting a typo in a date or contract number could leave the old, wrong version of the record live instead of replacing it. This surfaced three times at increasing scale before being fixed architecturally, with a separate stable identity key (project + unit code + contract number) that doesn't depend on the content hash at all.

A column shift that leaked PII. One source file had address_raw populated with the buyer's full name instead of a street address, for 99 rows — a structural defect specific to that file's column layout relative to the rest of the corpus. Found and tracked across three consecutive audit sessions, closed by masking the affected field only: 2 of the 99 resolved as a side effect of a routine data upload, the remaining 97 (all from one source file) had that one field replaced with a redaction marker via a targeted, per-row fix that touched no other field on those records. The database currently has zero rows flagged by the same PII check.

Bugs that standard tooling didn't catch on the first pass

Three specific cases where linting and normal code review missed a real bug, and specifically why.

1. A naive-datetime call hidden behind __import__. ruff's naive-datetime rule matches a static import (from datetime import datetime or import datetime) — it looks at the AST node for a call made through a statically known module name. One file wrote a timestamp as __import__("datetime").datetime.utcnow().isoformat() instead — a working but unusual way to avoid an import line at the top of the file. To the linter, that's a method call on the result of an arbitrary expression, not a call through a known name — the rule structurally cannot catch it, not because of an oversight in the codebase's config. Three other identical naive-datetime calls elsewhere in the same codebase were caught and fixed by ruff, precisely because they used an ordinary import. This one was found by manual line-by-line review of an adjacent file in the same session, and fixed separately.

2. A main() that silently swallowed upload errors. An ETL script collected write errors into a stats dictionary and printed them to the screen for a human to read, but the function that built that dictionary never returned it to its caller, and main() never checked an exit code. A failed database write (a network error, an RPC timeout) still produced exit 0 — visible to a human watching the terminal, invisible to a cron job or CI pipeline that only checks the return code. The linter's blind-except rule did catch the overly broad except Exception around the write call itself — but that rule is about what exception type gets caught, not about what happens to the result further down the control flow. The missing exit-code check lived in a separate function, outside that rule's scope. Found by manually reading through every instance of that lint category in the codebase, not by a tool flagging this specific one.

3. PII through a column shift, not an explicit error. Same incident as above, worth stating again as a third example of the same pattern: no static analyzer can catch "this particular column, in this particular file, means something different than it does in the other 82" — that was only found by measuring the live data against a PII pattern check, not at code-review time.

None of this is "the linter is bad." All three sit outside what static analysis can reach by construction — indirection around a known call, a broken data path between two functions, and the semantics of one specific file. Which is exactly why there's a second layer of checks sitting above the linter: a database-level invariant script and full manual review of an entire lint-rule category at once, not a sample of it.

The threshold for deleting dead code

By early September, the pipeline had accumulated nine files or groups of files with no live execution path. Deciding what to delete immediately versus only document was formalized as a checkable threshold, not a judgment call made by eye:

Safe to delete immediately only where zero importers anywhere (an import from another already-dead file still counts as an importer — not just "outside the dead set") and the file isn't named anywhere as a planned dependency in the project's own docs.

Two files met that bar cleanly and were deleted — neither was imported anywhere, including by other dead files. The other seven weren't, for a reason worth spelling out: a set of spider modules had genuinely never run in production (no scheduled job exists for them), but a scheduler file — itself also never run — imports them by string path from a dictionary. That scheduler file has no execution path either, but it exists and does perform the import. The transitive chain from there down to two parser modules doesn't satisfy "zero importers anywhere" at any single node — every node has at least one existing, if equally unexecuted, importer. Deleting one file out of the middle of a chain like that without a decision about the whole chain would leave the rest of the chain pointing at files that no longer exist. Separately, two more files that would otherwise have passed the "zero importers, not planned" test are directly required to exist by a legacy invariant script that runs before every commit — a dependency that doesn't show up in an import graph at all, only in the fact that a required check runs the script and checks its exit code.

What's still unresolved — not cleaned up for this post

• 89 groups of records share an identity key (218 live records) with no automatic way to pick which one is authoritative — mostly legitimate overlapping coverage of the same transaction across two different published lists, some likely weak contract-number extraction. None of it is auto-resolved; that's left as a human decision on purpose.
• One specific contract-number bug: for one group of files, the extractor grabs a reference letter's number instead of the actual purchase contract number. Found, not fixed, documented.
• 1,053 of 4,343 records aren't linked to a project — mostly traced to files whose address format doesn't match the fixed set of named residential projects the system knows about, or is too fragmentary to match by pattern. The exact current breakdown hasn't been re-measured at today's data volume.
• 58 file/sheet segments are still below 95% completeness by the row-number check, for a mix of already-identified reasons (an unrecognized text layer, table continuations across pages that don't get stitched together).
• 6 nationality values are only partially recovered from the overlapping-text-layer defect above, left as unrecognized rather than guessed.

Where this leaves things

Completeness and accuracy are different measurements, published separately, both as a number with a measurement date — neither one stands in for the other. Known losses and unresolved classes of problem are recorded as findings, not smoothed out of the aggregates for a cleaner presentation. The live numbers behind this post are browsable on the insights dashboard, and the full methodology — including exactly how each of the numbers above is computed — is at /methodology.