The problem I’m having
It seems that two different Redshift accounts can not perform
dbt run on the same schema, as whichever account first creates a view/table becomes the owner, preventing the other account from performing the necessary actions. We believe we have narrowed this down to the ALTER TABLE privileges needed to rename the view/table during a
dbt run for an existing view/table.
The context of why I’m trying to do this
As per this page, we have set up development schemas for each user. For our CI pipeline, we set up one CI schema for everyone’s PRs to run/test against (using a prod Redshift account). However, we started seeing frequent race conditions, where two different PRs would be running against the CI schema simultaneously, and one would cause the other to fail (typically by dropping a model needed by the other). So we wanted to see if we could instead use each person’s development schema in our CI pipeline - but then we encountered the issue that our prod Redshift account could not touch models pushed by the users in their own schemas (and vice versa).
What I’ve already tried
We’ve tried granting all manner of Redshift permissions to the CI Redshift account - it seems the last one we can’t get around is the ALTER TABLE permission needed to temporarily rename views/tables during a
Is this something that anyone else has encountered, or found a way around? We are aware that dbt Cloud creates temporary schemas for PRs, but that is not yet an option with our setup.