Intermittent failures on dbt jobs due to Fabric connection error

[sophiad96]

A dbt user is experiencing intermittent job failures due to the following error occurring on different models:

('IMC06', '[IMC06] [Microsoft][ODBC Driver 18 for SQL Server]The connection is broken and recovery is not possible. The connection is marked by the client driver as unrecoverable. No attempt was made to restore the connection. (0) (SQLExecDirectW)')

This is causing frequent failures in job runs for this user, which impacts their production data.

[prdpsvs]

@sophiad96 , This is a transient error. The adapter is not using ConnectRetryCount property, which is exposed by ODBC. ODBC driver will attempt to automatically reestablish the connection if ConnectRetryCount and ConnectRetryInterval are provided. Let me add these options to the connection string to recover the connection. I will do a patch release 1.7.4 this week to handle this issue. I will use retries property in profiles.yaml file for ConnectRetryCount and add ConnectRetryInterval to profiles.yaml default to 2 seconds.

However, adapter is currently retrying connections manually. Are you providing retry option in the profiles.yaml? If not, please provide that and see if it helps.

[sophiad96]

@prdpsvs thank you for this information! The user currently has their retries set to 5.

[sophiad96]

@prdpsvs Curious if we have any updates to share with the customer?

[prdpsvs]

@sophiad96 , I came to know that there are connection drops reported by a few customers who are not using dbt as well. This issue is related to Fabric DW itself. However, the latest patch 1.7.4 will be available by tomorrow with ConnectRetryCount in the connection string.

[TheBishop-98]

When will this patch be available in dbt Cloud?

[amychen1776]

@SaintofSpadesSOA this patch should already be in dbt Cloud - has it resolved your issues?

[TheBishop-98]

@amychen1776 No. We have been experiencing the same errors since the aforementioned release above. Retries are still set at 5. It's does not always occur, but is still seen quite often.

[TheBishop-98]

@prdpsvs Is there any timeline to the issue being fixed by Microsoft pertaining to the Fabric DW? Have any of the affected customers you've previously mentioned found any workarounds to the issue? I encounter this issue daily which has pretty much thrown all reliability/dependability out the window with this tool.

[prdpsvs]

@TheBishop-98 , Are you still facing this issue? the adapter has a few configurations included in V1.8.9 including connection pooling and retrycount and retryconnect interval.