Clickhouse is advancing at data integration

1. You create a table with URL engine pointing to external API either manually or using dbt macro

-- DROP TABLE dbt.campaign_mapping ;
CREATE TABLE dbt.campaign_mapping (
 company String
, `source` String
, UTMCampaign String
, campaign_name String
, is_enabled UInt8
)
ENGINE=URL('https://docs.google.com/spreadsheets/d/1dQKDrUuB_MfZtXX8RxqbgzZxnigdPkBXw-s6GnnQwBc
/gviz/tq?tqx=out:csv&sheet=campaign_mapping', CSVWithNames)
;

2. You register this table as a dbt source

version: 2

sources:

 - name: gsheet
   schema: dbt
   tags: ["sources", "gsheet"]     
   description: "Google Sheets: https://docs.google.com/spreadsheets/d/1dQKDrUuB_MfZtXX8RxqbgzZxnigdPkBXw-s6GnnQwBc"

   tables:

     - name: campaign_mapping

3. You then reference this source anywhere in your dbt project

FROM {{ ref('intermediate_tracker') }} as tracker
 left join {{ source('margin_rate', 'margin_rate') }} as margin_rate on margin_rate.date_month = date_trunc('month', tracker.create_date)
       and margin_rate.company = tracker.company