The source: two public APIs
Brazil's two legislative houses publish open data. The Chamber of Deputies exposes collections such as deputies, parties, bodies, propositions and votes, with interactive Swagger documentation and a collection that lists recent updates. The Federal Senate has its own open data service with a different structure.
For Como Votam, I built an automated pipeline that collects from both and keeps our database current, on a nonprofit budget. This article collects the design lessons that apply to any pipeline over public data, without going into the project's internals.
Two sources, one model
The Chamber and the Senate describe similar things (people, parties, matters, votes) in different shapes. The first decision is where the difference lives. Keep each source behind its own adapter that speaks the source's format and outputs a common internal model. Everything after that point, from storage to analysis, does not know which house the data came from.
That one boundary pays for itself every time a source changes a field name or an endpoint.
Make every load idempotent
A pipeline that runs on a schedule will, sooner or later, run twice, or crash halfway and run again. The only sane rule is that running a load again must produce the same result as running it once.
In a relational database, that usually means upserts keyed by a stable identifier from the source:
INSERT INTO votes (source, source_id, matter_id, choice, voted_at)
VALUES ($1, $2, $3, $4, $5)
ON CONFLICT (source, source_id)
DO UPDATE SET
choice = EXCLUDED.choice,
voted_at = EXCLUDED.voted_at;The snippet is illustrative. Note the composite key: two houses may reuse the same numeric identifiers, so the source is part of the key.
Sync incrementally, using the source's own signal
Re-downloading everything on every run is slow and rude to a public service. When a source tells you what changed, use it. The Chamber's open data includes an updates collection for exactly this purpose: ask what changed in a time window and fetch only those records.
Two habits make incremental sync safe:
- Overlap your windows. Ask for slightly more than the time since the last run, and rely on idempotent loads to absorb the duplicates.
- Store the cursor only after the load succeeds. If a run fails, the next one starts from the same point.
Reconcile, because sync can drift
Incremental sync is efficient, and it can also drift quietly: a missed update, a record corrected upstream without a notification, a bug on your side. The answer is a separate reconciliation step that periodically compares what you hold with what the source reports (counts, totals, a sample of records) and flags differences.
Keeping sync and reconciliation as distinct concerns matters. Sync is optimized for speed. Reconciliation is optimized for trust, and it is the part that lets you tell users, honestly, that the numbers match the official record.
Be a good citizen of a public API
Public services have limits you may not see in the documentation. Cap concurrency, back off on errors, identify your client clearly and cache what does not change. It keeps the pipeline reliable and keeps the service available for everyone else.
Takeaways
- Isolate each source behind an adapter and a common model.
- Idempotent loads make failure boring.
- Use the source's change feed, overlap the windows and commit the cursor last.
- Reconcile separately, because trust needs its own check.
