presupuesto-nlq-mx
Ask Mexico’s federal budget a question in Spanish, and see the SQL that answered it.
01Problem
Mexico publishes its federal budget as open data, but reading it takes both data skills and government-accounting knowledge. The figures live in tables keyed by alphanumeric codes that only make sense against external catalogs; the rules that explain them live in regulatory PDFs. The data is open, but most people can’t use it.
02Approach
A system that answers Spanish questions by one of two routes. The data route translates the question into SQL over a documented semantic layer, validates it, runs it under a read-only role and returns the table with the exact query. The document route retrieves passages from official regulations and answers with citations. A router picks the route, and the system abstains when the evidence can’t support an answer.
- fact rows loaded and reconciled
- 1,285,233
- fiscal years normalized, 2020–2025
- 6
- conformed dimensions
- 12
- security checks passing
- 11/11
03Architecture
- Built
- In progress
- Planned
Spanish, natural language
LLM vs trained classifier vs both routes
Data route · text-to-SQL
Descriptive views + classification docs v1
One SELECT only; reject SET and set_config()
consulta_nlq role, 15 s limit
The query is always shown
Document route · RAG
Articles and clauses with provenance
BM25 vs dense vs hybrid, ± reranking
Faithfulness measured separately
Every claim points to a source
Foundations
Star schema · 1,285,233 rows · validated load
61 questions drafted · human verification gate
Either route can end in an abstention. When the evidence can’t support an answer, saying so is the correct output.
04Engineering notes
The CSV and the XLSX disagree.
The Ministry of Finance publishes every fiscal year as both CSV and XLSX. A row-level cross-check showed they aren’t equivalent, so the source format is chosen per year. Every correction is declared in a normalization log, because a silent fix can’t be told apart from altering the data.
Loads run in a single transaction and end by reconciling row counts and per-stage totals (approved, accrued, paid) against the source, to the peso. Any mismatch rolls back the whole load. If the load log exists, validation passed.
| Year | Source | Why |
|---|---|---|
| 2020 | .xlsx | CSV drops 10 rows |
| 2021 | .xlsx | CSV drops 2 rows and blanks amounts |
| 2022 | .xlsx | CSV drops 3 rows, adds 828,067 filler rows |
| 2023 | .xlsx | CSV drops 1 row and corrupts a key |
| 2024 | .xlsx | CSV overstates branch 51 by MXN 9,699 M |
| 2025 | .csv | Formats identical; CSV parses faster |