FAQ: Configuring models based on environments

We’ve had a couple of questions recently about configuring models based on your environment (or target) over on Slack, spefically around:

  • Using views in dev and tables in prod
  • [Snowflake only] Using transient tables in dev and non-transient in prod.

The ideal solution to these problems would be to be able to configure these values from your dbt_project.yml file, like so:

  materialized: "{{ 'table' if target.name == 'prod' else 'view' }}

Unfortunately, this is currently unsupported. Essentially, it’s a dependency ordering issue; behind the scenes:

  1. dbt parses the dbt_project.yml
  2. dbt picks up your profile from this file
  3. dbt then parses your profile to find the target, and therfore the target.name.

As such, dbt doesn’t know about your target.name when it reads your dbt_project.yml file, so this approach won’t work.

We have plans to fix this in the near future, check out this related issue!

In the meantime, the most reasonable workaround is to set an environment variable and use that to determine the configuration value:

  materialized: "{{ 'table' if env_var('ENV') == 'prod' else 'view' }}

Other reasonable workarounds include setting this on a per-model level:

        materialized=('table' if target.name == 'prod' else 'view')

^ This would be my preferred approach on Redshift since I tend to configure tables in the model directly, as I need to set sort and dist keys on a per-model basis.

You could also conceivably create a custom materialization that packages up this logic, but I’d recommend this as a last resort!


Is there a way to accomplish this in DBT Cloud?

Yep, good catch – dbt Cloud currently doesn’t support environment variables (it’s an open issue on our side!).

You can use the second (optional) argument for var to give a default value, which can help handle the dbt Cloud case as you avoid a compilation error.
It gets a bit janky but here’s the extension to that pattern:

  materialized: "{{ 'table' if env_var('ENV', 'prod') == 'prod' else 'view' }}

Fortunately we’re working on some issues on the Core front so you won’t need this workaround!