We recently published a data study on the French business registry — 14.4 million active establishments, sliced by workforce, sector and geography. The numbers themselves aren’t the interesting part for this post. What is: the rules we forced ourselves to follow so that anyone re-running the same queries on the same open dataset would get the exact same numbers, and so every headline percentage could be traced back to a total someone else can check.
This is a walkthrough of that process, using INSEE’s SIRENE registry (France’s official open-data business registry) as the example dataset, because it’s public, it’s messy in the ways real government data tends to be, and the mistakes we ran into are the boring, common kind — not exotic edge cases.
Reconcile before you round
The registry snapshot we worked from (dated July 1, 2026) contains 14,387,175 active, publishable establishments and 26,756,173 legal units in total, of which 14,470,762 are active. Those three numbers are the anchor for everything else: before computing a single percentage, we re-derived each of them independently from the raw stock and diffed against the reference totals published alongside the dataset. Zero-diff or the query is wrong — not the data.
That discipline caught a real bug early on: our first pass at “establishments with no declared workforce” summed the wrong INSEE status codes and landed a few points off. The correct answer splits into two codes — NN (workforce not reported, 12,486,437 establishments) and 00 (zero salaried staff, 71,325) — for a combined 12,557,762, or 87.28% of the active stock. Small distinction, but it changes the number, and it only surfaces if you reconcile against a known total instead of trusting the first query that runs without an error.
Document exclusions, don’t quietly drop them
Any geographic breakdown of this dataset means deriving a département (the French equivalent of a county) from a postal code — and postal codes in a 14-million-row open dataset are not clean. Corsica is aggregated under a synthetic “20” code, overseas départships need 3-character handling, and there’s a long tail of malformed or foreign entries.
Rather than silently filtering rows that didn’t fit and reporting a tidy total, we counted and published every excluded bucket: 157,187 establishments with no postal code at all, 27,525 under code “99” (foreign addresses), 5,107 under the “98x” range (Monaco, French Polynesia), 1,290 in Saint-Pierre-et-Miquelon, and 4,574 residual malformed codes (stray formats like partial UK postcodes that clearly don’t belong in a French address field). None of this moves the order of magnitude of the geographic ranking — Paris still dominates with 1,159,693 establishments — but it’s the difference between a defensible percentage and one that quietly excludes 200k rows without saying so.
Treat a surprising ranking as a prompt to investigate, not a headline
Rank establishments by sector and the top result is “real estate activities” — 3,326,963 establishments, 23.1% of the entire stock. That’s not a booming property market; it’s an artifact. Sociétés civiles immobilières (SCIs), a common French legal wrapper used purely to hold or transfer property, register as establishments like any operating business, despite rarely being a sales or outreach target for anyone. A naive “top sectors” chart would have reported this uncritically. Flagging it meant going back to first principles for every ranking in the study: is this number describing economic activity, or a legal-structure artifact that happens to be countable?
Sanity-check date boundaries
The “new registrations in 2026” count needed a lower bound (start of year) and, less obviously, an upper one. Filtering the raw creation-date field turned up 102 records dated in 2027 or later — clearly bad input, not a preview of the future. They were excluded after inspecting the bound, and the exclusion was logged rather than silently absorbed into the total.
What this adds up to
None of these four steps are novel techniques. They’re closer to a checklist: reconcile every aggregate against an independent total before publishing it, count and disclose what you exclude instead of hiding it in a WHERE clause, question rankings that look too clean, and check your date boundaries before you trust a “recent activity” number. The interesting part isn’t any single check — it’s that skipping any one of them produces a plausible-looking, wrong number, and open government datasets this large will always have enough edge cases to hide one.
We do this kind of data engineering as part of building OutSend, a lead-generation platform — the reconciliation habits above came directly from having to trust the same pipelines in production, not just for a one-off report.
답글 남기기