Search code examples
dbt

multiple profiles in one profiles.yml is possible?

dbt version: 1.3.1

python version: 3.9.6

adapter = dbt-synapse.yml

# profiles.yml

default: dbt_project

dbt_project:
  target: dev
  outputs:
    dev:
      type: synapse  
      driver: 'ODBC Driver 17 for SQL Server' 
      server: XXXXXXX
      database: ###
      port: 1433
      schema: #######
      user: ######
      password: #####


azure_blob:
  target: dev
  outputs:
      dev:
        type: azure_blob
        account_name: ##
        account_key: ##
        container: ##
        prefix: delta_lake

--- after implied this change here is the error message a get--01/30/2023--- @2:32 pm central time----

i get this error when try to read the file from azure blob storage

-- the is the profiles.yml--------
 
default: dbt_project

dbt_project:
  target: dev
  outputs:
    dev:
      type: synapse  #synapse  #type: Azuresynapse
      driver: 'ODBC Driver 17 for SQL Server' # (The ODBC Driver installed on your system)
      server: XXXXXXX
      database: XXXXXXX
      port: 1433
      schema: XXXXXXX
      #authentication: sqlpassword
      user: XXXXXXX
      password: XXXXXXX
    azure_blob:
       type: azure_blob
       account_name: XXXXXXX
       account_key: XXXXXXX
       container: data-platform-archive  #research-container/Bronze/Freedom/ABS_VESSEL/
       prefix: abc/FGr1/fox/

--------------- dbt_project.yml-------------------------

name or the intended use of these models

name: 'dbt_project'
version: '1.0.0'
config-version: 2

# This setting configures which "profile" dbt uses for this project.
profile: 'dbt_project'

model-paths: ["models"]
analysis-paths: ["analyses"]
test-paths: ["tests"]
seed-paths: ["seeds"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]

target-path: "target"  
clean-targets:         
  - "target"
  - "dbt_packages"


models:
  dbt_project:
     staging:
      +materialized: table
     utilities:
      +materialized: view
  azure_Blob:
    staging:
      +materialized: view   

--------------------------------

Model name=dbt_stg_DL_abs_acm_users.sql"
and here is the code

{{ config(
    materialized='view',
    connection='azure_blob'
) }}

select *
from {{ source('data-platform-archive/abc/FGr1/fox/', 'abc.parquet') }}



Compilation Error in model dbt_stg_DL_abs_acm_users
  Model 'model.dbt_project.dbt_stg_DL_abs_acm_users' 'abc.parquet' which was not found
Solution

  • Yes, what you've shown here is multiple profiles in a single profiles.yml file. However, there is no default key in the profiles.yml file, since the profile must be specified by the project.

    In your dbt_project.yml file, there is a key that names the profile that the project should use. This project config use the dbt_project profile that you have defined:

    # dbt_project.yml
name: 'jaffle_shop'
profile: 'dbt_project'

    And this one will use the azure_blob profile that you have defined:

    # dbt_project.yml
name: 'jaffle_shop'
profile: 'azure_blob'

    This is a common pattern if you are developing on multiple projects on a single machine. For a SINGLE project, it is more common to define multiple targets:

    # profiles.yml

dbt_project:
  target: dev
  outputs:
    dev:
      type: synapse
      ...
    blob:
      type: azure_blob
      ...

    You can select which target to use at runtime by passing the -t or --target parameter to the CLI: dbt run -t blob would run the project against the azure_blob connection. The target: dev line in the file above specifies that the dev target (so the Synapse connection) should be the default, if the target is not specified at runtime.

    It's somewhat unusual to have multiple targets use different types of databases, and some care must be taken to write compatible syntax. But it is possible -- many packages do this for integration tests, for example.