🤮
dbt-external-tablesでpartition_dateをmetadata$filenameから取ろうとしたらハマった話

2025/07/31に公開
 tl:drs3をIFレイヤにする場合、適切にpruning効かせられるようにするためにpartitions列は設定すべし
もしデータ列にparitition_keyがなくてもfile_pathの工夫次第でmetadata$filenameからpartition_keyを設定できる
ただし、expressionに設定できる式の中では使える関数に限りがある、詳細はこちら
そこで起こったエラーと対処法を簡単に共有します

 説明しないこと外部テーブルそのものとかdbt-external-tablesそのものの使い方には言及していません
上記内容に関してはdbtのpackage hubだったり、kevinさんの記事がすごくわかりやすいので見ていただければと思います
https://zenn.dev/dataheroes/articles/snowflake-external-table
https://hub.getdbt.com/dbt-labs/dbt_external_tables/latest/

 経緯現在データ基盤のIFレイヤとして、dbt-external-tablesを使ってS3を境界にする設計で対応しています。

上流システム自体はスキーマの変更が発生し得ないので、管理が簡単な外部テーブルで管理することにしました。

上流システム自体にはpartition_dateとして設定できるフィールドがないため、s3に連携してくる時のfilenameを以下のようにし、正規表現で日付文字列を取得してpartition_dateとして設定しようとしていました。
<sysytem_name>/<sub_system_name>/<yyyymmdd>/<file_name>
ですが、このようにしようとしてexpressionでsnowflakeで使えるような関数を使おうとした時にエラーになっていました。

 原因通常のカラムとしての指定と、外部テーブルのpartitionとして指定する式には違いがあるそうです。

現状、partitionに設定できる式ではregexp_substrのような正規表現の関数はサポートされておらず、split_partなどを使って設定することが推奨されているようです。(参照：Snowflakeドキュメント)
version: 2

sources:
  - name: sample_external_table
    database: sample_db
    schema: sample_schema
    loader: S3
    freshness:
      error_after: {count: 7, period: day}
    loaded_at_field: PARTITION_DATE
    tables:
      - name: sample_external_table
        external:
          location: "@sample_ext_stage
          pattern: .*\.gz
          file_format: sample_format
          auto_refresh: true
          partitions:
            - name: PARTITION_DATE
              data_type: DATE
              expression: "TO_DATE(SPLIT_PART(SPLIT_PART(metadata$filename, '/', -1), '.', 1), 'YYYYMMDD')" #→これはOK
              # expression: "xpression: "TO_DATE(REGEXP_SUBSTR(metadata$filename, '[0-9]{8}'), 'YYYYMMDD')" #→これはNG(REGEXP_SUBSTRに対応していない)
        columns:
          - name: sample_string
            data_type: STRING
            expression: value:c1
以上です、見てみたらなんてことないんですが、結構沼ったので何かの参考になれば幸いです。
tl:dr

説明しないこと

経緯

原因

Discussion
