Search code examples
dbt

Declaring multiple warehouses in dbt

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

Solution

  • 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.