Declaring multiple warehouses in dbt

Question

I am pretty new to dbt , i want to use two kinds of warehouses in one project , currently i declared my clickhouse warehouse which i am going to make tables for and i need to add another warehouse mindsDB becuase i want to reference some of the tables in it

currently my prfofile.yml looks like this

dbt-project:
  target: dev
  outputs:
    dev:
      type: clickhouse
      schema : clickhouse_l
      host: 8.77.780.70
      port: 6000
      user: xxx
      password: xxxx

i want to add the below warehouse too

type: mysql
host: mysql.mindsdb.com
user: [email protected]
password: xxx
port: 3306
dbname: mindsdb
schema: exampl_xxx
threads: 1

is there a way to do it? thank you

Answer 1

This is a bit outside what dbt is designed to do. Is there any reason you can't use multiple projects with their own deployments? Presumably the models have dependencies on each other?

If I had to do this I would:

1. Create two targets (or sets of targets), one for each warehouse (or dev/prod for each warehouse, etc.)
2. Create a custom materialization that wraps the typical table materialization, but no-ops if target.type does not match a specified adapter
3. Run the project on each adapter in series, in a shell script
4. Use tags to select parts of the DAG that are up/downstream from the other adapter's models

I think the core of the problem is that dbt run needs a database connection to compile your project, so you really can't run against two databases simultaneously. What I described above is not really any better than having two standalone projects. You may want to look into using an orchestrator, like Airflow, Dagster, or Prefect.