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

LayerWhat runs there
StorageLocal disk on a home server: ~/rodado/<dataset>/<table>/*.parquet. Add-only backup to a second disk every 6 hours.
Public copyThe same files synced to object storage (Hetzner, Helsinki) after each update. Every file is fully read and validated before it is copied.
Public accessdb.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 accessmcp/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 layerYAML files versioned in the repository (next section), read by the MCP tools at query time.
SiteStatic 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.

PieceSizeWhat it does
Join concepts68resolve_join(a, b) returns a ready ON clause, including key normalization such as unpadded CNPJs.
False friends21Same column name, different meaning: a column named just valor appears in 153 tables.
Coded differently13Concepts such as sex and race whose codes change by dataset and year.
Named metrics16Per-capita values, rates and ratios with verified units. get_metric() comes before any hand-written formula.
Hierarchies3Territorial, CNAE and CID-10 rollups.
Gotchas290 datasetsPer-dataset warnings returned by describe_table: snapshot-only tables, empty years, masked identifiers.
Ontology87 classes · 137 relationsThe domain model across 13 domains: municipality, company, person, contract, health event and so on.
Theme overviews47Short summaries per theme, used as context by agents.

Join keys

KeyTablesObject
ano658Year (partition)
sigla_uf510State
id_municipio401Municipality
mes173Month (partition)
cnpj / cnpj_basico59Company
id_setor_censitario39Census tract
id_municipio_tse29Municipality (electoral code)
cpf26Person
cbo_20028Occupation
cnae_2_subclasse7Economic 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

PartitionsLarge tables need ano, mes or sigla_uf filters.
IdentifiersCPF 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.
FreshnessVaries 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 shrinksPublishers 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 numberState 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.