api: Return expected error when query fails

[rampage644]

At this moment service returns 500 http status code when query fails for some reason: be it parser error or anything else. snowflake connector should stop immediately but it retries instead

[eadgbear]

Is that something on our side or in the nature of the connector?

[osipovartem]

https://github.com/snowflakedb/snowflake-connector-python/blob/main/src/snowflake/connector/errorcode.py

https://github.com/snowflakedb/snowflake-connector-python/blob/main/src/snowflake/connector/errors.py
https://docs.snowflake.com/en/developer-guide/python-connector/python-connector-api#object-exception
https://peps.python.org/pep-0249/#databaseerror

Maybe we can return some correct https code and dbt connector code

[YaroslavLitvinov]

Dbt retry

Snowflake's endpoint client has retry logic. Supposedly enable_retry_reason_in_query_response Snowflake connection parameter can be used to disable it.
When retry-able http errror happens then client triggers retry and add following query_string params: clientStartTime, retryCount, retryReason. Retry triggered on err codes: >= 500 && < 600; 400, 403, 405, 408, 413, 429. Retry count is limited by 26 (from dbt log) .

Found useful following document: rfc9110 HTTP Semantics
For instance following http err codes are not retriable and could be used for returning errors related to a business logic:

404 - Not Found
409 - Conflict
410 - Gone
415 - Unsupported Media Type
422 - Unprocessable content

1. Initial request request{method=POST uri=/queries/v1/query-request?requestId=2b2f872d-3a6f-4573-a7b0-985e0784758d ...

2. Error code=500 expected in http response ERROR request{method=POST uri=/queries/v1/query-request?requestId=2b2f872d-3a6f-4573-a7b0-985e0784758d version=HTTP/1.1}: tower_http::trace::on_failure: response failed classification=Status code: 500 Internal Server Error ...

3. Attempted request with retry params ERROR request{method=POST uri=/queries/v1/query-request?requestId=2b2f872d-3a6f-4573-a7b0-985e0784758d&clientStartTime=1739383716286&retryCount=1&retryReason=500