IplanRio2026in production
Nota Carioca data pipelines
A BigQuery to Oracle load up to 33 times faster, taken from proof of concept to production in a few days.
33×
faster than conventional inserts
- 35 s
- to load 3.4 million rows
- 0
- mismatches in parity validation
The problem
During a performance crisis on the database behind Nota Carioca, the city's service e-invoicing system, heavy processing had to move out of Oracle and the consolidated data had to be loaded back. Loading through the JDBC connector took hours.
The solution
I replaced the entire load path with BigQuery extraction to Cloud Storage, reads through named pipes and SQL*Loader in direct path mode, which writes blocks straight into the datafiles. The solution became a set of Prefect flows covering extraction from Oracle to BigQuery, dbt transformations and the load back.
Technical notes
Full reloads with no downtime, using alternating tables and a synonym swap at the end.
Handling of the cases that broke the load, such as line breaks inside fields, blank values turning into nulls and column sizing.
Parity verified across 300 aggregate metrics and more than 3,000 rows compared cell by cell.
A Discord bot to trigger and monitor production loads, with mutual exclusion between runs.
An architecture plan to move this flow off Oracle, with direct ingestion from the national API, BigQuery, PostgreSQL and a Go API.
Outcome
In the proof of concept, 3.4 million rows loaded in 35 seconds, at 87,000 to 121,000 rows per second, compared with about 17 minutes using conventional inserts. The pipeline runs in production twice a day.