# 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.

Source: https://docs.querylab.io/bigquery/biglake-and-iceberg/

Updated: 2026-10-06

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](https://docs.querylab.io/bigquery/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: files via 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:

```sql
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](https://docs.querylab.io/bigquery/row-level-security/) and [column-level security](https://docs.querylab.io/bigquery/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

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

```sql
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](https://docs.querylab.io/bigquery/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`:

| If you                                                    | Then                                                                          |
| --------------------------------------------------------- | ----------------------------------------------------------------------------- |
| add files to that prefix outside BigQuery                 | they aren't part of the table, and background garbage collection deletes them |
| create the table on a prefix that already has files       | those files are treated as untracked and deleted                              |
| change or replace a data file                             | the table fails its consistency check and becomes unreadable                  |
| create two Iceberg managed tables on overlapping prefixes | each table's garbage collection deletes the other's files                     |
| drop the table                                            | its data files are not deleted; clean up the prefix yourself                  |

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

| Situation                                                      | Use                                                                   |
| -------------------------------------------------------------- | --------------------------------------------------------------------- |
| Data only BigQuery uses                                        | native BigQuery tables: cheapest to query, free to load, all features |
| BigQuery writes, Spark or other engines also read              | an Iceberg managed table                                              |
| Another engine writes Iceberg, BigQuery reads                  | an Iceberg external table or a catalog                                |
| Parquet files written by another system, queried with security | a BigLake table with metadata caching                                 |
| A quick look at some files                                     | a plain [external table](https://docs.querylab.io/bigquery/external-tables/)                  |

Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [Introduction to BigLake tables](https://cloud.google.com/bigquery/docs/biglake-intro)

- [Apache Iceberg managed tables](https://cloud.google.com/bigquery/docs/biglake-iceberg-tables-in-bigquery)

- [Apache Iceberg external tables](https://cloud.google.com/bigquery/docs/iceberg-external-tables)

- [Metadata caching for external tables](https://cloud.google.com/bigquery/docs/metadata-caching-external-tables)