[ADAP-871] [Regression] Views referencing aliased tables of the same name cause build failure

[btello]

Is this a regression in a recent version of dbt-redshift?

• I believe this is a regression in dbt-redshift functionality
• I have searched the existing issues, and I could not find an existing issue for this regression

Current Behavior

When I run a dbt build against the project in this demo repository https://github.com/btello/test_dbt_bug/tree/main I get the following error every other time I run it:

Database Error in model test (models/stage/test.sql)
relation "stage_stage.test" does not exist

What I've found is that any time I have a view model with name x and then query a table model with an alias to that same name x (but in a different schema of course) I get this error.

This problem is affecting my real production project as well but this super basic repro should be more clear. I applied minimal configuration to the project and there are only two models.

Expected/Previous Behavior

I did not have this error on 1.4.7 which is the version we use in production and I've tested all the way up to 1.5.8 which works fine.

Steps To Reproduce

Environment:
dbt-core = 1.6.1
dbt-redshift = 1.6.1
Python 3.11.4
OS: I've tested this on both Ubuntu (in the Docker container mcr.microsoft.com/devcontainers/universal:2) as well as Windows 11. It fails the same way on both OSs

To reproduce, point this project https://github.com/btello/test_dbt_bug/tree/main at a redshift database and run dbt build

Relevant log output

These logs are from a run in my prod environment but it looks like the problem is that every other run the behavior of the `alter table...` command that comes after the creation of the view model changes.

 
=== 1.6 fail ===

/* dimrenewalgrouping table */

�[0m19:33:29.881106 [debug] [Thread-1 (]: Began running node model.acumen_core.dimrenewalgrouping_model
...
�[0m19:33:29.937738 [debug] [Thread-1 (]: On model.acumen_core.dimrenewalgrouping_model: /* {"app": "dbt", "dbt_version": "1.6.1", "profile_name": "test_db_acumen_pipeline", "target_name": "prod", "node_id": "model.acumen_core.dimrenewalgrouping_model"} */
  create  table
    "db890"."model"."dimrenewalgrouping__dbt_tmp"
  as (
SELECT 
	dimrenewalgrouping_key :: bigint,
	renewalgroupingmin :: integer,
	renewalgroupingmax :: integer,
	nullif(renewalgroupingname,'') :: varchar(50),
	getdate() as date_added,
	getdate() as date_updated,
	'A' as record_status
FROM "db890"."stage"."vw_dimrenewalgrouping"
  );
�[0m19:33:30.037094 [debug] [Thread-1 (]: SQL status: SUCCESS in 0.0 seconds
...
alter table "db890"."model"."dimrenewalgrouping" rename to "dimrenewalgrouping__dbt_backup"
�[0m19:33:30.055436 [debug] [Thread-1 (]: SQL status: SUCCESS in 0.0 seconds
...
alter table "db890"."model"."dimrenewalgrouping__dbt_tmp" rename to "dimrenewalgrouping"
�[0m19:33:30.067442 [debug] [Thread-1 (]: SQL status: SUCCESS in 0.0 seconds
...
drop table if exists "db890"."model"."dimrenewalgrouping__dbt_backup" cascade
�[0m19:33:30.248525 [debug] [Thread-1 (]: SQL status: SUCCESS in 0.0 seconds


/* dimrenewalgrouping view */

�[0m19:33:44.388985 [debug] [Thread-1 (]: Began running node model.acumen_core.dimrenewalgrouping
...
�[0m19:33:44.446416 [debug] [Thread-1 (]: On model.acumen_core.dimrenewalgrouping: /* {"app": "dbt", "dbt_version": "1.6.1", "profile_name": "test_db_acumen_pipeline", "target_name": "prod", "node_id": "model.acumen_core.dimrenewalgrouping"} */

  create view "db890"."data"."dimrenewalgrouping__dbt_tmp" as (
    SELECT
        dimrenewalgrouping.dimrenewalgrouping_key,
        dimrenewalgrouping.renewalgroupingmin,
        dimrenewalgrouping.renewalgroupingmax,
        dimrenewalgrouping.renewalgroupingname
    FROM
        "db890"."model"."dimrenewalgrouping" dimrenewalgrouping
  ) ;
...
alter table "db890"."data"."dimrenewalgrouping" rename to "dimrenewalgrouping__dbt_backup"
�[0m19:33:44.485495 [debug] [Thread-1 (]: Redshift adapter: Redshift error: relation "data.dimrenewalgrouping" does not exist


=== 1.4 success ===


�[0m19:42:09.205407 [debug] [Thread-1 (]: Began running node model.acumen_core.dimrenewalweek_model
...
�[0m19:42:09.259224 [debug] [Thread-1 (]: On model.acumen_core.dimrenewalweek_model: /* {"app": "dbt", "dbt_version": "1.4.7", "profile_name": "test_db_acumen_pipeline", "target_name": "prod", "node_id": "model.acumen_core.dimrenewalweek_model"} */
  create  table
    "db890"."model"."dimrenewalweek__dbt_tmp"
  as (
    
SELECT 
	dimrenewalweek_key :: bigint,
	renewalweekmin :: integer,
	renewalweekmax :: integer,
	nullif(renewalweekname,'') :: varchar(50),
	getdate() as date_added,
	getdate() as date_updated,
	'A' AS record_status
FROM "db890"."stage"."vw_dimrenewalweek"
  );

�[0m19:42:09.419366 [debug] [Thread-1 (]: SQL status: SELECT in 0 seconds
...
�[0m19:42:09.425223 [debug] [Thread-1 (]: On model.acumen_core.dimrenewalweek_model: /* {"app": "dbt", "dbt_version": "1.4.7", "profile_name": "test_db_acumen_pipeline", "target_name": "prod", "node_id": "model.acumen_core.dimrenewalweek_model"} */
alter table "db890"."model"."dimrenewalweek" rename to "dimrenewalweek__dbt_backup"
�[0m19:42:09.428997 [debug] [Thread-1 (]: SQL status: ALTER TABLE in 0 seconds
...
alter table "db890"."model"."dimrenewalweek__dbt_tmp" rename to "dimrenewalweek"
�[0m19:42:09.434857 [debug] [Thread-1 (]: SQL status: ALTER TABLE in 0 seconds
...
drop table if exists "db890"."model"."dimrenewalweek__dbt_backup" cascade
�[0m19:42:09.515518 [debug] [Thread-1 (]: SQL status: DROP TABLE in 0 seconds

                             



�[0m19:42:22.150662 [info ] [Thread-1 (]: 74 of 323 START sql view model data.dimrenewalweek ............................. [RUN]
...
�[0m19:42:22.200044 [debug] [Thread-1 (]: On model.acumen_core.dimrenewalweek: /* {"app": "dbt", "dbt_version": "1.4.7", "profile_name": "test_db_acumen_pipeline", "target_name": "prod", "node_id": "model.acumen_core.dimrenewalweek"} */


  create view "db890"."data"."dimrenewalweek__dbt_tmp" as (
    SELECT
  dimrenewalweek.dimrenewalweek_key AS renewalweek_key,
  dimrenewalweek.renewalweekmin,
  dimrenewalweek.renewalweekmax,
  dimrenewalweek.renewalweekname
FROM
  "db890"."model"."dimrenewalweek" dimrenewalweek
  ) ;

�[0m19:42:22.209250 [debug] [Thread-1 (]: SQL status: CREATE VIEW in 0 seconds
...
alter table "db890"."data"."dimrenewalweek__dbt_tmp" rename to "dimrenewalweek"
...
drop view if exists "db890"."data"."dimrenewalweek__dbt_backup" cascade
�[0m19:42:22.269041 [debug] [Thread-1 (]: SQL status: DROP VIEW in 0 seconds

### Environment

```markdown
- OS:
- Python:
- dbt-core (working version):
- dbt-redshift (working version):
- dbt-core (regression version):
- dbt-redshift (regression version):

Additional Context

No response

[dataders]

@btello thanks for the write-up! this is indeed a bug. this isn't a use case we commonly see of aliasing models to correspond to other names of models, but it is certainly a valid one.

Using version 1.6.1 of both dbt-core and dbt-redshift, I can reproduce this issue only when:

• the models have been created already
• both models are run in a single dbt run.

dbt run -s foo; dbt run -s bar # works
dbt run                        # doesn't work
dbt run -s +bar                # doesn't work

the run will fail not on the model creation step, but rather when attempting to rename the pre-existing target relation to a backup so that the model that was just made can be renamed. The following SQL errors with: "relation "dataders_downstream.bar" does not exist

alter table "db"."dataders_downstream"."bar" rename to "bar__dbt_backup"

This is the place in the view materialization (dbt-redshift, uses dbt-core's default version). What strange is that line only executes if dbt thinks the relation already exists

simple reproducible example

two models

• foo.sql which aliases to use bar as identifier
• bar.sql which also uses bar as identifier, but in a different schema

-- foo.sql
{{
    config(
        materialized='table',
        schema='upstream',
        alias='bar'
    )
}}

SELECT 1 AS x

-- bar.sql
{{
    config(
        materialized='view',
        schema='downstream'
    )
}}

SELECT x as id from {{ref('foo')}}

theories

• we rename relations using ALTER TABLE which in redshift only accepts an identifier not a schema or database argument?
• something to do with load_cached_relation() because it finds the "dataders_downstream.bar" relation, but later in the view materialization, it cannot?
• [CT-2636] TestVersionedModels::test_pp_versioned_models is flaky dbt-core#7781 the PR has not yet been shipped within a backport. however I tested both the before and after and still got the error message
• weird transaction stuff?
• redshift's late binding views?

[gshank]

I fixed an intermittent test error in this area in the base view.sql for 1.6:

  {% if existing_relation is not none %}
     /* Do the equivalent of rename_if_exists. 'existing_relation' could have been dropped
        since the variable was first set. */
    {% set existing_relation = load_cached_relation(existing_relation) %}
    {% if existing_relation is not none %}
        {{ adapter.rename_relation(existing_relation, backup_relation) }}
    {% endif %}
  {% endif %}

[github-actions]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please comment on the issue or else it will be closed in 7 days.

[github-actions]

Although we are closing this issue as stale, it's not gone forever. Issues can be reopened if there is renewed community interest. Just add a comment to notify the maintainers.