[CT-1340] [Feature] Support Python models (dbt-py) on Redshift/AWS

[ChenyuLInx]

Is this your first time submitting a feature request?

• I have read the expectations for open source contributors
• I have searched the existing issues, and I could not find an existing issue for this feature
• I am requesting a straightforward extension of existing dbt-redshift functionality, rather than a Big Idea better suited to a discussion

Describe the feature

Background:

There's a Spark redshift connector. This would allow user to run python transformation code on EMR cluster that load data from Redshift, and write transformed data back to Redshift. The whole process is very similar to using Dataproc to run python models on GCP/BigQuery.

Items needed for implementation:

• If there's additional profile information needed for EMR cluster, we can add it as optional attributes at Credentials(existing example for bigquery).
• We need one macro to generate the final code to run on EMR cluster, Previous example for dbt-bigquery here
• Now that we have the profile info and the macro to generate final code, we need submission classes to submit python code to the cluster. Existing submission code for dbt-bigquery, Function to define in impl.py(link1, link2). And how those classes are being used by dbt-core(This doesn't need to be changed.)

Describe alternatives you've considered

No response

Who will this benefit?

No response

Are you interested in contributing this feature?

No response

Anything else?

No response

[lostmygithubaccount]

some relevant community discussion:

• https://getdbt.slack.com/archives/C03QUA7DWCW/p1661355103409279
• https://getdbt.slack.com/archives/C03QUA7DWCW/p1666385964965569
• https://getdbt.slack.com/archives/C03QUA7DWCW/p1663148313108629

per @colin-rogers-dbt, it may be easier to run on Glue than EMR. I personally have no preference -- whatever is easier for users to setup and faster to run on

[colin-rogers-dbt]

I wonder if the right thing here to do is just pick on to implement with a long term of supporting both, can we leverage the existing spark/glue adapters?

[lostmygithubaccount]

new redshift integration with Apache Spark announced: https://aws.amazon.com/blogs/aws/new-amazon-redshift-integration-with-apache-spark/

[saraleon1]

+1 dbt Cloud Enterprise Customer - This team is using Redshift and are really interested in leveraging python models in their dbt project. Their AWS contact passed this resource along as a possible way to run python + redshift.

[colin-rogers-dbt]

@lostmygithubaccount thanks for sharing, we should definitely look into leverage this!

This is particularly interesting as it means we don't need to recreate the issues with the dbt-bigquery adapter where we shoehorned dataproc in. Going forward we should adhere to the rough principle that an adapter should only know how to leverage the capabilities of a single data transformation tool. If we want to support emr/glue we should focus on multi-adapter/project so folk can use the existing adapters for those tools.

[lostmygithubaccount]

@saraleon1 great to hear! on the Python connector there, we should use that in dbt-redshift in general for making the connection in the way we use the similar Snowflake connector. however, I don't think that'll get us Python models -- or at least not the ones we want. I see it can read into numpy or pandas locally, but we want to maintain the principle of executing Python code remotely (in the "warehouse")

@colin-rogers-dbt very much agreed! as fyi BigQuery was working on more native integration (forwarded you an email)

cc: @jtcohen6

[ryadav03]

Any ETA on this?

[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.

[zdravis]

Hi, is this feature going to be implemented? @colin-rogers-dbt

[viniciusnunest]

+1 Great ideia !

[dnascimento]

:+1 dbt-fal supports this but not with incremetal

[joshua-pgatour]

Would love this feature please.

[ipcleary]

+1

[cschouten]

+1

[rohaldb]

+1

[rohaldb]

I would really like to move my pipelines into DBT but don't want to lock myself into having to use SQL for transformations. So unfortuantely until this is done i'm going to have to stick to Glue :/

[erees-embarkvet]

+1

[qoqajr]

• 1

[SkinnyPigeon]

We would love this feature

[marzaccaro]

Is this going to be implemented?

[MICHAELFOLA]

This will be a great addition. I was looking to use this today but unfortunately I will have to use sql again.

[spagnoloe-amenitiz]

+1, Redshift is one of the main DWHs in the market and this feature is a great tool for Data Science/Analytics. It is a pity not to have it there...