[Bug] Unable to Find Source Schemas on Database that is Different From the Target Database (Multi-Database Setup)

### Is there an existing issue for this?

- [X] I have searched the existing issues

### Describe the issue

Selecting the source database using relevant `_database` (`reddit_ads_database` etc.) variables in the `dbt_project.yml` file is not working as expected when the target database is not the same as the database where the data is stored. In this instance many "Please be aware" messages about being unable to find source tables in the database.

After reviewing the debug level logs and running a few tests, I observed that when running the staging models, a query against the Redshift `information_schema.tables` table is run but is pointing to the `information_schema` schema on the target database and not the data source database. Which is why the tables cannot be found.

### Relevant error log or model output

CLI Output:
```shell
Please be aware: The CAMPAIGN_REPORT table was not found in your REDDIT_ADS schema(s). The Fivetran dbt package will create a completely empty CAMPAIGN_REPORT staging model as to not break downstream transformations. To turn off these warnings, set the `fivetran__remove_empty_table_warnings` variable to TRUE (see https://github.com/fivetran/dbt_fivetran_utils/tree/releases/v0.4.latest#union_data-source for details).
```

Debugging Logs:
```
select
table_catalog as database,
table_name as name,
table_schema as schema,
'table' as type
from information_schema.tables
where table_schema ilike 'fivetran_reddit_ads'
and table_type = 'BASE TABLE'
union all
select
table_catalog as database,
table_name as name,
table_schema as schema,
case
when view_definition ilike '%create materialized view%'
then 'materialized_view'
else 'view'
end as type
from information_schema.views
where table_schema ilike 'fivetran_reddit_ads'
01:29:19.613576 [debug] [Thread-3 (]: SQL status: SUCCESS in 0.133 seconds
01:29:19.614802 [debug] [Thread-3 (]: While listing relations in database=ingestion, schema=fivetran_reddit_ads, found:
```

### Expected behavior

Expected that the required source tables could be identified in the `ingestion` database. There is no issue if the target database is the same as the target database.

### Possible solution

The `redshift__list_relations_without_caching` appears to be what is used to list the tables in a schema. Not sure what needs to happen to get it pointing at the correct database.

### dbt Project configurations

```yaml
vars:
linkedin_ads_database: ingestion
linkedin_ads_schema: fivetran_linkedin_ads
pinterest_database: ingestion
pinterest_schema: fivetran_pinterest_ads
reddit_ads_database: ingestion
reddit_ads_schema: fivetran_reddit_ads
```

### Package versions

- package: dbt-labs/redshift
version: 0.9.0
- package: fivetran/ad_reporting
version: [">=1.10.0", "<1.11.0"]

### What database are you using dbt with?

redshift

### How are you running this dbt package?

dbt Core™

### dbt Version

Core:
- installed: 1.8.9
- latest: 1.8.9 - Up to date!

Plugins:
- redshift: 1.8.1 - Up to date!
- postgres: 1.8.2 - Up to date!

### Additional Context

_No response_

### Are you willing to open a PR to help address this issue?

- [ ] Yes.
- [X] Yes, but I will need assistance.
- [ ] No.

Start with redshift__list_relations_without_caching and the dbt_project.yml database variables, then reproduce the staging-model run using the shown multi-database configuration. Trace the information_schema query and verify that relation discovery targets the configured ingestion database rather than the target database; done means source tables are found without the empty-table warnings.

Written by the indexing model from the issue text.