Update the remote_target_path config docs to reflect what all files are uploaded to remote

[pankajkoti]

It’s currently observed that users may assume, after reading the documentation, that all files from the target directory of the dbt project are uploaded to the remote target directory. However, this isn’t yet the case. The remote_target_path configuration, introduced in PR #1224, currently only uploads files from the compiled directory within the target directory of the dbt project—and only when the execution mode is set to ExecutionMode.AIRFLOW_ASYNC. We need to update the documentation for this configuration to clarify this behavior and indicate that additional files will become available as we expand support for uploading them within the operators.

Reference where users assumed https://apache-airflow.slack.com/archives/C059CC42E9W/p1730206906312459.
The question in that Slack conversation said

Hi, folks! I'm trying to configure [remote_target_path](https://astronomer.github.io/astronomer-cosmos/configuration/cosmos-conf.html#remote-target-path) but I doesn't send any file to the s3 location that I've set. What else is needed to configure it?

Acceptance Criteria

• Docs says what all target directory files are currently uploaded in the remote target path
• Mention the future work in the docs for supporting more files to be uploaded to remote target path