I need to generate multiple views in BigQuery, each querying a different set of tables identified by a specific suffix. The challenge is further compounded by the need to manage these views across multiple environments (tenants, dev, sit, prod etc.).
The specific suffix used for table identification relates to a unique identifier in my business context, let’s call it
ID1. The number of such IDs is quite large and each needs its own view. Moreover, these views need to be managed across multiple environments (like development, staging, production, etc.).
I have considered several approaches to handle this situation:
Separate SQL Models: One approach would be to create a separate SQL model for each combination of
ID1and environment. However, this would result in hundreds of files which could become unmanageable.
Looped Run Operation: Another option is to run a dbt
run-operationin a loop, passing different arguments each time. However, I’m unsure if this aligns with dbt best practices.
- Custom Materialization: I’ve also explored creating a custom materialization a little bit I’m not sure how it could help me here (I’m new to dbt).
I’m looking for advice on the best way to handle this situation. Is there a standard path in dbt for dealing with such complex scenarios that I might have overlooked?