Nota Carioca data pipelines
A BigQuery → Oracle load 31 to 33x faster, in production a few days after the first test.
My role
I built the PoC alone, took it to production on Prefect and wrote the plan for moving off Oracle.
- 33x
- faster than conventional inserts
- 35 s
- for 3.4 million rows
- 0
- mismatches in parity checks
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 come back finished. Loading through the JDBC connector took hours.
Solution
I replaced the whole path: bq extract to GCS, named pipes, and SQL*Loader direct path, which writes blocks straight into the datafiles. It then became a set of Prefect flows: Oracle → BigQuery extraction, dbt transforms and the load back.
Highlights
- Full reloads with no downtime, using A/B tables and a synonym swap at the end.
- Handled the cases that broke the load, like newlines inside fields, blanks turning into NULL and column sizing.
- Parity checked across 300 aggregate metrics and more than 3,000 rows compared cell by cell.
- A Discord bot to trigger and follow production loads, with mutual exclusion between them.
- An architecture plan to take this path off Oracle for good, with direct ingestion from the national API, BigQuery, PostgreSQL and a Go API.
Result
In the PoC, 3.4 million rows loaded in 35 s (87k to 121k rows/s) versus about 17 minutes with conventional inserts. The pipeline runs in production twice a day.