3.3 BigQuery External Tables, BigLake & Federated Queries

3.3 BigQuery External Tables, BigLake & Federated Queries

Traditional data warehousing patterns require extracting data from operational sources, transforming it, and loading it into dedicated warehouse storage before any analysis can take place. However, as enterprise data lakes grow to petabyte and exabyte scales, duplicating raw files into proprietary data warehouse storage introduces significant storage costs, data staleness, and synchronization complexity.

Google Cloud provides advanced zero-copy and in-place analytics capabilities that allow organizations to query data directly where it lives—whether in Google Cloud Storage, Amazon Web Services (AWS) S3, Microsoft Azure Blob Storage, or live operational databases like Cloud SQL and Cloud Spanner.

---

1. Standard BigQuery External Tables on Cloud Storage

An External Table (also known as a federated data source) is a BigQuery table definition that points directly to an external storage location rather than storing data within BigQuery's native Capacitor columnar storage.

+-----------------------------------------------------------------------------------+
|                         BIGQUERY EXTERNAL TABLE ARCHITECTURE                      |
+-----------------------------------------------------------------------------------+
|  BigQuery SQL Engine (Dremel Slots)                                               |
|       |                                                                           |
|       | Reads files on-the-fly via Jupiter network fabric                         |
|       v                                                                           |
|  Google Cloud Storage Bucket (gs://my-bucket/lake/data/*.parquet)                 |
|  Formats: Parquet, ORC, Avro, CSV, JSON, Google Sheets, Bigtable                  |
+-----------------------------------------------------------------------------------+

Creating an External Table with Hive Partitioning

External tables can leverage directory structures in Cloud Storage to automatically derive partition columns, a technique known as Hive Partitioning (e.g., gs://bucket/logs/year=2026/month=10/data.parquet):

CREATE EXTERNAL TABLE `telemetry.ext_server_logs`
WITH PARTITION COLUMNS (
  year INT64,
  month INT64
)
OPTIONS (
  format = 'PARQUET',
  uris = ['gs://company-telemetry-lake/logs/*'],
  hive_partition_uri_prefix = 'gs://company-telemetry-lake/logs/'
);

When a query executes WHERE year = 2026 AND month = 10, BigQuery prunes Cloud Storage prefixes, scanning only the relevant directories rather than listing the entire bucket.

Trade-offs of Standard External Tables

While external tables eliminate data duplication and storage costs, they introduce significant technical trade-offs:

• Advantages:
  • Zero Ingestion Latency: Files landing in Cloud Storage are immediately queryable without running load jobs.
  • Zero Storage Duplication: You pay only for Cloud Storage; there are zero BigQuery active storage charges.
  • Ideal Staging Ground: Perfect for ELT transformation pipelines where raw files are cleaned via SQL before loading into native tables.
• Disadvantages:
  • Substantially Slower Query Performance: External queries must transfer raw data across network links, parse file formats at runtime, and lack BigQuery's deep native storage optimizations.
  • No Native Clustering or Caching Optimizations: BigQuery cannot re-order or co-locate records inside external files.
  • No DML Modifications: You cannot execute INSERT, UPDATE, or DELETE statements against external tables.
  • Inconsistency Risk: If an external process overwrites or deletes files while a query is running, the query will fail with an I/O read error.

---

2. BigLake: The Unified Lakehouse Storage Engine

Standard external tables suffer from a fundamental security and governance limitation: to query an external table, the end-user analyst must possess direct read permissions (e.g., storage.objects.get) on the underlying Cloud Storage bucket. Granting direct bucket access bypasses BigQuery's column-level security, row-level security, and audit logging, creating a severe data governance loophole.

BigLake solves this problem by decoupling compute from storage while enforcing enterprise data governance directly over open file formats (Apache Parquet, Apache ORC, Apache Avro) stored across multi-cloud environments (Cloud Storage, AWS S3, and Azure Blob Storage via BigQuery Omni).

Loading diagram...

Key Capabilities of BigLake Tables

1. Unified Fine-Grained Security:
   • Row-Level Security (RLS): BigLake allows administrators to apply row access policies (e.g., SESSION_USER() = manager_email) directly over Parquet or ORC files in Cloud Storage.
   • Column-Level Security (CLS): Policy tags can be applied to BigLake table columns, restricting sensitive columns (e.g., credit card numbers or medical identifiers) to authorized IAM groups.
   • Dynamic Data Masking: BigLake masks, hashes, or redacts specific fields on-the-fly without modifying underlying lake files.
2. Cloud Resource Connection Delegation: BigLake tables authenticate to Cloud Storage using a Google-managed service account tied to a CLOUD_RESOURCE connection. Analysts do not need any IAM access to the Cloud Storage bucket. They only need bigquery.jobs.create and dataset read permissions. The BigQuery connection securely reads the files on their behalf.
3. Performance Acceleration via Metadata Caching: BigLake tables support automated metadata caching (metadata_cache_mode = 'AUTOMATIC'). BigQuery caches file metadata (file paths, block byte offsets, Parquet min/max statistics), eliminating expensive Cloud Storage LIST API operations and accelerating partition pruning.
4. Materialized Views Support: BigQuery can maintain materialized views on top of BigLake tables, providing automated query rewriting and near-native query performance.

-- Creating a BigLake table with Cloud Resource Connection and metadata caching
CREATE EXTERNAL TABLE `enterprise_lake.customer_transactions`
WITH CONNECTION `us-central1.gcs-lake-connection`
OPTIONS (
  format = 'PARQUET',
  uris = ['gs://corp-data-lake-prod/transactions/*.parquet'],
  metadata_cache_mode = 'AUTOMATIC',
  max_staleness = INTERVAL 1 HOUR
);

---

3. BigLake Object Tables for Unstructured Data

Enterprise data lakes frequently store vast quantities of unstructured data—such as radiology images, PDF invoices, audio call recordings, and video feeds. Traditionally, analyzing these assets required custom Python scripts or specialized ML engineering workflows.

BigLake Object Tables expose unstructured objects in Cloud Storage as structured SQL tables. Each row in an object table represents a physical file, exposing system metadata as queryable columns:

-- Creating an Object Table over PDF contract documents
CREATE EXTERNAL TABLE `legal_ops.contracts_object_table`
WITH CONNECTION `us-central1.gcs-lake-connection`
OPTIONS (
  object_metadata = 'SIMPLE',
  uris = ['gs://legal-vault-prod/contracts/*.pdf']
);

Object Table Schema Columns:

• uri (STRING): The fully qualified Cloud Storage path (gs://...).
• generation (INT64): The unique object generation number.
• size (INT64): File size in bytes.
• content_type (STRING): MIME type (e.g., application/pdf, image/png).
• updated (TIMESTAMP): Modification timestamp.
• metadata (ARRAY<STRUCT<STRING, STRING>>): User-defined object metadata key-value pairs.

Multimodal Analysis with Gemini Models

By combining object tables with a BigQuery ML remote model over Gemini (hosted on Vertex AI, which Google renamed Gemini Enterprise Agent Platform in April 2026), analysts can analyze unstructured files with SQL. Section 7.4 explains how remote models are created.

-- AI.GENERATE_TEXT is a table function: it reads the object table and
-- returns one row per file with the model's response
SELECT *
FROM AI.GENERATE_TEXT(
  MODEL `legal_ops.gemini_model`,             -- remote model over a Gemini endpoint
  TABLE `legal_ops.contracts_object_table`,
  STRUCT('Extract the total contract value, renewal date, and vendor name as JSON.' AS prompt)
);

---

4. Federated Queries: Querying Operational Relational Databases

While external tables query object storage files, Federated Queries allow BigQuery to query live, operational relational databases—specifically Cloud SQL (MySQL and PostgreSQL), AlloyDB, and Spanner—without extracting or replicating data.

Federated queries execute using the EXTERNAL_QUERY SQL function and a dedicated BigQuery database connection:

-- Joining BigQuery analytics data with live Cloud SQL customer profiles
SELECT 
  bq_orders.order_id,
  bq_orders.total_amount,
  csql_cust.customer_name,
  csql_cust.loyalty_tier
FROM `analytics_dw.orders` AS bq_orders
JOIN EXTERNAL_QUERY(
  'projects/my-prod/locations/us/connections/cloudsql-postgres-conn',
  '''SELECT customer_id, customer_name, loyalty_tier FROM customers WHERE account_status = 'ACTIVE';'''
) AS csql_cust
  ON bq_orders.customer_id = csql_cust.customer_id;

Pushdown Predicate Architecture

The internal mechanics of EXTERNAL_QUERY are crucial for performance optimization:

1. BigQuery identifies the SQL string inside EXTERNAL_QUERY and sends it directly to the Cloud SQL or Spanner database engine.
2. The remote database engine executes the query locally, applying all filtering, indexing, and joins inside its native engine (predicate pushdown).
3. Only the filtered, compact result set is serialized and streamed back over Google's internal network to BigQuery's worker slots for final joining with warehouse tables.

Constraints and Anti-Patterns of Federated Queries:

• Impact on Operational Databases: Heavy federated queries consume CPU, memory, and IOPS on your production Cloud SQL instance, potentially impacting live transactional applications.
• Not a bulk-extraction tool: Federated queries suit small, frequently changing lookup data. Pulling very large tables through EXTERNAL_QUERY is slow and adds load to the source database; replicate large tables into BigQuery instead (for example, with Datastream).
• Data Type Mappings: Specific proprietary relational datatypes (e.g., PostgreSQL UUID or custom geometry types) must be cast to standard SQL types (such as VARCHAR or STRING) within the pushed-down query string.

---

5. Architectural Decision Matrix

To choose the correct data access pattern, evaluate workloads against this decision matrix: