カラムの数や構成がバラバラのファイルを1つのSnowflakeテーブルにロードしてみた

CREATE OR REPLACE FILE FORMAT TEST_DBT.suto_csv_format
  TYPE = csv
  COMPRESSION = AUTO
  RECORD_DELIMITER = '\n'
  FIELD_DELIMITER = ','
  PARSE_HEADER = TRUE,
  DATE_FORMAT = 'YYYY-MM-DD'
  TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS'
  TRIM_SPACE = TRUE
  FIELD_OPTIONALLY_ENCLOSED_BY = '"'
  EMPTY_FIELD_AS_NULL = TRUE
  ENCODING = UTF8
  ERROR_ON_COLUMN_COUNT_MISMATCH=FALSE
;

CREATE STAGE IF NOT EXISTS TEST_DBT.suto_s3_test_stage
    STORAGE_INTEGRATION = s3_test_integration
    URL = 's3://cm-suto-sample/snowflake-data/'
    FILE_FORMAT = suto_csv_format
;

CREATE OR REPLACE TABLE TEST_DBT.test_users (
  ID VARCHAR,
  EMAIL VARCHAR,
  LAST_LOGIN_IP VARCHAR,
  REGISTER_DATE DATE
);

-- INFER_SCHEMAでファイルのスキーマを検出と確認
SELECT *
FROM TABLE(
  INFER_SCHEMA(
    LOCATION => '@suto_s3_test_stage/test_users/users-main.csv',
    FILE_FORMAT => 'suto_csv_format',
    IGNORE_CASE => TRUE
  )
);

-- 検出したスキーマを元にテーブルを自動作成
CREATE OR REPLACE TABLE test_users
  USING TEMPLATE (
    SELECT ARRAY_AGG(OBJECT_CONSTRUCT(*))
    FROM TABLE(
      INFER_SCHEMA(
        LOCATION => '@suto_s3_test_stage/test_users/users-main.csv',
        FILE_FORMAT => 'suto_csv_format',
        IGNORE_CASE => TRUE
      )
    )
  );

COPY INTO TEST_DBT.test_users 
  FROM @suto_s3_test_stage/test_users/
  FILE_FORMAT = suto_csv_format
  MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE
  ON_ERROR = ABORT_STATEMENT
  PURGE = TRUE
;