Are you collaborating with multiple Analytics Engineers on the same dbt repository?

In the following hypothetical setup, dbt is used to load data into Snowflake, and Power BI is used to create reports based on that data.

Each engineer has their own schema when developing and deploying locally, following dbt best practices. In Snowflake, this could look like this: DBT_DBT_ACC.DEV_DEVELOPER1_BRONZE. Whenever a developer runs the dbt run command locally, objects are deployed to their respective schema, for example: DBT_DBT_ACC.DEV_DEVELOPER1_BRONZE.

When you want to review local dbt developments deployed to Snowflake in Power BI Desktop, it can be useful to use a parameter in Power Query to quickly switch between schemas.

Below is a Power Query snippet that creates a Cartesian product of all combinations of developers (DEV1, DEV2, etc.) and layers (BRONZE, SILVER, GOLD, SEEDS). You can use the resulting list as a Power Query parameter.

let

  // Create list with cartesian product of relevant values

  SchemaPrefix = {"DEV_DEVELOPER1", "DEV_DEVELOPER2", "DEV_DEVELOPER3"},

  SchemaSuffix = {"_BRONZE", "_SILVER", "_GOLD", "_SEEDS"},

  CartesianProduct = List.Combine(

    List.Transform(SchemaPrefix, each List.Transform(SchemaSuffix, (suffix) => _ & suffix))

  )

in

  CartesianProduct