Search code examples
clickhousedbt

running dbt with clickhouse cluster: DB::Exception: Syntax error: failed at position 201 ('ON') (line 5, col 5): ON CLUSTER

I am trying to run dbt on a clickhouse cluster the first run works just fine and dbt creates tables on all nodes with no issue, the second run however is causing the following error:

DB::Exception: Syntax error: failed at position 201 ('ON') (line 5, col 5): ON CLUSTER

This only happens with my marts models, but my incremental tables are working fine

profiles.yaml

project:
  target: dev
  outputs:
    dev:
      type: clickhouse
      host: 127.0.0.1
      port: 8123
      user: dbt
      password: dbt
      schema: databasename
      cluster_mode: true
      cluster: 'company_cluster'

dbt_project.yml

models:
  project:
    mart:
      +materialized: table

fct/fct_table.sql

{{

    config(
        materialized='incremental',
        on_schema_change='fail',
        unique_key='id',
        incremental_strategy='delete+insert',
        tags=['table']

    )
}}


    SELECT   id,
             created_at,
             updated_at
    FROM src_table_c
    
    {% if is_incremental() %}
    
      where updated_at > (select max(updated_at) from {{ this }})
    
    {% endif %}

mart/mart.sql

WITH fct_table AS (
    SELECT * FROM {{ref('fct_table')}}
)

SELECT id,
       created_at
from fct_table
Solution

  • it got fixed by upgrading dbt-clickhouse to 1.4.8 https://github.com/ClickHouse/dbt-clickhouse/issues/193