DBT Run from 2 different Redshift accounts

Help
1

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 dbt run.

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.

1 Like
2

Hey stlee,

Have you found a solution for that?

3

Hello!

We were unable to get two different Redshift accounts to perform dbt run on the same schema, so we had to update our CI pipeline with a mapping configuration, which maps users to the Redshift accounts they regularly use locally. Now, when a user pushes a PR, the CI pipeline identifies the author and uses the mapping to identify the appropriate Redshift account to use. While this of course requires an additional maintenance of the mapping configuration, we’ve found it to be manageable thus far at our scale.