Study guide
3.3 BigQuery External Tables, BigLake & Federated Queries
Google Cloud Associate Data Practitioner Exam
Track your progress as you read
Finished sections get a check mark.
1 Introduction & Exam Overview
About the Associate Data Practitioner Exam
2 Chapter 1: Data Processing Paradigms & Ingestion Strategies
1.1 ETL, ELT, and ETLT Transformation Paradigms 1.2 Data Transfer and Migration Tools 1.3 Data Quality, Cleaning, and Validation
3 Chapter 2: Data Formats & Storage Solution Selection
2.1 File Formats & Serialization Characteristics 2.2 Cloud Storage Buckets, Location Types & Storage Classes 2.3 Selecting Google Cloud Databases and Storage Services
4 Chapter 3: Loading Data & Modern Ingestion Architectures
3.1 Batch Loading Data into BigQuery 3.2 Streaming Ingestion & Deduplication Patterns 3.3 BigQuery External Tables, BigLake & Federated Queries 3.4 Schema Definition & Evolution for Loaded Data
5 Chapter 4: BigQuery SQL Analytics & Query Foundations
4.1 BigQuery SQL Syntax & Analytical Functions 4.2 Complex Data Types: Arrays, Structs, and JSON 4.3 Answering Business Questions with BigQuery: Worked Analyses
6 Chapter 5: BigQuery Performance Optimization & Cost Governance
5.1 Table Partitioning Strategies 5.2 Table Clustering & Data Skipping 5.3 Query Optimization, Execution Plans, and Cost Controls
7 Chapter 6: Interactive Analysis, Notebooks & Visual Presentation
6.1 Exploratory Analysis with Jupyter & Colab Enterprise 6.2 Looker Studio (Data Studio) Reporting & Dashboard Design 6.3 Enterprise BI with Looker & LookML Foundations 6.4 Building, Modifying & Sharing Looker Dashboards
8 Chapter 7: Machine Learning with BigQuery ML & Vertex AI
7.1 Planning an ML Project: BigQuery ML, AutoML, or Pretrained Models? 7.2 Creating, Training & Evaluating Models with BigQuery ML 7.3 Running Inference and Organizing Models in Model Registry 7.4 Using Pretrained Gemini Models from BigQuery with Remote Connections
9 Chapter 8: Pipeline Engines & Transformation Services
8.1 Transformation Engine Selection Matrix 8.2 Apache Beam & Cloud Dataflow Core Concepts 8.3 Stream Processing: Windowing, Watermarks, and Triggers
10 Chapter 9: Workflow Orchestration, Automation & Monitoring
9.1 SQL Pipelines with Dataform & Scheduled Queries 9.2 Workflow Orchestration with Cloud Composer & Workflows 9.3 Event-Driven Ingestion: Pub/Sub to BigQuery & Eventarc Triggers 9.4 Monitoring Pipelines: Dataflow Job UI, Cloud Logging & Cloud Monitoring
11 Chapter 10: Access Control, Governance & Data Sharing
10.1 IAM Principles & Role Hierarchies for Data Services 10.2 Cloud Storage Security & Access Controls 10.3 Fine-Grained Security: Column-Level & Row-Level Controls 10.4 Secure Data Sharing with Analytics Hub (BigQuery Sharing)
12 Chapter 11: Lifecycle Management, Reliability & Compliance
11.1 Object Lifecycle Management & Storage Tiering 11.2 High Availability & Disaster Recovery Architectures 11.3 Data Retention, Time Travel, and Point-in-Time Recovery 11.4 Encryption Key Management & Privacy Compliance
3.3 BigQuery External Tables, BigLake & Federated Queries
Key Takeaways Explain This Topic AI
External tables enable BigQuery SQL queries directly against files in Cloud Storage, AWS S3, or Azure Blob Storage without duplicating data or incurring BigQuery storage charges.
BigLake unifies data lakes and warehouses by extending BigQuery enterprise governance—including row-level security, column-level security, and dynamic data masking—directly to open file formats like Parquet, ORC, and Avro.
BigLake Object Tables expose unstructured files (images, audio, PDFs) as tabular SQL records, so Gemini remote models and AI functions in BigQuery can analyze them with SQL.
Federated queries use EXTERNAL_QUERY to push SQL subqueries down to operational relational engines (Cloud SQL, AlloyDB, Spanner), streaming only filtered results back to BigQuery.
Standard external tables experience lower query performance than native tables and lack DML mutation support, making them best suited for raw data staging and low-frequency queries.
Last updated: October 2026
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
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.
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.
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.
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:
BigQuery identifies the SQL string inside EXTERNAL_QUERY and sends it directly to the Cloud SQL or Spanner database engine.
The remote database engine executes the query locally, applying all filtering, indexing, and joins inside its native engine ( predicate pushdown ).
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:
Feature Native BigQuery Tables Standard External Tables BigLake Tables Federated Queries ( EXTERNAL_QUERY )
Storage Location BigQuery (Colossus) Cloud Storage / Multi-cloud Cloud Storage / AWS / Azure Cloud SQL / AlloyDB / Spanner
Storage Billing BigQuery Active/Long-term Cloud Storage pricing only Cloud Storage pricing only Database instance storage
Query Performance Maximum (sub-second) Moderate to Slow Fast (with metadata cache) Dependent on remote database
- Governance (RLS/CLS) Full support Not supported Full support (via BigQuery) Handled at database source
- DML Support Full ( INSERT , UPDATE , etc.) Read-only Read-only Read-only from BigQuery
Primary Use Case Core data warehouse & BI Raw ELT staging & archives Governed multi-cloud data lake Live transactional dimension joins