dbt incremental update bigquery

はじめに

dbtを使ってBigQueryのデータを管理する場合は、大きなテーブルでは incremental updateにすることで、コストを時間的にも経済的にも、下げることができる。

incremental updateのタイプ

• merge: unique_keyを設定することで、新しい値で更新することができるが、すべてのデータをスキャンするので、大きなテーブルには向かない
• insert_overwrite: 対象となるテーブルはpartitionされている必要があるが、対象のpartitionのみを全取り換えすることで、読み込みと書き込みの量を減らせる
• microbatch (beta): (ref)

microbatchはbetaなので今回はまだ読んでいないので、この時点で巨大テーブルのコストを下げるために使うのは insert_overwriteであることがわかります。

insert_overwriteの設定

以下のように設定できます。

{{ config(
    materialized="incremental",
    incremental_strategy="insert_overwrite",
    partition_by={
      "field": "created_date",
      "data_type": "timestamp",
      "granularity": "day",
      "time_ingestion_partitioning": true,
      "copy_partitions": true
    }
) }}

設定内容:

1. materialized="incremental": incrementalを指定することでテーブルが作成されます。
2. incremental_strategy="insert_overwrite": strategyを指定します。
3. partition_by には、partitionの設定を書いていきます
   1. created_dateでpartitionを行っています
   2. data_type で partitionするdata_typeを指定します
   3. granularity で day, hour などを指定することが出来ます。
   4. time_ingestion_partitioning とすることでingest timeを使ってpartitionすることができます。 (ref: https://cloud.google.com/bigquery/docs/partitioned-tables#ingestion_time)
   5. "copy_partitions": true で dynamic partitionの中でも tempのtableを bigquery copy table APIをつかって atomicに入れ替えるので、insertコストがかからず安く、さらに速くなります。

結論

dbtで管理する大きなBQテーブルには、以下の設定を!

• incremental
• insert_overwrite
• copy_partitions

Ref

• https://docs.getdbt.com/reference/resource-configs/bigquery-configs