topic Re: Intermittent Failures during Fabric Warehouse Table Updates with dbt on Azure Container Instances in Data Warehouse

Intermittent Failures during Fabric Warehouse Table Updates with dbt on Azure Container Instances

ShunOshidari — Fri, 06 Oct 2023 04:48:19 GMT

I’m writing to ask if anyone else has encountered the following error while updating tables on the Fabric Warehouse: (‘08S01’, ’[08S01] [Microsoft][ODBC Driver 18 for SQL Server]TCP Provider: Error code 0x274C (10060) (SQLExecDirectW)’).

This issue started occurring at some unknown point and happens sporadically, making it difficult to write a support ticket. It doesn’t always happen, and the exact conditions causing it remain unclear. That’s why I’m seeking to gather information about this issue.

For context, I’m using the dbt-fabric data transformation framework to update data in the Fabric warehouse. The framework seems to use pyodbc to update the Warehouse via the SQL endpoint.

https://github.com/microsoft/dbt-fabric

In the job of updating around 50 tables, all can be successful at times, but there are also instances where a few fail. I'm running dbt on Azure Container Instances.

If anyone else has experienced a similar issue, I would greatly appreciate your input.

Re: Intermittent Failures during Fabric Warehouse Table Updates with dbt on Azure Container Instances

Anonymous — Fri, 06 Oct 2023 15:13:31 GMT

Hi ShunOshidari ,
Thanks for using Fabric Community.

The error code 0x274C (10060) indicates that the SQL Server client cannot connect to the server. This could occur for a number of reasons, including:

The firewall on the server is blocking the connection.
The server is not configured to accept remote connections.
The server is down or unreachable.
There is a network problem between the client and the server.

Please refer these links for help : Link1

Link2

Hope this helps. Do let us know if you have any further issues.

Re: Intermittent Failures during Fabric Warehouse Table Updates with dbt on Azure Container Instances

Anonymous — Mon, 09 Oct 2023 07:01:04 GMT

Hi ShunOshidari ,
We haven’t heard from you on the last response and was just checking back to see if your issue got resolved. Otherwise, will respond back with the more details and we will try to help.

Re: Intermittent Failures during Fabric Warehouse Table Updates with dbt on Azure Container Instances

ShunOshidari — Tue, 10 Oct 2023 00:41:49 GMT

Thank you for your response. Unfortunately, the issue is not yet resolved. I did not quite understand how your reply could lead to a resolution. It seems there might be some misunderstanding about our current situation.

As I mentioned in my initial post, we are using the Fabric Warehouse database. It seems unlikely that the server-side firewall is blocking communication. Also, as stated before, it's not that we can't update at all - only about 0 to 4 out of 50 attempts randomly fail. This means that over 90% are processed correctly. If the server is not configured to accept remote connections, we would expect a 0% success rate.

Given this random behavior, it's challenging to investigate further details, which is why I posted on this forum hoping to gather information from others facing similar issues.

If anyone has experience with these types of issues in Fabric Warehouse and can share the conditions under which they occur, your information would be greatly appreciated.

Re: Intermittent Failures during Fabric Warehouse Table Updates with dbt on Azure Container Instances

Anonymous — Wed, 11 Oct 2023 08:45:24 GMT

Hi ShunOshidari ,

Apologies that you are still facing this issue.

Please make sure that all the pre-requisites mentioned in this section has been taken care and also make sure to follow the step by step guide to set up dbt in microsoft fabric as mentioned in this tutorial: Link1
If 'dbt test' step fails, try to jump to next step 'dbt run' before test as discussed in this video: Link2

Additionally, if issue still persists, then try the following workaround:

We have several options to mitigate this issue:

Enable connection pooling in Linux has to be enabled both by the application and in the driver manager, and is off by default might and also we are going to improve the connection at client side.
Change to redirect, Link3 improving the connection at server side and remove idle connection threshold.
Implement the retry logic as follow in Python.

Hope this helps. Please let us know if you have any further queries.

Re: Intermittent Failures during Fabric Warehouse Table Updates with dbt on Azure Container Instances

ShunOshidari — Wed, 11 Oct 2023 12:09:31 GMT

Thank you for your advice.
I looked at the "Link3" page about "Change to redirect", but does this also apply to Fabric Warehouse? It looked like a server-side setting, but if it's something that can be set on the user side in Fabric Warehouse, could you please tell me if there are any documents or procedures written about it?

Re: Intermittent Failures during Fabric Warehouse Table Updates with dbt on Azure Container Instances

Anonymous — Thu, 12 Oct 2023 10:43:47 GMT

Hi ShunOshidari ,
Please refer these documents for your help.

Hope this helps. Please let me know if you have any further queries. Glad to help.