Dataflows Refreshment is not working multiple failure log

[CheSan]

I’ve set up several dataflows in Power BI, scheduled to refresh every 30 minutes in sequence. The data source is AWS Redshift.

We’ve been experiencing frequent failures due to Redshift’s cold start issue. To mitigate this, I created dummy dataflows at the beginning of the refresh chain to warm up Redshift before the actual queries run  and that workaround is working for now.

However, we’re still seeing occasional failures that aren’t consistent day to day. The error logs point to multiple potential causes. The gateway team has already increased capacity, so I’m trying to understand what else might be contributing to these failures.

What additional checks or configurations can I explore to prevent the error shown below? Any suggestions on how to further stabilize the refresh process would be appreciated.

[Nasif_Azam]

Hey  @CheSan ,

Thank you for sharing the details and confirming that the dataflows are running on Gen1. Based on the error logs and your setup, here are some additional considerations:

1) Gateway & Gen1 Limitations

• Gen1 dataflows rely heavily on the gateway for refresh orchestration. If multiple refreshes overlap, gateway resource contention can cause intermittent failures.
• Ensure the gateway is updated to the latest version and check logs for spikes during refresh windows.

Power BI DataGateway for AWS Redshift 

2) Consider Migrating to Dataflows Gen2

• Gen2 offers:
• Improved retry logic for transient errors.
• Better resource isolation and parallel refresh handling.
• Enhanced monitoring and diagnostics.
• Migration can significantly reduce failures caused by gateway bottlenecks.

Understand and optimize dataflows refresh

Troubleshoot Refresh Scenarios – Power BI

3) Timeout & Connection Settings

• Increase ODBC command timeout in Power Query M code:

Sql.Database("server", "database", [CommandTimeout = #duration(0, 0, 30, 0)])

• This helps avoid failures during long-running queries.

Dataflow Timeout

4) Redshift Optimization

• Enable KeepAlive or persistent connections in ODBC settings.
• Use materialized views or pre-aggregated tables for heavy queries.
• Monitor WLM queues for query performance during refresh windows.

Connect Redshift to Power BI: A Comprehensive Guide

5) Retry & Scheduling

• Gen1 does not have robust retry logic, so consider using Power Automate to trigger retries for failed refreshes.
• Add buffer time between refreshes to reduce gateway contention.

Power BI Dataflow Refresh: A Comprehensive Guide

6) Address ODBC HY000 Errors

• These errors often indicate transient network issues or IAM misconfigurations.
• Verify:
• Latest Amazon Redshift ODBC driver installed.
• Correct IAM profile and permissions.
• Network stability between gateway and Redshift cluster.

Using an Amazon Redshift ODBC driver on Microsoft Windows

AWS Blog – Integrate Power BI with Redshift

Best Regards,
Nasif Azam

[v-menakakota]

Hi @CheSan ,
Thanks for reaching out to the Microsoft fabric community forum.

I would also take a moment to thank @Nasif_Azam   , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference. 
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.

Best Regards, 
Community Support Team 

[v-menakakota]

Hi @CheSan ,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.

Best Regards, 
Community Support Team 

[CheSan]

By the way Just adding a note my current gateways are Dataflows GEN1

[GilbertQ · Accepted solution]

Hi @CheSan 

The error is indicating that there's no connection to your Redshift server. This could possibly be either the Redshift instance hasn't started or potentially something else. What happens if you try and connect from your gateway server to Redshift? Does it connect immediately or what happens?