[Bug] on_schema_change failure with alternate schema

[magno32]

Is this a new bug in dbt-athena?

• I believe this is a new bug in dbt-athena
• I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

I have an incremental table int_nightly_sales, that is an intermediate table in our workflow.

When attempting to add a column to this table, I get a failure:

16:17:13  dbt.adapters.athena.constants adapter: Error running SQL: alter table `base_db_intermediate`.`int_nightly_sales`
          replace columns (<columns>)
  
16:17:13  On model.daily_sales_sync.int_nightly_sales: Close
16:17:13  Runtime Error in model int_nightly_sales (models/intermediate/upload/int_nightly_sales.sql)
  FAILED: SemanticException [Error 10072]: Database does not exist: base_db

The table should actually exist on: base_db_intermediate as called out in the config:

{{ config(
    materialized='incremental',
    incremental_strategy='append',
    partitioned_by=['processed_on'],
    on_schema_change='sync_all_columns',
    schema='intermediate'
) }}

I dug through the adapter source and could not find a culprit on this, but the odd thing is the first message references the correct db, where the semantic error thrown references the base_db (which does not actually contain anything at all).

Expected Behavior

The table exists on the proper database in athena, the adapter should reference the correct database instead of the base database for on_schema_change.

Steps To Reproduce

1. Create an athena dbt project with a base schema (base)
2. Create an incremental model with a different schema (sub) -> base_sub
3. dbt build
4. Add a column to the model
5. dbt build

This should fail as described.

Environment

- OS: Mint
- Python: 3.10.12
- dbt: 1.8.4
- dbt-athena-community: 1.8.4

Additional Context

No response

[amychen1776]

Hello @magno32 ! Apologies on the delayed response - we didn't realize that our github triage bot wasn't working correctly so I wasn't see new tickets on this repo. That's fixed now.

I'm struggling to recreate this error so would like to ask some follow up questions:

• did you create the incremental model first in base_db and then moved it to base_db_intermediate?
• What is the schema that the table int_nightly_sales is trying to select from? (is it selecting tables in base_db?)