dbt databricks snapshot materialization timestamp quotation problem

Question

I am trying to implement a snapshot materialization using the dbt cloud IDE and databricks, using the strategy = 'timestamp' and a updated_at-column called created_at.

This is the code to generate the snapshot model:

{% snapshot products_snapshot %}

    {{
        config(
            target_schema='bronze',
            strategy='timestamp',
            unique_key='id',
            updated_at='created_at'
        )

    }}

    select * from {{ source('landing', 'products') }}

{% endsnapshot %}

However, this does not work as intended and the run fails when using the dbt snapshot command.

Here is a part of the error logs in dbt cloud IDE, showing how the query is generated:

12:20:05 Failure in snapshot products_snapshot (snapshots/products_snapshots.sql)
12:20:05   Database Error in snapshot products_snapshot (snapshots/products_snapshots.sql)
  
  [PARSE_SYNTAX_ERROR] Syntax error at or near '12'. SQLSTATE: 42601 (line 4, pos 15)
  
  == SQL ==
  /* {"app": "dbt", "dbt_version": "2025.5.20+0929d26", "dbt_databricks_version": "1.10.1post1+500b635afd75fc0072f5f784406b5cc7eae9d098", "databricks_sql_connector_version": "4.0.3", "profile_name": "user", "target_name": "default", "node_id": "snapshot.dbt_databricks_project.products_snapshot"} */
  select * from (
          select 
      2025-05-21 12:20:04.833253
  ---------------^^^
   as dbt_snapshot_time
      ) as __dbt_sbq
      where false
      limit 0

As it seems, the timestamp is not enclosed in quotes and databricks SQL does not recognise the format.

I confirmed this by checking the query history in databricks itself:

select
  *
from
  (
    select
      *,
      md5(
        coalesce(cast(id as string), '') || '|' || coalesce(
          cast(2025 -05 -21 11 :35 :11.555602 as string),
          ''
        )
      ) as dbt_scd_id,
      2025 -05 -21 11 :35 :11.555602 as dbt_updated_at,
      2025 -05 -21 11 :35 :11.555602 as dbt_valid_from,
      coalesce(
        nullif(
          2025 -05 -21 11 :35 :11.555602,
          2025 -05 -21 11 :35 :11.555602
        ),
        null
      ) as dbt_valid_to
    from
      (
        select
          *
        from
          `dbt_project_catalog`.`landing`.`products`
      ) sbq
  ) as __dbt_sbq

Trying to execute this statement results in the same error. Does anyone know how to fix this problem with the snapshots in dbt?

mbillion · Accepted Answer · 2025-06-05 19:32:08Z

0

list out your columns instead of doing a select *
cast your columns to explicit types instead of allowing DBT/Databricks to interpret

answered Jun 5 at 19:32

mbillion

741 silver badge12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

dbt databricks snapshot materialization timestamp quotation problem

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related