parametizing dbt_project.yml; eg different hostnames for external location_root for dev and prod

[jakub-auger]

Hi all,

I'm stumped on how to approach an environment problem I have (databricks on azure)

I'm using external tables and I define the path using the location_root property in dbt_project.yml

the hostname of the storage for dev and prod are different

I cant see any ability to define a property/variable in my profiles.yml file for the different environments
nor a way to define a variable in the dbt_project.yml and then use it based on the target env selected

How are others approaching this problem? I dont want to have a separate project for dev and prod, and I can't see anything in the doco about this

here's an example of what I mean

models:
  +file_format: delta
  cdp:
    DataMart:
       +location_root: "abfss://deltalake@< dev or prod based on target>.dfs.core.windows.net/DataMart/"

[jakub-auger]

figured out i could do a conditional jinja if based on the passed in target (dev/prod) property for the location_root path

[EvanMiiiiler]

@jakub-auger - I've got this exact same issue, it would be incredible if you could post a simple code stub showing how you did it?

[jakub-auger]

@EvanMiiiiler

in your dbt_projects.yml under the relevant model node

models:
  +file_format: delta
  project: # need to put the delta files into separate storage accs/subscriptions for prod and dev
    DataMart:
       +location_root: "abfss://deltalake@{{ 'prod' if target.name == 'prod' else 'dev'}}.dfs.core.windows.net/DataMart/"
 

this will resolve to abfss://[email protected]/DataMart/ for prod & abfss://[email protected]/DataMart/ for dev

I just tried the same syntax in the config block of an .sql model but it didn't execute the jinja, so not sure what to do there.. but i always set my location @ the schema/folder level not individually against each model so it's never been a problem for me

[EvanMiiiiler]

@jakub-auger - you are such a champion for posting your solution!!! This is gonna help me loads