Datagroups and caching with dynamic schema value

Hi everyone! I have a somewhat open ended question that I can’t seem to find any information on… We have a Looker instance at my organization that connects to a Redshift DW with multiple schemas. The schema that is the connected to is determined by a user attribute (this article describes how this is implemented: https://cloud.google.com/looker/docs/best-practices/how-to-create-a-dynamic-schema-with-user-attribu...). My question is how do I build datagroups and handle caching in this type of architecture? Since groups of users have difference schemas, and each schema is updated on its own cadence, there’s not really a “central” table for a sql_trigger to check for updates. In addition to this, even if I were to somehow trigger a cache refresh, which schema’s cache would be refreshed? If the answer is “all of them” then when one team update’s their schema, would it cause the instance-wide cache to be reloaded? Has anyone ever implemented caching with the above design? If so, how did you do it? Thanks!

1 1 197
1 REPLY 1

Some additional information I figured I should add… This is our desired behavior, though I have no idea whether this is possible in Looker, just what would be optimal for us:

  • Our Setup:
    • Each team (there are hundreds of them) has a unique schema in Redshift that only they can access
    • These schemas have an identical structure, but different data, meaning the same Looker model/views can be laid “on top” of the schemas, but display different data to different teams
  • Our Caching Needs:
    • Datagroup somehow checks individual schemas for updates
    • When a schema is modified with new data, datagroup is triggered using sql_trigger
      • We cannot use an interval_trigger as the data update cadence is very irregular
    • The datagroup then refreshes the cache for that schema separate from other schemas’ caches
      • This is important as we dont want the entire instance’s cache to refresh every time one team updates their data (there are hundreds of teams that can update their schemas daily, weekly, monthly, or randomly)

Again, I have a feeling this exact behavior isn’t possible, and I’m probably misunderstanding how caching works in Looker, but I wanted to describe the use case a bit more.

Top Labels in this Space
Top Solution Authors