We run Crime Brasil, a site that turns official Brazilian public-safety data into maps and rankings for every neighbourhood and municipality in the country. Under the hood that is about 1 billion rows: police reports from state security departments, health-ministry mortality records, court and prison data, traffic and more.
Most of the hard problems are not about scale. They are about data that looks right and isn't. Here are two rules we now apply before any number leaves the database, plus what they say about São Paulo in 2026.
Rule 1: the last month in the file is always incomplete
State police data arrives in batches. A report filed today may describe a crime from last week, so the newest month keeps growing for weeks after it "ends".
Here is the São Paulo police-report feed by day, at the end of our current extract:
| Day | Reports |
|---|---|
| 2026-08-26 | 2,459 |
| 2026-08-27 | 2,413 |
| 2026-08-28 | 2,275 |
| 2026-08-29 | 2,061 |
| 2026-08-30 | 1,665 |
| 2026-08-31 | 994 |
A normal day in São Paulo is 2,400 to 2,900 reports. If you compare "August 2026" with "August 2025" you get a drop in crime that is really a drop in paperwork.
The check is one query. Count by day over the last two weeks and look for the tail:
SELECT data_fato::date AS day, count(*)
FROM crimes
WHERE state = 'SP'
AND data_fato::date >= (SELECT max(data_fato::date) - 14 FROM crimes WHERE state = 'SP')
GROUP BY 1
ORDER BY 1;
If the last days have half the volume of the days before, close your window one month earlier. We do this for every state, every time.
Rule 2: keep huge microdata out of your main tables
Some official datasets are far bigger than what a website needs. Brazil's hospital-admission records alone are over 120 million rows. Loading that into normal Postgres tables would eat disk and make backups crawl, for data the site touches a few times a day.
So every dataset gets one of two homes:
- Site-facing data lives in regular Postgres tables, partitioned by state, indexed for the queries the pages actually run.
- Big research microdata stays as Parquet files on disk, queried from inside Postgres with pg_duckdb. Same SQL, same connection, no copy.
The rule we enforce: never bulk-load a Parquet dataset into a heap table. When we need a slice for the site, we aggregate it first and store only the result.
What São Paulo looks like in 2026
With both rules applied (January to July only, because August is still filling in), here is São Paulo state compared with the same months of 2025. These are counts of police reports from the SSP-SP open-data portal:
| Crime | Jan–Jul 2025 | Jan–Jul 2026 | Change |
|---|---|---|---|
| Robbery (excluding vehicles, cargo and banks) | 96,706 | 80,180 | −17.1% |
| Intentional homicide | 1,415 | 1,278 | −9.7% |
| Robbery followed by death (latrocínio) | 84 | 47 | −44.0% |
The robbery decline is not a one-month blip: the monthly count went from about 15,000 in mid-2024 to about 10,500 in July 2026, falling a little almost every month.
You can explore the same data by city and neighbourhood on the São Paulo state page, or compare places in the rankings.
Takeaways
- Before you compute a change, prove the newest month is complete.
- Decide where each dataset lives before you load it, not after the disk fills up.
- Show your window. "January to July" is less exciting than "2026", and much harder to get wrong.
If you work with Brazilian open data, I'd like to hear which datasets gave you the most trouble.
Top comments (2)
The incomplete-last-month check is a good habit for any late-arriving data. A way to automate it is to store the max reporting date per source and compare each day's count to a trailing median (say 14 days), then only publish windows where every day is above something like 80% of that median. For the pg_duckdb split, I'd be curious whether you hit planner surprises when joining Parquet-backed results with partitioned heap tables, and if you materialise the small side first..
Rule 1 is the one that bites hardest. The day-by-day tail is a good check. One thing I added on top: compare the last day's count to the same weekday's median over the previous 8 weeks, and alert when it's under about 70%. It catches a late feed without you having to eyeball the table, and it keeps working when a state's normal volume drifts. Do the other states lag by about the same number of weeks as SP, or does each one need its own cutoff?