How can I support multiple Redshift schemas in a single Looker environment?

redshift

(Paul Graff) #1

My team is currently using DBT to build models against custom schemas in Redshift. This allows us to avoid overwriting each other’s changes in the public schema (which is where DBT builds models by default) while we’re testing out model changes. However, this has introduced challenges in Looker because there isn’t an easy way for data analysts using Looker to reference the models in these custom schemas.

Our team has a few ideas for solving this, but none of them are as user-friendly or automated as we’d like. Does anyone have experience setting up a multi-user development workflow in Looker, or ideas for how this could be addressed without a lot of recurring manual configuration?

For reference, we’re currently running a “test” Looker environment alongside our “prod” one so we can connect the test environment to our “test” Redshift database. This is helpful as it allows us to perform user acceptance testing in Looker before deploying DBT model updates.

Ideas we’re currently considering:

  • Having data analysts hardcode schema names in their LookML files during development
    • Pros: Very flexible
    • Cons: Introduces possibility for references to custom schemas (rather than “public”) to be merged into production
  • Using User Attributes to specify schemas in their user configuration
    • Cons:
      • Users have to manually update their configuration in Looker to see the correct schema (and need to be aware of which one they’re looking at)
      • We aren’t currently building every DBT model required by our LookMLs in these custom schemas… we’re only building new/updated models
  • Require data analysts to build models against the “public” schema in our test Redshift environment before viewing them in Looker
    • Pros: Easy to implement
    • Cons: Requires coordination by data analysts to ensure everyone is aware of which test models currently exist in the “public” schema in our test Redshift environment to avoid a) clobbering each other’s work b) creating confusion around our source data

(will.adams) #2

Hi Paul,

I think the first option there will be the safest and most flexible. If you also enabled pull requests on the project, it would allow a Git admin to review and correct the table names before it makes it to production. We have a doc outlining how to set that up here: https://docs.looker.com/data-modeling/getting-started/setting-up-version-control#integrating_pull_requests_for_your_project

Idea 2 would require more effort in keeping things lined up properly, and idea 3 relies on perfect communication between everyone doing LookML development. Hope this is helpful :slight_smile: