dbt Iceberg Version Guessing Error - Investigation Report

Date: December 7, 2025
Status: ✅ FIXED - Implemented Option 1 (unsafe_enable_version_guessing)

Fix Applied

Added SET unsafe_enable_version_guessing = true; to bronze model pre-hooks in dbt_project.yml.

This is safe in our environment because:

• Dagster controls all writes (no concurrent external writers)
• Writes are sequential (one sync at a time per partition)
• dbt runs AFTER bronze sync completes (via sensors)

See detailed research: debt/bug-report-analysis/iceberg-version-handling-research-2025-12-07.md

---

Original Status: 🔴 Critical Issue - All Bronze dbt Models Failing

Executive Summary

All 5 bronze layer dbt models are failing with the same error when attempting to read from Iceberg tables. The error occurs because DuckDB’s iceberg_scan() function requires either a version parameter or unsafe_enable_version_guessing to be enabled, but neither is currently configured.

Impact: ⚠️ CRITICAL - Blocks entire dbt transformation pipeline (bronze → silver → gold)

Error Details

Error Message

Runtime Error in model raw_products (models/bronze/raw_products.sql)
Failed to read iceberg table. No version was provided and no version-hint could be found,
globbing the filesystem to locate the latest version is disabled by default as this is
considered unsafe and could result in reading uncommitted data. To enable this use
'SET unsafe_enable_version_guessing = true;'

Affected Models

All 5 bronze layer models are failing:

1. raw_cross_references - ERROR in 0.26s
2. raw_customers - ERROR in 0.21s
3. raw_products - ERROR in 0.22s
4. raw_ship_to_links - ERROR in 0.21s
5. raw_shipping_addresses - ERROR in 0.22s

Error Pattern

Completed with 5 errors, 0 partial successes, and 0 warnings:
Failure in model raw_<table> (models/bronze/raw_<table>.sql)
  Runtime Error in model raw_<table> (models/bronze/raw_<table>.sql)
  Failed to read iceberg table. No version was provided and no version-hint could be found...

Root Cause Analysis

Current Configuration

1. DuckDB Settings (profiles.yml):

settings:
  unsafe_enable_version_guessing: false # ← Disabled for security/stability

2. iceberg_source Macro (macros/iceberg_source.sql):

iceberg_scan(
  's3://{{ env_var("R2_DATA_CATALOG_BUCKET") }}/{{ dataset }}/{{ table_name }}',
  allow_moved_paths=true
  -- ← No version parameter specified
)

3. Pre-hooks (dbt_project.yml):

bronze:
  +pre-hook:
    - 'INSTALL httpfs; LOAD httpfs;'
    - 'INSTALL iceberg; LOAD iceberg;'
  # ← No SET unsafe_enable_version_guessing = true

The Problem

DuckDB’s iceberg_scan() function has two modes:

1. Explicit version mode: Specify a version number/snapshot ID
2. Version guessing mode: Let DuckDB automatically find the latest version (requires unsafe_enable_version_guessing = true)

Current state:

• ❌ No version parameter in iceberg_scan() calls
• ❌ unsafe_enable_version_guessing is false in profiles.yml
• ❌ No pre-hook to enable version guessing for the session

Result: DuckDB refuses to read the Iceberg tables because it can’t determine which version to use.

Why This Happened

The error message indicates this is a safety feature in DuckDB:

• Version guessing is disabled by default to prevent reading uncommitted data
• This is a recent change in DuckDB Iceberg extension behavior
• The codebase was likely working before, but a DuckDB update changed the default behavior

Impact Assessment

Immediate Impact

• ✅ Bronze Python assets: Working (write to Iceberg)
• ❌ Bronze dbt models: All failing (can’t read from Iceberg)
• ❌ Silver dbt models: Skipped (depend on bronze)
• ❌ Gold dbt models: Skipped (depend on silver)
• ❌ Typesense syncs: Not triggered (no gold assets materialized)

Pipeline Status

Bronze Python Assets → ✅ Working
         ↓
Bronze dbt Models → ❌ FAILING (5 errors)
         ↓
Silver dbt Models → ⏭️ SKIPPED (depends on bronze)
         ↓
Gold dbt Models → ⏭️ SKIPPED (depends on silver)
         ↓
Typesense Assets → ⏭️ NOT TRIGGERED (no gold materializations)

Solution Options

Option 1: Enable Version Guessing via Pre-hook (RECOMMENDED)

Approach: Add a pre-hook to bronze models to enable version guessing for the session.

Pros:

• ✅ Minimal code changes
• ✅ Only affects bronze models (where needed)
• ✅ Keeps profiles.yml secure (global setting stays false)
• ✅ Session-scoped (doesn’t affect other connections)

Cons:

• ⚠️ Enables “unsafe” behavior (but safe in our controlled environment)

Implementation:

dbt_project.yml

models:
  erp_pipeline:
    bronze:
      +materialized: view
      +tags:
        - bronze
      +pre-hook:
        - 'INSTALL httpfs; LOAD httpfs;'
        - 'INSTALL iceberg; LOAD iceberg;'
        - 'SET unsafe_enable_version_guessing = true;' # ← ADD THIS

Option 2: Enable Version Guessing in profiles.yml

Approach: Change unsafe_enable_version_guessing: false to true in profiles.yml.

Pros:

• ✅ Simple one-line change
• ✅ Applies to all models automatically

Cons:

• ⚠️ Global setting (affects all DuckDB connections)
• ⚠️ Less granular control
• ⚠️ Comment in profiles.yml says “Only enable for controlled testing”

Implementation:

profiles.yml

settings:
  unsafe_enable_version_guessing: true # ← Change from false

Option 3: Specify Explicit Version in iceberg_scan() Calls (PREFERRED IF FEASIBLE)

Approach: Modify iceberg_source macro to query metadata and pass explicit snapshot ID to iceberg_scan().

Pros:

• ✅ Most explicit and safe
• ✅ No “unsafe” flags needed
• ✅ Deterministic (always uses specific snapshot)
• ✅ Better for reproducibility

Cons:

• ❌ Requires version discovery logic (complex)
• ❌ Need to query Iceberg metadata JSON to find latest snapshot ID
• ❌ More code changes
• ❌ Performance overhead (metadata lookup before each scan)
• ❌ Requires DuckDB to read JSON from S3 (or Python helper)

Implementation Complexity:

Step 1: Create macro to get latest snapshot ID

-- macros/get_iceberg_snapshot.sql
{% macro get_iceberg_snapshot(table_name, dataset='erp_data_native_v2') %}
  {#
    Query metadata to get latest snapshot ID.
    This requires:
    1. Listing S3 objects to find latest metadata.json
    2. Reading the JSON file
    3. Extracting current_snapshot_id
  #}
  {%- set bucket = env_var("R2_DATA_CATALOG_BUCKET", "erp-data-catalog") -%}
  {%- set metadata_path = f"{dataset}/{table_name}/metadata/" -%}

  {# Use DuckDB to read metadata JSON from S3 #}
  WITH metadata_files AS (
    SELECT name
    FROM glob('s3://{{ bucket }}/{{ metadata_path }}*.metadata.json')
    ORDER BY name DESC
    LIMIT 1
  ),
  metadata_json AS (
    SELECT * FROM read_json_auto(
      (SELECT name FROM metadata_files),
      format='newline_delimited'
    )
  )
  SELECT current_snapshot_id
  FROM metadata_json
{% endmacro %}

Step 2: Update iceberg_source macro

{% macro iceberg_source(table_name, dataset='erp_data_native_v2') %}
  {%- set connection_id = var('connection_id', none) -%}
  {%- set snapshot_id = get_iceberg_snapshot(table_name, dataset) -%}

  {%- if connection_id -%}
    (
      SELECT * FROM iceberg_scan(
        's3://{{ env_var("R2_DATA_CATALOG_BUCKET", "erp-data-catalog") }}/{{ dataset }}/{{ table_name }}',
        snapshot_id={{ snapshot_id }},  # ← Explicit version
        allow_moved_paths=true
      )
      WHERE connection_id = '{{ connection_id }}'
    )
  {%- else -%}
    iceberg_scan(
      's3://{{ env_var("R2_DATA_CATALOG_BUCKET", "erp-data-catalog") }}/{{ dataset }}/{{ table_name }}',
      snapshot_id={{ snapshot_id }},  # ← Explicit version
      allow_moved_paths=true
    )
  {%- endif -%}
{% endmacro %}

Challenges:

1. DuckDB JSON reading: Need to verify DuckDB can read JSON from S3 paths
2. Metadata structure: Need to understand Iceberg metadata JSON structure to extract current_snapshot_id
3. Performance: Adds a metadata lookup query before each iceberg_scan() call
4. Error handling: What if metadata file doesn’t exist? What if snapshot_id is null?

Alternative: Pass version from Dagster

• Dagster could query metadata and pass snapshot_id as dbt variable
• Requires coupling between Dagster and dbt
• More complex but avoids dbt-side metadata queries

Key Question: Does DuckDB iceberg_scan() actually support a snapshot_id parameter?

Research Needed:

1. Check DuckDB Iceberg extension documentation for iceberg_scan() signature
2. Verify if parameter is snapshot_id, version, or something else
3. Test if passing explicit snapshot ID works

If DuckDB doesn’t support snapshot_id parameter:

• Option 3 is not feasible
• Must use Option 1 (unsafe flag) or Option 2 (global setting)
• This is likely why the unsafe flag exists - as a workaround when explicit versions aren’t supported

Investigation: Can We Use Explicit Versions?

Critical Question: Does DuckDB’s iceberg_scan() function support a snapshot_id or version parameter?

Action Required:

1. Check DuckDB Iceberg extension documentation
2. Test iceberg_scan() function signature
3. Verify if we can pass explicit snapshot ID

If DuckDB supports explicit snapshot IDs:

• ✅ Use Option 3 (explicit versions) - Best practice, no unsafe flags
• Implementation: Query metadata to get snapshot ID, pass to iceberg_scan()

If DuckDB does NOT support explicit snapshot IDs:

• ⚠️ Use Option 1 (unsafe flag) - Only viable option
• The unsafe flag exists specifically for this case

Recommended Solution

First: Investigate DuckDB API, then decide between Option 3 (if supported) or Option 1 (if not)

Decision Matrix

Factor	Option 1 (Unsafe Flag)	Option 3 (Explicit Versions)
Safety	⚠️ Uses “unsafe” flag	✅ Explicit, deterministic
Complexity	✅ Simple (1 line)	❌ Complex (metadata queries)
Performance	✅ No overhead	⚠️ Metadata lookup overhead
Maintainability	✅ Easy	⚠️ More code to maintain
Reproducibility	⚠️ Uses latest (may change)	✅ Uses specific snapshot
Implementation Time	✅ 5 minutes	❌ 1-2 hours

Recommendation

Short-term (Immediate fix): Use Option 1 (pre-hook) to unblock the pipeline quickly.

Long-term (Best practice): Implement Option 3 (explicit versions) for better safety and reproducibility.

Rationale for Option 1 (Short-term):

1. Controlled environment: We control the data source (R2), so version guessing is safe
2. Minimal changes: Only affects bronze models where needed
3. Security: Keeps global setting disabled, only enables for specific models
4. Maintainability: Clear and explicit in dbt_project.yml
5. Quick fix: Unblocks pipeline immediately

Rationale for Option 3 (Long-term):

1. Explicit is better than implicit: Always know which snapshot you’re reading
2. Reproducibility: Same snapshot ID = same data, even if new writes happen
3. No “unsafe” flags: Follows best practices
4. Better for debugging: Can reference specific snapshot in logs

Implementation Plan

Step 0: Investigate DuckDB iceberg_scan() API (IF USING OPTION 3)

Before implementing Option 3, verify DuckDB supports explicit snapshot IDs:

-- Test in DuckDB console
-- Check function signature
DESCRIBE iceberg_scan;

-- Or try calling with snapshot_id parameter
SELECT * FROM iceberg_scan(
  's3://bucket/path/table',
  snapshot_id=123456789  -- Test if this parameter exists
);

If snapshot_id parameter is NOT supported:

• DuckDB iceberg_scan() may only support version guessing
• Must use Option 1 (unsafe flag) - this is the intended workaround
• Proceed with Step 1 below

If snapshot_id parameter IS supported:

• Implement Option 3 (explicit versions)
• Create macro to query metadata and extract snapshot ID
• Update iceberg_source macro to use explicit snapshot_id

Step 1: Update dbt_project.yml (Option 1 - Quick Fix)

Add the pre-hook to enable version guessing:

models:
  erp_pipeline:
    bronze:
      +materialized: view
      +tags:
        - bronze
      +pre-hook:
        - 'INSTALL httpfs; LOAD httpfs;'
        - 'INSTALL iceberg; LOAD iceberg;'
        - 'SET unsafe_enable_version_guessing = true;' # ← ADD THIS LINE

Step 2: Test

1. Run dbt build for one bronze model:

   cd apps/dagster/dbt
   uv run dbt build --select raw_products --vars '{"connection_id": "996523e0-aee0-452e-8ac7-35a44f2e8812"}'

2. Verify it succeeds without errors

3. Run full bronze layer:

   uv run dbt build --select tag:bronze --vars '{"connection_id": "996523e0-aee0-452e-8ac7-35a44f2e8812"}'

4. Verify all 5 bronze models succeed

Step 3: Verify Pipeline Flow

1. Trigger bronze sync → Should succeed
2. Verify dbt transform runs → Should succeed (bronze models work)
3. Verify silver/gold models run → Should succeed (depends on bronze)
4. Verify Typesense sync triggers → Should trigger (gold assets materialized)

Alternative: Why Not Use Explicit Versions?

While specifying explicit versions would be the “safest” approach, it’s impractical because:

1. Version discovery complexity: Would need to query Iceberg metadata files to find latest version
2. Performance overhead: Extra S3 calls to discover version before each scan
3. Maintenance burden: More complex code, harder to maintain
4. Not necessary: In our controlled environment, version guessing is safe

Related Code Locations

Files Involved

1. apps/dagster/dbt/dbt_project.yml - Pre-hook configuration
2. apps/dagster/dbt/profiles.yml - DuckDB settings
3. apps/dagster/dbt/macros/iceberg_source.sql - Iceberg scan macro
4. apps/dagster/dbt/models/bronze/*.sql - Bronze layer models (all 5 affected)

Error Location

• Function: iceberg_scan() in DuckDB Iceberg extension
• Trigger: When DuckDB tries to read Iceberg table without version info
• Context: dbt models calling {{ iceberg_source('table_name') }}

Testing Checklist

After implementing the fix:

• raw_products model builds successfully
• raw_customers model builds successfully
• raw_cross_references model builds successfully
• raw_ship_to_links model builds successfully
• raw_shipping_addresses model builds successfully
• Silver layer models build (depends on bronze)
• Gold layer models build (depends on silver)
• Typesense sync triggers after gold materialization
• Full pipeline runs end-to-end

Logs Reference

Run ID: c4d8fd66-0131-40d3-8d9a-48c2e37d229a
Connection ID: 996523e0-aee0-452e-8ac7-35a44f2e8812
Timestamp: 2025-12-07 13:09:41 UTC
dbt Command: dbt build --vars {"connection_id": "996523e0-aee0-452e-8ac7-35a44f2e8812"} --select tag:bronze tag:silver tag:gold --exclude tag:snapshot

Error Count:

• 5 errors (all bronze models)
• 0 partial successes
• 0 warnings
• 35 skipped (silver/gold models skipped due to bronze failures)

---

Report Generated: 2025-12-07 13:15 UTC
Investigator: AI Assistant
Status: Ready for Fix Implementation