dbtでレセプトデータをモデリングしてみたら、定石が全然通用しなかった話

はじめに

• この記事は開発部データチームの石田が医療データ（レセプト）の標準化をdbtで進める中で体験した、理想と現実のギャップを記録したものです
• 医療系データエンジニアの方や、複雑なドメインでdbtを使ったデータ基盤構築の経験がある方を想定しています
• レセプト（診療報酬明細書）データの特殊性について触れるので、レセプトデータの構造をある程度知っている方を前提としています

dbtの「理想」と医療データの「現実」のギャップ

最近ではデータ分析基盤にdbtを使うのが定番になっています。少し前まではDataform等のライバルも存在していましたが、コミュニティの盛り上がりや公式ドキュメントの整備具合からみても、同系統のツールならdbt一択という感じがします。

しかし、いざ公式ドキュメントに沿った実装を行おうとすると、なかなか難しいという現実に直面します。

dbt公式のBest Practicesを見ると、こんな前提で書かれています：

• stagingでは軽い変換のみ行う
• 1つのソーステーブル = 1つのstagingモデル
• インクリメンタル処理はupdated_atベース
• モデルの粒度は明確に分離する

dev.classmethod.jp

この処理レイヤーによる整理は秀逸で、データの品質を担保する上で非常に有効です。大概のデータは、これに習って整理することで、データパイプラインの構築時に突き当たる課題を解決することができます。 ところが、レセプトのような複雑な構造を持つ医療データを扱っていると、なかなか難しい部分があります。

dbtベストプラクティスの前提	レセプトデータの現実
構造が統一されている	ファイル種別で構造が全然違う
カラム数が一定	同一ファイル内でも行ごとにカラム数が変わる
仕様書が明確	医療制度の知識がないと仕様書すら読めない
データ品質が安定している	仕様通りじゃないことが日常茶飯事
シンプルなJOIN設計	識別単位が複雑に絡み合っている
updated_atでインクリメンタル処理	過去月データが後から修正・再送される

つまり、公式のベストプラクティスを「教科書通り」に適用しようとすると、ことレセプトデータに限って通用しない部分が多いです。

「比較的シンプルな構造向けのベストプラクティス」と「複雑な構造を持つデータへの現実的な対応」では、dbtの使い方が全然違うよね、を最初に理解して次に進んでいただければと思います。

なぜ医療データでは定石が通用しないのか

レセプトデータって何がそんなに厄介なの？

レセプト（診療報酬明細書）データは、CSV形式で提供されるんですが、実態としては「CSV の皮を被った何か」みたいな感じです。 特に厄介なのが以下の点：

• 医療機関の種類ごとにファイルが4つに分かれている
  • 医科・DPC・歯科・調剤でそれぞれ構造が異なる（医科とDPCは似てるけど、歯科・調剤は別物）
• 同一ファイル内でも行ごとに構造が変わる
  • MN、IR、RE、SY、IYなど、データ識別単位（レコード種別）によってカラム数が全然違う
• 仕様書通りじゃないことが多い
  • データ不備や変則的な構造が存在するのが普通
  • 保険者ごとに微妙に違ったりもする
• 読み手に高い専門性が求められる
  • 医療知識・制度の理解がないと、そもそも仕様書が読み解けない

この時点で「あ、これはdbtの想定してる世界じゃないな...」って気づくわけです。

参考：

www.phm-jmdc.com

dbtの定石に「従えなかった」具体例と工夫

1. stagingで"軽く整える"どころじゃない問題

通常、dbtではstagingモデルで名前変換・型付け程度を行い、ロジックはintermediateに任せる設計が推奨されています。

従わなかった場合

-- 理想的には staging では軽い処理のみのはずが...
with raw as (
  select * from {{ source('app_db', 'receipt_files') }}
),

-- 実際にはバリデーション処理がガッツリ必要
validated as (
  select
    id as insurer_receipt_file_id,
    receipt_file_id,
    seq_number,
    -- ファイル形式チェック
    case when regexp_contains(file_name, r'\\.csv$') then true else false end as is_valid_format,
    -- 保険者コードの正当性チェック
    case when length(insurer_code) = 8 then insurer_code else null end as validated_insurer_code,
    -- 診療年月の妥当性チェック
    case when regexp_contains(medical_care_date, r'^20[0-9]{2}(0[1-9]|1[0-2])$')
         then medical_care_date else null end as validated_medical_care_date

    -- 以下、延々とCTEが続く.....
  from raw
  where deleted_at is null
    and file_status = 'completed'
)

select * from validated

• レセプトデータは「仕様通りじゃない」ことが前提なので、stagingの段階で相当なバリデーション処理が必要になります
• 「後工程でエラーになるより、早い段階で不正データを除外した方が効率的」という判断をしました
• きれいなデータなら staging → intermediate → mart の責務分離が理想ですが、汚いデータでは「staging でできるだけ整える」方が現実的だと考えています

2. モデルの粒度設計で悩みまくった話

データ識別ごとにstagingモデルを作る正攻法は理想的ですが、現実にはファイル種別×識別単位の組み合わせが100近くになってしまいました。

従わない場合

-- 本来なら SI、IY、TO、CO それぞれ別モデルにするべきだが...
with si_records as (
  select * from {{ source('receipt', 'raw_receipt_data') }}
  where record_type = 'SI'  -- 診療行為
),

iy_records as (
  select * from {{ source('receipt', 'raw_receipt_data') }}
  where record_type = 'IY'  -- 医薬品
),

-- さらに TO（特定機材）、CO（コメント）も...
-- これを100パターン作るのは現実的じゃない

修正後

-- 構造が似ており連携が強い識別単位はまとめて処理
with tekiyo_info as (
  select
    receipt_id,
    record_type,
    procedure_code,
    procedure_name,
    unit_price,
    quantity,
    points
  from {{ source('receipt', 'raw_receipt_data') }}
  where record_type in ('SI', 'IY', 'TO', 'CO')  -- 摘要情報をまとめて処理
    and procedure_code is not null
),

final as (
  select
    receipt_id,
    record_type,
    procedure_code,
    procedure_name,
    coalesce(unit_price * quantity, points) as total_points
  from tekiyo_info
)

select * from final

• 理想的には1つのレコード種別＝1つのモデルですが、品質・コスト両面で現実的ではありませんでした
• SI（診療行為）、IY（医薬品）、TO（特定機材）、CO（コメント）などの「摘要情報」は構造も類似しており、相互に依存しているため、ひとつのstagingモデルに統合しました
• 「ベストプラクティスを破る」ことで、設計の複雑さと実行効率のバランスを取る判断をしました

3. インクリメンタルが全然うまくいかない問題

従わない場合

-- 普通のインクリメンタル処理
{{
  config(
    materialized='incremental',
    unique_key='receipt_id'
  )
}}

select * from {{ ref('stg_receipt') }}
{% if is_incremental() %}
  where updated_at > (select max(updated_at) from {{ this }})
{% endif %}

修正後

-- レセプト特有の事情に対応
{{
  config(
    materialized='incremental',
    unique_key=['invoice_year_month', 'receipt_id'],
    incremental_strategy='insert_overwrite'
  )
}}

select
  invoice_year_month,
  receipt_id,
  medical_institution_code,
  patient_id,
  total_points
from {{ ref('stg_receipt') }}
{% if is_incremental() %}
  where invoice_year_month >= '{{ var("target_month") }}'
{% endif %}

• レセプトでは毎月、過去の月データが再送・修正されることがあります
• 「最終更新日時」でのインクリメンタル処理では、「3月分が6月に再送される」といったケースに対応できません
• そこで、invoice_year_month + receipt_idをキーに全件削除・再生成する構成にしました
• 実行時間は少し増えますが、データの整合性を確保できる設計になっています

現実的な工夫と"ゆるやかな妥協"

中間テーブルの materialized 戦略

-- models/intermediate/receipt/receipt_detail_flattened.sql
{{ config(materialized='table') }}  -- 再利用するので table に

select
  receipt_id,
  procedure_code,
  procedure_date,
  unit,
  point
from {{ ref('stg_receipt_details') }}
where procedure_code is not null

• 中間テーブルのmaterializedはviewやephemeralにするのが理想ですが、レセプトデータの場合はそれだとパフォーマンスが悪くなるため、tableにしました
• 再利用する中間ロジックはmaterialized='table'にし、BigQueryの分散処理に乗せることでコストを抑えました
• Pythonで複雑な処理を書くこともできますが、実行環境のリソース管理やデータサイズのばらつき（保険者による）を考えると非現実的でした

最後に

レセプトのパイプラインを構築して改めて「ドメインの特性を理解すること」の大切さを感じました。 dbtの知見が詰まったベストプラクティスは強力ですが、医療データのような複雑なドメインでは、それを形式的に守るのは難しいこともあります。 ただし、以下の設計思想そのものは、むしろ医療データのような難しい領域でこそ活きると感じました：

1. モデルの責務を明確に分ける

   • stagingの役割が重くなっても、「データ検証」という明確な責務を持たせる
   • 無理に軽量化するより、各段階で何をするかを明確にする方が重要

2. 定義を統一し、再利用可能にする

   • マクロやテスト定義で、医療制度特有のバリデーションロジックを標準化
   • 「保険者コード8桁チェック」「診療年月の妥当性確認」などを共通化

3. 依存関係を可視化して、影響範囲を把握する

   • レセプトの複雑な識別単位でも、lineageグラフで上流・下流の関係が一目瞭然
   • データ修正時の影響範囲が事前に分かるため、安全にメンテナンスできる

4. テストによる品質担保

   • 「仕様通りじゃない」前提だからこそ、自動テストでデータ品質をチェック
   • severity: warnで処理を止めずに問題を可視化する柔軟性

このような状況を前にすると、ベストプラクティスに従わず「オレオレ設計」で切り抜けたくなるのですが、結局のところ「なぜそのプラクティスが存在するのか」を理解した上で、自分たちのドメインに適応させることが大切だと思いました。 医療データのような複雑な領域で格闘している方の参考になれば幸いです。

---

採用情報

もし似たような課題でお困りの方がいらっしゃれば、ぜひコメントやSNSでお気軽にお声がけください。一緒に解決策を考えましょう！

hrmos.co