on_schema_change: append_new_columns not adding new column

Help
1

Problem:

This is the setting for my dbt-project.yml

materialized: incremental
incremental_strategy: merge
on_schema_change: append_new_columns

On first run the table is created inside the database and data is materialized successfully.

However, on second run where there was a change in Schema i.e. source table as introduced with new Column “journalCreation”. I am getting error

column “journalCreation” of relation “glAccountingEntityHist” does not exist

when I run model with debug i.e.

dbt run --debug --model +glJournal+

I can see there dbt tried to execute command

01:54:24  SQL status: SELECT 25 in 0.0 seconds
01:54:24  
    In "gds"."public"."glAccountingEntityHist":
        Schema changed: True
        Source columns not in target: [<Column journalCreation (text)>]
        Target columns not in source: []
        New column types: []
  
01:54:24  Using postgres connection "model.airbyte_utils.glAccountingEntityHist"
01:54:24  On model.airbyte_utils.glAccountingEntityHist: /* {"app": "dbt", "dbt_version": "1.7.4", "profile_name": "normalize", "target_name": "postgres", "node_id": "model.airbyte_utils.glAccountingEntityHist"} */

    alter table "gds"."public"."glAccountingEntityHist"

            
               add column journalCreation text
            

            
  
01:54:24  SQL status: ALTER TABLE in 0.0 seconds
01:54:24  
    In "gds"."public"."glAccountingEntityHist":
        Schema change approach: append_new_columns
        Columns added: [<Column journalCreation (text)>]
        Columns removed: 
        Data types changed: 
  
01:54:24  Writing runtime sql for node "model.airbyte_utils.glAccountingEntityHist"
01:54:24  Using postgres connection "model.airbyte_utils.glAccountingEntityHist"
01:54:24  On model.airbyte_utils.glAccountingEntityHist: /* {"app": "dbt", "dbt_version": "1.7.4", "profile_name": "normalize", "target_name": "postgres", "node_id": "model.airbyte_utils.glAccountingEntityHist"} */

-- back compat for old kwarg name
merge into "gds".public."glAccountingEntityHist" as DBT_INTERNAL_DEST
        using "glAccountingEntityHist__dbt_tmp015424054195" as DBT_INTERNAL_SOURCE
        on (
                DBT_INTERNAL_SOURCE._airbyte_unique_key = DBT_INTERNAL_DEST._airbyte_unique_key
            )

Postgres adapter: Postgres error: column "journalCreation" of relation "glAccountingEntityHist" does not exist
LINE 22: ...ySourceId" = DBT_INTERNAL_SOURCE."entitySourceId","journalCr..

dbt model

SELECT
“journalCreation” AS “journalCreation”,
“name” AS “name”,
FROM
{{ ref(‘glAccountingEntityHist_staging’) }}
WHERE
1 = 1
{{ incremental_clause(‘_airbyte_extracted_at’, this) }}

2

If you rerun it the second time, does the error appear? Even the 1st run failed, the alter table add column query was already executed, so did the next run fail with the same error?

3

It is keep happening