dbt-labs/dbt-redshift

[Bug] The `--empty` argument doesn't work on models with table aliases

armandduijn opened this issue · 3 comments

Is this a new bug in dbt-core?

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

Current Behavior

I get a Database Error when I use the --empty argument on a model that uses table aliases.

Expected Behavior

I expect dbt to materialize the empty model.

Steps To Reproduce

  1. Create a model my_model. Alias the referenced model.

    SELECT e.*
    FROM {{ source("atomic", "events") }} e
  2. Run dbt with the --empty argument.

    dbt --debug run --select my_model --empty

I get the same error if I add AS in front of the alias name.

Relevant log output

10:25:16  On model.snowplow.my_model: /* {"app": "dbt", "dbt_version": "1.8.0", "profile_name": "studyportals", "target_name": "staging", "node_id": "model.snowplow.my_model"} */


  create view studyportals.dbt_test.my_model__dbt_tmp as (
    SELECT e.*
FROM (select * from studyportals.atomic.events where false limit 0) _dbt_limit_subq_events e
  ) with no schema binding;
10:25:16  Redshift adapter: Redshift error: syntax error at or near "e"
10:25:16  On model.snowplow.my_model: ROLLBACK
10:25:16  On model.snowplow.my_model: Close
10:25:16  Database Error in model my_model (models\my_model.sql)
  syntax error at or near "e"
  compiled Code at target\run\snowplow\models\my_model.sql

Environment

- OS: Windows
- Python: 3.10.11
- dbt: 1.8.0

Which database adapter are you using with dbt?

redshift

Additional Context

No response

@armandduijn This is a known limitation on data platforms that require aliases for subqueries (e.g. Postgres):

However, it looks like you're using Redshift, so I'm surprised to hear you're hitting this error —

It looks like that PR was merged to main but wasn't backported to 1.8.latestfor inclusion in v1.8.0. As soon as we merge that backport and include it in adbt-redshift` patch release, I hope this will start working for you:

In the meantime, I'm going to transfer this issue to dbt-redshift.