The problem I’m having
I am using dbt-core to apply some transformations on data residing in S3 Tables via Redshift. I want to connect to Redshift using an IAM Principal, which has accesses granted to the S3 Tables via Lakeformation. But in Redshift profile, even though there is an IAM authentication option, still I have to provide a database user, which is used to authenticate the connection (temporary password generated using the IAM Role). But I can’t grant permissions to users other than IAM principals to S3 Tables via Lakeformation. I need to the IAM principal itself to establish the connection and read from S3 Tables.
The context of why I’m trying to do this
I have my raw data residing in S3 Tables. I want to use dbt to read this using Redshift Sprctrum, apply transformations and write to Native Redshift Tables.
What I’ve already tried
I have tried the authentication mechanism as in the dbt documentation page for Redshift Support. As far as I understand, the authentication is only via a Redshift Database user, which is not enough for me to read from S3 Tables and write to Redshift Native Tables. Could you please let me know if there is a way to do this?