Zenn
🍣

DataFormを使ったSQLのテスト

2022/11/13に公開
GCP
BigQuery
#
dataform
tech

DataForm

DataFormは今はGCPのサービスの一つで、BigQuery上で動かすSQLのパイプラインを定義・実行したり、そのSQLに対してテストを書いたりすることが出来るようだ。
https://dataform.co/

Test SQL workflows

https://cloud.google.com/dataform/docs/assertions

DataFormではSQLXというSQLの拡張版を使用しており、下のように設定（config）パートとSQLパートを含んでいる。

SQLX例
config {
  type: "table",
  assertions: {
    nonNull: ["id", "customer_id", "amount"]
  }
}

SELECT
  orders.date AS order_date,
  orders.id AS id,
  orders.customer_id AS customer_id,
  orders.status AS order_status,
  charges.status AS payment_status,
  charges.payment_method AS payment_method,
  SUM(orders.item_count) AS item_count,
  SUM(charges.amount) AS amount

FROM
  dataform-demos.dataform_tutorial.shopify_orders AS orders
  LEFT JOIN dataform-demos.dataform_tutorial.stripe_payments AS charges
    ON orders.payment_id = charges.id

WHERE
  orders.id <= 999
  AND orders.item_count > 0
  AND orders.status <> 'internal'
  AND charges.payment_method IN ('debit_card', 'subscription', 'coupon')

GROUP BY 1, 2, 3, 4, 5, 6

config部分でそのSQLで作成したデータに対して簡易的なテストを行うことが出来る。
上記の例ではid, customer_id, amountのカラムにNULLが含まれていないことをテストしていて、NULLが含まれている場合はassertionsを実行したときに失敗になるはず。

組み込みassertions

nonNULL

NULLを含むカラムの場合はassertion実行が失敗する。
（だけどこれってテーブルにNOT NULL制約つければいいだけなのでは...？）
ただ、こういった制約系を一か所に集められるのは管理がしやすくていいかも。

config {
  type: "table",
  assertions: {
    nonNull: ["user_id", "customer_id", "email"]
  }
}
SELECT ...

rowConditions

条件式を文字列として書ける制約。
正規表現なども使えるので文字列に対しての制約として重宝しそう。

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

uniqueKey

ユニーク制約と同じ効果のassertion。
これもnonNULLと同じで、制約系を一括で管理できるのがメリットな気がする。

config {
  type: "view",
  assertions: {
    uniqueKey: ["user_id"]
  }
}
SELECT ...

uniqueKeys

これはおそらく複合キーと同じような意味合いだと思う。

config {
  type: "table",
  assertions: {
    uniqueKeys: ["user_id", "customer_id"]
  }
}
SELECT ...

カスタムassertion

組み込みassertionの場合は既存のSQLにconfigを追加しているようなイメージだったが、カスタムassertionはassertionのためのSQLを作成する。

config { type: "assertion" }

SELECT
  *
FROM
  ${ref("sometable")}
WHERE
  a IS NULL
  OR b IS NULL
  OR c IS NULL

config {type: "assertion"}の場合はSQLが0行を返すことを期待していて、上の例だとカラムa, b, cのいずれかにNULLが含まれていたら検知できるようになっている。
これであればある程度自由度の高い制約をかけることが出来る。

使えるかどうか...

テストといってももともとSQL内に書けるものが多く、メリットとしてはconfigの中にまとめて書けるので管理しやすいくらいな気がする...
「テストによってデータの品質管理が容易」みたいなことをDataFormのドキュメントで見た気がするが、別になにか特別な機能が用意されているわけではなさそうなので、これを目的としてDataFormを使うことはあまりなさそう。
（おそらく、DataFormのメインはSQLのパイプライン機能だと思うので、これは副次的なものなのかな）

Discussion