【dbt】dbt ～ 更新 / Delete and Insert ～

dbt

■ はじめに

https://dk521123.hatenablog.com/entry/2023/12/07/060129

の続き。

今回は、Delete and Insert について、深堀っていく

目次

【１】構文
【２】サンプル

【１】構文

{{
    config(
        materialized='incremental',
        unique_key=['user_id' , ...],
        incremental_strategy='delete+insert'
    )
}}

【２】サンプル

[0] サンプルテーブル定義 

DROP TABLE IF EXISTS daily_sample_purchase_history;
CREATE TABLE daily_sample_purchase_history (
  purchase_id VARCHAR NOT NULL,
  item_name VARCHAR NOT NULL,
  number INT NOT NULL,
  created_at DATE NOT NULL
);

[1] 初期データ (1回目) 

INSERT INTO daily_sample_purchase_history (purchase_id, item_name, number, created_at) VALUES ('Y001', 'Apple', 2, '2023-12-01');
INSERT INTO daily_sample_purchase_history (purchase_id, item_name, number, created_at) VALUES ('Y002', 'Melon', 5, '2023-12-01');

SELECT * FROM daily_sample_purchase_history;
~~~~~~
"purchase_id","item_name","number","created_at"
"Y001","Apple","2","2023-12-01"
"Y002","Melon","5","2023-12-01"
~~~~~~

[3] モデル「models/full_sample_purchase_history.sql」定義 (1回目) 

{{
  config(
    materialized='incremental',
    unique_key=['created_at'],
    incremental_strategy='delete+insert'
  )
}}

WITH
updated_purchase_history AS (
  SELECT
    purchase_id AS purchase_id,
    item_name AS item_name,
    number AS number,
    created_at::DATE AS created_at
  FROM
    {{ source('demo_source', 'daily_sample_purchase_history') }}
)

SELECT * FROM updated_purchase_history

[4] dbt run (1回目) 

dbt run

[5] 確認 

SELECT * FROM full_sample_purchase_history;
~~~~~~
"purchase_id","item_name","number","created_at"
"X001","Apple","2","2023-12-01"
"X002","Melon","5","2023-12-01"
~~~~~~

[6] 追加データ投入 (2回目) 

-- 1回目のデータをクリア
Truncate table daily_sample_purchase_history;

-- 更新データ
INSERT INTO daily_sample_purchase_history (purchase_id, item_name, number, created_at) VALUES ('Y001', 'Orange', 12, '2023-12-01');
INSERT INTO daily_sample_purchase_history (purchase_id, item_name, number, created_at) VALUES ('Y003', 'Pineapple', 21, '2023-12-01');

SELECT * FROM daily_sample_purchase_history;
~~~~~~
"purchase_id","item_name","number","created_at"
"Y001","Orange","12","2023-12-01"
"Y003","Pineapple","21","2023-12-01"
~~~~~~

[7] dbt run (2回目) 

dbt run

[8] 確認 

SELECT * FROM full_sample_purchase_history ORDER BY purchase_id;
~~~~~~
"purchase_id","item_name","number","created_at"
"Y001","Orange","12","2023-12-01"
"Y003","Pineapple","21","2023-12-01"
~~~~~~

[9] 追加データ投入 (3回目) 

-- 2回目のデータをクリア
Truncate table daily_sample_purchase_history;

-- 更新データ
INSERT INTO daily_sample_purchase_history (purchase_id, item_name, number, created_at) VALUES ('Y001', 'Mango', 23, '2023-12-02');
INSERT INTO daily_sample_purchase_history (purchase_id, item_name, number, created_at) VALUES ('Y005', 'Banana', 34, '2023-12-02');

SELECT * FROM daily_sample_purchase_history;
~~~~~~
"purchase_id","item_name","number","created_at"
"Y001","Mango","23","2023-12-02"
"Y005","Banana","34","2023-12-02"
~~~~~~

[10] dbt run (3回目) 

dbt run

[11] 確認 

SELECT * FROM full_sample_purchase_history ORDER BY purchase_id;
~~~~~~

~~~~~~

関連記事

dbt ～ 基礎知識編 ～
https://dk521123.hatenablog.com/entry/2023/06/30/000000
dbt ～ 入門編 ～
https://dk521123.hatenablog.com/entry/2023/05/30/151003
dbt ～ 基本編 / Source ～
https://dk521123.hatenablog.com/entry/2023/12/08/111012
dbt ～ 更新 / 基礎知識編 ～
https://dk521123.hatenablog.com/entry/2023/12/07/060129
dbt ～ 更新 / Update or Insert ～
https://dk521123.hatenablog.com/entry/2023/12/19/224453
dbt ～ 更新 / DROP ＋ CTAS ～
https://dk521123.hatenablog.com/entry/2023/12/04/000000