Iceberg catalogs, revisited

[ajhlee-dbt]

Since our last discussion post over a year ago, we have seen expanding support for the Iceberg table format and the Iceberg REST Catalog from data warehouse vendors. dbt now has support for materializing Iceberg tables on three platforms (Snowflake, Databricks, and BigQuery) through the catalogs.yml spec we previously defined.

But although dbt supports reading and writing Iceberg to one platform at a time, it does not yet support reading Iceberg from one platform and writing to another. In the past year, we’ve seen efforts to add read and write support between warehouses using Iceberg, and we believe Snowflake’s catalog-linked databases are the start of support in dbt for cross-platform workflows.

Setting the Scene

Catalog-linked databases are Snowflake databases that sync metadata from external catalogs through the Iceberg REST API. This creates a bidirectional connection between Snowflake and an external catalog:

• Snowflake automatically discovers namespaces and Iceberg tables, and syncs them to the catalog-linked database.
• Snowflake supports writing to the external catalog through the catalog-linked database, by syncing the new data / metadata after the write completes.

We have extensively tested catalog-linked database performance at scale over the past few months: read the blog, and check out our work. We think there is a lot of promise in the construct of a catalog-linked database to drive cross-platform workflows in dbt, but in our research we have seen challenges with the current catalogs.yml specification:

• Although catalog-linked databases are a Snowflake feature, they are actually defined by the external remote catalog they are connected to.
• Schema and table names, for example, must follow the remote catalog’s name conventions, not Snowflake’s.
• The spec only has enough information to generate DDL and DML for Iceberg tables on one platform. dbt has no idea which catalog the database is linked to.

Looking back over the past year, as users have adopted catalogs.yml into their projects, we've noticed that the current specification is difficult to work with. Our current spec attempts to abstract over the idea of catalog integrations, but warehouse vendors have different ideas of what the term even means. As a result, configuring catalogs.yml feels unintuitive.

With external sources also on the potential future horizon for Iceberg support in dbt, we think that it’s time that catalogs.yml got an upgrade.

Our proposal: a new spec

What’s different and why?

From Colin’s post last year: “Broadly speaking, when we say “catalogs,” we are referring to services like Glue Data Catalog or anything that implements Iceberg REST. These services provide a warehouse-agnostic interface for managing data in object storage.”

• The notion of a catalog is still vague, and the lack of specificity makes configuring them unintuitive.
• We think that our old spec was too closely coupled with Snowflake’s idea of what a catalog (integration) is.

We had four goals in redesigning the catalog spec:

1. We believe that catalogs.yml should represent catalogs, rather than catalog integrations.
   1. Rather than thinking about the type of catalog integration (“built-in” vs. “Iceberg REST”), you should be able to specify your actual catalog type in catalogs.yml - glue | unity | polaris (strongly typed for the catalog providers we have tested and verified), and iceberg_rest for all the rest (Lakekeeper, Nessie, et al).
   2. The strongly-typed catalogs give dbt the information it needs to handle materialization behaviors that differ by catalog providers. As we learn about and test more catalog providers, we will “graduate” them from generic iceberg_rest to lakekeeper, nessie, etc.
2. The catalog spec should be consistent (abstracted) across adapters, to the extent possible. Meaning: I can switch which DWH I’m running against, without needing to significantly refactor catalogs.yml, because adapter-specific configurations are nested under <adapter_name> keys. (This is an early foray; we have aspirations of introducing adapter-specific configurations more broadly.)
3. Ease of setup. Thanks to the simplification in (1), we no longer believe that we need multiple “write” integrations (or dedicated “read” integrations, in the future) - so we’re pulling more catalog fields up to be top-level attributes.
4. Ease of migration. We are introducing this behind a behavior-change flag. If you already have catalogs.yml defined, don’t worry — dbt Core will continue supporting the current spec indefinitely. We are also planning to add support to dbt-autofix to make the old → new spec migration even easier.

Here is an example of the current spec:

# catalogs.yml - current spec

catalogs:
  - name: finance_db
    active_write_integration: finance_pub_models
    write_integrations:
      - name: finance_pub_models
        catalog_type: iceberg_rest
        table_format: iceberg
        adapter_properties:
          catalog_linked_database: snowflake_cld__finance_db
          ...

And here is the same example, rewritten with the new proposed spec:

# catalogs.yml - new proposed spec (w/ behavior-change flag enabled)

catalogs:
  - name: finance_db
    catalog_type: unity # that's it
    # table_format: iceberg - optional, iceberg is the default
    config:
      snowflake:
        catalog_database: snowflake_cld__finance_db # the name of the catalog-linked database
                                                    # this field's name is up for discussion -- see comment below 
        # set defaults for any other Snowflake-specific, Iceberg-specific configs
        external_volume: ...
        auto_refresh: ...
        ...
      databricks:
        ...

In the comments below you can find more information about our plans for the new spec, how it differs, from the old spec, and future work:

• How do catalogs enable cross-project, cross-platform workflows?
• What are the major changes in the old -> new spec migration?
• Next steps & open questions

[ajhlee-dbt]

How do catalogs enable cross-project, cross-platform workflows?

Let's imagine two "mesh" projects, jaffle_finance and jaffle_marketing, with a cross-project dependency jaffle_finance -> jaffle_marketing.

So long as the jaffle_finance project writes its public models to an Iceberg catalog that the jaffle_marketing project can read from, and both projects configure that Iceberg catalog in catalogs.yml by the same name — these projects can now run on different data platforms.

# jaffle_finance/dbt_project.yml
models:
  jaffle_finance:
    marts:
      access: public
      catalog_name: finance_db

# jaffle_finance/catalogs.yml
catalogs:
  - name: finance_db
    catalog_type: unity
    config:
      databricks:
        # where jaffle_finance project will write its public models to
        catalog_database: finance_db # optional - not required if matches catalog 'name'

# jaffle_marketing/catalogs.yml
catalogs:
  - name: finance_db
    catalog_type: unity
    config:
      snowflake:
        # catalog-linked database pointing to same Unity catalog
        # where jaffle_marketing project will read jaffle_finance public models from
        catalog_database: snowflake_cld__finance_db

# jaffle_marketing/dbt_project.yml
# Unfortunately, Snowflake CLD <> Unity catalog requires quoted lowercase schemas + table identifiers
quoting:
  schema: true
  identifier: true

What's going on here?

• The jaffle_finance project, running on Databricks, will materialize "mart" models into the finance_db database in Unity catalog.
• This is actually an Iceberg catalog, which we've made accessible to Snowflake via a catalog-linked database. In Snowflake, the catalog-linked database is named snowflake_cld__finance_db.
• When running the jaffle_marketing project, dbt sees a cross-project reference to a public model in the jaffle_finance project:

-- marketing/models/marts/roi_by_channel.sql
with monthly_revenue as (

    select * from {{ ref('jaffle_finance', 'monthly_revenue') }}

),

...

This will work for both ways of resolving cross-project references:

1. package dependencies (supported in dbt Core + dbt platform)
2. project dependencies (dbt platform Enterprise; the advantages of this approach are described here)

For package dependencies:

# dependencies.yml
packages:
  - git: https://github.com/dbt-labs/jaffle-shop-mesh-finance

The upstream package model jaffle_finance.monthly_revenue is configured with catalog_name: finance_db. dbt will use the currently running (root) project's catalogs.yml to resolve its three-part relation name. In this project (jaffle_marketing) + this platform (Snowflake), that catalog is configured with database: snowflake_cld__finance_db. Therefore, dbt will resolve the reference to:

For project dependencies:

# dependencies.yml
projects:
  - name: jaffle_finance

Previously, if we only used the upstream model's database config, then dbt would resolve this to finance_db.jaffle_finance.monthly_revenue — which doesn't exist in Snowflake.

Instead, dbt will now match up the catalog_name for the referenced model with the entries in catalogs.yml for the currently running (root) project. dbt will see that jaffle_finance.monthly_revenue has catalog_name: finance_db, which for this project (jaffle_marketing) + this platform (Snowflake) is configured with database: snowflake_cld__finance_db. Therefore, dbt will resolve the reference to:

-- marketing/target/compiled/models/marts/roi_by_channel.sql
with monthly_revenue as (

    select * from snowflake_cld__finance_db."jaffle_finance"."monthly_revenue"

),

...

And it just works!

Behind the scenes, Snowflake is syncing Databricks' finance_db ↔ Snowflake's snowflake_cld__finance_db, with eventual consistency. This means that finance_db.jaffle_finance.monthly_revenue in Databricks and snowflake_cld__finance_db."jaffle_finance"."monthly_revenue" in Snowflake are pointers to the exact same Iceberg table in the Databricks-managed Unity catalog.

Looking ahead…

To take this one step further: Because the adapter-specific configurations live within snowflake: and databricks: configs, we could set up the exact same catalogs.yml across both projects.

# jaffle_finance/catalogs.yml AND jaffle_marketing/catalogs.yml
catalogs:
  - name: finance_db
    catalog_type: unity
    config:
      databricks:
        database: finance_db # optional - not required if matches catalog 'name'
      snowflake:
        database: snowflake_cld__finance_db

Let's say that each project's sources are also available in a shared Iceberg catalog. Then we have complete flexibility over where to run our projects:

• jaffle_finance (Databricks) → jaffle_marketing (Snowflake)
• jaffle_finance (Snowflake) → jaffle_marketing (Databricks)
• Both projects on Databricks
• Both projects on Snowflake

Note: If the Unity catalog name (Databricks) is different from the catalog-linked database name (Snowflake), this would require conditional configuration for sources. This could be motivation for us to support catalog_name as a configuration on sources too (as mentioned in our last roadmap post):

sources:
  name: finance_source
  # today
  database: "{{ 'finance_db' if target.type == 'databricks' else 'snowflake_cld__finance_db' }}"
  # future ??
  catalog_name: finance_db

[ajhlee-dbt]

What are the major changes in the old → new spec migration?

We are removing “write integrations.”

This was a Snowflake-specific term that didn’t have much meaning for other catalogs. The original reasoning for this was to make it easier to isolate your staging and production environments, but in practice this made it more difficult to reason about what the configurations were doing.

You can get the same behavior by using a single env_var call in your top-level catalog config:

# current spec
catalogs:
  - catalog_name: my_first_catalog
    # define as many as you want, but you can only have one 'active' integration per invocation
    active_write_integration: prod_glue_write_integration # not required if you only have 1 integration
    write_integrations:  
      - integration_name: "{{ env_var('INTEGRATION_NAME') }}"
        catalog_type: glue
        external_volume:  "{{ env_var('EXT_VOLUME') }}"
        table_format: iceberg

# new spec
catalogs:
  - catalog_name: "{{ env_var('CATALOG_NAME') }}"
    catalog_type: glue
    config:
      snowflake:
        external_volume: "{{ env_var('EXT_VOLUME') }}"
        table_format: iceberg
        

table_format is now an optional attribute, which defaults to “iceberg.”

Related - today, dbt Core inconsistently supports setting table_format at the model-level, in addition to catalogs.yml. Going forward, we intend to support this with catalogs.yml exclusively. (If you have good reasons to continue materializing one-off Iceberg tables, outside of a real Iceberg catalog, we want to hear them!)

Configurations live under adapter-name keys (e.g. snowflake: or databricks:), rather than a catch-all adapter_properties.

You can set any warehouse-specific configs that relate to materializing Iceberg tables — such as write path, auto refresh, storage/retention policies, etc — and dbt will apply these configs by default, when materializing any models in this catalog (as configured by catalog_name), and running on this adapter.

These are just model configs. You can the catalog definition to set reasonable defaults, for all models that are being materialized to the catalog — but you can also override these configurations for specific models by setting the same-named config for that model in any of the usual places.

For example, we set a default base_location_root for all models in the finance_db catalog:

# catalogs.yml
catalogs:
  - name: finance_db
    catalog_type: unity
    config:
      snowflake:
        base_location_root: 's3://my-bucket/finance_db'

But then we override that config for one particular model:

-- models/finance/my_special_model.sql

{{ config(
    catalog_name = 'finance_db',
    base_location_root = 's3://my-bucket/somewhere_else'
}}

The inheritance order for these configurations is: model file (sql/py) > model (yaml) > dbt_project.yml > catalogs.yml

(Note: Some Iceberg-related configurations are only available at the model configuration level, so they cannot be set in catalogs.yml. For example, the related config base_location_subpath determines the exact write path for a single Iceberg table, so it only makes sense to configure per-model, rather than setting a default for all models in the catalog.)

❓Open question: the “database name” config

We are introducing a catalog-level, adapter-specific configuration that will determine the top-level namespace of models configured in this catalog. Our goal is to replace catalog_linked_database (for Snowflake), and the strong assumption (on Databricks) that your catalog/database’s name in the data warehouse must exactly match the logical name of your catalog within catalogs.yml.

This config could be named the same as the model-level database config because it does exactly the same thing — it tells dbt how to template the relation name (<database>.<schema>.<identifier>) when materializing the model. But there are some tricky implications for config inheritance that may warrant picking a different name for this config.

We think there are three options here:

1. This config is called database, and it works like any other model config.
   1. catalogs.yml sets a default value, which can then be overridden within individual models / within dbt_project.yml for models based on FQN
   2. Pro: Consistent with all other configs.
   3. Con: This will lead to weird & surprising cases where my project-level configuration or generate_database_name macro causes dbt to materialize my model as an Iceberg table, but in the wrong database. Now I need to go add "if/else" exceptions to those project-level configs / that macro.
2. This config is called database, and it works differently.
   1. The database config set in catalogs.yml always wins, taking precedence over the database config set in other places for models configured with this catalog_name.
   2. Pro: More-opinionated workflow that avoids the common failure case described above
   3. Con: Not consistent with any other config behavior in dbt. Only catalogs have this behavior, and only for the database config.
3. (Our recommendation) Give this config a different name, e.g. catalog_database
   1. You set catalog_database in catalogs.yml. If a model has both a database config and a catalog_name config, the catalog’s catalog_database takes precedence over the model’s database config, to tell dbt where to materialize this model.
   2. Pro: Inheritance behavior is consistent with all other configs.
   3. Con: Two differently named configs are responsible for doing the same thing; users need to remember how catalog_database + database interact.

[ajhlee-dbt]

Next steps & open questions

We are planning to implement this first in the dbt Fusion engine (across all adapters that support the current catalogs.yml spec), and then in dbt Core.

There is a significant remaining challenge: Iceberg catalog providers have different, stricter requirements for schema and identifier names: quoted, lowercase, alphanumeric characters only (no underscores!). This is painful — especially on Snowflake, which prefers uppercase & unquoted identifiers whenever possible. We believe this new spec will make it easier for us to handle some of this automatically, but this is an overhaul we still need to do in the future.

First - we want to hear your feedback!

• Does the new spec make sense to you? Does it feel more or less intuitive than the v1 spec?
• What do you think of the proposal around database config for catalogs?
• Should you be able to configure catalogs in all yaml files (like model groups) or a dedicated catalogs.yml file (as a parallel to profiles.yml)? dbt-core#11707
• Are you materializing your dbt models as Iceberg tables today? Inside a catalog, or not? (Repeating from above: If you have good reasons to continue materializing one-off Iceberg tables, outside of a real Iceberg catalog, we want to hear them!)
• If you’re using Snowflake + Iceberg together - Have you tried catalog-linked databases? Are you planning to adopt them? Are there any reasons you can’t?
• Do you have use cases for reading Iceberg tables from an external catalog as sources?

[jtcohen6]

@jecolvin Thanks for the quick reply - that all makes a lot of sense!

Let's try translating that into the new spec, because I hope/hope this should all still work. For the upstream Snowflake project:

# catalogs.yml
catalogs:
  - name: my_awesome_snowflake_managed_catalog
    type: horizon # what should we name this? see comment below
    snowflake:
      catalog_database: snowflake_database_name # if you don't set this, each individual model's 'database' config will then determine the top-level namespace
      # whatever snowflake-specific configs you want for materializing Iceberg tables in this catalog
      data_retention_time_in_days: 1

Then dbt will build models configured with catalog_name: my_awesome_snowflake_managed_catalog into snowflake_database_name.<schema_name>.<table_name>.

Let's imagine that you're using dbt in a downstream project too, with a Spark or Glue adapter. (Not to write back, but to read from / resolve cross-project references.) Assuming that you've already set up catalog federation with Glue, to create a catalog named my_snowflake_iceberg_catalog:

# catalogs.yml
catalogs:
  - name: my_awesome_snowflake_managed_catalog # this needs to match the 'name' configured in the upstream project
    type: horizon
    spark: # or 'glue'
      catalog_database: my_snowflake_iceberg_catalog # matches the 'physical' catalog name in Glue

Then dbt would resolve references to public models coming from the upstream project (as synced Iceberg tables) to my_snowflake_iceberg_catalog.<schema_name>.<table_name>.

---

What should be the catalog type for "Snowflake-managed Iceberg catalog"? (Helpfully, Snowflake calls this catalog = 'snowflake'.) IMO:

• catalog_type should represent the canonical "absolute" name of that catalog (horizon, unity, glue, etc) - in a way that provides enough information to inform dbt's DDL/DML templating on each adapter that can integrate with a catalog of that type
• If that means we need a distinction between snowflake_managed and horizon, or snowflake_open_catalog and polaris, because the DDL/DML needs are different — make it so. But ideally we pick one consistent & canonical name for each actually distinct catalog type.
• If we keep built_in, it should be for back-compat only

[jecolvin]

On initial readthrough this all looks good to me. horizon seems like a good name for Snowflake-managed vs. Open Catalog. My initial thinking is you'll probably want to use snowflake_open_catalog for the other, and save polaris for when it's more popular for people to host Polaris standalone. It could probably start off in the generic iceberg_rest group.

[VersusFacit]

ICYMI: We chatted with snowflake team members and based on those conversations (and this feedback) I've currently made v2 use horizon 🤝 trying that out for now

[tdoberneck]

Love everything about this post!

We've been running on catalog-linked databases for a while now, and we have found they are a much simpler setup than defining individual tables with create iceberg table statements. I agree that the new catalog.yml changes are much cleaner and make it easier to conceptualize what and how you are defining your catalog.

I would love to be able to define our REST catalog once in dbt and have multiple warehouses connect to it from there, with no per-project config drift and no manual reconciliation between them. If catalogs.yml becomes the single place that describes "here's the catalog, here's how each warehouse reaches it," that will be a huge win for some of the work we have planned. We are very invested in this!

[jussapaavo]

We're also exploring these cross-platform data shares via Iceberg catalogs. I have just setup dbt's BigQuery Iceberg catalog integration and have faced some limitations about external interoperability:

Tables created through dbt's catalog_name with BigQuery appear to be registered in BigQuery's internal catalog instead of Lakehouse runtime catalog (formerly BigLake Metastore?). As the public BigLake REST catalog API (https://biglake.googleapis.com/iceberg/v1/restcatalog) is provided via Lakehouse runtime catalog (and not for BigQuery's internal managed Iceberg tables), we cannot access these via REST catalog API.

This means that in Snowflake, we cannot setup REST catalog integration and as such cannot use CLD's. So instead, in Snowflake, we are forced to use the external volume based method and setup external tables that read the metadata file directly from GCS.

These same findings are also documented in this blog post: https://medium.astrafy.io/building-a-lakehouse-on-gcp-lessons-learned-with-dbt-iceberg-and-bigquery-5bb7d78e6a98

---

If you’re using Snowflake + Iceberg together - Have you tried catalog-linked databases? Are you planning to adopt them? Are there any reasons you can’t?

So answering this question, we would like to but can't. I don't know if the correct solution is to:

• For Google team to provide a way to link BigQuery-managed tables to Lakehouse runtime catalog (formerly BigLake Metastore) so these tables could be accessed via REST catalog?
• Or have feature in dbt's catalogs.yml that could define how tables are created in BigQuery so they could be listed in Lakehouse runtime catalog from the get-go?