Skip to main content

Snowflake 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 Snowflake Horizon (managed catalog), using Snowflake-managed storage
  • Extensible: Define an Iceberg catalog in catalogs.yml and configure this model with catalog_name

Creating Iceberg tables​

dbt supports creating Iceberg tables for three of the Snowflake materializations:

Iceberg catalogs​

Snowflake supports writing Iceberg tables to Snowflake Horizon (its managed catalog), and to external catalogs through catalog-linked databases. Those external catalogs include Polaris (self-hosted), Open Catalog (Snowflake's managed Polaris), AWS Glue, GCP BigLake, Databricks Unity, and (in theory) any other catalog that implements Iceberg REST compatibility.

Snowflake Horizon (Snowflake-managed)​

Simplest: Create a single Iceberg table​

models/MODEL_NAME.sql

{{
  config(
    materialized = "table",
    table_format="iceberg",
  )
}}

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

The following configurations are supported.

For more information, check out the Snowflake reference for CREATE ICEBERG TABLE (Snowflake as the catalog).

ParameterTypeRequiredDescriptionSample inputNote
table_formatStringYesConfigures the objects table format.icebergiceberg is the only accepted value.
external_volumeStringYes(*)Specifies the identifier (name) of the external volume where Snowflake writes the Iceberg table's metadata and data files.my_s3_bucket*You don't need to specify this if the account, database, or schema already has an associated external volume. More info
base_location_rootStringNoIf provided, the input overrides the default dbt base_location value of _dbt
base_location_subpathStringNoAn optional suffix to add to the base_location path that dbt automatically specifies.jaffle_marketing_folderWe recommend that you don't specify this. Modifying this parameter results in a new Iceberg table. See Base Location for more info.
iceberg_versionIntegerNoSpecifies the Iceberg format version for the table. Defaults to 2. Cannot be changed after table creation.3Set to 3 for improved VARIANT type support and better incremental/snapshot performance through deletion vectors.
Loading table...

Extensible: Configure horizon catalog​

First, configure a catalog with type: horizon in catalogs.yml:

catalogs.yml

catalogs:
  - name: my_horizon_catalog
    type: horizon
    table_format: iceberg  # optional - default
    config:
      snowflake:
        # optional - specify additional Snowflake-specific configurations
        change_tracking: true
        iceberg_version: 3  # available in v1.12+

Next, configure a dbt model with the name of your Horizon catalog.

models/my_iceberg_model.sql

{{
    config(
        materialized='table',
        catalog_name='my_horizon_catalog',
        iceberg_version=3,  # available in v1.12+
    )
}}

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

Finally, run the model: dbt run -s my_iceberg_model. Because dbt understands that type: horizon refers to Snowflake's managed catalog, dbt templates the appropriate Snowflake DDL/DML for creating and updating managed Iceberg tables.

External catalogs​

dbt can also template Snowflake DDL/DML for creating and updating Iceberg tables managed by external catalogs.

First, you need to set up a catalog integration and (recommended) catalog-linked database within Snowflake. See Snowflake docs for how to create a catalog integration and catalog-linked database.

Caveats:

  • For some external catalogs (for example, AWS Glue), table and column identifiers must use only alphanumeric characters (letters and numbers), be lowercase, and surrounded by double quotes.
  • Starting in dbt Core v1.11, dbt-snowflake supports basic table materialization on Iceberg tables registered in a Glue catalog through a catalog-linked database. Note that incremental materializations aren't yet supported.

After you create the external catalog integration, you can do two things:

  • Query an externally managed table: Snowflake can query Iceberg tables whose metadata lives in the external catalog. In this scenario, Snowflake is a "reader" of the external catalog. The table’s data remains in external cloud storage (AWS S3 or GCP Bucket) as defined in the catalog storage configuration. Snowflake uses the catalog integration to fetch metadata using the REST API. Snowflake then reads the data files from cloud storage.

  • Write tables to the external catalog, using Snowflake compute: You can materialize a dbt model as an Iceberg table using Snowflake's compute, and Snowflake registers and syncs that table to the external catalog (for example, AWS Glue or Databricks Unity). The dbt model appears in that catalog, and other query engines can read its data there.

Now, we can configure that external catalog in catalogs.yml. Here is an example for an AWS Glue catalog:

catalogs.yml

catalogs:
  - name: my_glue_catalog
    type: glue
    table_format: iceberg
    config:
      snowflake:
        catalog_database: catalog_linked_db_glue  # name of catalog-linked database in Snowflake

Snowflake-specific configs for Iceberg catalogs​

These are the additional configurations, specific to Snowflake, that can be supplied and nested under config.snowflake (in the new catalog spec) or adapter_properties (in the old catalog spec). Available configurations are different when writing dbt models as Snowflake-managed Iceberg tables (Snowflake Horizon catalog) versus writing to external catalogs.

Snowflake-managed (Horizon)​

FieldRequiredAccepted values
change_trackingOptionalTrue or False
data_retention_time_in_daysOptionalStandard Account: 1, Enterprise or higher: 0 to 90, default 1
max_data_extension_time_in_daysOptional0 to 90 with a default of 14
storage_serialization_policyOptionalCOMPATIBLE or OPTIMIZED
base_location_rootOptionalRelative path segment (for example, 'subpath1/subpath2')
base_location_subpathOptionalRelative path segment (for example, 'subpath1/subpath2'), only configurable per-model
Loading table...

External catalogs​

FieldRequiredAccepted values
auto_refreshOptionalTrue or False
catalog_linked_databaseRequired for catalog type: iceberg_restCatalog-linked database name
catalog_linked_database_typeOptionalCatalog-linked database type. For example, glue
max_data_extension_time_in_daysOptional0 to 90 (default: 14)
target_file_sizeOptionalValues like 'AUTO', '16MB', '32MB', '64MB', '128MB'. Case-insensitive
Loading table...
  • storage_serialization_policy: The serialization policy tells Snowflake what kind of encoding and compression to perform on the table data files. If not specified at table creation, the table inherits the value set at the schema, database, or account level. If the value isn’t specified at any level, the table uses the default value. You can’t change the value of this parameter after table creation.
  • max_data_extension_time_in_days: The maximum number of days Snowflake can extend the data retention period for tables to prevent streams on the tables from becoming stale. The MAX_DATA_EXTENSION_TIME_IN_DAYS parameter enables you to limit this automatic extension period to control storage costs for data retention, or for compliance reasons.
  • data_retention_time_in_days: For managed Iceberg tables, you can set a retention period for Snowflake Time Travel and undropping the table over the default account values. For tables that use an external catalog, Snowflake uses the value of the DATA_RETENTION_TIME_IN_DAYS parameter to set a retention period for Snowflake Time Travel and undropping the table. When the retention period expires, Snowflake doesn't delete the Iceberg metadata or snapshots from your external cloud storage.
  • change_tracking: Specifies whether to enable change tracking on the table.
  • catalog_linked_database: Catalog-linked databases (CLD) in Snowflake ensure that Snowflake can automatically sync metadata (including namespaces and Iceberg tables) from the external Iceberg catalog and registers them as remote tables in the catalog-linked database. The reason we require the usage of Catalog-linked databases for building Iceberg tables with external catalogs is that without it, dbt is unable to truly manage the table end-to-end. Snowflake doesn't support dropping the Iceberg table on non-CLDs in the external catalog; instead, it only allows unlinking the Snowflake table, which creates a discrepancy with how dbt expects to manage the materialized object.
  • auto_refresh: Specifies whether Snowflake should automatically poll the external Iceberg catalog for metadata updates. If REFRESH_INTERVAL_SECONDS isn’t set on the catalog integration, the default refresh interval is 30 seconds.
  • target_file_size: Specifies a target Parquet file size. Default is AUTO.
  • base_location_root: Specifies the prefix of the BASE_LOCATION, the write path for the Iceberg table.
  • base_location_subpath: Specifies the suffix of the BASE_LOCATION, the write path for the Iceberg table. This property can only be set in model configurations, not in catalogs.yml.

Base location​

Snowflake's CREATE ICEBERG TABLE DDL requires that a base_location be provided. dbt defines this parameter on the user's behalf to streamline usage and enforce basic isolation of table data within the EXTERNAL VOLUME. The default behavior in dbt is to provide a base_location string of 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 the 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

While you can customize paths with base_location_root and base_location_subpath, we don't recommend you rely on these for environment isolation (such as separating development and production environments). These configuration values can be easily modified by anyone with repository access. For true environment isolation, use separate EXTERNAL VOLUMEs with infrastructure-level access controls.

An example model with a customized base_location:

iceberg_model.sql

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

    )
}}

select * from {{ ref('jaffle_shop_customers') }}
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.

Rationale​

By default, dbt manages base_location on behalf of users to enforce best practices. With Snowflake-managed Iceberg format tables, the user owns and maintains the data storage of the tables in an external storage solution (the declared external volume). The base_location parameter declares where to write the data within the external volume. The Snowflake Iceberg catalog keeps track of your Iceberg table regardless of where the data lives within the external volume declared and the base_location provided. However, Snowflake permits passing anything into the base_location field, including an empty string, even reusing 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 (S3/Azure blob)
  • 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 volume 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.

Limitations​

  • When you use Iceberg tables with dbt, dbt materializes your query in Iceberg. However, dbt often creates intermediary objects as temporary and transient tables for certain materializations, such as incremental ones. You can't configure these temporary objects to be Iceberg-formatted. You may see non-Iceberg tables created in the logs to support specific materializations, but they are dropped after usage.

  • You can't incrementally update a pre-existing incremental model to be an Iceberg table. To do so, you must fully rebuild the table with the --full-refresh flag.

  • As of Snowflake change bundle 2025-01, the SHOW TABLES command doesn't include the is_iceberg column in its output. This forced dbt v1.9 to run a command similar to the following query for all the models in the dbt project (regardless of whether they're configured as iceberg models):

    select all_objects.*, is_iceberg
    from table(result_scan(last_query_id(-1))) all_objects
    left join INFORMATION_SCHEMA.tables as all_tables
    on all_tables.table_name = all_objects."name"
    and all_tables.table_schema = all_objects."schema_name"
    and all_tables.table_catalog = all_objects."database_name"

    This query may be relatively inefficient and potentially expensive, depending on the size of your Snowflake warehouse. Thus, the ability to run Iceberg models is gated behind the enable_iceberg_materializations flag.

Was this page helpful?

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

0
Loading