Priority of Configuration Settings and Apply

Help
,
1

I want to make sure understanding the priority order in which config settings are applied.
Is there a priority between conf value in models and same field value in profiles.yaml?

The problem I’m having

data insert method not expected.

The context of why I’m trying to do this

  1. hive.insert_existing_partitions_behavior value as OVERWRITE in profiles.yml.
  2. In a specific model, I wanted to use the ‘append’ method for insert data.
  3. set conf value in model.sql
  4. However, the actual insert method still remained as ‘overwrite.’

What I’ve already tried

  1. removed the insert_existing_partitions_behavior setting from profiles.yml.
  2. then data insert as append strategy

Some example code or error messages

  • set incremental_strategy value in model.sql config()
    {{
  config(
    alias='table_name',
    materialized = 'incremental',
    unique_key='unique_key_column',
    incremental_strategy='append',
    properties = {
      'format': "'PARQUET'",
      'partitioned_by': "partition",
      'external_location':"path"
    },
  )
}}
  • profiles.yml
    ...
session_properties:
        hive.insert_existing_partitions_behavior: OVERWRITE

Question

From my understanding of the documentation on the dbt global flag, the setting in the most specific context should take precedence, but it seems the setting in profiles.yml has a higher priority. Is there a reason for this?

  • If I want to use a different setting only in the model, would using a pre-hook be the only correct approach?
  • My guess is that even if I set the insert method in dbt, the actual insert method used in the Hive connection settings is still set to ‘overwrite.’

I’ve organized it as follows: lower priority < higher priority.

[dbt_project.yml - models:] < [ .yml - config resource property] < [model - config() Jinja macro] < [profiles.yaml -session properties]
[dbt_project.yml - models:] < [environment variable] < [CLI option]

If I’ve misunderstood anything, please let me know.

reference