Incremental loads don't work where schema is different. Implement on_schema_change

[prgx-aeveri01]

Hey guys,

If your destination table doesn't have the same columns as your model the whole model fails.
Example

If Table A has ID, Text1, Date1 in it's field list and the dbt model has TableA as ID, Text1 on dbt run the model will fail regardless of the on_schema_change setting. It seems the incremental macro retrieves the list of columns to insert into from the destination table and never checks them against the model for schema diffs?

[findinpath]

Relevant documentation:
https://docs.getdbt.com/docs/building-a-dbt-project/building-models/configuring-incremental-models#what-if-the-columns-of-my-incremental-model-change

[prgx-aeveri01]

I've sorted my particular issue with an override macro using your function:

{% macro dbt_trino_get_append_sql(tmp_relation, target_relation) %}

    {%- set dest_columns = adapter.get_columns_in_relation(tmp_relation) -%}
    {%- set dest_cols_csv = dest_columns | map(attribute='quoted') | join(', ') -%}
    insert into {{ target_relation }} ({{ dest_cols_csv }})
    select {{dest_cols_csv}} from {{ tmp_relation.include(database=true, schema=true) }};

    drop table if exists {{ tmp_relation }};

{% endmacro %}

Had to switch to use the tmp_relation to retrieve the columns and then add a list of fields to insert into otherwise data type mismatches happen when inserting ordinally
I did want to use an intersect function to only get fields that were in both destination and tmp but there aren't any built in jinja functions. I might throw a PR in to extend but for now this suits my needs

[hovaesco]

@prgx-aeveri01 would you like to open a PR to fix incremental macro? Your snippet looks like a fix for the issue.

[prgx-aeveri01]

Done! I haven't update the .readme in case other changes were getting wrapped up into the released version. If you want me to do that, feel free to add comments into the PR

#49