Would it be possible to switch this package back to using psycopg2?

[pdebelak]

The switch in 1.5.0 to using redshift-connector has been a large breaking change causing issues for many people. Would people be open to switching back? Or at least making the underlying library configurable? At the moment, it seems like my company's options are to either stay on 1.4.0 forever or create a fork or wrapper adapter project that uses psycopg2 instead of redshift-connector.

[dbeatty10]

We've definitely had several issues during the switch from psycopg2 to redshift_connector.

However, we are unable to take on the additional maintenance burden of supporting multiple drivers, and redshift_connector is the direction we are committed to.

Our main approach will be to provide fixes or workarounds for dbt-redshift 1.5.x with dbt-redshift 1.4.x as the fall-back for those that can't upgrade yet due to a regression that affects them.

[szhang63]

We were pretty excited about some of the new features added to dbt-core 1.5, particularly the programmatic invocation of dbt CLI, and also the model governance features. Unfortunately, we've been stalled on being able to upgrade due to #429, and after the first couple of weeks of the 1.5 release It seems like we're likely to hit some other regressions as well. How ill-advised would it be to use dbt-core 1.5.x along with dbt-redshift 1.4.x?

[pdebelak]

I haven't tested it, but I'm starting to think our best path forward it to fork this repo and revert back before the commits in #251 and seeing if that works with dbt-core 1.5, possibly pulling in any commits not related to the change in the underlying connection library.

[Fleid]

Hi all,

That's including @kostek-pl, @alejandrofm, @IDoneShaveIt, @craigchurch, @Kamalakar4Pelago, @jaklan, @elongl and others I'm sure I'm forgetting, that have been impacted by the recent migration from psycopg2 to the redshift connector in 1.5.

For most of you that don't know me, I'm the product manager for adapters at dbt Labs. I made the decision to move forward with that initiative. We discussed it as a team, with people from the community, but in the end I'm the one accountable for it.

What brought us here

The first thing I want to say is that I'm sorry of the impact this move has had on your respective dbt projects, solutions, implementations. We were not expecting this level of regressions. I take the situation very seriously and we are working on fixing it.

To take a step back, we had that move in the backlog for a long time, from before I joined (10 months ago). We didn't act on it before because we lacked capacity. We inherit a lot of code from dbt-postgres in dbt-redshift, which translates into less work for us maintainers, but also limits differentiating features for Redshift dbt users. What changed recently is that the AWS Redshift team has started contributing to dbt-redshift. They wanted to tackle that one early, in part because it unlocks new connection methods for more advanced authentication setups. On paper it looked ideal.

What we've all experienced on that release is 2 teams learning to collaborate on a single code base. Please note that I'm not blaming any engineers here. As the product manager I'm the one accountable. I pick the scopes, I identify the risks, I set the teams up for success. I dropped the ball here. In hindsight, I should have had them ramp up on something more straightforward, to get to know the code base better. At the same time, the authentication piece is fairly unique and supposedly decoupled from the rest (you're connected or you're not), so it looked like an easy pull. We are all learning a lot here, and re-setting assumptions...

The good news for every dbt-redshift users, having the Redshift team contributing to the repo, is that you should see more activity here. That means more issues being processed faster. The downside, you're experiencing it right now. It's a learning curve, and it will get better quickly.

What's next

Because these things never happen in isolation, last week has been difficult for us at dbt Labs. Being good maintainers of dbt is a critical function of dbt Labs as a company. We are not shying away from this mandate. But we may be slower to react to this particular situation than usual.

In terms of the action plan, I still want us to persevere with the Redshift Connector. We released 1.5 one month ago (2 sprints), and the team was focusing on shipping Materialized Views for all adapters in 1.6b3. The first 2 big regressions have PRs that should go through quickly (#427, #429) in a patch release (1.5.1, potentially 1.5.2 for #429). I'm now looking at the ones created in the past 2 weeks, and it looks like 2 main issues still need to be investigated (% are misbehaving in #514, #549, and transactions are not committed in certain specific cases in #557, #562).

What would be a show stopper, is if the use of the connector definitely prevents us from supporting a base feature of dbt. I haven't seen that yet. I know #347 is painful to some, I fully understand that, but to me it doesn't meet the bar of either discarding the connector (and the new auth methods) or supporting both libraries (at a high engineering cost to us maintainers - which would be diverted from feature work).

I am known to change my mind when the situation requires it. So please keep voicing your concern, and keep me honest if you think I'm missing the point.

Thanks for your understanding.

[alejandrofm]

It resonates with me how the deploy passed tests against a Redshift database, and how can those tests be improved.
Another thing is, because this version isn't working, why not make a "hotfix" release a 1.5.1b "today" that sets by default:
connect_timeout: 999999
and remove it for the next versions, so the users don't have to hardcode variables for this version only.
Thanks

[dataders]

It resonates with me how the deploy passed tests against a Redshift database, and how can those tests be improved.

I couldn't have said it better myself! In some sense, there's certainly opportunity for a database+driver compatibility test suite to get a pulse on what is and isn't supported before we even enter dbt and jinja land. This hypothetical suite would help answer questions like:

• what's the query parameter placeholder? %s or ?
• are transactions supported?
• what data is returned after query execution?

basically all the things that the DB API 2.0 left as an exercise for the reader, that are actually very important

[pdebelak]

I think the other thing is - what kind of communication happened before the 1.5.0 release to make sure people knew this was a big change and to get testing from people during the pre-release phase? When I tested out 1.5 and found a bunch of breaking changes, I went to the release notes (which can be found here but don't appear to be linked anywhere) to figure out what happened and this is just listed as an "under the hood" change rather than what is in effect a totally new adapter.

Did I just miss the ahead-of-time communication about this? I didn't see anything in the dbt slack, but there might just be a channel that I'm not in where this was discussed and people got a chance to test this change before it was released.

[Fleid]

Hey @pdebelak, it boils down to a wrong assumption on our side. We really thought that replacing libraries would be simple, and would have a minimal impact on the main workloads.

Note that I want to look into is our response time more than this decision making. It's always better to not make mistakes, for sure, but that's not a reality. We should be faster at fixing them though.

[Fleid]

Hey everyone! We heard you loud and clear, and the team dropped everything to fix the situation.
It looks like we've solved everything on deck - could you please let us know if see anything misbehaving in 1.5.4?

[pdebelak]

1.5.4 still has 2 breaking changes for us (we need to set a region even though it isn't necessary to connect to redshift using the redshift_connector and we have to set sslmode to disabled when we didn't have it set before), but it is now possible for us to use it without any blocking bugs since we aren't affected by the bug with large seed files.

[dataders]

repeating back so that I understand you @pdebelak:

you and your team are unblocked to finally make use of 1.5, but, to do so, y'all needed to add both an sslmode and region parameter to your profile that weren't needed in 1.4?

For the region parameter, we've opened #484, and we're meeting with the Redshift team next week to try and find a way to not require region.

For the sslmode parameter, I'd love to know more, perhaps can you open an issue? Our understanding is that default value of sslmode in psycopg2 was prefer (sauce), which should be what was used by dbt-redshift==1.4.0. Would you mind opening an issue that includes the error message you get when trying to connect with 1.5.4 and no sslmode specified?

[pdebelak]

Yes, things work with sslmode set to disabled but fails with the following error message if not specified:

22:08:39  Encountered an error:
Database Error
  ('communication error', SSLCertVerificationError(1, '[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: unable to get local issuer certificate (_ssl.c:1131)'))

[dataders]

hey @pdebelak as an update, i'm working on another patch release that includes #485, which will solve the first issue you have. As for your SSL issue, it's a bit thornier, and I'd love to run some ideas by you if you're interested to meet with me? I sent you a DM on the community Slack last week with a link to my calendar. happy to chat async there as well. let me know!