dbt project: one project, three adapters

A small dbt project (seeds, a staging view, an incremental mart, tests, a snapshot) that runs unchanged with dbt-clickhouse, dbt-snowflake or dbt-postgres. Pointing it at MachWarehouse changes only the host fields in profiles.yml.

The only change

# dbt-clickhouse
 host: clickhouse.internal
 host: warehouse.internal
# dbt-snowflake
 account: myorg-myaccount (host derived)
 account: demo, host: warehouse.internal
# dbt-postgres
 host: postgres.internal
 host: warehouse.internal

See profiles.yml.example.

Run

python3 -m venv .venv && . .venv/bin/activate
pip install dbt-core==1.12.6 dbt-clickhouse==1.10.3 dbt-snowflake==1.12.4 dbt-postgres==1.11.0
cp profiles.yml.example ~/.dbt/profiles.yml
DBT_PASSWORD=... dbt build --target clickhouse
DBT_PASSWORD=... dbt build --target snowflake
DBT_PASSWORD=... dbt build --target postgres
DBT_PASSWORD=... dbt snapshot --target postgres

Versions: dbt-core 1.12.6, dbt-clickhouse 1.10.3, dbt-snowflake 1.12.4, dbt-postgres 1.11.0. Install only the adapter you use; the versions above are the latest at the time of writing and are compatible with dbt-core 1.12 where the adapter supports it.

Source

The program as it is kept and tested in the Mach Database Warehouse repository; highlighted lines read the connection setting.

name: machwarehouse_demo
version: "1.0.0"
config-version: 2
profile: machwarehouse_demo

model-paths: ["models"]
seed-paths: ["seeds"]
snapshot-paths: ["snapshots"]

models:
  machwarehouse_demo:
    staging:
      +materialized: view
    marts:
      +materialized: table

seeds:
  machwarehouse_demo:
    raw_events:
      +column_types:
        revenue: "decimal(12, 2)"

{{
  config(
    materialized='incremental',
    unique_key=['event_date', 'country'],
    incremental_strategy='delete+insert'
  )
}}

select
    e.event_date,
    e.country,
    c.region,
    count(*)                  as events,
    count(distinct e.user_id) as users,
    sum(e.revenue)            as revenue
from {{ ref('stg_events') }} as e
join {{ ref('countries') }} as c on c.country = e.country
{% if is_incremental() %}
where e.event_date >= (select max(event_date) from {{ this }})
{% endif %}
group by e.event_date, e.country, c.region

version: 2

models:
  - name: stg_events
    columns:
      - name: country
        data_tests: [not_null]
      - name: revenue
        data_tests: [not_null]
  - name: daily_revenue
    columns:
      - name: region
        data_tests:
          - not_null
          - accepted_values:
              arguments:
                values: [EMEA, AMER, APAC]

select
    cast(event_date as date)      as event_date,
    cast(event_time as timestamp) as event_time,
    user_id,
    upper(country)                as country,
    lower(device)                 as device,
    revenue
from {{ ref('raw_events') }}

# Copy to ~/.dbt/profiles.yml. Each target is the adapter's normal profile;
# only host / account URL fields differ between the original and MachWarehouse.
machwarehouse_demo:
  target: clickhouse
  outputs:
    clickhouse:
      type: clickhouse
      host: warehouse.internal        # before: clickhouse.internal
      port: 8443
      secure: true
      user: app
      password: "{{ env_var('DBT_PASSWORD') }}"
      schema: analytics

    snowflake:
      type: snowflake
      account: demo                   # before: myorg-myaccount
      host: warehouse.internal        # before: unset (myorg-myaccount.snowflakecomputing.com)
      port: 443
      protocol: https
      user: app
      password: "{{ env_var('DBT_PASSWORD') }}"
      role: TRANSFORMER
      warehouse: TRANSFORM            # a MachWarehouse compute pool
      database: DEMO
      schema: ANALYTICS
      threads: 8

    postgres:
      type: postgres
      host: warehouse.internal        # before: postgres.internal
      port: 5432
      user: app
      password: "{{ env_var('DBT_PASSWORD') }}"
      dbname: demo
      schema: analytics
      sslmode: verify-full
      threads: 8

country,name,region
PL,Poland,EMEA
DE,Germany,EMEA
FR,France,EMEA
US,United States,AMER
JP,Japan,APAC

event_date,event_time,user_id,country,device,revenue
2026-10-01,2026-10-01 00:00:00,1000,PL,desktop,0.00
2026-10-02,2026-10-02 07:13:00,1037,DE,desktop,1.73
2026-10-03,2026-10-03 14:26:00,1033,US,mobile,3.46
2026-10-04,2026-10-04 21:39:00,1029,FR,desktop,5.19
2026-10-05,2026-10-05 04:52:00,1025,JP,tablet,6.92
2026-10-06,2026-10-06 11:05:00,1021,PL,desktop,8.65
2026-10-07,2026-10-07 18:18:00,1017,DE,desktop,10.38
2026-10-08,2026-10-08 01:31:00,1013,US,desktop,12.11
2026-10-09,2026-10-09 08:44:00,1009,FR,mobile,13.84
2026-10-10,2026-10-10 15:57:00,1005,JP,desktop,15.57
2026-10-01,2026-10-01 22:10:00,1001,PL,tablet,17.30
2026-10-02,2026-10-02 05:23:00,1038,DE,desktop,19.03
2026-10-03,2026-10-03 12:36:00,1034,US,desktop,20.76
2026-10-04,2026-10-04 19:49:00,1030,FR,desktop,22.49
2026-10-05,2026-10-05 02:02:00,1026,JP,mobile,24.22
2026-10-06,2026-10-06 09:15:00,1022,PL,desktop,25.95
2026-10-07,2026-10-07 16:28:00,1018,DE,tablet,27.68
2026-10-08,2026-10-08 23:41:00,1014,US,desktop,29.41
2026-10-09,2026-10-09 06:54:00,1010,FR,desktop,31.14
2026-10-10,2026-10-10 13:07:00,1006,JP,desktop,32.87
2026-10-01,2026-10-01 20:20:00,1002,PL,mobile,34.60
2026-10-02,2026-10-02 03:33:00,1039,DE,desktop,36.33
2026-10-03,2026-10-03 10:46:00,1035,US,tablet,38.06
2026-10-04,2026-10-04 17:59:00,1031,FR,desktop,39.79
2026-10-05,2026-10-05 00:12:00,1027,JP,desktop,41.52
2026-10-06,2026-10-06 07:25:00,1023,PL,desktop,43.25
2026-10-07,2026-10-07 14:38:00,1019,DE,mobile,44.98
2026-10-08,2026-10-08 21:51:00,1015,US,desktop,46.71
2026-10-09,2026-10-09 04:04:00,1011,FR,tablet,48.44
2026-10-10,2026-10-10 11:17:00,1007,JP,desktop,0.17
2026-10-01,2026-10-01 18:30:00,1003,PL,desktop,1.90
2026-10-02,2026-10-02 01:43:00,1040,DE,desktop,3.63
2026-10-03,2026-10-03 08:56:00,1036,US,mobile,5.36
2026-10-04,2026-10-04 15:09:00,1032,FR,desktop,7.09
2026-10-05,2026-10-05 22:22:00,1028,JP,tablet,8.82
2026-10-06,2026-10-06 05:35:00,1024,PL,desktop,10.55
2026-10-07,2026-10-07 12:48:00,1020,DE,desktop,12.28
2026-10-08,2026-10-08 19:01:00,1016,US,desktop,14.01
2026-10-09,2026-10-09 02:14:00,1012,FR,mobile,15.74
2026-10-10,2026-10-10 09:27:00,1008,JP,desktop,17.47
2026-10-01,2026-10-01 16:40:00,1004,PL,tablet,19.20
2026-10-02,2026-10-02 23:53:00,1000,DE,desktop,20.93
2026-10-03,2026-10-03 06:06:00,1037,US,desktop,22.66
2026-10-04,2026-10-04 13:19:00,1033,FR,desktop,24.39
2026-10-05,2026-10-05 20:32:00,1029,JP,mobile,26.12
2026-10-06,2026-10-06 03:45:00,1025,PL,desktop,27.85
2026-10-07,2026-10-07 10:58:00,1021,DE,tablet,29.58
2026-10-08,2026-10-08 17:11:00,1017,US,desktop,31.31
2026-10-09,2026-10-09 00:24:00,1013,FR,desktop,33.04
2026-10-10,2026-10-10 07:37:00,1009,JP,desktop,34.77
2026-10-01,2026-10-01 14:50:00,1005,PL,mobile,36.50
2026-10-02,2026-10-02 21:03:00,1001,DE,desktop,38.23
2026-10-03,2026-10-03 04:16:00,1038,US,tablet,39.96
2026-10-04,2026-10-04 11:29:00,1034,FR,desktop,41.69
2026-10-05,2026-10-05 18:42:00,1030,JP,desktop,43.42
2026-10-06,2026-10-06 01:55:00,1026,PL,desktop,45.15
2026-10-07,2026-10-07 08:08:00,1022,DE,mobile,46.88
2026-10-08,2026-10-08 15:21:00,1018,US,desktop,48.61
2026-10-09,2026-10-09 22:34:00,1014,FR,tablet,0.34
2026-10-10,2026-10-10 05:47:00,1010,JP,desktop,2.07

{% snapshot countries_snapshot %}
{{
  config(
    unique_key='country',
    strategy='check',
    check_cols=['name', 'region']
  )
}}
select * from {{ ref('countries') }}
{% endsnapshot %}