Intermittent refresh error with ODBC connection to ClickHouse

Hi mariomm17 

 I'll be honest, I haven't faced this issue before and don't even know where to start to fix it, but your post has been viewed 70 times with no attempt at an answer which is very rare so I wanted to try and help. Here is an AI generated response. I hope it's helpful but I can't guarantee it will be.

Understanding the Error

The error message:

ODBC: ERROR [HY000] Syntax error: Not a valid integer: y

...suggests that ClickHouse is trying to interpret a string value ("y" in this case) as an integer, and failing. This typically happens when:

• Power BI or the ODBC driver implicitly casts a column to an integer.
• The column contains mixed types (e.g., mostly integers but some strings).
• There’s a type mismatch between what Power BI expects and what ClickHouse returns.

🧠 Why It’s Intermittent

The intermittent nature of the error suggests that:

• The problematic value only appears in some refreshes, possibly due to data changes or query folding.
• Power BI might be inferring types based on a sample of the data, and then applying transformations that don’t match the full dataset.

✅ Steps to Troubleshoot and Fix

1. Double-check the source data

Ensure that the column you're casting to Int32 or Int64 (like funnel_step_id or funnel_step_n_elements) never contains non-numeric strings. Even a single "y" or "null" string can cause this.

You can test this with a query like:

SELECT funnel_step_id
FROM output
WHERE funnel_step_id NOT LIKE '%[^0-9]%'

Or better:

SELECT funnel_step_id
FROM output
WHERE NOT isInt(funnel_step_id)

If isInt() isn’t available, you can use regex or try casting and catching errors.

2. Use tryCast() instead of cast()

ClickHouse supports tryCast() which returns NULL instead of throwing an error if the cast fails:

SELECT 
    cast(sector as String) as sector,
    cast(company as String) as company,
    tryCast(funnel_step_id as Int32) as funnel_step_id,
    cast(funnel_step_name as String) as funnel_step_name,
    toDate(publication_date_day) as publication_date_day,
    tryCast(funnel_step_n_elements as Int64) as funnel_step_n_elements
FROM output

This can prevent the refresh from failing due to bad values.

3. Disable query folding

Power BI sometimes rewrites queries during refresh. To prevent this:

• In Power BI, go to Advanced Editor and wrap your query with Value.NativeQuery(...) to ensure it’s passed directly to ClickHouse.
• Example:

let
    Source = Odbc.DataSource("dsn=ClickHouseDSN"),
    Query = "
        SELECT ...
        FROM output
    ",
    Result = Value.NativeQuery(Source, Query)
in
    Result

4. Check Power BI data types

After loading the data, check the Data Type in Power BI for each column. If Power BI expects an integer but gets a string, it might try to convert it during refresh.

5. Log or isolate the failing rows

If possible, create a diagnostic query that logs or filters out rows where casting fails. This can help you identify problematic data.