BigLake and Apache Iceberg tables in BigQuery

BigLake tables read files in Cloud Storage or other clouds through a connection, so users need access to the table only and row- and column-level security work. Iceberg managed tables let BigQuery write and optimize Iceberg in your own bucket; files added to that bucket outside BigQuery are deleted.

BigLake is BigQuery’s answer to the data lake: tables whose data lives in open file formats in your own Cloud Storage bucket (or Amazon S3 or Azure Blob Storage), but which behave much more like BigQuery tables than plain external tables do. There are two kinds that matter. A BigLake table reads files that something else writes. An Iceberg managed table is written by BigQuery itself, in Apache Iceberg format, so Spark and other engines can read the same data.

BigLake tables: reading files through a connection

A plain external table reads files with the querying user’s own access, so every analyst needs read access to the bucket. A BigLake table reads through a connection whose service account has the bucket access. Users only need access to the table:

CREATE EXTERNAL TABLE mydataset.events
WITH CONNECTION `myproject.us.lake-connection`
OPTIONS (
  format = 'PARQUET',
  uris = ['gs://my-lake/events/*.parquet'],
  max_staleness = INTERVAL 1 HOUR,
  metadata_cache_mode = 'AUTOMATIC'
);

Separating table access from bucket access is what makes these possible on files:

• row-level security and column-level security, including dynamic data masking on Cloud Storage tables;
• sharing a table without exposing the bucket;
• reading the table from Spark and other engines through the BigQuery Storage Read API, with the same policies applied.

Metadata caching

Listing thousands of files and reading their footers takes time on every query. With max_staleness and metadata_cache_mode, BigQuery caches the file list and file statistics and refreshes them in the background (AUTOMATIC) or when you call BQ.REFRESH_EXTERNAL_METADATA_CACHE (MANUAL). Queries then plan faster and can skip files using the cached statistics. The trade-off is that new files show up only after the next refresh, up to max_staleness later. Plain external tables can’t cache metadata.

Upgrading an external table

An existing external table can be turned into a BigLake table by adding a connection. Nothing about the files changes.

Iceberg managed tables: BigQuery writes Iceberg

An Iceberg managed table is a table BigQuery owns and writes, but its data is Parquet files with Iceberg metadata in your bucket:

CREATE TABLE mydataset.orders_iceberg (
  order_id STRING,
  ordered_at TIMESTAMP,
  amount NUMERIC
)
CLUSTER BY order_id
WITH CONNECTION `myproject.us.lake-connection`
OPTIONS (
  file_format = 'PARQUET',
  table_format = 'ICEBERG',
  storage_uri = 'gs://my-lake/orders_iceberg'
);

You can INSERT, UPDATE, DELETE and MERGE, stream into it, use transactions, and BigQuery compacts, clusters and garbage-collects the files in the background. Spark and other Iceberg engines read the same files.

The bucket belongs to BigQuery now

This is the part that surprises people. BigQuery manages the files under the table’s storage_uri:

Write only through BigQuery, give each table its own empty prefix, and let other engines read, not write.

What Iceberg managed tables can’t do

• No rename, no copy, no clone and no snapshot.
• No BIGNUMERIC, INTERVAL, JSON, RANGE or GEOGRAPHY columns, no collations and no default values.
• No materialized views over them.
• The console and the API show their storage size as 0 bytes.

Cost

• Storage is billed by Cloud Storage, not BigQuery, for all files including old versions kept for time travel.
• Background optimization (compaction, clustering, garbage collection) is billed in Data Compute Units (DCUs) per second.
• Queries are billed like other BigQuery queries, per TiB or per slot.
• Loads and exports use Enterprise edition pay-as-you-go slots, unlike native tables, where batch loads are free.

Iceberg tables written by other engines

For Iceberg tables that Spark, Flink or another system writes, BigQuery reads them as Iceberg external tables (from a metadata file) or through a catalog. You can query them, including past snapshots with FOR SYSTEM_TIME AS OF, but not change them. Reading tables with merge-on-read deletes is billed for the data files plus the delete files, and for deleted rows still present in the data files, so frequent small deletes in the writer make BigQuery reads more expensive until the writer compacts.

Which to choose