we have a multi-stage ETL pipeline with the resulting tables living in different GCP projects in BigQuery as in the following example.
Stages: unstable, staging, sandbox, prod
Table Schema (for all four tables):
- timestamp ts
- string user_id
- string status
Access to these projects, datasets, and tables is fully managed in GCP and in Looker we grant access to our users and viewers via OAuth. Some accounts will have access to staging only, while others will have access to all stages.
My goal is to develop ONE “My Event Dashboard” for all four stages.
I want to avoid duplicating Looker Projects, Models, and Dashboards, if possible.
As a user I would expect to have a filter control in a dashboard that allows selecting the GCP Project (or stage), such that I can see the data from that particular stage only. If I do not have access to some stages these should not be visible in the filter values (if possible). Also not having access to some stages should not break the Dashboard for me.
The access rights granted in GCP (i.e., obtained via OAuth) should define which values to filter on. If possible, the filter control (or underlying Looker model) should enforce that the user selects exactly ONE project/stage in the dashboard and this should then translate to which tables are queried (or views included in the LookML).
I tried using user attributes and liquid variables, manually adding the four project IDs as Advanced String values in a “project” attribute, with no success. Also this would be just a hack. Having a native OAuth way of accessing data would be much preferred.
What is the best approach here?