BQBrains | BigQuery Managed Iceberg Tables: A Hands-On Guide

Apache Iceberg stopped being a "maybe later" item for Google Cloud data teams the moment BigQuery started writing Iceberg itself. There are now three distinct things in BigQuery with "Iceberg" in the name, and choosing the wrong one is the most common mistake we see on client projects:

	Who writes it	Where metadata lives	DML support
BigQuery tables for Apache Iceberg (managed)	BigQuery	BigQuery, with Iceberg metadata exported to Cloud Storage	Full `INSERT`/`UPDATE`/`DELETE`/`MERGE`, streaming
External Iceberg tables (BigLake)	Spark, Flink, Dataproc, another engine	An external catalog or a metadata JSON file in GCS	Read-only from BigQuery
Plain BigQuery (native) tables	BigQuery	BigQuery, private format	Full DML

Managed Iceberg tables are the interesting new option: you get BigQuery's writer, storage optimization and streaming ingestion, but the bytes sit in your Cloud Storage bucket in open Parquet with Iceberg metadata that Spark, Trino, Snowflake or DuckDB can read. This tutorial builds one end to end.

Prerequisites

A Google Cloud project with the BigQuery and BigQuery Connection APIs enabled
A Cloud Storage bucket in the same region as your BigQuery dataset (the examples use US and gs://acme-lakehouse)
A Cloud Resource connection whose service account has Storage Object Admin on that bucket

-- Create the connection in BigQuery Studio (Add data -> Connections) or with bq:
-- bq mk --connection --location=US --connection_type=CLOUD_RESOURCE lakehouse
-- then grant roles/storage.objectAdmin to the connection's service account.

Copy the service account address from the connection details page — BigQuery will not warn you clearly if the grant is missing; you will just get a permission error on first write.

Step 1: Create a managed Iceberg table

CREATE SCHEMA IF NOT EXISTS `acme.lakehouse` OPTIONS (location = 'US');

CREATE OR REPLACE TABLE `acme.lakehouse.orders` (
  order_id      INT64,
  customer_id   INT64,
  order_ts      TIMESTAMP,
  status        STRING,
  total_amount  NUMERIC
)
CLUSTER BY customer_id
WITH CONNECTION `us.lakehouse`
OPTIONS (
  file_format = 'PARQUET',
  table_format = 'ICEBERG',
  storage_uri = 'gs://acme-lakehouse/orders'
);

Three details matter:

file_format and table_format must be PARQUET and ICEBERG; they are the switch that makes this a managed Iceberg table rather than a native one.
storage_uri must point at an empty prefix that no other table uses. BigQuery owns everything under it — do not hand-edit files there.
CLUSTER BY is how you express partitioning intent. Managed Iceberg tables do not accept the classic PARTITION BY clause on ingestion time; clustering plus BigQuery's automatic storage optimization does the equivalent work, and Iceberg's hidden partitioning is written into the exported metadata.

Step 2: Load and mutate it with ordinary SQL

This is the payoff — a lakehouse table you can treat exactly like a warehouse table.

INSERT INTO `acme.lakehouse.orders`
SELECT
  order_id,
  customer_id,
  order_ts,
  status,
  total_amount
FROM `acme.staging.orders_raw`
WHERE order_ts >= TIMESTAMP('2026-01-01');

-- Row-level correction
UPDATE `acme.lakehouse.orders`
SET status = 'cancelled'
WHERE order_id = 88213;

-- Upsert from a CDC feed
MERGE `acme.lakehouse.orders` T
USING `acme.staging.orders_cdc` S
ON T.order_id = S.order_id
WHEN MATCHED AND S.op = 'D' THEN DELETE
WHEN MATCHED THEN UPDATE SET
  status = S.status, total_amount = S.total_amount, order_ts = S.order_ts
WHEN NOT MATCHED AND S.op != 'D' THEN
  INSERT (order_id, customer_id, order_ts, status, total_amount)
  VALUES (S.order_id, S.customer_id, S.order_ts, S.status, S.total_amount);

Streaming works too: the Storage Write API and LOAD DATA both target managed Iceberg tables, so a Datastream or Pub/Sub pipeline can land rows continuously while Spark readers keep seeing consistent snapshots.

LOAD DATA is the cheapest way to bulk-append existing files:

LOAD DATA INTO `acme.lakehouse.orders`
FROM FILES (
  format = 'PARQUET',
  uris = ['gs://acme-landing/orders/2026/02/*.parquet']
);

Step 3: Make it readable from Spark, Trino, and friends

BigQuery keeps the authoritative metadata internally and periodically publishes an Iceberg metadata snapshot to storage_uri. External engines read that snapshot, so a reader can lag a live writer by a few minutes. When you need a reader to see the very latest state, force a publish:

EXPORT TABLE METADATA FROM `acme.lakehouse.orders`;

Then point Spark at the metadata file, or — better — register the dataset in BigLake Metastore, which exposes an Iceberg REST catalog endpoint so engines can discover tables by name instead of by path:

spark = (
    SparkSession.builder.appName("iceberg-read")
    .config("spark.sql.catalog.bq", "org.apache.iceberg.spark.SparkCatalog")
    .config("spark.sql.catalog.bq.type", "rest")
    .config("spark.sql.catalog.bq.uri",
            "https://biglake.googleapis.com/iceberg/v1beta/restcatalog")
    .config("spark.sql.catalog.bq.warehouse", "gs://acme-lakehouse")
    .getOrCreate()
)

spark.sql("SELECT status, COUNT(*) FROM bq.lakehouse.orders GROUP BY status").show()

Give the Spark service account BigQuery Data Viewer on the dataset and Storage Object Viewer on the bucket. Readers must stay read-only: an external engine writing into a BigQuery-managed prefix will corrupt the table's state.

Step 4: Keep an eye on storage and file layout

Managed Iceberg tables are storage-optimized automatically — BigQuery compacts small files, clusters data and garbage-collects old snapshots in the background, and you are billed for the underlying Cloud Storage plus the optimization compute. Two habits pay off:

-- Storage footprint and file counts
SELECT table_name, total_rows, total_logical_bytes, total_physical_bytes
FROM `acme.lakehouse`.INFORMATION_SCHEMA.TABLE_STORAGE
WHERE table_name = 'orders';

-- Confirm the table really is Iceberg-managed
SELECT table_name, option_name, option_value
FROM `acme.lakehouse`.INFORMATION_SCHEMA.TABLE_OPTIONS
WHERE table_name = 'orders'
  AND option_name IN ('table_format', 'file_format', 'storage_uri');

Time travel behaves like a native table (FOR SYSTEM_TIME AS OF) within the dataset's travel window, and undelete is available for accidental drops — but the drop also removes the data under storage_uri, so treat DROP TABLE on a lakehouse table with the same care as rm -rf on a bucket.

Step 5: Migrating an existing table

Two clean paths:

From a native BigQuery table — create the Iceberg table with the same schema and CREATE TABLE ... AS SELECT, or INSERT ... SELECT in date-ranged batches for large tables so each job stays under job limits:

CREATE TABLE `acme.lakehouse.events`
CLUSTER BY user_id
WITH CONNECTION `us.lakehouse`
OPTIONS (file_format = 'PARQUET', table_format = 'ICEBERG',
         storage_uri = 'gs://acme-lakehouse/events')
AS SELECT * FROM `acme.warehouse.events` WHERE event_date >= '2026-01-01';

From an existing Iceberg table written by Spark — either keep it external and read-only in BigQuery (CREATE EXTERNAL TABLE ... OPTIONS (format='ICEBERG', uris=['gs://.../metadata/v3.metadata.json'])), or take ownership by copying the data into a managed table and repointing writers. You cannot have two writers; pick one engine as the owner of each table.

When not to use managed Iceberg

Only BigQuery reads and writes the data. Native tables are simpler, slightly cheaper to query and support the full PARTITION BY surface. Openness has a cost; do not pay it for nothing.
Sub-second dashboard latency on small tables. BI Engine and native storage still win.
An external engine must write. Then the table belongs to that engine, and BigQuery should read it as an external Iceberg table via BigLake Metastore.
Features that lag native tables. Check the current documentation before assuming a specific feature (certain DDL options, some ML integrations) is available; the gap has narrowed but is not zero.

A sensible target architecture

For most mid-market estates we end up with: raw landings in Cloud Storage, curated and conformed layers as managed Iceberg tables so Spark/ML workloads read them without exports, and hot serving marts as native BigQuery tables feeding BI Engine and Looker. One writer per table, BigLake Metastore as the catalog, and lifecycle policies on the bucket that never touch BigQuery-owned prefixes.

If you are planning a lakehouse migration and want the file-layout, cost and catalog decisions made by people who have done it before, our BigQuery Lakehouse & Apache Iceberg service and data architecture consulting cover exactly this. Get in touch with your current table sizes and reader engines and we will sketch the target state.