Dbt: Intermittent relation does not exist on view model in Redshift

[grahamlyus]

Describe the bug

When running dbt run, I intermittently encounter a 'relation does not exist' error for a view model. It's always the same view, but that may be because all other models get skipped after the error.

I've verified that the view existed before the run command

Steps To Reproduce

e.g. dbt run --target staging

Expected behavior

Expected to recreate the view without failure

Screenshots and log output

12:39:53 | 14 of 62 ERROR creating view model dbt_staging.identifies_by_day..... [ERROR in 2.08s]
12:39:53 | 15 of 62 START incremental model dbt_staging.lookup_identifies_by_month [RUN]
...
Completed with 1 error and 0 warnings:

Database Error in model identifies_by_day (models/users/identifies_by_day.sql)
  relation "dbt_staging.identifies_by_day" does not exist
  compiled SQL at target/run/faw_segment/users/identifies_by_day.sql

This appears to be on the operation that renames the existing view to a backup (snippet from logs/dbt.log):

2020-05-27 19:39:52.871333 (Thread-1): On model.faw_segment.identifies_by_day: /* {"app": "dbt", "dbt_version": "0.16.1", "profile_name": "faw-segment", "target_name":
 "staging", "node_id": "model.faw_segment.identifies_by_day"} */
alter table "events"."dbt_staging"."identifies_by_day" rename to "identifies_by_day__dbt_backup"
2020-05-27 19:39:52.970532 (Thread-1): Postgres error: relation "dbt_staging.identifies_by_day" does not exist

2020-05-27 19:39:52.970829 (Thread-1): On model.faw_segment.identifies_by_day: ROLLBACK
2020-05-27 19:39:53.072371 (Thread-1): Error running SQL: macro rename_relation
2020-05-27 19:39:53.072606 (Thread-1): Rolling back transaction.

System information

Which database are you using dbt with?

• [ ] postgres
• [X] redshift
• [ ] bigquery
• [ ] snowflake
• [ ] other (specify: ____________)

The output of dbt --version:

installed version: 0.16.1
   latest version: 0.16.1

Up to date!

The operating system you're using:
macOS 10.15.4, but also happens in production on Amazon Linux 2 AMI 2.0.20200406.0 x86_64 HVM gp2

The output of python --version:

Python 3.7.7

Additional context

This started happening after upgrading from dbt 0.13.1. I've tried going back to 0.15.3, 0.14.4 and it still happens. If I go back to 0.13.1 now I get a Could not find adapter type redshift! error.

Happy to get into specifics on slack - I used the join the community, but have not received any invite to the slack community

[jtcohen6]

Hey @grahamlyus, thanks for the writeup. I have a guess as to what's going on, though I may be off base.

In your dbt run, are you also including models that are:

• materialized='table'
• parents of lookup_identifies_by_month (i.e. the view model refs that table model)

If that's the case, when dbt replaces the upstream table, it cascade-drops the view... but it seems that dbt doesn't update its cache to account for the view having dropped, so when it comes time to create the model, it thinks the preexisting object is still there.

Generally, we resolve this by setting bind: false to use late-binding views on Redshift. They aren't cascade-dropped along with tables they select from; the trade-off is that they're validated on read instead of write.

The alternative is that we endeavor toward a more-complex implementation of caching such that, whenever a materialization drops a table object, it also attempts to update the cache entries for child models with materialized='view' and bind != false...

[grahamlyus]

@jtcohen6 Thanks for the quick response. One of the parents of the problem view identifies_by_day is lookup_identifies_by_day which is "materialized": 'insert_by_period'.

I'll look into your suggestion and report back

[beckjake]

The alternative is that we endeavor toward a more-complex implementation of caching such that, whenever a materialization drops a table object, it also attempts to update the cache entries for child models with materialized='view' and bind != false...

dbt actually should be doing that. On postgres/redshift we keep track of all the binding-view relationships. When we drop a table we mark all its downstream values as also being dropped and remove them from the cache. That's been the case since the cache was first written... modulo any bugs, of course.

[grahamlyus]

@jtcohen6 I added bind: false to my dbt_project.yml then run a --full-refresh and then ran 10 regular runs with no failures, so this must be it

Thanks very much, I think I can stop scratching my head :)