原本の整形とスキーマ契約

目標

文字列だけでできた元データの表をプロファイリングし、形式を正規化し、クリーンテーブルとリジェクトテーブルに分けてロードする、1サイクルを完成させます。

なぜ重要なのか

データパイプラインで最も危険な事故は、失敗ではなく静かな消失です。ロードに失敗した行をそのままスキップすると、何のエラーも出ず、数字だけが少し小さくなります。数か月後に見つかると、いつから何が抜けたのかを追跡する方法がありません。

そのため、クレンジングのパイプラインは、保存則を守る必要があります。クリーンの件数とリジェクトの件数の合計が、元データの件数とちょうど同じで、どちらにも属さない行があってはいけません。この検証をパイプラインの中に入れておけば、消失が起きた瞬間に明らかになります。

リジェクトの理由も、一緒に残す必要があります。理由なく捨てられた行は復旧できません。そして、値がないことと0は違うという点も、覚えておいてください。空の金額を0で埋めた瞬間に、「情報が欠落していた」という事実そのものが消えます。

ステップ

対象はstaging.orders_raw表です。

1. t_raw_profile表を作成します。カラムはcol、bad_countで、ちょうど3行です。
   • customer_email: 値が空(NULL)の行数
   • amount: NULLまたは空白だけの行数
   • order_date: YYYY-MM-DD形式ではない行数
2. v_raw_datesビューを作成します。カラムはraw_id、order_date_parsed(date型)で、すべての行がパースされる必要があります。形式は、YYYY-MM-DD、MM/DD/YYYY、YYYY.MM.DD、YYYYMMDDの4つです。
3. v_raw_amountビューを作成します。カラムはraw_id、amount_num(numeric型)で、通貨記号と千の位のカンマを取り除き、空文字列は0ではなく値なしとして扱います。
4. v_raw_statusビューを作成します。カラムはraw_id、status_normで、前後の空白を消して、小文字に統一します。結果は3種類である必要があります。
5. orders_clean表を作成します。カラムはraw_id、order_ref、customer_email、order_date、amount、statusの順で、メールがあり、金額が空でない行だけを、型を備えてロードします。
6. orders_reject表を作成します。カラムはraw_id、reasonで、ステップ5で除外された行が、理由と一緒に入ります。理由は空であってはいけません。
7. orders_cleanの件数とorders_rejectの件数の合計が、staging.orders_rawの件数と同じで、両方に同時に入った行がないかを確認します。
8. schema_contract表を作成します。カラムはcolumn_name、data_typeで、orders_cleanのカラムと型を、そのまま入れます。

参考

• 正規表現マッチ: order_date ~ '^[0-9]{4}-[0-9]{2}-[0-9]{2}$'
• 形式ごとのパース: to_date(order_date, 'MM/DD/YYYY')
• 空文字列を値なしに: nullif(btrim(값), '')(プレースホルダーは値です)
• カタログの参照: SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'orders_clean'
• よくある間違い1: 空文字列はNULLではないので、IS NULLだけでは捕まえられません。
• よくある間違い2: MM/DD/YYYYをDD/MM/YYYYとして読むと、12日まではエラーも出ずに、静かに間違います。

元データの欠陥を数えてみる

t_raw_profile表を作成します。カラムはcol、bad_countで、ちょうど3行です。

• customer_email: 値が空(NULL)の行数
• amount: NULLまたは空白だけの行数
• order_date: YYYY-MM-DD形式ではない行数

カラムごとに、何が欠陥かの定義が違います。空文字列はNULLではないという点に注意してください。

4つの日付形式をパースする

v_raw_datesビューを作成します。カラムはraw_id、order_date_parsed(date型)で、すべての行がパースされる必要があります。形式は、YYYY-MM-DD、MM/DD/YYYY、YYYY.MM.DD、YYYYMMDDの4つです。

正規表現で形式を判別してから、それぞれ別のパースルールを適用します。月と日の順序が異なる形式が1つあります。

金額から記号とカンマを取り除く

v_raw_amountビューを作成します。カラムはraw_id、amount_num(numeric型)で、通貨記号と千の位のカンマを取り除き、空文字列は0ではなく値なしとして扱います。

文字を消してから、数値に変えます。空文字列は、0ではなく値なしとして扱う必要があります。

ステータス値を正規化する

v_raw_statusビューを作成します。カラムはraw_id、status_normで、前後の空白を消して、小文字に統一します。結果は3種類である必要があります。

前後の空白を消して、大文字小文字を統一すると、何種類に減るかを確認してください。

クリーンテーブルにロードする

orders_clean表を作成します。カラムはraw_id、order_ref、customer_email、order_date、amount、statusの順で、メールがあり、金額が空でない行だけを、型を備えてロードします。

メールがない行や、金額が空の行は除外します。カラムの型を正しく備える必要があります。

リジェクトテーブルに理由を添えて残す

orders_reject表を作成します。カラムはraw_id、reasonで、ステップ5で除外された行が、理由と一緒に入ります。理由は空であってはいけません。

なぜ捨てたのかを、必ず書きます。理由が空だと、あとで誰も復旧できません。

保存則を確認する

orders_cleanの件数とorders_rejectの件数の合計が、staging.orders_rawの件数と同じで、両方に同時に入った行がないかを確認します。

クリーンとリジェクトの合計が元データと同じである必要があり、両方に同時に入った行があってはいけません。

スキーマの契約書を残す

schema_contract表を作成します。カラムはcolumn_name、data_typeで、orders_cleanのカラムと型を、そのまま入れます。

カタログからカラム名と型をそのまま読み取って表にするほうが安全です。