Federated Queries in BigQuery: EXTERNAL_QUERY Against Cloud SQL, AlloyDB, Spanner, and Bigtable

Most BigQuery estates have a gap that no amount of warehouse tuning closes: the freshest, most authoritative version of some table lives in an operational database — a Cloud SQL Postgres instance behind the billing app, an AlloyDB cluster, a Spanner database, a Bitable-backed event store. The usual answer is a pipeline. Sometimes the better answer is a federated query: BigQuery reaches into the source system at query time, runs SQL there, and streams the result back as a table expression you can join to.

This tutorial covers how federation actually works, the syntax for each supported source, the pushdown and quota behaviour that determines whether it will be fast or awful, and the decision rule for federating versus replicating with Datastream.

What a federated query is (and is not)

A federated query has three moving parts:

1. A BigQuery connection — a resource in the BigQuery Connection API that holds the target instance and credentials. It gets its own Google-managed service account.
2. The EXTERNAL_QUERY table function, which takes a connection ID and a SQL string written in the source dialect.
3. BigQuery's execution engine, which treats the returned rows as an ephemeral table in the surrounding GoogleSQL query.

What it is not: it is not a copy, not a materialised view, and not a cache. Every execution hits the source database. It is also not an external table over object storage (Parquet/Iceberg in GCS) — that is a different feature with different economics.

Step 1 — Create the connection

Connections are regional and must live in the same region as the dataset you query from. A connection in us-central1 cannot be used by a query in EU.

# Cloud SQL for PostgreSQL
bq mk --connection \
  --display_name="billing-prod (Cloud SQL PG)" \
  --connection_type=CLOUD_SQL \
  --properties='{"instanceId":"my-project:us-central1:billing-prod","database":"billing","type":"POSTGRES"}' \
  --connection_credential='{"username":"bq_reader","password":"REDACTED"}' \
  --project_id=my-project \
  --location=us-central1 \
  billing_prod

For AlloyDB and Spanner the --connection_type differs (CLOUD_RESOURCE-style Spanner connections take a database property in the form projects/p/instances/i/databases/d), but the pattern is identical. Terraform users should reach for google_bigquery_connection, which keeps the credential in Secret Manager instead of shell history:

resource "google_bigquery_connection" "billing_prod" {
  connection_id = "billing_prod"
  location      = "us-central1"
  cloud_sql {
    instance_id = google_sql_database_instance.billing.connection_name
    database    = "billing"
    type        = "POSTGRES"
    credential {
      username = "bq_reader"
      password = data.google_secret_manager_secret_version.bq_reader.secret_data
    }
  }
}

Inspect what you created, including the auto-provisioned service account:

bq show --connection my-project.us-central1.billing_prod

Step 2 — Grant the least privilege that works

Two separate permission layers trip people up:

• Google Cloud IAM. The caller needs bigquery.connections.use on the connection (role roles/bigquery.connectionUser). Grant it to the analyst group, not to allAuthenticatedUsers. The connection's own service account needs roles/cloudsql.client on the instance.
• Database-native grants. The bq_reader role inside Postgres should be read-only and scoped:

CREATE ROLE bq_reader LOGIN PASSWORD '...';
GRANT CONNECT ON DATABASE billing TO bq_reader;
GRANT USAGE ON SCHEMA public TO bq_reader;
GRANT SELECT ON public.invoices, public.customers TO bq_reader;
ALTER ROLE bq_reader SET statement_timeout = '120s';

That statement_timeout is the cheapest production safeguard you will ever configure: it guarantees a runaway analytics query cannot pin your OLTP primary indefinitely.

Step 3 — Query it

SELECT
  c.customer_id,
  c.plan,
  live.open_invoice_count,
  live.open_invoice_total
FROM `analytics.dim_customer` AS c
JOIN EXTERNAL_QUERY(
  'my-project.us-central1.billing_prod',
  '''SELECT customer_id,
            COUNT(*)      AS open_invoice_count,
            SUM(amount)   AS open_invoice_total
     FROM invoices
     WHERE status = 'open'
       AND updated_at >= NOW() - INTERVAL '7 days'
     GROUP BY customer_id'''
) AS live
USING (customer_id);

Three things to notice:

• The inner string is PostgreSQL, not GoogleSQL. NOW() - INTERVAL '7 days' is Postgres syntax; it would be a syntax error in BigQuery.
• Triple-quoting is what keeps the single quotes inside the source SQL legal.
• The aggregation happens in Postgres. Only one row per customer crosses the wire.

That last point is the whole game.

Pushdown: the rule that decides performance

BigQuery does not rewrite your outer query into the source dialect. Whatever is inside the EXTERNAL_QUERY string is what the source executes; whatever is outside runs in BigQuery after every returned row has been materialised. Filters in the outer query are not pushed down.

So this is fast:

-- 1 row crosses the wire
SELECT * FROM EXTERNAL_QUERY(conn,
  '''SELECT COUNT(*) FROM events WHERE created_at >= CURRENT_DATE''');

And this is a self-inflicted outage:

-- 900M rows crossed the wire, then filtered
SELECT COUNT(*)
FROM EXTERNAL_QUERY(conn, '''SELECT * FROM events''')
WHERE created_at >= CURRENT_DATE;

The checklist for every federated query you write:

1. Push every filter, aggregate, and column projection inside the string.
2. Make sure the source has an index supporting that filter — federation inherits the source's query plan, warts and all.
3. Keep the returned row count in the thousands, not the millions.
4. Parameterise dates rather than hardcoding them, so the query is cacheable at the application layer.

BigQuery also cannot cache federated results the way it caches native queries: a federated query is treated as non-deterministic, so repeated runs re-hit the source. Budget for that.

Type mapping and NULL surprises

The source's type system is mapped to BigQuery's, and the edges matter:

Source type	BigQuery type	Watch for
Postgres numeric (unconstrained)	BIGNUMERIC / NUMERIC	Precision loss beyond BigQuery's scale limits
Postgres timestamptz	TIMESTAMP	Fine; timestamp without zone becomes DATETIME
Postgres json / jsonb	STRING	Re-parse with PARSE_JSON in the outer query
Postgres arrays, hstore, enums	STRING or unsupported	Cast to text in the inner SQL
MySQL tinyint(1)	INT64	Not BOOL — compare to 1, not TRUE
Spanner NUMERIC	NUMERIC	Spanner's Graph/JSON types need explicit casts

Defensive habit: cast exotic types to text or a numeric inside the inner query. SELECT id, payload::text AS payload FROM events removes an entire class of failure.

Spanner, AlloyDB, and Bigtable specifics

Spanner. Federated queries against Spanner accept a query_mode hint and can target the read-only replica. Because Spanner is strongly consistent and horizontally scalable, federating against it is far less risky than federating against a single-writer OLTP primary — but you still pay Spanner CPU for the scan. For heavy analytics, prefer Spanner's own data-boost / external dataset integration, which runs the scan on isolated compute and avoids touching serving capacity.

AlloyDB. Treat it like Cloud SQL Postgres, but point federated reads at a read pool node, never the primary. AlloyDB's columnar engine can make a federated aggregate genuinely fast.

Bigtable. This one is not EXTERNAL_QUERY — Bigtable is exposed as an external table defined over a table URI with a column-family schema. Row-key prefix filters are the only filter that pushes down efficiently; anything else is a full scan of a serving cluster, which is precisely what Bigtable is worst at tolerating.

Federate or replicate? The decision table

Signal	Federate with EXTERNAL_QUERY	Replicate with Datastream / CDC
Freshness requirement	Seconds, must be authoritative	Minutes is fine
Result size	Thousands of rows	Millions of rows
Query frequency	Occasional, human-initiated	Dashboards, scheduled models
Source headroom	OLTP primary has spare CPU / read replica available	Source is at capacity
Joins to warehouse history	Small dimension lookup	Large fact-to-fact joins
Schema volatility	Stable	Stable or evolving (CDC handles drift)
Governance	Source is the record of truth, no copy desired	Copy is acceptable and auditable

A pattern that works well in practice is both: replicate the wide history with Datastream into a partitioned BigQuery table, and use a small federated query only for the trailing few hours the CDC pipeline has not yet landed, unioned together in a view.

CREATE OR REPLACE VIEW analytics.invoices_live AS
SELECT * FROM analytics.invoices_cdc
WHERE updated_at < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 15 MINUTE)
UNION ALL
SELECT * FROM EXTERNAL_QUERY(
  'my-project.us-central1.billing_prod',
  '''SELECT * FROM invoices WHERE updated_at >= NOW() - INTERVAL '15 minutes' ''');

Readers get one object, near-real-time freshness, and the source only ever serves a 15-minute slice.

Operational guardrails

• Concurrency. Federated queries consume source connections. Cap them: a scheduled query fan-out of 40 dashboards against one Cloud SQL instance will exhaust max_connections long before BigQuery notices.
• Timeouts. Set statement_timeout server-side and keep inner queries simple. BigQuery will surface a source error as a job failure with the source's message intact.
• Auditing. Federated jobs appear in INFORMATION_SCHEMA.JOBS like any other. Find them with:

SELECT creation_time, user_email, total_slot_ms, query
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND query LIKE '%EXTERNAL_QUERY%'
ORDER BY total_slot_ms DESC
LIMIT 50;

• Cost. Bytes processed for the federated portion are the bytes returned, not the bytes scanned in the source. That looks cheap in the BigQuery bill while the real cost lands as CPU and IO on the operational database — a bill nobody on the data team sees. Review it deliberately.
• Region drift. If you move a dataset between regions, every connection referenced by its views breaks. Inventory connections in Terraform so the blast radius is visible.

A short checklist before you ship one

1. Read-only database role with an explicit SELECT grant list and a statement timeout.
2. Connection in the same region as the querying dataset, defined in Terraform.
3. roles/bigquery.connectionUser granted to a group, not individuals.
4. All filters and aggregation inside the EXTERNAL_QUERY string.
5. Returned row count measured, not assumed.
6. Source CPU and connection-count dashboards alerting before the data team notices.
7. A written answer to "what happens to this dashboard when the source is failing over?"

Federation is a scalpel. Used on small, well-filtered, freshness-critical lookups it removes whole pipelines from your architecture. Used as a substitute for ingestion it turns your warehouse into a denial-of-service tool pointed at your own production database.

If you need help deciding where that line falls in your estate — or building the hybrid CDC-plus-federation pattern above — our BigQuery data engineering and system integration teams do this work every week. Get in touch with the shape of your source systems and we will sketch the architecture.