dbt project on Snowflakeでdbtパッケージを使用する方法を確認しつつdbt_external_tablesを試してみた

-- 自分のデータベース・スキーマを作成
CREATE DATABASE IF NOT EXISTS KAWABATA_DBT_EXTERNAL;
CREATE SCHEMA IF NOT EXISTS KAWABATA_DBT_EXTERNAL.STAGE;

--  ファイルフォーマットの作成
create or replace file format my_csv_format
  type='csv'
  compression = 'auto' field_delimiter = ','
  record_delimiter = '\n'
  skip_header = 0
  field_optionally_enclosed_by = '\042'
  trim_space = false
  error_on_column_count_mismatch = false
  escape = 'none'
  escape_unenclosed_field = '\134'
  date_format = 'auto'
  timestamp_format = 'auto'
  null_if = ('')
  comment = 'file format for ingesting data for zero to snowflake'
;

-- 管理者権限
USE ROLE ACCOUNTADMIN;

-- データベースへのアクセス権
GRANT USAGE ON DATABASE KAWABATA_DBT_EXTERNAL TO ROLE KAWABATA_DBT_DEV_ROLE;

-- スキーマへのアクセス権
GRANT USAGE ON SCHEMA KAWABATA_DBT_EXTERNAL.STAGE TO ROLE KAWABATA_DBT_DEV_ROLE;
GRANT CREATE STAGE ON SCHEMA KAWABATA_DBT_EXTERNAL.STAGE TO ROLE KAWABATA_DBT_DEV_ROLE;
GRANT CREATE EXTERNAL TABLE ON SCHEMA KAWABATA_DBT_EXTERNAL.STAGE TO ROLE KAWABATA_DBT_DEV_ROLE;

-- ステージへの権限
GRANT USAGE ON ALL STAGES IN SCHEMA KAWABATA_DBT_EXTERNAL.STAGE TO ROLE KAWABATA_DBT_DEV_ROLE;
GRANT USAGE ON FUTURE STAGES IN SCHEMA KAWABATA_DBT_EXTERNAL.STAGE TO ROLE KAWABATA_DBT_DEV_ROLE;

--  スキーマ内のテーブル（外部テーブル含む）へのSELECT権限
GRANT SELECT ON ALL TABLES IN SCHEMA KAWABATA_DBT_EXTERNAL.STAGE TO ROLE KAWABATA_DBT_DEV_ROLE;
GRANT SELECT ON ALL VIEWS IN SCHEMA KAWABATA_DBT_EXTERNAL.STAGE TO ROLE KAWABATA_DBT_DEV_ROLE;

--  (推奨) 将来作られるテーブルも自動で見えるようにしておく
GRANT CREATE VIEW ON SCHEMA KAWABATA_DBT_EXTERNAL.STAGE TO ROLE KAWABATA_DBT_DEV_ROLE;
GRANT CREATE TABLE ON SCHEMA KAWABATA_DBT_EXTERNAL.STAGE TO ROLE KAWABATA_DBT_DEV_ROLE;
GRANT SELECT ON FUTURE TABLES IN SCHEMA KAWABATA_DBT_EXTERNAL.STAGE TO ROLE KAWABATA_DBT_DEV_ROLE;
GRANT SELECT ON FUTURE VIEWS IN SCHEMA KAWABATA_DBT_EXTERNAL.STAGE TO ROLE KAWABATA_DBT_DEV_ROLE;

-- External Access Integration
  USE ROLE ACCOUNTADMIN;

-- 1. 接続先（GitHub/dbt Hub）を許可するネットワークルール
CREATE OR REPLACE NETWORK RULE dbt_network_rule
  MODE = EGRESS
  TYPE = HOST_PORT
  VALUE_LIST = ('github.com', 'api.github.com', 'codeload.github.com', 'hub.getdbt.com');

-- 2. 外部アクセス統合の作成
CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION dbt_external_access_integration
  ALLOWED_NETWORK_RULES = (dbt_network_rule)
  ENABLED = TRUE;

-- 3. dbtを実行するユーザー/ロールに権限付与
GRANT USAGE ON INTEGRATION dbt_external_access_integration TO ROLE [あなたのロール];

packages:
  - package: dbt-labs/dbt_external_tables
    version: 0.11.1

version: 2

sources:
  - name: trips_source
    database: KAWABATA_DBT_EXTERNAL
    schema: STAGE
    loader: Snowflake

    tables:
      - name: trips
        description: "CITIBIKE"
        external:
          location: "@KAWABATA_DBT_EXTERNAL.STAGE.CITIBIKE_TRIPS"
          # ここにファイルフォーマット定義をインラインで記述
          file_format: KAWABATA_DBT_EXTERNAL.STAGE.my_csv_format
        columns:
          - name: tripduration
            data_type: integer
          - name: starttime
            data_type: timestamp
          - name: stoptime
            data_type: timestamp
          - name: start_station_id
            data_type: integer

dbt run-operation stage_external_sources

version: 2

sources:
  - name: trips_source
    database: KAWABATA_DBT_EXTERNAL
    schema: STAGE
    loader: Snowflake

    tables:
      - name: trips
        description: "CITIBIKE"
        external:
          location: "@KAWABATA_DBT_EXTERNAL.STAGE.CITIBIKE_TRIPS"
          # ここにファイルフォーマット定義をインラインで記述
          file_format: KAWABATA_DBT_EXTERNAL.STAGE.my_csv_format
        columns:
          - name: tripduration
            data_type: integer
          - name: starttime
            data_type: timestamp
          - name: stoptime
            data_type: timestamp
          - name: start_station_id
            data_type: integer
          - name: start_station_name
            data_type: string
          - name: start_station_latitude
            data_type: float
          - name: start_station_longitude
            data_type: float
          - name: end_station_id
            data_type: integer
          - name: end_station_name
            data_type: string
          - name: end_station_latitude
            data_type: float
          - name: end_station_longitude
            data_type: float
          - name: bikeid
            data_type: string
          - name: membership_type
            data_type: string
          - name: usertype
            data_type: string
          - name: birth_year
            data_type: integer
          - name: gender
            data_type: integer

dbt run-operation stage_external_sources --vars "ext_full_refresh: true"