This is the behavior if on_schema_change: ignore, which is set by default, and on older versions of dbt.
If you add a column to your incremental model, and execute a dbt run, this column will not appear in your target table.
Similarly, if you remove a column from your incremental model, and execute a dbt run, this column will not be removed from your target table.
When I add a column it ignores it, but when I remove a column it raises an error. I tested it in 3 different adapters (snowflake, bigquery, duckdb) and it throws the same error. It says it can't find the missing column.
Expected Behavior
I expected it not to throw an error and just ignore it (add nulls maybe).
Steps To Reproduce
Run this model
{{ config(
materialized='incremental'
, on_schema_change='ignore'
) }}
select
1 as id
, 100 as value
, 'created' as status
, cast('2024-01-01' as date) as date
union all
select
2 as id
, 200 as value
, 'processed' as status
, cast('2024-01-02' as date) as date
Then remove a column and try rerunning it. In this case, I am also adding a new column
{{ config(
materialized='incremental'
, on_schema_change='ignore'
) }}
select
3 as id
, 300 as value
, True as is_processed
, cast('2024-01-03' as date) as date
Relevant log output
Snowflake:
SQL compilation error: error line 3 at position 34
invalid identifier 'STATUS'
BigQuery:
Unrecognized name: status at [38:25]
DuckDB:
Binder Error: Referenced column "status" not found in FROM clause!
Is this a new bug?
Current Behavior
In the docs it says
When I add a column it ignores it, but when I remove a column it raises an error. I tested it in 3 different adapters (snowflake, bigquery, duckdb) and it throws the same error. It says it can't find the missing column.
Expected Behavior
I expected it not to throw an error and just ignore it (add nulls maybe).
Steps To Reproduce
Run this model
Then remove a column and try rerunning it. In this case, I am also adding a new column
Relevant log output
Environment
Additional Context
version 1.8
I also tested on 1.3 to test an older version, and same problem