Technical appendix

Engineering behind rodado

The public-facing site is a sociological reading of Brazilian public data. This page is the engineering appendix for readers interested in how the platform behind it is built — ingestion, ontology, semantic layer, MCP query interface, and a direct comparison to how the same architecture maps onto Palantir Foundry.

Engineering competencies demonstrated

Each capability below is backed by a concrete, deployed piece of this project — not a claim, a running system.

Delivered case studies

Five shipped applications over Brazilian public data — each with a stakeholder problem, a data model, an application built, and a measurable outcome.

User workflows

Three analyst scenarios showing the full data → insight → decision arc.

Compliance — contracts signed with sanctioned suppliers

A compliance team checks which federal contracts were signed with companies while they were 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

Decision: check each sanction's scope (abrangencia_sancao) — a suspension may bind only the sanctioning agency — then route the remaining cases to procurement governance.

Policy — infrastructure gap prioritization

A state health secretariat identifies municipalities with critically low SUS hospital bed coverage to prioritize federal budget allocation.

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

Decision: ranked shortlist delivered to the budget committee; the top 10 flagged for emergency transfer.

Investigation — campaign suppliers that later won federal contracts

An investigative journalist checks which 2022 campaign suppliers went on to win federal contracts. The CPF in the company-partner register is 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

Decision: the minimum-payment filter matters — without it, national telecom and utility suppliers paid a few reais by hundreds of campaigns dominate the ranking. The shortlist goes to the editorial team with source data for verification.

Domain ontology

Brazilian public data modeled as typed entities with explicit join keys.

Architecture

Everything is local: partitioned Parquet on one machine, queried on demand by DuckDB over SSH. No live web service, no cloud object storage.

Table selection

With 1,403 tables, the hard step is picking the handful worth reading before any SQL is written — the step that keeps a large schema from producing hallucinated joins. The local harness puts the whole dataset catalog in the prompt (~2k tokens, cached after the first question) and lands on the right dataset 88% of the time. An embedding index over ~8 synthetic questions per table scored ~53% on the same measure and was removed.

Palantir Foundry mapping

Not a Foundry deployment. rodado is an open-source system built around the same idea: datasets alone are not enough. You need a typed semantic layer that says what the data means, and tools that act on that meaning instead of on table names. The table translates each layer for readers who know Foundry.

Solved by hand here, by construction in Foundry

What a Foundry deployment would add

Porting sketch

1. Sync the Parquet tables as datasets through Data Connection.
2. Write transforms that apply each bridge normalization and emit clean keys. Turn each gotcha into a Data Expectation.
3. Ontology: object types for the hub concepts (Municipality, Company, Person [marked], Establishment, PublicContract, HealthEvent), link types from the bridges, and an interface for anything located in a municipality.
4. Turn the named metrics into functions, each tested against its recorded measurement. Move the harness cases into AIP Evals.
5. Build a Workshop app for the supplier integrity check, with an action type that routes a flagged company to review.

Data quality & governance

Stack

An earlier version served live queries from db.xn--2dk.xyz: a BigQuery → GCS → Hetzner Object Storage pipeline with httpfs reads, a Caddy + HMAC auth layer, a browser SQL shell and a Rust NL→SQL TUI. All of it has been retired in favor of the local setup described above.

Full engineering reference (environment, MCP setup, regeneration order): see docs/tecnico/TECHNICAL.md in the repository.