Choose how to access BigQuery data from AlloyDB

To access BigQuery data from AlloyDB for PostgreSQL, you can use the bigquery_fdw (foreign data wrapper) and alloydb_sync extensions. Together, these extensions provide methods for integrating your analytical and operational data: real-time data access (lakehouse federation), one-time data operations, and periodic synchronization. This document describes the options to access BigQuery data from AlloyDB.

Lakehouse federation

Lakehouse federation provides real-time access to your BigQuery data directly from AlloyDB for PostgreSQL without moving or duplicating the data. To bridge the two systems, this method uses the bigquery_fdw extension, which lets you query live datasets in place. Because you query the source data directly, you gain insights from the most current data available, bypassing the latency and maintenance overhead of data pipelines or synchronization intervals.

To optimize performance, AlloyDB pushes standard filters and aggregations down to BigQuery, streaming only the relevant, pre-filtered results back to your instance. For more information, see AlloyDB access to real-time data in BigQuery overview.

Use cases

Lakehouse federation supports the following use cases:

  • Real-time operational analytics: you need access to the freshest analytical data available in BigQuery to make instant business decisions, without waiting for batch processing.
  • Hybrid transactional analytical processing (HTAP): you need to run analytics that join live, "hot" operational data residing in AlloyDB with massive volumes of historical, "cold" data stored in BigQuery.
  • Ad hoc and exploratory data analysis: you want to run immediate queries on BigQuery data without building, maintaining, or waiting for complex extract, transform, and load (ETL) pipelines.
  • Zero-copy architecture: you want to minimize storage costs and data governance overhead by keeping your analytical data in one place while maintaining access through PostgreSQL semantics.

One-time table synchronization

A one-time operation moves or accesses data from BigQuery once, rather than on an ongoing schedule. You can perform a one-time operation using sync tables or by importing foreign tables.

Sync tables

You can perform a one-time sync using the alloydb_sync.import_bq_table() function in the alloydb_sync extension. This function streams data from BigQuery into local AlloyDB storage.

The result is a fully independent, writable PostgreSQL table in your AlloyDB cluster. Because the synced table is writable, you can freely execute INSERT, UPDATE, and DELETE operations on the local data. For more information, see Sync BigQuery data to AlloyDB.

Import tables

You can perform a one-time import using the bigquery_fdw extension. This method uses IMPORT FOREIGN SCHEMA or CREATE FOREIGN TABLE to map the BigQuery dataset into AlloyDB.

Foreign tables created with bigquery_fdw are read-only references to the remote BigQuery data. To create a local copy of the data, you can run a CREATE TABLE local_table AS (SELECT * FROM foreign_table) query. For more information, see Import BigQuery data to AlloyDB.

Use cases

One-time data operations support the following use cases:

  • Data enrichment: pull pre-computed analytical outputs (such as customer segmentation buckets or machine learning predictions) from BigQuery into AlloyDB to enrich your operational database.
  • Low-latency application serving: provide immediate access to a subset of historical data where the overhead or latency of querying BigQuery remotely is unacceptable.
  • Isolated data modification: obtain a local copy of analytical data to process, modify, or index independently of the source dataset (for example, generating vector embeddings with AlloyDB AI).

Periodic table synchronization

Periodic operations refresh data on a recurring schedule—for example, hourly or daily. You can set up periodic operations using sync tables or by scheduling queries on imported tables.

Sync tables

You can establish an automated refresh schedule using the alloydb_sync.create_bq_sync_table() function in the alloydb_sync extension. This function configures background workers to periodically pull updated data from BigQuery and refresh your local AlloyDB table to mirror the source.

Periodic sync tables created with alloydb_sync are managed, read-only tables. This ensures data integrity while allowing applications to query the data locally with high performance and scale horizontally across read pools. For more information, see Sync BigQuery data to AlloyDB and Data synchronization overview.

Import tables

You can set up periodic imports by combining the bigquery_fdw extension with the PostgreSQL pg_cron extension. In this approach, bigquery_fdw provides the read-only foreign table definition, and pg_cron periodically executes SQL queries (such as TRUNCATE and INSERT INTO ... SELECT or recreating the table) to refresh a local table.

Unlike sync tables, this approach requires you to manually manage and maintain the pg_cron schedules and SQL refresh scripts. For more information, see Set up a schedule to periodically import data.

Use cases

Periodic operations support the following use cases:

  • High-concurrency serving: serve analytical insights to thousands of concurrent users. By maintaining local data in AlloyDB and scaling out with read pools, you can bypass the concurrent connection limits inherent in BigQuery.
  • Performance acceleration: process data using the AlloyDB columnar engine and local buffer cache for maximum query performance, where your application can tolerate data updated on a schedule.
  • Automated data mirroring: keep your operational applications supplied with fresh data from your data warehouse on a set-it-and-forget-it schedule.

What's next