Technical reference · updated November 2026
How rodado is built
The analyses on this site all come from one place: a local mirror of Brazilian public data, plus a semantic layer that says how the tables join and where they mislead. This page describes both, and the read-only ways in.
The mirror
1,759 tables in 295 datasets from 140 sources, 521 GB and 33.7 billion rows as of 11 November 2026. 1,216 tables mirror Base dos Dados; the other 543 are scraped from the original publishers to fill its gaps: sanctions lists, SICAF, SINAN microdata, consumer complaints, CGU payments and more. Each table is a directory of Parquet files compressed with zstd, and DuckDB reads the files directly, with nothing imported into a database.
A metadata catalog records rows, size, source URL, collection date, status and provenance for every table. It is rebuilt after each update, and it is what the SQL terminal shows when a table is opened.
Architecture
| Layer | What runs there |
|---|---|
| Storage | Local disk on a home server: ~/rodado/<dataset>/<table>/*.parquet. Add-only backup to a second disk every 6 hours. |
| Public copy | The same files synced to object storage (Hetzner, Helsinki) after each update. Every file is fully read and validated before it is copied. |
| Public access | db.rodado.xyz: a container that reads the public copy. It offers a browser SQL terminal, POST /query for curl and the MCP server over HTTP. Open and read-only, with per-IP rate limits and a daily row quota. |
| Agent access | mcp/mcp_server.py: 17 read-only MCP tools, served locally over stdio (queries go to the home server over SSH) or remotely at https://db.rodado.xyz/mcp. Setup: MCP. |
| Semantic layer | YAML files versioned in the repository (next section), read by the MCP tools at query time. |
| Site | Static pages on GitHub Pages. The home page, the platforms index and the Atlas of tables and join keys are generated from the catalog and the semantic layer. |
Every session is opened read-only, with the readable directories allow-listed and the configuration locked. The SQL comes from a model or from a stranger, and a query that only reads can still read the wrong file.
Semantic layer
Table and column names are not enough to query 1,759 tables correctly. The same code means different things in different years, and two columns with the same name can hold different quantities. The semantic layer records this once, measured on the real data and dated, so a query does not have to rediscover it.
| Piece | Size | What it does |
|---|---|---|
| Join concepts | 68 | resolve_join(a, b) returns a ready ON clause, including key normalization such as unpadded CNPJs. |
| False friends | 21 | Same column name, different meaning: a column named just valor appears in 153 tables. |
| Coded differently | 13 | Concepts such as sex and race whose codes change by dataset and year. |
| Named metrics | 16 | Per-capita values, rates and ratios with verified units. get_metric() comes before any hand-written formula. |
| Hierarchies | 3 | Territorial, CNAE and CID-10 rollups. |
| Gotchas | 290 datasets | Per-dataset warnings returned by describe_table: snapshot-only tables, empty years, masked identifiers. |
| Ontology | 87 classes · 137 relations | The domain model across 13 domains: municipality, company, person, contract, health event and so on. |
| Theme overviews | 47 | Short summaries per theme, used as context by agents. |
Join keys
| Key | Tables | Object |
|---|---|---|
ano | 658 | Year (partition) |
sigla_uf | 510 | State |
id_municipio | 401 | Municipality |
mes | 173 | Month (partition) |
cnpj / cnpj_basico | 59 | Company |
id_setor_censitario | 39 | Census tract |
id_municipio_tse | 29 | Municipality (electoral code) |
cpf | 26 | Person |
cbo_2002 | 8 | Occupation |
cnae_2_subclasse | 7 | Economic activity |
Table selection
With 1,759 tables, the hard step is choosing the few worth reading before any SQL is written. A wrong choice there produces joins that look valid and are not. The local harness puts the whole dataset catalog in the prompt (about 2,000 tokens, cached after the first question) and lands on the right dataset 88% of the time. An embedding index over about eight synthetic questions per table scored around 53% on the same test and was removed. Agents then narrow down with list_datasets → list_tables → describe_table.
Example queries
Three questions answered with the mirror. All three run as written against the current schema (checked on 13 November 2026).
Federal contracts signed with sanctioned suppliers
Contracts signed while the supplier was listed in CGU's sanctions register (CEIS).
SELECT c.cpf_cnpj_contratado,
any_value(c.nome_contratado) AS contractor,
COUNT(DISTINCT c.id_contrato) AS contracts,
SUM(c.valor_final_compra) AS total_brl,
any_value(s.categoria_sancao) AS sanction
FROM br_cgu_licitacao_contrato.contrato_compra c
JOIN br_cgu_sancoes.ceis s
ON s.cpf_cnpj_sancionado = c.cpf_cnpj_contratado
AND c.data_assinatura_contrato >= s.data_inicio_sancao
AND (s.data_final_sancao IS NULL OR c.data_assinatura_contrato <= s.data_final_sancao)
WHERE c.ano = 2024
GROUP BY 1
ORDER BY total_brl DESC
LIMIT 20
A sanction's scope (abrangencia_sancao) matters: a suspension may bind only the agency that imposed it.
Municipalities with the fewest SUS hospital beds
SUS beds per thousand residents in municipalities above 50,000 inhabitants, next to the early-years IDEB score.
SELECT m.nome, m.sigla_uf, pop.populacao,
ROUND(cnes.leitos_sus * 1000.0 / NULLIF(pop.populacao, 0), 2) AS leitos_sus_por_mil,
ideb.ideb AS ideb_anos_iniciais
FROM br_bd_diretorios_brasil.municipio m
JOIN br_ibge_populacao.municipio pop
ON m.id_municipio = pop.id_municipio AND pop.ano = 2024
JOIN ( SELECT id_municipio,
SUM(quantidade_leito_cirurgico + quantidade_leito_clinico
+ quantidade_leito_complementar) AS leitos_sus
FROM br_ms_cnes.estabelecimento
WHERE ano = 2024 AND mes = 12 AND indicador_vinculo_sus = 1
GROUP BY id_municipio ) cnes ON m.id_municipio = cnes.id_municipio
JOIN br_inep_ideb.municipio ideb
ON m.id_municipio = ideb.id_municipio AND ideb.ano = 2023
AND ideb.rede = 'publica' AND ideb.ensino = 'fundamental'
AND ideb.anos_escolares = 'iniciais (1-5)'
WHERE pop.populacao > 50000
ORDER BY leitos_sus_por_mil ASC, ideb_anos_iniciais ASC
LIMIT 50
Campaign suppliers that later won federal contracts
CPFs in the company-partner register are masked, so the link goes through the supplier's CNPJ rather than the candidate's CPF.
WITH fornecedores AS (
SELECT d.cpf_cnpj_fornecedor, c.nome, c.sigla_partido, c.cargo,
SUM(d.valor_despesa) AS pago_na_campanha
FROM br_tse_eleicoes.despesas_candidato d
JOIN br_tse_eleicoes.candidatos c
ON c.ano = d.ano AND c.sigla_uf = d.sigla_uf
AND c.sequencial = d.sequencial_candidato
WHERE d.ano = 2022 AND d.sigla_uf = 'SP'
AND length(d.cpf_cnpj_fornecedor) = 14
GROUP BY 1, 2, 3, 4
HAVING SUM(d.valor_despesa) >= 50000
)
SELECT f.nome, f.sigla_partido, f.cargo,
COUNT(DISTINCT f.cpf_cnpj_fornecedor) AS fornecedores_contratados,
SUM(f.pago_na_campanha) AS pago_na_campanha,
SUM(k.valor_federal) AS contratos_federais_2023_2025
FROM fornecedores f
JOIN ( SELECT cpf_cnpj_contratado, SUM(valor_final_compra) AS valor_federal
FROM br_cgu_licitacao_contrato.contrato_compra
WHERE ano BETWEEN 2023 AND 2025
GROUP BY 1 ) k ON k.cpf_cnpj_contratado = f.cpf_cnpj_fornecedor
GROUP BY 1, 2, 3
HAVING contratos_federais_2023_2025 > 1000000
ORDER BY contratos_federais_2023_2025 DESC
The minimum-payment filter matters: without it, national telecom and utility suppliers paid a few reais by hundreds of campaigns dominate the ranking.
Data quality
| Partitions | Large tables need ano, mes or sigla_uf filters. |
| Identifiers | CPF and CNPJ are read-only and never exported in bulk. Masking varies by source, so masked CPFs from different sources cannot be joined to each other. |
| Freshness | Varies by source: CNPJ monthly, health data 12–18 months behind, the census in 2010 and 2022. The CNPJ tables hold only the latest snapshot; history is in the *_historico tables. |
| When a source shrinks | Publishers sometimes drop columns or years in a new release. The mirror merges instead of replacing, so what only the mirror still has is kept. |
| Before reporting a number | State the expected order of magnitude, flag rows above it and check the count two independent ways. |
Stack
DuckDB · Parquet + zstd · Python and Bun/TypeScript · MCP · YAML for the semantic layer · Caddy and a container on Hetzner for db.rodado.xyz · GitHub Pages for the site.
Full engineering reference (environment, MCP setup, regeneration order): docs/tecnico/TECHNICAL.md in the repository.