Skip to main content

BigQuery and Apache Iceberg

dbt supports materializing models in the Iceberg table format in two ways:

  • Simplest: The model config table_format = 'iceberg' instructs dbt to materialize this model as an Iceberg table in BigLake Metastore (managed catalog)
  • Extensible: Define an Iceberg catalog in catalogs.yml and configure this model with catalog_name

Creating Iceberg tables​

dbt supports creating Iceberg tables for two of the BigQuery materializations:

Iceberg catalogs​

BigQuery supports Iceberg tables through its built-in catalog BigLake Metastore today. No setup is needed to access the BigLake Metastore. However, you need to have a storage bucket and the required BigQuery roles configured prior to creating an Iceberg table.

BigQuery-specific configs for biglake_metastore catalogs​

The following table outlines the configuration fields required to set up a catalog integration for BigLake Iceberg tables in BigQuery.

Supply and nest these additional configurations, unique to BigQuery, under config.bigquery (in the new catalog spec) or adapter_properties (in the old catalog spec).

FieldTypeRequiredDescriptionNote
file_formatStringYesThe file format for the Iceberg table.parquet is the only accepted value.
external_volumeStringYesThe Cloud Storage bucket where Iceberg table data is written.For example, gs://BUCKET_NAME.
base_location_rootStringNoIf provided, the input overrides the default dbt base_location value of _dbt.Can be set in catalogs.yml.
base_location_subpathStringNoAn optional suffix to add to the base_location path that dbt automatically specifies.Only configurable per-model.
storage_uriStringNoIf provided, the input overrides the dbt storage_uri value.Only configurable per-model.
Loading table...
  • base_location_root: Specifies the prefix of the base location path within the storage bucket where Iceberg table data is written.
  • base_location_subpath: Specifies the suffix of the base location path within the storage bucket where Iceberg table data is written. This property can only be set in model configurations, not in catalogs.yml.
  • storage_uri: Completely overrides the storage_uri, allowing you to specify the full path directly instead of using the catalog integration's external volume and base_location components.

Example​

  1. Create a catalogs.yml at the top level of your dbt project.
catalogs.yml
catalogs:
  - name: my_biglake_catalog
    type: biglake_metastore
    table_format: iceberg
    config:
      bigquery:
        external_volume: 'gs://mydbtbucket'
        file_format: parquet

  1. Apply the catalog configuration at either the model, folder, or project level:
iceberg_model.sql

{{
    config(
        materialized='table',
        catalog_name='my_biglake_catalog'

    )
}}

select * from {{ ref('jaffle_shop_customers') }}

  1. Finally, run the model: dbt run -s my_iceberg_model.

Limitations​

BigQuery today doesn't support connecting to external Iceberg catalogs. In terms of SQL operations and table management features, refer to the BigQuery docs for more information.

Base location​

BigQuery's DDL for creating Iceberg tables requires that a fully qualified storage_uri be provided, including the object path. Once the user has provided the bucket name as the external_volume in the catalog integration, dbt manages the storage_uri input. The default behavior in dbt is to provide an object path, referred to in dbt as the base_location, in the form: _dbt/{SCHEMA_NAME}/{MODEL_NAME}. We recommend using the default behavior, but if you need to customize the resulting base_location, you can configure base_location with the model configuration fields base_location_root and base_location_subpath.

  • If no inputs are provided, dbt outputs for base_location {{ external_volume }}/_dbt/{{ schema }}/{{ model_name }}
  • If base_location_root = foo, dbt outputs {{ external_volume }}/foo/{{ schema }}/{{ model_name }}
  • If base_location_subpath = bar, dbt outputs {{ external_volume }}/_dbt/{{ schema }}/{{ model_name }}/bar
  • If base_location_root = foo and base_location_subpath = bar, dbt outputs {{ external_volume }}/foo/{{ schema }}/{{ model_name }}/bar
note

While you can customize paths with base_location_root and base_location_subpath, we don't recommend relying on them for environment isolation (such as separating development and production environments). Anyone with repository access can easily modify these configuration values. For true environment isolation, use separate external_volume values with infrastructure-level access controls.

You can also completely override the storage_uri with the model configuration field storage_uri. This overrides both the catalog integration path and the other model configuration fields to supply the entire storage_uri path directly.

An example model with a customized base_location:

iceberg_model.sql

{{
    config(
        materialized='table',
        catalog_name='my_bigquery_iceberg_catalog',
        base_location_root='foo',
        base_location_subpath='bar',

    )
}}

select * from {{ ref('jaffle_shop_customers') }}
catalogs.yml
catalogs:
  - name: my_bigquery_iceberg_catalog
    type: biglake_metastore
    table_format: iceberg
    config:
      bigquery:
        external_volume: 'gs://mydbtbucket'
        file_format: parquet
        base_location_root: foo

Rationale​

By default, dbt manages the full storage_uri on behalf of users for ease of use. The base_location parameter specifies the location within the storage bucket where the data is written. Without guardrails (for example, if the user forgets to provide a base location root), it's possible for BigQuery to reuse the same path across multiple tables.

This behavior could result in future technical debt because it limits the ability to:

  • Navigate the underlying object store
  • Read Iceberg tables through an object-store integration
  • Grant schema-specific access to tables through object store
  • Use a crawler pointed at the tables within the external storage to build a new catalog with another tool

To maintain best practices, dbt enforces an input and, by default, writes your tables within a _dbt/{SCHEMA_NAME}/{TABLE_NAME} prefix to ensure easier object-store observability and auditability.

Was this page helpful?

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

0
Loading