[Bug] MicrobatchBuilder generates invalid table names with spaces for hourly batches

[pei0804]

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 using incremental_strategy="microbatch" with batch_size="hour", MicrobatchBuilder generates a batch ID that contains spaces and special characters. This leads to invalid temporary table names and SQL syntax errors in database adapters.

Example error from Snowflake adapter:

SQL compilation error: syntax error line 1 at position 150 unexpected '00'.

The error occurs because the generated temporary table name contains spaces and timezone information:

create or replace temporary table [...].model_name__dbt_tmp_20241218 00:00:00+00:00

Expected Behavior

MicrobatchBuilder should generate a valid batch ID without spaces or special characters for hourly batches, similar to how it handles daily batches.

For example:

create or replace temporary table [...].model_name__dbt_tmp_20241218T000000Z

Steps To Reproduce

1. Create a model with microbatch incremental strategy:

{{
    config(
        materialized="incremental",
        incremental_strategy="microbatch",
        begin="2022-01-01T00:00:00",
        batch_size="hour",
        event_time="_partition_hourly",
        unique_key="_partition_hourly",
        lookback=1,
    )
}}

3. Run the model with dbt run --select hoge --event-time-start "2024-12-18T00:00:00" --event-time-end "2024-12-18T01:00:00"
4. Observe the SQL syntax error due to invalid temporary table name

Relevant log output

02:13:16  Using snowflake connection "model.fuga.hoge"
02:13:16  On model.fuga.hoge: create or replace temporary table dbname.schemaname.modelname__dbt_tmp_20241218 00:00:00+00:00
         as
        (

with
    source as (

        select *
        from dbname.schemaname.modelname

    ),

    deduplicated as (
        select *
    from source
    qualify
        row_number() over (
            partition by id
            order by _partition_hourly asc, bid_at asc
        ) = 1
    )

select *
from deduplicated
        )
02:13:17  Snowflake adapter: Snowflake query id: hoge
02:13:17  Snowflake adapter: Snowflake error: 001003 (42000): SQL compilation error:
syntax error line 1 at position 150 unexpected '00'.
02:13:17  Unhandled error while executing
Exception on worker thread. Database Error
  001003 (42000): SQL compilation error:
  syntax error line 1 at position 150 unexpected '00'.
02:13:17  Batch 1 of 1 ERROR creating batch 2024-12-18 00:00:00+00:00 of dbname.schemaname.modelname [ERROR in 0.39s]

Environment

- OS: macOS Sonoma 14.6.1
- Python: 3.12.6
- dbt-core: 1.9.1
- dbt-snowflake: 1.9.0

Which database adapter are you using with dbt?

snowflake

Additional Context

The issue occurs in several parts of the codebase:

1. First, the batch ID is set in core/dbt/task/run.py:
   https://github.com/dbt-labs/dbt-core/blob/main/core/dbt/task/run.py#L352-L356

self.node.batch = BatchContext(
    id=MicrobatchBuilder.batch_id(batch[0], self.node.config.batch_size),
    event_time_start=batch[0],
    event_time_end=batch[1],
)

2. This batch ID is then used in the global make_temp_relation macro to create unique temporary table names:
   https://github.com/dbt-labs/dbt-adapters/blob/5407391c5cef22a5c0431daa469d6a8295c026d8/dbt/include/global_project/macros/adapters/relation.sql#L9-L16

{% macro make_temp_relation(base_relation, suffix='__dbt_tmp') %}
  {#-- This ensures microbatch batches get unique temp relations to avoid clobbering --#}
  {% if suffix == '__dbt_tmp' and model.batch %}
    {% set suffix = suffix ~ '_' ~ model.batch.id %}
  {% endif %}
  {{ return(adapter.dispatch('make_temp_relation', 'dbt')(base_relation, suffix)) }}
{% endmacro %}

3. The problematic batch ID generation occurs in the MicrobatchBuilder class:
   

   dbt-core/core/dbt/materializations/incremental/microbatch.py

   Lines 195 to 203 in a175793

   	@staticmethod
   	def batch_id(start_time: datetime, batch_size: BatchSize) -> str:
   	return MicrobatchBuilder.format_batch_start(start_time, batch_size).replace("-", "")
   	@staticmethod
   	def format_batch_start(batch_start: datetime, batch_size: BatchSize) -> str:
   	return str(
   	batch_start.date() if (batch_start and batch_size != BatchSize.hour) else batch_start
   	)

@staticmethod
def batch_id(start_time: datetime, batch_size: BatchSize) -> str:
    return MicrobatchBuilder.format_batch_start(start_time, batch_size).replace("-", "")

@staticmethod
def format_batch_start(batch_start: datetime, batch_size: BatchSize) -> str:
    return str(
        batch_start.date() if (batch_start and batch_size != BatchSize.hour) else batch_start
    )

For hourly batches (batch_size="hour"), format_batch_start returns str(batch_start) which generates a datetime string like "2024-12-18 00:00:00+00:00". While batch_id removes hyphens, it does not handle spaces and timezone information, resulting in an invalid table name.

For non-hourly batches (day/month/year), it correctly uses batch_start.date() which produces a clean format like "2024-12-18", and after removing hyphens becomes "20241218".

The issue stems from the fact that the batch ID flows from run.py through the Jinja templating system and into SQL table names without proper sanitization for hourly batches. The fix would likely involve modifying the format_batch_start method to ensure hourly timestamps use a database-friendly format (e.g., ISO format "20241218T000000Z") similar to how it handles daily batches.

[yanithx]

I am experiencing the same issue!

SQL compilation error: syntax error line 1 at position 87 unexpected '12'.

create or replace  temporary view RAW.xxx.campaign_email_events__dbt_tmp_20241220 12:00:00+00:00
  
   as (
...    

[syakesaba]

+1

[dbeatty10]

Thanks for reporting this @pei0804 (and to @yanithx and @syakesaba for confirming)!

I was able to reproduce this issue -- see "reprex" below for details.

Potential fix

@pei0804 very nice research on the relevant areas of code 🤩.

Making the following modifications to this code worked when I tested it out -- although our final fix might look a bit different:

    @staticmethod
    def batch_id(start_time: datetime, batch_size: BatchSize) -> str:
        return MicrobatchBuilder.format_batch_start(start_time, batch_size)

    @staticmethod
    def format_batch_start(batch_start: datetime, batch_size: BatchSize) -> str:        
        # If we want a date only
        if batch_size != BatchSize.hour:
            return batch_start.strftime('%Y%m%d')  # e.g. "20241218"

        # If we want date + time
        return batch_start.strftime('%Y%m%dT%H%M%SZ')  # e.g. "20241218T000000Z"

This changed the SQL from this:

create or replace temporary table analytics_dev.dbt_dbeatty.my_microbatch_model__dbt_tmp_20250110 14:00:00+00:00

to this instead:

create or replace temporary table analytics_dev.dbt_dbeatty.my_microbatch_model__dbt_tmp_20250110T140000Z

Note:

• I didn't test out what would happen if the batch_size is something else besides hour.
• also didn't test with positive or negative UTC offsets or naive timestamps

Repex

Create this file:

models/my_microbatch_model.sql

{{
    config(
        materialized="incremental",
        incremental_strategy="microbatch",
        begin="2025-01-10T00:00:00",
        batch_size="hour",
        event_time="_partition_hourly",
        unique_key="_partition_hourly",
        lookback=1,
    )
}}

select 1 as id, {{ dbt.current_timestamp() }} as _partition_hourly

Run this command:

dbt run -s my_microbatch_model

Get this error:

15:50:51  1 of 1 START sql microbatch model dbt_dbeatty.my_microbatch_model .............. [RUN]
15:50:51  Batch 1 of 2 START batch 2025-01-10 14:00:00+00:00 of dbt_dbeatty.my_microbatch_model  [RUN]
15:50:52  Unhandled error while executing 
Exception on worker thread. Database Error
  001003 (42000): SQL compilation error:
  syntax error line 1 at position 98 unexpected '14'.

[dearhari]

I got the same error for hourly batch size..

Model config
{{ config( materialized = 'incremental', incremental_strategy = 'microbatch', event_time='event_timestamp', begin='2025-01-12', batch_size='hour', loopback = 48 ) }}

Error message

(dbt-latest) PS C:\datamesh\data-ai-kea-datamart-dbt-development> dbt build --select web_events
03:38:54 Running with dbt=1.9.1
03:38:56 Registered adapter: snowflake=1.9.0
03:38:57 Found 10 models, 1 analysis, 8 sources, 791 macros
03:38:57
03:38:57 Concurrency: 8 threads (target='non_prod')
03:38:57
03:39:00 1 of 1 START sql microbatch model DBT_DEVELOPMENT_VELAYH.web_events ............ [RUN]
03:39:00 Batch 1 of 2 START batch 2025-01-12 02:00:00+00:00 of DBT_DEVELOPMENT_VELAYH.web_events [RUN]
03:39:01 Unhandled error while executing
Exception on worker thread. Database Error
001003 (42000): SQL compilation error:
syntax error line 1 at position 98 unexpected '02'.
03:39:01 Batch 1 of 2 ERROR creating batch 2025-01-12 02:00:00+00:00 of DBT_DEVELOPMENT_VELAYH.web_events [ERROR in 1.22s]
03:39:01 Batch 2 of 2 SKIP batch 2025-01-12 03:00:00+00:00 of DBT_DEVELOPMENT_VELAYH.web_events [SKIPPED in 0.00s]
03:39:01 1 of 1 ERROR creating sql microbatch model DBT_DEVELOPMENT_VELAYH.web_events ... [ERROR in 1.22s]
03:39:01
03:39:01 Finished running 1 incremental model in 0 hours 0 minutes and 4.39 seconds (4.39s).
03:39:01
03:39:01 Completed with 1 error, 0 partial successes, and 0 warnings:
03:39:01
03:39:01 ERROR
03:39:01
03:39:01 Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1
(dbt-latest) PS C:\datamesh\data-ai-kea-datamart-dbt-development>