Elementary does not find the "alerts" table / get_relation "dbt_invocations"

[galodoidobsb]

Describe the bug
When creating a new staging catalog in Databricks, we are able to install the dbt-elementary package and run all elementary models.
However, when trying to run the elementary CLI (either edr monitor or edr report), Elementary fails to run the dbt command and throws the following error. The alerts_v2 table exists on the schema, but the alerts table doesn't.

2024-07-23 09:00:01 — INFO — Running with edr=0.15.1
2024-07-23 09:00:10 — ERROR — Unable to get the latest invocation: Failed to run dbt command.
Encountered an error while running operation: Runtime Error
  Compilation Error
    get_relation returned more than one relation with the given args. Please specify a database or schema to narrow down the result set.
    {'identifier': 'dbt_invocations', 'schema': 'elementary', 'database': 'hmg'}
    
    [<DatabricksRelation `hmg`.`elementary`.`dbt_invocations`>, <DatabricksRelation `hmg`.`elementary`.`dbt_invocations`>]
    
    > in macro get_latest_invocation (macros/get_latest_invocation.sql)
    > called by macro log_macro_results (macros/utils/log_macro_results.sql)
    > called by <Unknown>
2024-07-23 09:00:16 — INFO — Elementary's database and schema: '"hmg.elementary"'
2024-07-23 09:00:16 — INFO — Running dbt --log-format json run-operation elementary.log_macro_results --args {"macro_name": "elementary_cli.get_test_results", "macro_args": {"days_back": 7, "invocations_per_test": 720, "disable_passed_test_metrics": false}} --project-dir /Users/galodoido/Dev/metaloop/data_stack_dbt/dbt-env/lib/python3.9/site-packages/elementary/monitor/dbt_project
2024-07-23 09:00:22 — INFO — Running dbt --log-format json run-operation elementary.log_macro_results --args {"macro_name": "elementary_cli.get_source_freshness_results", "macro_args": {"days_back": 7, "invocations_per_test": 720}} --project-dir /Users/galodoido/Dev/metaloop/data_stack_dbt/dbt-env/lib/python3.9/site-packages/elementary/monitor/dbt_project
2024-07-23 09:00:27 — INFO — Running dbt --log-format json run-operation elementary.log_macro_results --args {"macro_name": "elementary_cli.get_models", "macro_args": {"exclude_elementary": true}} --project-dir /Users/galodoido/Dev/metaloop/data_stack_dbt/dbt-env/lib/python3.9/site-packages/elementary/monitor/dbt_project
2024-07-23 09:00:32 — ERROR — Could not generate the report - Error: Failed to run dbt command.
Encountered an error while running operation: Runtime Error
  Compilation Error
    get_relation returned more than one relation with the given args. Please specify a database or schema to narrow down the result set.
    {'identifier': 'dbt_models', 'schema': 'elementary', 'database': 'hmg'}
    
    [<DatabricksRelation `hmg`.`elementary`.`dbt_models`>, <DatabricksRelation `hmg`.`elementary`.`dbt_models`>]
    
    > in macro load_cached_relation (macros/adapters/relation.sql)
    > called by macro load_relation (macros/adapters/relation.sql)
    > called by macro relation_exists (macros/utils/table_operations/relation_exists.sql)
    > called by macro get_models (macros/get_models.sql)
    > called by macro log_macro_results (macros/utils/log_macro_results.sql)
    > called by <Unknown>
Please reach out to our community for help with this issue.
Traceback (most recent call last):
  File "/Users/galodoido/Dev/metaloop/data_stack_dbt/dbt-env/lib/python3.9/site-packages/elementary/clients/dbt/dbt_runner.py", line 88, in _run_command
    result = subprocess.run(
  File "/opt/homebrew/Cellar/[email protected]/3.9.13_1/Frameworks/Python.framework/Versions/3.9/lib/python3.9/subprocess.py", line 528, in run
    raise CalledProcessError(retcode, process.args,
subprocess.CalledProcessError: Command '['dbt', '--log-format', 'json', 'run-operation', 'elementary.log_macro_results', '--args', '{"macro_name": "elementary_cli.get_models", "macro_args": {"exclude_elementary": true}}', '--project-dir', '/Users/galodoido/Dev/metaloop/data_stack_dbt/dbt-env/lib/python3.9/site-packages/elementary/monitor/dbt_project']' returned non-zero exit status 1.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Users/galodoido/Dev/metaloop/data_stack_dbt/dbt-env/lib/python3.9/site-packages/elementary/monitor/api/report/report.py", line 68, in get_report_data
    models = models_api.get_models(exclude_elementary_models)
  File "/Users/galodoido/Dev/metaloop/data_stack_dbt/dbt-env/lib/python3.9/site-packages/elementary/monitor/api/models/models.py", line 117, in get_models
    models_results = self.models_fetcher.get_models(
  File "/Users/galodoido/Dev/metaloop/data_stack_dbt/dbt-env/lib/python3.9/site-packages/elementary/monitor/fetchers/models/models.py", line 38, in get_models
    run_operation_response = self.dbt_runner.run_operation(
  File "/Users/galodoido/Dev/metaloop/data_stack_dbt/dbt-env/lib/python3.9/site-packages/elementary/clients/dbt/dbt_runner.py", line 160, in run_operation
    success, command_output = self._run_command(
  File "/Users/galodoido/Dev/metaloop/data_stack_dbt/dbt-env/lib/python3.9/site-packages/elementary/clients/dbt/dbt_runner.py", line 99, in _run_command
    raise DbtCommandError(err, command_args, logs=logs)
elementary.exceptions.exceptions.DbtCommandError: Failed to run dbt command.
Encountered an error while running operation: Runtime Error
  Compilation Error
    get_relation returned more than one relation with the given args. Please specify a database or schema to narrow down the result set.
    {'identifier': 'dbt_models', 'schema': 'elementary', 'database': 'hmg'}
    
    [<DatabricksRelation `hmg`.`elementary`.`dbt_models`>, <DatabricksRelation `hmg`.`elementary`.`dbt_models`>]
    
    > in macro load_cached_relation (macros/adapters/relation.sql)
    > called by macro load_relation (macros/adapters/relation.sql)
    > called by macro relation_exists (macros/utils/table_operations/relation_exists.sql)
    > called by macro get_models (macros/get_models.sql)
    > called by macro log_macro_results (macros/utils/log_macro_results.sql)
    > called by <Unknown>

To Reproduce
Steps to reproduce the behavior:

1. Create a new Catalog on Databricks
2. Run dbt deps
3. Add the elementary_full_refresh: true flag to the dbt_project.yml and run elementary models dbt run -s elementary
4. Build the project dbt build
5. Try to run edr monitor or edr report

Expected behavior
Run all models and trigger the alerts.
Run all models and generate a valid report.

Screenshots

Databricks hmg catalog with the elementary schema and alerts_v2 model.

Environment (please complete the following information):

• Elementary CLI (edr) version: 0.15.1
• Elementary dbt package version: 0.15.1
• dbt version you're using: 1.8.2
• Data warehouse: databricks
• Infrastructure details (e.g. operating system, prod / dev / staging, deployment infra, CI system, etc)
  • environment: staging
  • operating system: MacOS Sonoma 14.5
  • CPU: arm64 (M1)
  • Python: 3.9.13
• venv installed requirements:
  • dbt-core==1.8.2
  • dbt-databricks==1.8.2
  • databricks-sdk==0.17.0
  • databricks-sql-connector==3.1.2
  • elementary-data==0.15.1
• dbt profiles.yml:

dbt_metaloop:
  target: hmg
  outputs:
    hmg:
      type: databricks
      catalog: hmg
      schema: dbt_hmg
      host: HOST
      port: 443
      http_path: HTTP_PATH
      token: "{{ env_var('DBT_ACCESS_TOKEN') }}"
      connect_timeout: 10
      retries: 1
      threads: 10

elementary:
  outputs:
    default:
      type: databricks
      catalog: hmg
      host: HOST
      http_path: HTTP_PATH
      schema: elementary
      token: "{{ env_var('DBT_ACCESS_TOKEN') }}"
      threads: 10

Additional context
We're using a macro on our dbt project to generate custom schemas names, so we can have separate schemas for bronze/silver/gold layers on the staging and prd catalogs. Macro code generate_schema_name.sql is the following:

{% macro generate_schema_name(custom_schema_name, node) -%}
    {%- set default_schema = target.schema -%}
    {#-
        This macro will return:
        * custom schema without prefix in prod enviroment,
        * custom schema with prefix in non specified environments
    -#}
    {%- if custom_schema_name is not none and target.name in ('prd', 'hmg', 'samples') -%}
        {{ custom_schema_name | trim }}
    {%- else -%}
        {{ default_schema }}
    {%- endif -%}
{%- endmacro %}

Would you be willing to contribute a fix for this issue?
Unfortunately not

[ofek1weiss]

Hey @galodoidobsb , sorry for the late response here.
seems like the issue is caused when elementary uses adapter.get_relation, which is a function that is implemented in dbt-databricks, i suggest trying to seek out help over at their github