[Feature] Build WHERE filter for reading data of microbatch incremental builds from properties

[QMalcolm]

Housekeeping

• I am a maintainer of dbt-core

Short description

In #10624 we're building a new type of incremental model strategy, microbatch. The microbatch strategy is cool for a lot of reasons, and all of those can be read about elsewhere. This feature is that the incremental filter for an incremental model should automatically get generated. What that means is that when using the microbatch strategy for an incremental model, one will NO LONGER need to put the following in the model

-- my_model.sql
...
{% if is_incremental() %}

  -- this filter will only be applied on an incremental run
  -- (uses >= to include records whose timestamp occurred since the last run of this model)
  -- (If event_time is NULL or the table is truncated, the condition will always be true and load all records)
  where event_time >= (select coalesce(max(event_time),'1900-01-01') from {{ this }} )
{% endif %}

INSTEAD the incremental where filter will automatically be generated when appropriate.

Acceptance criteria

• a "where filter" is automatically generated for microbatch strategies
  • depends on event_time, event_time_start, event_time_end, batch_size properties being present

Suggested Tests

1. A "where filter" is only generated for microbatch incremental models
2. Generated "where filter" is correct in regards to event_time, event_time_start, event_time_end, batch_size properties

Impact to Other Teams

Adapters?

Will backports be required?

No

Context

Depends on:

1. [Feature] --event-start-time and --event-end-time as CLI flags for specifying microbatch start and end times #10635
2. [Feature] Allow setting of batch_size as a top-level property of a model #10637

[tsafacjo]

can I pick it ?

[MichelleArk]

Hey @tsafacjo ! What do you mean by 'pick'? If you mean pick up this PR for development, we've already got that work in flight in #10594 by the maintainer team. If you mean, "can users set this where filter?", we are building out CLI flags as well as other model-level configurations to do so here: #10635

[tsafacjo]

Actually it's was more about pick up this PR for development.
Thanks.

[QMalcolm]

Resolved by #10594