[Bug] Batch ID for Hourly Microbatch Model fails in v1.9

[danlsn]

Is this a new bug in dbt-core?

• I believe this is a new bug in dbt-core (in v1.9 at least)
• I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

This is a bug that is fixed in v1.10 as per #11165 but is an issue that affects v1.9

When running a microbatch model with a batch size of hour, dbt fails with an SQL compilation error:

04:17:32  1 of 1 START sql microbatch model DBT_DLLEDU_staging_edu_sams.stg_sams__ps_ams_tcs_enr_su1_v2  [RUN]
04:17:32  Batch 1 of 1 START batch 2025-07-08 04:00:00+00:00 of DBT_DLLEDU_staging_edu_sams.stg_sams__ps_ams_tcs_enr_su1_v2  [RUN]
04:17:33  Unhandled error while executing 
Exception on worker thread. Database Error
  001003 (42000): SQL compilation error:
  syntax error line 1 at position 116 unexpected '04'.
04:17:33  Batch 1 of 1 ERROR creating batch 2025-07-08 04:00:00+00:00 of DBT_DLLEDU_staging_edu_sams.stg_sams__ps_ams_tcs_enr_su1_v2  [ERROR in 1.59s]
04:17:33  1 of 1 ERROR creating sql microbatch model DBT_DLLEDU_staging_edu_sams.stg_sams__ps_ams_tcs_enr_su1_v2  [ERROR in 1.60s]

However, in tracing the cause of this issue I noticed that the main branch has already had a fix implemented in the commit f26d822 which has seemingly been released in v1.10.3

Expected Behavior

When replacing the method MicrobatchBuilder.format_batch_start() with the following from @dbeatty10 and @QMalcolm, it works perfectly fine, even in v1.9.8

class MicrobatchBuilder:
    @staticmethod
    def format_batch_start(batch_start: datetime, batch_size: BatchSize) -> str:
        """Format the passed in datetime based on the batch_size.

        2024-09-17 16:06:00 + Batchsize.hour  -> 2024-09-17T16
        2024-09-17 16:06:00 + Batchsize.day   -> 2024-09-17
        2024-09-17 16:06:00 + Batchsize.month -> 2024-09
        2024-09-17 16:06:00 + Batchsize.year  -> 2024
        """
        if batch_size == BatchSize.year:
            return batch_start.strftime("%Y")
        elif batch_size == BatchSize.month:
            return batch_start.strftime("%Y-%m")
        elif batch_size == BatchSize.day:
            return batch_start.strftime("%Y-%m-%d")
        else:  # batch_size == BatchSize.hour
            return batch_start.strftime("%Y-%m-%dT%H")

Here is a snippet of the dbt logs

04:46:03  Running with dbt=1.9.8
04:46:03  Registered adapter: snowflake=1.9.4
[...]
04:47:33  Found 2980 models, 58 seeds, 6 operations, 3862 data tests, 1352 sources, 152 exposures, 1154 macros, 2 groups
04:47:33  
04:47:33  Concurrency: 20 threads (target='edu')
04:47:33
04:47:40  1 of 1 START sql microbatch model DBT_DLLEDU_staging_edu_sams.stg_sams__ps_ams_tcs_enr_su1_v2  [RUN]
04:47:40  Batch 1 of 1 START batch 2025-07-08T04 of DBT_DLLEDU_staging_edu_sams.stg_sams__ps_ams_tcs_enr_su1_v2  [RUN]
04:47:44  Batch 1 of 1 OK created batch 2025-07-08T04 of DBT_DLLEDU_staging_edu_sams.stg_sams__ps_ams_tcs_enr_su1_v2  [SUCCESS 0 in 4.73s]
04:47:44  1 of 1 OK created sql microbatch model DBT_DLLEDU_staging_edu_sams.stg_sams__ps_ams_tcs_enr_su1_v2  [SUCCESS in 4.74s]

Steps To Reproduce

1. using dbt-core v1.9.8 and dbt-snowflake v.1.9.4
2. configure a microbatch model to use batch_size='hour'
3. run the model

Relevant log output

create or replace temporary table DEV.DBT_DLLEDU_staging_edu_sams.stg_sams__ps_ams_tcs_enr_su1_v2__dbt_tmp_20250708 04:00:00+00:00

# SQL compilation error: syntax error line 1 at position 116 unexpected '04'.

Environment

- OS: Windows 11
- Python: 3.12.6
- dbt: 1.9.8 (dbt-snowflake 1.9.4)

Which database adapter are you using with dbt?

snowflake

Additional Context

As I understand it, there is a process to back propagate changes to previous versions of dbt-core.

Our team (RMIT University) is not quite ready to update our dbt-core installations to v1.10.x just yet, and we have a usecase where hourly microbatch models would be ideal.

[jessebrowning]

Thanks for this issue @danlsn. I'm using dbt-metricflow which currently doesn't support dbt-core 1.10.3, so this is a welcome request!