Intermittent refresh error with ODBC connection to ClickHouse

[mariomm17]

Hi everyone,

I’m using an ODBC connection to connect Power BI to a ClickHouse database, and I’m running into a difficult issue when refreshing the data.

➡️ The error message I’m getting is "ODBC: ERROR [HY000] Syntax error: Not a valid integer: {value}"

Note: The {value} part is a short text string that varies — it’s not always the same, and can be between 1 and 4 characters.

Please find at the end of the post an example of the complete error message I'm receiving.

I use the ODBC connection as my data source, and I write a custom SQL query for each table I need. That is, each query sent to ClickHouse becomes a table in my Power BI model.

The error occurs intermittently — sometimes it affects one table, other times a different one, and occasionally it doesn’t appear at all. It’s not tied to a single table; it happens across several.

I tried to fix it by adding an explicit CAST at the end of every query (I’m attaching an example of the code), but the error still showed up. So I’m starting to suspect that Power BI might be applying some additional transformation that I can’t identify.

-- Table 'FACT_g-funnel-contents'

select 
    cast(sector as String) as sector,
    cast(company as String) as company,
    cast(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,
    cast(funnel_step_n_elements as Int64) as funnel_step_n_elements
from output

The biggest challenge is that the error message doesn’t specify which field is causing the issue, making it very difficult to troubleshoot.

Has anyone experienced something similar? Is there a better way to debug this kind of error when Power BI doesn’t provide specific details?

➡️ I’m attaching an example of the error message to help clarify, for a case where I got the error on table 'FACT_g-funnel-contents', whose query ending I shared as an example above. 

Data source error: {"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"ODBC: ERROR [HY000] Syntax error: Not a valid integer: y"}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ErrorCode","detail":{"type":1,"value":"10478"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourceKind","detail":{"type":1,"value":"Odbc"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourcePath","detail":{"type":1,"value":"database=foreign-policy;driver={ClickHouse ODBC Driver (Unicode)};host=xrx1q4fjb1.germanywestcentral.azure.clickhouse.cloud;maxmemoryusage=17179869184;port=8443;sockettimeout=240000"}},{"code":"Microsoft.Data.Mashup.ValueError.OdbcErrors","detail":{"type":1,"value":"#table({\"SQLState\", \"NativeError\", \"Message\"}, {})"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"DataSource.Error"}}],"exceptionCulprit":1}}} Table: FACT_g-funnel-contents.

Thanks in advance for any help!

[v-echaithra]

Hi @mariomm17 ,

We would like to confirm if you've successfully resolved this issue or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.

Thank you for your patience and look forward to hearing from you.

Best Regards,
Chaithra E.

[v-echaithra]

Hi @mariomm17 ,

We would like to confirm if you've successfully resolved this issue or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.

Thank you for your patience and look forward to hearing from you.

Best Regards,
Chaithra E.

[v-echaithra]

Hi @mariomm17 ,

Thank you for @wardy912  your inputs.

We wanted to follow up to see if the issue you reported has been fully resolved. If you still have any concerns or need additional support, please don’t hesitate to let us know, we’re here to help.

We truly appreciate your patience and look forward to assisting you further if needed.

Warm regards,
Chaithra E.

[wardy912]

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.