Can I use athena adapter to read files then write in RDS using dbt? Or do i have to make profiles & sources yamls use the same adapter?

Alternative Approaches

1. Use a Trino adapter as an intermediary (a common workaround close to the official dbt approach)

Trino (formerly PrestoSQL) is a distributed SQL engine capable of maintaining connectors for both Athena (Iceberg/Hive tables on S3) and PostgreSQL-compatible RDS. The configuration involves presenting only a single Trino connection to dbt, while Trino itself mediates both the reading of Athena/S3 sources and the reading/writing for RDS.

dbt (dbt-trino adapter)
└─ Trino
├─ Athena/S3 connector (read source)
└─ PostgreSQL connector (RDS, write destination)

Since dbt perceives this as a single adapter (Trino), it does not violate dbt's constraints. In fact, a Datameer blog post illustrates a concrete example of joining BigQuery and on-premises PostgreSQL within a single dbt project via Trino.

2. Perform ETL/ELT outside of dbt to pre-load data into RDS, then transform it using dbt (the most common design)

This is the intended usage pattern recommended by the official FAQ.

Extract data from Athena/S3 and load it into RDS first using tools like AWS Glue, Amazon AppFlow, or custom Python/Lambda scripts. Once loading is complete, perform transformations on the loaded tables using a dbt project that operates entirely within RDS (using only the RDS adapter).

3. Use Federated Query capabilities (possible depending on the RDS engine)

If the RDS instance is Aurora PostgreSQL, you can use postgres_fdw or AWS's federated query features to allow RDS to reference Athena/S3 data as if it were a local table. In this scenario, dbt connects exclusively to RDS (using the Postgres adapter) and references the external table definitions on the RDS side as sources. This allows dbt to continue using a single adapter.

In dbt, you cannot mix adapters between your profile and sources in the way you're describing. The adapter specified in your dbt profile determines how dbt connects to your data warehouse and executes transformations. All dbt models in a project use the same adapter defined in the profile.

For your use case of reading from S3 via Athena and writing to RDS, you have a few architectural options:

1. Use the Athena adapter throughout: Configure your dbt profile to use the Athena adapter. Your models would read from S3-based tables (cataloged in AWS Glue) and write transformed results back to S3. You cannot directly write to RDS from dbt when using the Athena adapter, as Athena is designed for querying data in S3, not writing to relational databases.

2. Use the RDS adapter with an intermediate step: If you need the final output in RDS, you would typically use the RDS adapter in your dbt profile. However, this means dbt would need to read from tables accessible via that RDS connection. You could set up a separate process (outside of dbt) to load data from S3 into staging tables in RDS first, then use dbt with the RDS adapter to perform transformations within RDS.

3. Orchestrate a multi-step pipeline: Use a workflow orchestration tool like Apache Airflow to coordinate the process. First, run dbt with the Athena adapter to transform data in S3, then use a separate task to load the results into RDS.

The fundamental limitation remains: dbt uses a single adapter per project, and that adapter determines both where dbt reads from and where it writes to. You cannot use Athena as a source adapter and RDS as a destination adapter within the same dbt project.
Sources
Serverless analytics pipelines using the Apache Spark engine in Amazon Athena | AWS Big Data Blog
Building scalable AWS Lake Formation governed data lakes with dbt and Amazon Managed Workflows for Apache Airflow | AWS Big Data Blog