【小ネタ】dbt project on Snowflakeで内部ステージのCSVファイルを直接モデルのFROM句で指定してみた

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

-- データを格納する内部ステージを作成
CREATE STAGE dbt_external
	DIRECTORY = ( ENABLE = true )
	ENCRYPTION = ( TYPE = 'SNOWFLAKE_SSE' );

-- ファイルフォーマットの作成
CREATE OR REPLACE FILE FORMAT my_csv_format
  TYPE = 'CSV'
  FIELD_DELIMITER = ','             -- カンマ区切り
  FIELD_OPTIONALLY_ENCLOSED_BY = '"' -- ダブルクォートで囲まれた値を正しく処理（必須）
  SKIP_HEADER = 1                   -- 1行目のヘッダーをスキップ
  NULL_IF = ('', 'NULL', 'null')    -- 空文字などをNULLとして扱う
  TRIM_SPACE = TRUE                 -- フィールド前後の余計な空白を除去（推奨）
  ERROR_ON_COLUMN_COUNT_MISMATCH = TRUE; -- 列数が合わない場合にエラーにする

SELECT 
    $1 AS passenger_id, -- 1列目
    $2 AS survived,     -- 2列目
    $3 AS pclass,       -- 3列目
    $4 AS name,
    $5 AS sex,
    $6 AS age,
    $7 AS sibsp,
    $8 AS parch,
    $9 AS ticket,
    $10 AS fare,
    $11 AS cabin,
    $12 AS embarked
FROM @DBT_EXTERNAL.STAGE.DBT_EXTERNAL/train.csv
(file_format => 'DBT_EXTERNAL.STAGE.my_csv_format')

SELECT 
    $1::INT       AS passenger_id, 
    $2::INT       AS survived,
    $3::INT       AS pclass,
    $4::STRING    AS name,
    $5::STRING    AS sex,
    $6::FLOAT     AS age,
    $7::INT       AS sibsp,
    $8::INT       AS parch,
    $9::STRING    AS ticket,
    $10::FLOAT    AS fare,
    $11::STRING   AS cabin,
    $12::STRING   AS embarked
FROM @DBT_EXTERNAL.STAGE.DBT_EXTERNAL/train.csv
(file_format => 'DBT_EXTERNAL.STAGE.my_csv_format')

SELECT 
    $1::INT       AS passenger_id, 
    $2::INT       AS survived,
    $3::INT       AS pclass,
    $4::STRING    AS name,
    $5::STRING    AS sex,
    $6::FLOAT     AS age,
    $7::INT       AS sibsp,
    $8::INT       AS parch,
    $9::STRING    AS ticket,
    $10::FLOAT    AS fare,
    $11::STRING   AS cabin,
    $12::STRING   AS embarked
FROM @DBT_EXTERNAL.STAGE.DBT_EXTERNAL
(file_format => 'DBT_EXTERNAL.STAGE.my_csv_format')