Bridging the gap between analytical systems (OLAP) and transactional databases (OLTP) is notoriously complex. Synchronizing data between the two systems, often called Reverse ETL (Extract, Transform, Load), traditionally requires heavy middleware, staging buckets, and complex orchestration, especially at enterprise scale.
We’re announcing the general availability (GA) of BigQuery exports to AlloyDB, allowing data engineering teams to use a single, declarative SQL statement to export data from BigQuery to AlloyDB. This approach completely removes the need for complex ETL middleware.
To demonstrate the benefits of this feature, we are sharing how Copel, a Brazilian electric power provider, eliminated a massive orchestration bottleneck and scaled their smart meter telemetry by migrating to high-performance, built-in Reverse ETL.
The challenge of scaling smart meter telemetry
At Copel, managing telemetry data from millions of smart electricity meters is a mission-critical operation. This data is streamed continuously and consolidated in an analytical Gold layer in BigQuery. However, to power Copel’s “Systems of Action” — low-latency customer support dashboards, mobile applications, and operational billing systems — this rich dataset must be constantly synced back into its high-performance transactional database, AlloyDB for PostgreSQL.
Until recently, Copel orchestrated this Reverse ETL architecture with a complex DAG in Google’s Managed Service for Apache Airflow. However, there were multiple brittle, intermediate stages in the process of enriching and exporting hundreds of millions of records from BigQuery to AlloyDB:
Figure 1: Copel’s legacy multi-stage architecture requiring Cloud Storage file dumps and sequential gCloud scripts.
-
BigQuery ran enrichment and extraction queries, dumping SQL results into dozens of fragmented CSV files inside a Google Cloud Storage bucket.
-
Apache Airflow executed SQL to create temp tables in AlloyDB to prevent write locking or data corruption for production tables.
-
Sequential gCloud scripts combed through the Cloud Storage CSVs to load data into a respective AlloyDB cluster and shards.
-
Finally, a SQL transaction block safely dropped the production table and renamed the temporary table to production.
To run this legacy architecture, Copel’s team needed to maintain the pipeline, carefully manage file staging, perform complex IAM impersonation across multiple services, and constantly monitor end-to-end system health.
The breakthrough: Built-in Reverse ETL
With BigQuery’s EXPORT DATA to AlloyDB, Copel was able to replace this entire multi-step pipeline infrastructure with a single, elegant SQL statement.
Now, BigQuery exports communicate directly with AlloyDB instances over highly optimized, parallelized connections — no staging files in Cloud Storage, gCloud scripts, or managing temporary tables required!
Figure 2: New architecture showing direct parallel Reverse ETL connection between BigQuery and AlloyDB.
Here is how simple the new Reverse ETL process looks in code:
- code_block
- <ListValue: [StructValue([('code', 'EXPORT DATArn WITH CONNECTION `project_id.location.connection_id`rn OPTIONS(rn format='ALLOYDB',rn uri="https://alloydb.googleapis.com/v1/projects/my-project/locations/my-location/clusters/my-cluster/instances/my-instance",rn alloydb_options="""{rn "table": "operational_telemetry"rn }"""rn )rnAS SELECT rn meter_id,rn reading_timestamp,rn — On-the-fly serialization to JSONBrn TO_JSON(hourly_metrics) AS hourly_metrics_jsonbrnFROM rn `project_id.dataset_id.smart_meter_telemetry`;'), ('language', ''), ('caption', )])]>
The resulting BigQuery values map perfectly to AlloyDB’s columns during the export, leaving the nested structures fully queryable and indexable on the PostgreSQL side.
Evaluating performance and scalability
To validate this new architecture, Copel executed two large-scale Reverse ETL benchmarks: comparing the performance and scalability of a smaller incremental Reverse ETL export against a much larger full-table Reverse ETL export.
The performance results exceeded all initial expectations:
|
Metric |
Test 1: Incremental Export |
Test 2: Full-Table Export |
|
Total Rows Exported |
18,145,589 rows |
207,484,577 rows |
|
Logical Data Size |
2.32 GB |
188.87 GB |
|
Export Throughput |
~3.1 Million rows / minute |
~4.2 Million rows / minute |
|
AlloyDB Specs |
N2 (16 vCPUs, 128 GB RAM) |
N2 (16 vCPUs, 128 GB RAM) |
|
CPU Utilization |
Hovered stably at ~85% |
Hovered stably at ~85% |
Pro-tip on ingestion: Sustaining an export throughput of ~4.2 million rows/minute on complex JSONB telemetry fields is an outstanding technical achievement. It highlights the low-level optimizations between BigQuery’s distributed processing engine and AlloyDB’s advanced storage layer.
Key takeaways and benefits
By adopting this declarative SQL approach, Copel successfully deleted dozens of lines of complex scripting and custom error-handling blocks. Their Airflow DAG now contains a single, simplified BigQueryInsertJobOperator task that executes the export query.
This built-in approach unlocks several core business benefits:
-
Direct cost reduction: By bypassing Cloud Storage entirely, there are no staging storage costs and associated API read/write operation fees.
-
Enhanced data security: Data moves directly from BigQuery to AlloyDB inside Google’s secure internal network without ever being dumped into readable CSV files.
-
Near-zero maintenance: Pipeline errors are now contained within a single SQL statement, making troubleshooting and logging drastically simpler.
-
Massive distributed writing: The direct export achieved write speeds exceeding 4 million records per minute by leveraging BigQuery’s massive parallelism combined with AlloyDB’s optimized PostgreSQL write performance.
Now, rather than spending valuable engineering hours monitoring pipeline failures and intermediate storage buffers, data engineering teams can focus on what matters: delivering smart analytics and premium experiences to customers.
Get started today: Modernize your Reverse ETL
Now that BigQuery exports to AlloyDB are generally available, there has never been a better time to simplify your data architecture. If your team is struggling with complex, multi-stage pipelines to push BigQuery data into downstream OLTP databases, we invite you to try built-in export functionality for AlloyDB, Bigtable, Spanner, or Pub/Sub today.