この記事は <a href="https://qiita.com/advent-calendar/2024/commune" target="_blank" rel="nofollow noopener noreferrer">Commune Advent Calendar 2024</a>シリーズ1と、<a href="https://qiita.com/advent-calendar/2024/dbt" target="_blank" rel="nofollow noopener noreferrer">dbt Advent Calendar 2024</a>の 6日目の記事です。
dbtのincremental modelを使った、日々の集計データを追記する履歴テーブルを皆さん運用していると思います。 
何かしらの理由により空振りしてしまったり、洗い替えをするときに空白の期間が発生した時にすぐに検知出来るテストが欲しいですよね。
そんな時にぴったりなものが <a href="https://github.com/calogica/dbt-expectations" target="_blank" rel="nofollow noopener noreferrer">dbt-expectations</a> の <a href="https://github.com/calogica/dbt-expectations/blob/main/macros/schema_tests/distributional/expect_row_values_to_have_data_for_every_n_datepart.sql" target="_blank" rel="nofollow noopener noreferrer">expect_row_values_to_have_data_for_every_n_datepart</a> です。
ネット上に記事が7件、それも利用者の記事は0件と、とても知名度が低いので今回取り上げることにしました。 
きっと1年後には皆が使っている事があたりまえなテストの1つになってるはずです。
<h1 id="%E3%82%A4%E3%83%B3%E3%82%B9%E3%83%88%E3%83%BC%E3%83%AB%E6%96%B9%E6%B3%95" data-line="11" class="code-line">
<a class="header-anchor-link" href="#%E3%82%A4%E3%83%B3%E3%82%B9%E3%83%88%E3%83%BC%E3%83%AB%E6%96%B9%E6%B3%95" aria-hidden="true"></a> インストール方法</h1>
<code>packages.yml</code> に下記の行を追加し、<code>dbt deps</code>コマンドを実行します。
<div class="code-block-container">
<div class="code-block-filename-container">packages.yml</div>
<pre class="language-yaml"><code class="language-yaml code-line" data-line="15">packages:
 - package: calogica/dbt_expectations
 version: ["&gt;=0.10.0", "&lt;0.11.0"]
 # &lt;see https://github.com/calogica/dbt-expectations/releases/latest&gt; for the latest version tag
 - package: calogica/dbt_date
 version: 0.10.1
</code></pre>
</div>次に、<code>dbt_project.yml</code> の<code>vars</code>に下記の行を追加します。コロンが変なところにありますがtypoではありません。
<div class="code-block-container">
<div class="code-block-filename-container">dbt_project.yml</div>
<pre class="language-yaml"><code class="language-yaml code-line" data-line="26">vars:
 'dbt_date:time_zone': "Asia/Tokyo"
 session_date: '{{ run_started_at.astimezone(modules.pytz.timezone("Asia/Tokyo")).strftime("%Y-%m-%d") }}'
</code></pre>
</div><h1 id="%E8%A8%AD%E5%AE%9A%E3%83%91%E3%83%A9%E3%83%A1%E3%83%BC%E3%82%BF" data-line="32" class="code-line">
<a class="header-anchor-link" href="#%E8%A8%AD%E5%AE%9A%E3%83%91%E3%83%A9%E3%83%A1%E3%83%BC%E3%82%BF" aria-hidden="true"></a> 設定パラメータ</h1>
このテストではdate_col列を対象にdate_partの単位で、過去から現在までの範囲内のすべてのデータが欠落無く連続していることをチェックします。 
1つでも欠落している日付があれば、テストは失敗します。
<ul data-line="37" class="code-line">
<li data-line="37" class="code-line">(必須) date_col : 日付列の指定を行います。</li>
<li data-line="38" class="code-line">(任意) date_part : 日付をどのような粒度で入れているか指定します。'day', 'month', 'year'から選べます。デフォルトは日次の<code>day</code>です。</li>
<li data-line="39" class="code-line">(任意) test_start_date と、test_end_dateを用いてテストする日付の範囲を指定できます。指定が無ければ最も若いデータが観測された日から現在までの連続値でテストします。この記事では、varで指定したsession_dateを用いて1日前までテストすることをお勧めしています。</li>
<li data-line="40" class="code-line">(任意) row_condition : 特定の行をテストから除外できます。</li>
<li data-line="41" class="code-line">(任意) exclusion_condition : 週末や休日の日付を除外するなど、除外するデータをさらに細かく制御できます。例えば<code>DAYOFWEEK(transaction_date) IN (1, 7)</code>と指定すると、テスト対象から週末を除外します。</li>
</ul>
<h1 id="%E8%A8%AD%E5%AE%9A%E3%82%B5%E3%83%B3%E3%83%97%E3%83%AB" data-line="43" class="code-line">
<a class="header-anchor-link" href="#%E8%A8%AD%E5%AE%9A%E3%82%B5%E3%83%B3%E3%83%97%E3%83%AB" aria-hidden="true"></a> 設定サンプル</h1>
2018-08-06から、実行日の前日までの毎日1件以上レコードが存在し、欠損がないかテストする設定は以下の通りです。
<div class="code-block-container">
<div class="code-block-filename-container">schema.yml</div>
<pre class="language-yaml"><code class="language-yaml code-line" data-line="47">version: 2
models:
 - name: example_cnt_daily
 data_tests:
 - dbt_expectations.expect_row_values_to_have_data_for_every_n_datepart:
 date_col: fct_date_jst
 date_part: day
 test_start_date: '2018-08-06'
 test_end_date: "{{ var('session_date') }}"
</code></pre>
</div>例えば欠損だらけの下記テーブルでテストすると、is_missing:trueとなっている行がエラーとなった日付としてリストされます。
<img src="https://storage.googleapis.com/zenn-user-upload/6ef6715af2bc-20240929.png" width="450" loading="lazy" class="md-img">
<h1 id="%E9%A1%9E%E4%BC%BC%E3%81%AE%E3%83%86%E3%82%B9%E3%83%88%E6%89%8B%E6%B3%95%E3%81%A8%E3%81%9D%E3%81%AE%E9%81%95%E3%81%84" data-line="63" class="code-line">
<a class="header-anchor-link" href="#%E9%A1%9E%E4%BC%BC%E3%81%AE%E3%83%86%E3%82%B9%E3%83%88%E6%89%8B%E6%B3%95%E3%81%A8%E3%81%9D%E3%81%AE%E9%81%95%E3%81%84" aria-hidden="true"></a> 類似のテスト手法とその違い</h1>
<h3 id="dbt_expectations.expect_row_values_to_have_recent_data" data-line="65" class="code-line">
<a class="header-anchor-link" href="#dbt_expectations.expect_row_values_to_have_recent_data" aria-hidden="true"></a> dbt_expectations.expect_row_values_to_have_recent_data</h3>
<div class="code-block-container">
<div class="code-block-filename-container">schema.yml</div>
<pre class="language-yaml"><code class="language-yaml code-line" data-line="67">version: 2
models:
 - name: example_cnt_daily
 columns:
 - name: fct_date_jst
 description: 集計日(JST)
 data_type: DATE
 data_tests:
 - dbt_expectations.expect_row_values_to_have_recent_data:
 datepart: day
 interval: 1
</code></pre>
</div>これは、テストを実行したタイミングの丸めてない時刻から一定の時間以内のデータが存在することをテストします。以下のユースケースにフィットします。
<ul data-line="81" class="code-line">
<li data-line="81" class="code-line">source.ymlにて元データとなるログテーブルに対してテストする</li>
<li data-line="82" class="code-line">毎時間かそれ以上の高頻度で追記される履歴テーブル</li>
</ul>
1日単位で丸めたデータを入れてる日次集計テーブルでは、以下の挙動となり役に立ちません。 
例えば、翌日朝6時の処理時点で既に24時間+6時間の30時間の開きが発生します。実行が1時間遅れたら31時間の開きが観測され、エラーとなってしまいます。
<h3 id="dbt_utils.recency" data-line="87" class="code-line">
<a class="header-anchor-link" href="#dbt_utils.recency" aria-hidden="true"></a> dbt_utils.recency</h3>
先ほどの物に似てますが、タイムスタンプの列を日付に丸めて比較できます。そのため当日のデータが入っているかのテストだけであれば、これで実現できます。
<div class="code-block-container">
<div class="code-block-filename-container">schema.yml</div>
<pre class="language-yaml"><code class="language-yaml code-line" data-line="91">version: 2
models:
 - name: example_cnt_daily
 data_tests:
 - dbt_utils.recency:
 datepart: day
 field: timestamp(fct_date_jst)
 interval: 1
 ignore_time_component: true
</code></pre>
</div><h1 id="%E3%81%BE%E3%81%A8%E3%82%81" data-line="103" class="code-line">
<a class="header-anchor-link" href="#%E3%81%BE%E3%81%A8%E3%82%81" aria-hidden="true"></a> まとめ</h1>
かゆい所に手が届く、とても便利なテストでしたね！ 
BIダッシュボードに連携しているマートテーブルには、dbt_expectations.expect_row_values_to_have_data_for_every_n_datepart のテストをすぐにでも取り入れましょう！ 
ついでに、dbt_project.ymlにvar: session_dateを定義するとさらに便利ですよー

dbtで毎日データが欠損無く入っていることをテストする方法を比べてみた

dbt_expectations.expect_row_values_to_have_recent_data

Discussion