← all work · data engineering · analytics engine · local LLM

EduData

Ask questions in Portuguese about eight editions of Brazil's higher-education statistics. Numbers come from the database — never from a model.

PythonDuckDBSQLParquetOllamapytest

Try the live demo ↗ free tier: the first visit may take ~1 min to wake the server

662

spreadsheet tabs diagnosed

3.8 GB

of raw XLSX ingested

458

automated tests

architecture decisions logged

source: INEP/MEC — Sinopses Estatísticas da Educação Superior
period: 2017–2024 (8 annual editions)
storage: DuckDB (~4.5 GB) · Parquet staging
runtime: local (Python 3.12 · Windows) or online (Render · rules only · 105 MB slim database)
llm: Ollama + Qwen2.5 7B (optional)
status: v1 delivered · independent project
live demo: consulta-sinopses.onrender.com ↗
repository: github.com/Igor-Millis/consulta-sinopses-educacao-superior ↗

01 · problem

Public data, unfriendly format

The INEP Synopses are the official yearly statistics of Brazilian higher education — but they ship as hundreds of spreadsheet tabs per year, with layouts that change between editions. Answering “how did enrollment evolve in Espírito Santo?” means opening workbooks, finding the right tab and hoping the columns mean the same thing as last year.

Goal: a tool where anyone can ask in plain Portuguese and get a number that is computed, traceable and honest about what the data does not contain.

02 · pipeline

Seven layers from ZIP to answer

Raw files are never touched. Every value in the core keeps the tab and cell of the original INEP spreadsheet it came from. Select a stage:

Faithful copy. XML of each sheet is streamed with lxml (no Excel), one Parquet table per tab and year, every value kept as raw text with its source row.

Curated header mapping turns spreadsheet columns into explicit dimensions for the course-level annexes, validated against the original files.

Long-format fact tables in DuckDB with dimensions, a quality status and cell-level origin for every value.

Synopsis tables (sex, race, age, institution type…) mapped with a vocabulary and validated — including totals checked against the source XML.

Year-to-year correspondences (e.g. course codes revised in 2024) so comparisons only happen when compatibility is verified.

Analytical views on top of the core, including the national annex reconstructed as a view after proving Annex I = II + III cell by cell.

Three compact query marts with explicit aggregation rules (“sum only leaves”), validated against the facts. The engine reads only these.

03 · engine

Question → query → SQL

A rule-based interpreter with a semantic catalog turns the question into a structured Consulta. That JSON query is validated against the catalog and the database, then compiled into parameterized SQL executed on a read-only connection. Rankings, growth and shares are computed in Python afterwards.

Replay real questions from the project's gold set:

edudata › query replaymotor/consultas.py · motor/seguranca.py

1 · consulta.json

2 · sql (read_only)

3 · outcome

Queries are taken from tests/gold_interpretacoes.jsonl. SQL reproduced from the query planner in motor/consultas.py — a replay, not a live database.

04 · llm

Where a local LLM helps — and where it doesn't

A local model (Ollama, on CPU) can help interpret questions the rules don't recognize. It is held to the same JSON query structure: it returns names, never SQL or numbers. The decision to keep it optional came from measuring it on 40 paraphrased and unseen questions:

interpreter	paraphrases /24	unseen control /16	total /40	mean time
Rules	24*	14	38*	0.2 s
LLM 3B alone	13	8	21	11 s
LLM 7B alone	14	13	27	21–23 s
Hybrid (rules + 7B)	24*	14	38*	0.5–1 s

* Rules were adjusted after seeing paraphrase failures, so 24/24 does not measure generalization; the unseen control set does. Source: _arquitetura/02_decisoes.md (D17).

Decision: rules by default; hybrid mode with the 7B model as an opt-in, with a mandatory warning to check the interpretation. No model alone matched the rules — the 7B made far fewer errors than the 3B (27 vs 21 of 40), so when the model is used, accuracy wins over latency.

05 · quality

Honesty rules, enforced in code

Nothing is silently fixed. Publication inconsistencies (occurrences Q01–Q13) stay as published, get a suspeito status and are cited in answers.
Absence ≠ zero. Missing rows never become zeros; shares without a numerator are undefined.
Ratios are ratios of sums — never averages of ratios.
Layered SQL security: read-only connection, SELECT/WITH only, table allow-list, no file access, 60 s timeout, 5,000-row limit, every query logged.
Reversible operations: database compaction by copy-validate-swap (5.11 → 4.48 GB), proven equivalent on all 61 gold answers.

06 · output

Charts that state the conclusion

Titles assert the finding, only Espírito Santo gets color, values are labeled at the line end, and annual series get a 3-year linear-trend projection with a 95% prediction interval — clearly marked as statistical extrapolation, not INEP data.

fig. 1 — Enrollment in ES grew 18.7% between 2017 and 2024; projection band = 95% prediction interval.

fig. 2 — Position of Espírito Santo among states.

fig. 3 — Local web interface (Python standard library only).

fig. 4 — An answer with quality flags, interpretation, sources and limitations.

07 · limits

Known limitations

Disability, quotas, financing, shift, nationality and faculty degree exist in the base but are not yet exposed by the engine.
The Synopses publish no cohort dropout or completion rates; distance-learning courses, seats and applicants exist only at the national level.
The rule interpreter covers the question forms documented in its guide; the local LLM is experimental (27/40 alone).
The database (~4.5 GB) is not in the repository — it is rebuilt from the public ZIPs (~3 h) using the manifest with MD5 checksums.

Independent project with public data — not an official product of any institution.