I turned Brazil's 73M-establishment company registry into second-fast lookups, with no server and no database

Jefferson Valandro — Sat, 03 Oct 2026 00:36:17 +0000

Brazil's tax authority (Receita Federal) publishes its entire company registry (CNPJ) as open data every month: every company, its address, phone, e-mail, industry code (CNAE), size, tax regime and partners. It's a goldmine for B2B sales, and it's painful to use: 37 zip files, ~8 GB compressed, ~27 GB of semicolon-separated text, and several traps along the way.

I wanted answers like "restaurants opened in the last 90 days in Campinas, with a phone number" or "which company owns acme.com.br?" in seconds, without paying for a server or a database. Here's the setup and, more usefully, everything that went wrong.

The architecture

Receita zips ─► DuckDB (on disk) ─► Parquet ─► private Hugging Face dataset
                                                        │
                 Apify Actors (DuckDB / pyarrow) ◄──────┘

DuckDB joins establishments, companies, the Simples/MEI table and lookup tables.
The result becomes Parquet laid out four ways, one per query shape: per state sorted by industry code (lead search), per CNPJ prefix (bulk lookup), per city (matching Google Maps places) and per e-mail domain.
The files live in a private Hugging Face dataset: free, no credit card, and it serves HTTP range requests.
Each search runs as an Apify Actor that reads only the row groups it needs. Because files are sorted, per-row-group min/max statistics tell you exactly where to look.

The traps

1. The files say ISO-8859-1. They aren't. Some bytes only make sense in Windows-1252 and DuckDB's latin-1 reader rejects the file. I transcode during extraction: chunk.decode("cp1252", errors="replace").

2. ignore_errors=true silently dropped 74% of the companies. Names like BAR ""DO ZE"" LTDA use doubled quotes as escapes. With default settings DuckDB treated those lines as malformed and ignore_errors skipped them without a word: my first build had 8M active establishments instead of 28M. The fix is escape='"'; the real lesson is always compare parsed rows with the file's line count. The converter now refuses to publish when they differ.

3. 32 GB of RAM wasn't enough. Joining 28M establishments with 70M companies and sorting got the process killed. A disk-backed DuckDB database, memory_limit='6GB', a temp directory and one state at a time fixed it. For the 73M-row lookup table, a single global sort hadn't finished after 20 minutes; partitioning by CNPJ prefix in one pass and sorting each ~700k-row piece took 15 minutes total.

4. DuckDB over HTTP was slow for point lookups. Every query re-validated the file and followed Hugging Face's redirect: ~2 s per CNPJ. Switching to pyarrow + HfFileSystem changed that: read each file's footer once, group the requested keys by the row group they fall in (min/max stats), fetch each row group once, 64 in parallel. Shrinking row groups from 20k to 2k rows cut the bytes downloaded for 2,000 scattered keys from ~3.2 GB to ~0.5 GB. Net result: 209 s → 53 s for 2,000 lookups, at 1/6 of the cost.

5. The registered e-mail is often the accountant's. One accounting firm appears as the contact e-mail of 438 companies. To find a company by domain, the chosen name has to resemble the domain (corsi.com.br → CORSI CONTABILIDADE); names that only share the city ("... CAMPINAS") don't count.

6. Matching Google Maps places to the registry. Phone and ZIP + street number produce candidates; name similarity and industry compatibility decide (a dentist must not match the real-estate agency in the same building). On 180 real places in Campinas, 60% matched, almost all with high confidence.

Privacy (LGPD)

It's public data, but: individual micro-entrepreneurs (MEI) are excluded from lead searches by default, partners come without their personal tax ID, and the personal ID that the registry appends to sole-proprietor names is stripped.

Keeping it fresh

A Windows scheduled task checks for a new month every night. When one appears it downloads, converts, compares row counts with the previous month, uploads to Hugging Face, verifies every file actually arrived (I once had an upload "succeed" without a whole folder), and runs a test against each Actor. Any failure becomes a desktop notification instead of a silently broken product.

Try it

The four searches are public on Apify, pay per result:

Brazilian company leads by industry and city, including companies opened in the last N days
Bulk CNPJ lookup with registration status, alphanumeric CNPJ supported
Find the CNPJ of Google Maps places
CNPJ by website domain or e-mail