has_dbr_capability not evaluated properly with SQL warehouses

[nicor88]

Describe the bug

When bumping the adapter to dbt-databricks v1.11.5, I started to receive this error:

Compilation Error in model my_model (models/my_model.sql)
  macro dateadd not implemented for datepart ~ 'DAY' ~ on Spark
  
  > in macro spark__dateadd (macros/utils/dateadd.sql)
  > called by macro databricks__dateadd (macros/utils/dateadd.sql)
  > called by macro dateadd (macros/utils/dateadd.sql)
  > called by macro default__date_spine (macros/sql/date_spine.sql)
  > called by macro date_spine (macros/sql/date_spine.sql)

I started to get suspiscious, because the adatper was calling some Spark macros.

After further investigation, the culprit seems to be on the {%- if adapter.has_dbr_capability('timestampdiff') -%} was evaluated to false, and the adapter was falling back to spark__dateadd leading to this error.

Overriding the macro to

{% macro databricks__dateadd(datepart, interval, from_date_or_timestamp) %}
    timestampadd({{datepart}}, {{interval}}, {{from_date_or_timestamp}})
{%- endmacro %}

fix the issue.

I'm using a SQL serverless Warehouse and I believe that there is a bug on how the has_dbr_capability is evaluated.

Steps To Reproduce

Create a model that uses

with date_spine AS (

    SELECT *
    FROM (

        {{
            dbt_utils.date_spine(
                datepart="DAY",
                start_date="CAST('2024-01-01' AS DATE)",
                end_date="CAST(CONVERT_TIMEZONE('Europe/Berlin', CURRENT_TIMESTAMP()) AS DATE)"
            )
        }} -- end_date handle snowflake config time being in LA time

    )

)

select * from date_spine

Should lead the macro databricks__dateadd to be invoked

Expected behavior

adapter.has_dbr_capability('timestampdiff') should be evaluated to true for SQL serverless warehouses

Screenshots and log output

Provided above inside the bug description

System information

The output of dbt --version:

Core:
  - installed: 1.11.6
  - latest:    1.11.6 - Up to date!

Plugins:
  - databricks: 1.11.5 - Up to date!
  - spark:      1.10.1 - Up to date!

The operating system you're using: MacOS 26.3 / Linux via Docker (python:3.12-slim-bullseye)

The output of python --version: Python 3.12.8

Additional context

Based on an initial discussion with @benc-db , it looks like that the culprit is the has_dbr_capability, which should be evaluated to true for SQL warehouses. Additionally I experience this only with timestampdiff capability, not sure if other capabilities are affected.

[sd-db]

When bumping the adapter to dbt-databricks v1.11.5

Do you reckon this is a regression, what version are you running on in prod at the moment ?

[nicor88]

Do you reckon this is a regression, what version are you running on in prod at the moment ?

We tried to upgrade from 1.9 to 1.11, so I won't consider a regression - it's a break that it's introduced via has_dbr_capability, which somehow it's not fully evaluated.

I found out that the issue seems to be during "compilation" time, during a run the timestampdiff capability seems to be correctly evaluated.

Additionally, I solved it in our setup, creating a custom macro, that skip the faulty has_dbr_capability

[nicor88]

@sd-db any clue about that can happen here? I fill that this has_dbr_capability is not really reliable, and it lead to weird side effects - what is weird, is that during compilation is evaluated to false, but at runtime it gets evaluated to true.

[nicor88]

After some digging here the reason why:

@available.parse(lambda *a, **k: False)
def has_dbr_capability(self, capability_name: str) -> bool:

seems to be the reason of the issue - at parsing time the "has_dbr_capability" return False by default, without invoking the function. Using @available do not work because the connection is not available yet.

My suggestion is easy, we retrun by default "True", therefore we assume that the capability is in place, that allow the parsing to proceed, anyhow at runtime (whe the execute actually happen), the macro invoke the method has_dbr_capability, effectively evaluating if the compute have the capability or not, and allowing to use the right approach.

Another way will be to add a feature flag, to control the default value, folks like us, can have a "has_dbr_capability" with a default "true" value, and folks that use not serverless compute can have "false" by default.

[sd-db]

Another way will be to add a feature flag, to control the default value, folks like us, can have a "has_dbr_capability" with a default "true" value, and folks that use not serverless compute can have "false" by default.

This makes sense and the easiest solve here. I will look at the best way to add such a feature flag. I think in the long run we should probably always keep it true for serverless (that can be a breaking change for later)

[sd-db]

Interestingly there was a related bug in capability detection which might/might not have affected you. I am not sure if this is related but can be --> #1414