When using force_batch=true with incremental models, it will fail if there is no data to write

[parsable-alex-antonison]

dbt version: 1.7.0
use case: I am processing CDC data from a database into Athena Iceberg tables.
issue: While I can successfully run the first load of the query, when I go to run an incremental model without any data, it will fail when it is attempting to query a table that does not exist.

1. Attempt to create a tmp table

create table "awsdatacatalog"."databasename"."tablename__dbt_tmp__tmp_not_partitioned"
.... (query that is looking for new records in the source table from the target table. it will return nothing because no additional data has been added to the source table)

2. Attempt to query partitions from the temp table

select distinct partition_1, partition_2 from "awsdatacatalog"."databasename"."tablename__dbt_tmp__tmp_not_partitioned" 
order by team_partition, team_id

3. And this last step is where it fails

select distinct partition_1, partition_2 from ("awsdatacatalog"."databasename"."tablename__dbt_tmp")
order by team_partition, team_id

[nicor88]

Adding a bit more context.
The issue occur also for simple models like:

{{ config(
    materialized='incremental',
    incremental_strategy='merge',
    unique_key='user_id',
    table_type='iceberg',
   force_batch='true'
) }}

select 1 as user_id, 'a' as user_name

The issue seems to be in get_partition_batches and create_table_as_with_partitions

Here are some possible solutions:

• if the underlying dataset is empty - create_table_as_with_partitions can create an empty table from SQL - no hurt/no pain - it's possible to check if __tmp_not_partitioned is empty and if it's the case, we just create an empty relation as tmp table.
• if the user try to use force_batch with not partitioned tables -> we have 2 options:
  • all works normally and force_batch it's just ignored, e.g. pass partitioning(bool) to safe_create_table_as
  • we raise an error and ask the user to remove force_batch from not partitioned tables
• if the user works with partitioned tables by the data is not more than 100 partitions partition batches everything should still work. - must be tested

@svdimchenko FYI.

[antonysouthworth-halter]

Oh, I didn't see this when I searched before opening dbt-labs/dbt-athena#656 , will close in favour of this one.

[antonysouthworth-halter]

I have a fix in progress on dbt-labs/dbt-athena#658