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
real data

3.4 million rows: 17 minutes or 35 seconds?

During a performance crisis on Nota Carioca's database, consolidated data had to get back into Oracle fast. Press play and compare conventional inserts with the direct-path load I rewrote.

Sped up. 1 second on screen equals 1 real minute.

Conventional inserts≈ 17 min
Direct-path load (SQL*Loader)35 s
Direct-path load (SQL*Loader): 35 s (3,400,000 rows loaded)

Proof-of-concept figures, between 87,000 and 121,000 rows per second on the direct-path load. The pipeline now runs in production twice a day.

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

  1. Full reloads with no downtime, using alternating tables and a synonym swap at the end.

  2. Handling of the cases that broke the load, such as line breaks inside fields, blank values turning into nulls and column sizing.

  3. Parity verified across 300 aggregate metrics and more than 3,000 rows compared cell by cell.

  4. A Discord bot to trigger and monitor production loads, with mutual exclusion between runs.

  5. 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.