[Bug] Initial models builds with microbatch incremental strategy resulting in row duplication

[bthomson22]

Is this a new bug in dbt-core?

• I believe this is a new bug in dbt-core
• I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

When creating a new model using the microbatch incremental strategy, the initial tmp table is a full copy of source data and is re-inserted, in full, for every batch. The delete queries are removing data from the approach batch range, but inserts are full-table loads.

The impact of this is heavily duplicated data on initial model builds, and increasing batch query times.

Expected Behavior

There's generally two ways I'd expect for this to happen:

1. The initial table creation is a one-time full_refresh, similar to existing incremental models (perhaps not ideal for very large tables).
2. The tmp table creation mirrors the event_time windows in the batch and only inserts values for the same period that were deleted. Currently there is no event_time filter on these tmp tables (probably ideal).

Steps To Reproduce

1. Create a new model with the microbatch incremental strategy. Ensure a table doesn't already exist with the same name.
2. Build the model without any event-time conditions, such as dbt build -s model_name
3. Review the logs and see the total table size re-inserted for each batch.

Relevant log output

No response

Environment

- OS: MacOS Sonoma 14.6.1
- Python: 3.8.10
- dbt: CLI 0.38.18 (versionless)

Which database adapter are you using with dbt?

snowflake

Additional Context

No response

[graciegoheen]

Looks like the issue was that there was no event_time configured for the direct parent(s) of the model.

Closing in favor of this issue #10926