Dataform の組み込みアサーションで発行されるクエリを調べてみた

以前 Apple Watch で取得したデータを Dataform で加工した記事を書きました。

引き続き開発していますが、Dataform のアサーションが役に立ったので記事にすることにしました。

この記事で書いたこと

Dataform の機能の１つに assertions があります。

この機能を使ってみて便利だと思ったので、ついでにその内容について調べました。
今回は特に組み込みアサーションについて記事にしました。

Dataform の assertions とは

上記のドキュメントによると次のとおりです。

アサーションは、クエリで指定された 1 つ以上のルールに違反する行を検出するデータ品質テストクエリです。クエリが行を返す場合、アサーションは失敗します。Dataform は、SQL ワークフローを更新するたびにアサーションを実行し、アサーションが失敗した場合にアラートを送信します。

データは簡単に壊れるため、 assertion は欠かせません。 Dataform の assertion は手軽に扱えて便利です。

実際に使ったときのデータの異常

睡眠データに対して Dataform のアサーションを設定したところ、想定外のアラートが発生しており、データの異常に気づくことができました。

異常の内容としては uniqueKey になると想定していたカラムがユニークになっていなかったことでした。

睡眠のタイプと開始時刻でユニークになる想定が、次のように睡眠時間の総量が異なるレコードの存在が判明しました。

なぜこのようなデータが発生しているかは調査中ですが、おそらく睡眠のデータが十分に apple watch から iPhone に連携されていないうちに GCS に連携されたデータと、iPhone に連携しきったあとに GCS に連携されたデータで差が出たのではないかと予想しています。

組み込みアサーションの詳細

各組み込みアサーションで発行されるクエリについて調べました。

uniqueKey

実運用上で設定していたのが uniqueKey です。
config は次のように設定していました。

config {
    type: "view",
    columns: {
        base_date: '日付',
        base_start_time: '時間幅の開始時刻',
        sleep_type: '睡眠の種類。レム睡眠、コア睡眠など。',
        ... 略
    },
    assertions: {
        uniqueKey: ["base_date", "base_start_time", "sleep_type"]
    }
}

上記の config のもとで出たアラートのクエリはこちらです。

-- ① データセット作成
BEGIN
  CREATE SCHEMA IF NOT EXISTS `project_id.dataform_assertions` OPTIONS(location="asia-northeast1");
EXCEPTION WHEN ERROR THEN
  IF NOT CONTAINS_SUBSTR(@@error.message, "already exists: dataset") AND
    NOT CONTAINS_SUBSTR(@@error.message, "too many dataset metadata update operations") AND
    NOT CONTAINS_SUBSTR(@@error.message, "User does not have bigquery.datasets.create permission")
  THEN
    RAISE USING MESSAGE = @@error.message;
  END IF;
END;
-- ② ビュー作成
        CREATE OR REPLACE VIEW `project_id.dataform_assertions.sleep_sleep_time_assertions_uniqueKey_0`
    OPTIONS()
    AS (
      
SELECT
  *
FROM (
  SELECT
    base_date, base_start_time, sleep_type,
    COUNT(1) AS index_row_count
  FROM `project_id.sleep.sleep_time`
  GROUP BY base_date, base_start_time, sleep_type
  ) AS data
WHERE index_row_count > 1

    );
-- ③ アサート処理
    ASSERT (
  ( SELECT COUNT(1) FROM `project_id.dataform_assertions.sleep_sleep_time_assertions_uniqueKey_0` ) = 0
) AS "Assertion failed, expected zero rows."

① は冒頭の BEGIN から END までは dataform ユーザーにはおなじみのデータセット作成の処理です。

② はアサーション内容に応じたビュー作成処理です。

CREATE OR REPLACE VIEW `project_id.dataform_assertions.sleep_sleep_time_assertions_uniqueKey_0`
    OPTIONS()
    AS (
      
SELECT
  *
FROM (
  SELECT
    base_date, base_start_time, sleep_type,
    COUNT(1) AS index_row_count
  FROM `project_id.sleep.sleep_time`
  GROUP BY base_date, base_start_time, sleep_type
  ) AS data
WHERE index_row_count > 1

    );

uniqueKey の場合はそのカラムで GROUP BY したときのカウントをとって、１より大きなレコードに絞り込んでいます。
作成したビューは dataform_assertions という名前のデータセットに含まれます。

このようにビューを作成する仕組みになっているため、どのようなレコードで違反していたかをすぐに確認できます。

SELECT
  *
FROM
  `project_id.dataform_assertions.データセット名_テーブル（ビューなど含む）名_assertions_uniqueKey_0`

クエリ結果

③ はこのビューにレコードが存在していたら ASSERT でアラートが出る仕組みになっています。

ASSERT (
  ( SELECT COUNT(1) FROM `project_id.dataform_assertions.sleep_sleep_time_assertions_uniqueKey_0` ) = 0
) AS "Assertion failed, expected zero rows."

uniqueKeys

複数の uniqueKey 条件を指定できます。
次のテーブルを作成し、2つのアサーションを設定します。

config {
    type: "view",
    schema: 'sample',
    assertions: {
        uniqueKeys: [["unique_id"] ,["column1", "column2"]]
    }
}

SELECT
  1 AS unique_id,
  'a' AS column1,
  'b' AS column2
UNION ALL
SELECT
  2,
  'a',
  'c'
UNION ALL
SELECT
  3,
  'a',
  'b'
UNION ALL
SELECT
  3,
  'z',
  'z'

この場合に発行されるクエリは uniqueKey のときと同じで単に設定した数の分だけアサーションのビューができます。

SELECT
  *
FROM
  `project_id.dataform_assertions.sample_assertion_sample_assertions_uniqueKey_0`

結果

SELECT
  *
FROM
  `project_id.dataform_assertions.sample_assertion_sample_assertions_uniqueKey_1`

結果

nonNull

指定したカラムが null になっているかのアサーションです。

次のように config を設定します。

config {
    type: "view",
    schema: 'sample',
    assertions: {
        nonNull: ["column1", "column2"]
    }
}

SELECT
  1 AS id,
  null AS column1,
  null AS column2
UNION ALL
SELECT
  2,
  null,
  2
UNION ALL
SELECT
  3,
  3,
  null
UNION ALL
SELECT
  4,
  4,
  4

この場合は次のような view が作成されます。

CREATE OR REPLACE VIEW `project_id.dataform_assertions.sample_assertion_sample_null_assertions_rowConditions`
    OPTIONS()
    AS (
      
SELECT
  'column1 IS NOT NULL' AS failing_row_condition,
  *
FROM `project_id.sample.assertion_sample_null`
WHERE NOT (column1 IS NOT NULL)
UNION ALL
SELECT
  'column2 IS NOT NULL' AS failing_row_condition,
  *
FROM `project_id.assertion_sample_null`
WHERE NOT (column2 IS NOT NULL)

    );

view を見ると次のようになっています。

rowConditions

カスタムロジックをアサートできます。

次のような config を設定します。

config {
    type: "view",
    schema: 'sample',
    assertions: {
        rowConditions: [
          'signup_date is null or signup_date > "2022-08-01"',
          'email like "%@%.%"'
        ]
    }
}

SELECT
  '2022-07-31' AS signup_date,
  '[e-mail]' AS email
UNION ALL
SELECT
  '2022-08-01',
  'fail'
UNION ALL
SELECT
  NULL,
  '[e-mail]'

この場合は次の SQL が発行されます。

CREATE OR REPLACE VIEW `project_id.dataform_assertions.sample_assertion_sample_row_conditions_assertions_rowConditions`
    OPTIONS()
    AS (
      
SELECT
  'signup_date is null or signup_date > "2022-08-01"' AS failing_row_condition,
  *
FROM `project_id.sample.assertion_sample_row_conditions`
WHERE NOT (signup_date is null or signup_date > "2022-08-01")
UNION ALL
SELECT
  'email like "%@%.%"' AS failing_row_condition,
  *
FROM `project_id.sample.assertion_sample_row_conditions`
WHERE NOT (email like "%@%.%")

    );

view は次のとおりです。

まとめ

自前でこのようなアサーションを実装して組み込むのは地味に面倒なので Dataform が手軽に使える機能を提供しているのはとても助かります。

特にエンジニアに比べるとこういった実装にあまり慣れていないであろうアナリストにも勧めやすい点が良いと思いました。

今回は組み込みアサーションのみ記事にしましたが使う機会があれば手動アサーションについても記事にしたいと思います。