Generating multiple views from one model

Help
1

The problem I’m having

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 context of why I’m trying to do this

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.).

What I’ve already tried

I have considered several approaches to handle this situation:

  1. Separate SQL Models: One approach would be to create a separate SQL model for each combination of ID1 and environment. However, this would result in hundreds of files which could become unmanageable.
  2. Looped Run Operation: Another option is to run a dbt run-operation in a loop, passing different arguments each time. However, I’m unsure if this aligns with dbt best practices.
  3. 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?

1 Like