[CT-471] Redshift: Retry n times on connection timeout

[barberscott]

Describe the feature

Runs/builds/tests can create hundreds of independent database connections depending on the size of the project and a single connection timeout due to transient network connections, EC2 load, or redshift load can cause an entire run to fail. Connection timeouts can, and most often are, transient and will often succeed on a retry.

Additional context

This would be similar to connect_retries on Snowflake.
See: https://github.com/dbt-labs/dbt-snowflake/pull/6/files

Who will this benefit?

Anyone using the redshift connector.

[nathaniel-may]

Hi @barberscott, safely retrying operations on our warehouses is definitely a good idea for exactly the reasons you describe. Today it looks like the the Snowflake connector for Python docs doesn't provide any of this functionality for us but it shouldn't be too much of a lift to implement ourselves since all the operations dbt runs on the warehouse are idempotent. We'd need to be purposeful with how we handle retries with hooks since you can write non-idempotent hooks even though that's not recommended. For example, we could retry as if they are always idempotent and document that the behavior of non-idempotent hooks is undefined.

For our own future reference, the comment in #5022 has some extra details for what might happen with other adapters that we should consider being consistent with.

[github-actions]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please remove the stale label or comment on the issue, or it will be closed in 7 days.