[Feature] Update ignore behaviour for on_schema_change to backfill nulls for removed columns, instead of failing

[joellabes]

Is this your first time submitting a feature request?

• I have read the expectations for open source contributors
• I have searched the existing issues, and I could not find an existing issue for this feature
• I am requesting a straightforward extension of existing dbt functionality, rather than a Big Idea better suited to a discussion

Describe the feature

When a column is removed from an incremental model, and its on_schema_change behaviour is set to ignore, it should backfill a null instead of failing.

@RatandeepSharma recently opened dbt-labs/docs.getdbt.com#5978 in the docs repo after being confused by on_schema_change: ignore failing when a column is removed. While investigating that, I tripped over dbt-labs/dbt-bigquery#1102 (comment), which proposed:

• A new classic mode, wired as the current ignore mode
• Fixing ignore to actually ignore removed columns

on_schema_change	In case of new columns...	In case of removed columns...
classic	columns ignored	run failed
ignore	columns ignored	columns ignored [i.e. insert null]
fail	run failed	run failed
append_new_columns	columns added	columns ignored [i.e. insert null]
sync_all_columns	columns added	columns removed

I agree that we should make it so that ignore doesn't cause the job to fail, but I don't think we need a whole new classic option anymore. Since that comment in early 2023, we've added support for behaviour change flags. I think that instead of adding the classic option, we just change the default behaviour of ignore to insert nulls and let people opt into the old behaviour if they depend on it.

Describe alternatives you've considered

Still adding a new mode. But given our commitment to backwards compatibility, we'd need to keep ignore behaving the same way and come up with a new name for the new behaviour, otherwise we'd have to go through a behaviour change flag anyway.

Who will this benefit?

People who don't want to drop a column after removing it from their model.

Are you interested in contributing this feature?

Not really, but it's incremental season right now so I bet someone could give it a gander!

Anything else?

No response