データウェアハウスプロジェクトは、長い間、ほとんどの大企業のITインフラストラクチャの一部でした。 ETLプロセスはこれらのプロジェクトの一部ですが、開発者はこれらのプロセスを設計および保守するときに同じ間違いをすることがあります。 これらのエラーの一部は、この投稿で説明されています。



私はすぐに議論の範囲を狭め、用語に同意したいと思います：

• データウェアハウス（Datawarehouse、DWH）は、従来のSQL DWH（Oracleデータベース、MS SQL Serverなど）を意味します。
  
  
• DWHをモデル化する場合、通常、単一バージョンの真実（単一バージョンの真実）と歴史的真実（歴史的真実）の概念が暗示されます。
  
  
• ETLプロセス（抽出-変換-ロード）は、1つ以上のソースシステムからDWHにデータをロードするプロセスを指します。
  
  
• DWHは昨日作成されておらず、現在、いくつかの開発チームが独自のプロジェクトで独立して作業しています。
  
  

ETLという用語は、略語の「単純な」デコードのために、さまざまな方法で解釈されることがよくあります。 実際、ETLタスクはデータ移動タスクのサブセットにすぎません。 Kimballの著書「The Data Warehouse ETL Toolkit」では、ETLプロセスで実行する必要がある3つの操作を区別しています。

1. 分析アプリケーションに最も便利な形式でデータをダウンロードします。
   
   
2. データをダウンロードするプロセスでは、追加情報でデータを充実させます。
   
   
3. データの系統（起源）を記録して文書化します。
   
   

最初のポイントは非常に明白なので、スキップします。 2番目の段落では、データをある場所から別の場所にリロードするだけでなく、たとえば新しい計算された属性、技術属性（ブートセッションID、ブート日付、ソースシステムなど）でプロセスを強化する必要があると述べています。 3番目は、どのレコードについても、このレコードがDWHでどこでいつ表示されたか、いつどのプロセスで変更されたかを追跡する機能があるはずだと言います。



一般に、ETL開発者のほとんどのエラーの本質は、この図からライフルールを無視することで説明できます。







将来、Oracle 11gに基づくDWHの例が使用される予定です。 それでは始めましょう。

1.ビジネスロジックでシステム日付（または同様の関数）を使用する

特に経験の浅い開発者の間で、最も単純で最も一般的な間違いの1つ。 ビジネスルールがあると仮定します。「ロードの夜の時間帯」に、その日（close_dateフィールド）にクローズされた注文をアンロードします。 結果は、このSQLステートメントのようなものになる場合があります。



<....>をtarget_tableに挿入します

注文から<....>を選択します

ここで、close_date> = sysdate（）-1



sysdate（）に日付だけでなく時刻も含めることができることを忘れたとしても、かなり一般的な理由でETLプロセスの通常の操作に違反するときにこのスクリプトに問題があります（元のシステムは新しいバージョンにアップグレードされますが、新しいETLプロセス、一時テーブルスペース内の場所などが原因で、ソースシステムとの接続が失われました）。 つまり ETLプロセスを何らかの理由で再起動するか、しばらく一時停止してから再起動する必要がある瞬間。 何らかの理由でこのプロセスが1日に2回起動された場合にも、興味深いことが起こります。



通常、このエラーの解決策は簡単です。このプロセスの呼び出しをパラメーター化し、必要に応じてsysdate（）を再定義の可能性のあるデフォルト値として使用します。 CDを維持するという観点からデルタを処理するために日時型のフィールドを使用することはあまり最適ではありませんが、代わりに、いくつかの個別のフィールド（たとえば、ブートセッションの整数idなど）でデルタを使用することをお勧めします

2.データプロファイリングは開発前に行われていません

すべてのルールと手順のソースシステムに従って最も文書化および開発されたものでさえ、開発者またはサポートチームの多数の保証にもかかわらず、通常は不正確または一貫性のないデータを含んでいます。 そして、バリケードの反対側の正確性の保証に依存することは、通常、開発の終わりに問題を抱えています。 データソース（テーブル、ファイル、xml、jsonなど）は、DWHの論理モデルに準拠しているかどうかを確認する必要があります。 データをプロファイリングするためのさまざまなツールがあり、ETLツールに組み込まれているものとそれらに依存しないものの両方があります。 最も人気のあるチェックをリストします。

チェック＃1：ソースデータの識別子と自然キーの一意性

識別子と自然キーの違いは、通常、識別子は文字列を技術的に識別する何らかの種類の代理値であり、自然キーはビジネス上意味のある値または値の組み合わせであることです。



Order_detailsテーブル：

order_details_id	document_position	order_id
35346346	10	1224114
35346365	20	1224114
...	...	...
35345464	10	1224438

この例では、order_details_idが識別子であり、document_position + order_idの組み合わせが自然キーです。



例：分散システム（インスタンスベース）からDWHにデータをロードするプロジェクトに参加し、ネットワークインフラストラクチャオブジェクトの記録を保持しました。 このシステムの開発者は、このオブジェクトのIDが一意であることを青い目で確認し、単語の確認でテーブル上のソースシステムに一意のインデックスを表示しました。 キャッチはすぐには明らかではありませんでした。これらのidの一意性はシステムの1つのインスタンスのフレームワーク内にのみ存在し、すべてのインスタンスからすべてのデータをダウンロードしようとすると、一意性に問題がありました。 その結果、一意性を確保するために、データモデルを変更し、エンティティ「ネットワークオブジェクト」の自然キーを追加の「インスタンス」フィールドで拡張する必要がありました。

チェック＃2：データ型

フィールドの名前がOrder_nrである場合、必ずしも数値のみが含まれているとは限りません。英数字のシーケンスが存在する可能性があります。 また、フィールドの長さを常に確認する価値があります。 この問題は、通常、ファイルデータソースでよく見られます。通常、データベーステーブルの型は適切です。

チェック＃3：参照整合性（FKチェック）

開発者がソースシステムのER図を表示し、DEV環境でテーブル間の既存のFKを表示し、一般的に母親がすべてが制御されていることを誓うという事実は、「ダングリング」レコードの存在をチェックしない理由ではありません。 なぜなら 実稼働環境では、パフォーマンスを改善するためにDBAがすでにこのチェックを無効にしていることに気付いていない可能性があります（もちろん、これを開発マネージャーと調整した後、誰も責任を負わない）。 また、参照整合性の問題は、ファイルデータソースでは非常に一般的です。 また、 遅延データスクリプトの使用を忘れないでください（たとえば、データが今日合意されて到着した場合、これが6か月後にも発生するという事実からはほど遠いです）。

チェック＃4：NULL値

NULL値の主な問題は、NULL <> NULLであるため、NULLを含む可能性のあるフィールド全体で結合を持つクエリは、予測できない結果を返します。 したがって、すべての重要なフィールドをnvl（）構造でラップする価値があります。 NULLを非キーフィールドにロードするか、いくつかのデフォルト値に置き換えることについて、別のホリバーがあります。 DWHを使用するためのより標準化されたアプローチのためにNULLを置き換えるという考えに近づいていますが、これを常に行うべきだと主張する勇気はありません。

チェック＃5：日付

日付を持つフィールドのチェックは通常、最も複雑です 標準チェックに加えて、データベースの観点から受け入れられるすべての日付がDWHの観点からのものではないという事実を考慮する必要があります：日付「21-07-1007」は、セルラーサービスの提供の契約の締結日にはほとんど有効ではありません。 DWHをモデリングするとき、いわゆる 「時間の始まり」および「時間の終わり」の日付（他の名前も可能）、およびこの時間範囲に該当しない日付は、デフォルト値に置き換える必要があります。



日付を格納するためにvarchar（8）などのデータ型を使用する場合（たとえば、 '20151201'の形式）、特別な言及に値します。 ここでのチェックの数はさらに多くなければなりません。

3. GROUP BYまたはDISTINCTを使用して重複を削除する

DWHは通常、ソースから来るすべてのデータをロードするという事実にもかかわらず、明らかに重複するデータが入ってくるシナリオがあります。 しかし、自然キーの一意性は、重複から1つのエントリのみを必要とします。 重複を削除するには、2つの間違った方法があります。

間違った方法＃1：GROUP BY

クライアントのアドレスをロードし、理論的には1つのクライアントにアドレス情報を持つ複数のレコードが来る可能性があることを知っていると仮定します（通常、同期などの問題により完全に重複しています）。 問題を「正面から」解決したいという欲求に負けた開発者は、次のリクエストを書くことができます。



customer_addressに挿入します

customer_id、max（street_name）、max（house_nr）を選択します

source_tableから

customer_idでグループ化



1人のクライアントの2つの本当に異なるレコードが入力されると問題が始まります（たとえば、オペレーター入力エラーが修正されましたが、両方の記録オプションがデータソースにありました）。

customer_id	street_name	house_nr
1321	モスコフスカヤstr	127
1321	プーシキンスカヤstr	34

リクエストは、次の結果を返す場合があります（ロケールによって異なります）。

customer_id	street_name	house_nr
1321	プーシキンスカヤstr	127

ソースデータにはそのようなエントリはありませんでした。DWHユーザーには妥当な質問があるかもしれません。 実際、ETLプロセスの3番目の要件に違反しています。元のシステムで追跡できないレコードがDWHにロードされました。つまり、そこにはありません。 そして、これはETL開発者の明らかな間違いです。

間違った方法＃2：DISTINCT

上記のシナリオでの「直接的な解決策」の2番目のオプションは、DISTINCTを使用して重複するレコードを削除することです。



customer_addressに挿入します

個別のcustomer_id、street_name、house_nrを選択します

source_tableから



この場合、1つではなく2つのレコードが取得され、自然キーの一意性が侵害され、ETLプロセスが失敗するため、異なる属性を持つ重複レコードのペアが以前に識別されます。

正しい方法の一つ

同じ自然キーを持つが、属性が異なる2つのレコードを持つという問題を解決する価値はありますか？ 明らかに、データモデルに変更が加えられていない場合、すべてのレコードから正しいレコードを1つだけ選択する必要があります。 所定の基準に従って選択する必要があります。情報が非常に重要な場合は、さまざまなデータ品質シナリオを実装できます。そうでない場合は、最後にロードされたシナリオを正しいレコードとして使用できます。



customer_addressに挿入します

（customer_id、street_name、house_nrを選択します（

customer_id、street_name、house_nrを選択し、

row_number（）over（customer_idによるパーティション、change_datetime descによるオーダー）row_num

source_tableから）

ここで、row_num = 1



一般に、DWHのレコードはビジネスルールに応じてデータソースまで追跡でき、「不可解な」レコードを作成しないことを忘れないでください。

4.ソースシステムから「静的」スクリプトを使用する

多くの場合、DWHエンティティのビジネスロジックは、SQLスクリプトの形式でソースシステムの開発者または分析者から提供されます。 これはETL開発者にとって大きな助けですが、彼らが言うように、「贈り物を持ってくるデンマーク人に注意してください」：原則として、これらのスクリプトはある時点で元のシステムの条件付きの「静的」状態を修正し、ETL開発者は通常データのダイナミクスを監視します変更のみをダウンロードします（「デルタ」）。 これらの「静的な」SQLスクリプトで何が警告されるべきでしょうか？ 以下がその一部です。

• 集約関数（SUM、AVG、COUNTなど）
  
  
• INおよびEXISTSステートメント
  
  
• ウィンドウ関数（OVER（PARTITION BY ...））
  
  

そのようなスクリプトの例：



order_idをorders_from_callsに挿入します

注文からorder_idを選択します

where order_id IN（order_id <> -1の呼び出しからorder_idを選択）

and changed_date> $ last_loaded_date



すべてが論理的なようです。order_from_callsテーブルに読み込むには、呼び出しテーブルで参照され、最後の変更の日付が最後の読み込みの日付よりも大きいすべての注文を読み込みます。 ここで、 コールテーブルがDWHで更新されておらず（たとえば、別のソースシステムからロードされ、何らかの理由で通信が切断されている）、このリクエストが注文IDをロードしなかったとします。 その後、 呼び出しテーブルが正しくロードされ、そこにこれらの欠落している注文IDが表示されましたが、それらはもうorder_from_callsテーブルに読み込まれません 。 注文テーブルでは何も変更されておらず、このリクエストを新たに開始しても何も提供されません。 したがって、この場合、 ordersテーブルだけでなく、 callsテーブルでもデルタを追跡する必要があります。

5.開発用の少量データの開発

原則として、DEV環境で開発するためのETL開発者は、ETLプロセスの開発とデバッグを行うことが提案されている本稼働システムからデータのごく一部をアンロードします。 残念ながら、このような少量のデータで開発されたソリューションは、通常、パフォーマンスの低下、中間テーブルのスペース不足など、生産システムでさまざまな問題を引き起こします（たとえば、開発者は、中間テーブルのビジネスロジックのステップを1から順にオーバーロードすることを決定しました）別の-しかし、生産的なデータシステムでは、それが多すぎることが判明し、一時テーブルのテーブルスペースが突然終了しました）。



残念ながら、ETL開発者は、さまざまな規制や指示、製品と同じデータ量の本格的なDEV環境の予算不足などのために、このエラーを自分で常に解決できるわけではありません。 したがって、プロジェクトのリスクとして考慮する価値があります。



解決策の1つは、プロジェクトの最後ではなく、少なくとも途中で問題を特定するために、プロジェクトの段階をより小さな段階に分割し、リリースをより頻繁に行うことです。

6.技術および営業日の誤用

DWHには2種類の日付があります。営業日と技術日付です。 違いはその起源にあります。営業日は、データソースから取得した日付、またはビジネスルールに従って作成された日付です。 技術日付は、ETLプロセスまたはDWH自体によって生成された日付です。 そして、非常に頻繁に間違って使用されます：

＃1技術日付として使用される営業日

エンティティがSCD2（緩やかに変化するディメンションタイプ2）として履歴化され、データソースにフィールド「_from」および「_to」が含まれ、ETL開発者がデータ有効範囲として使用する場合、すべての有効範囲を具体的に保証する必要があります各自然キーには、1）ばらばら、2）範囲間にギャップがない、3）これらの日付範囲の組み合わせが、DWHに設定された「時間の始まり」から「時間の終わり」までの日付範囲と一致します（たとえば、日付のペアの場合があります） 「01.01.1000 そして「31.12.9999」または「1111年11月11日」と「09.09.9999」）。 原則として、ソースシステムの開発者はそれほど気にしません。また、「日付範囲の不整合」の規則が通常尊重される場合、問題2および3が通常発生します。 いずれにしても、一般的な推奨事項は、SCD2に営業日を使用するのではなく、技術的な日付を生成することです。

＃2営業日として使用される技術日付

多くの場合、データソースはキー日付を追跡するためのフィールドを提供しません。たとえば、ドキュメントには終了ステータスのみがあり、このイベントが発生したときのタイムスタンプはありません。解決策として、技術日付「_from」と「_to」を使用することが提案されていますETLプロセスによって生成されました。 ただし、このソリューションは、最初のETLプロセスの障害（たとえば、数日間ETLプロセスを停止する）まで機能します。障害は月曜日に発生し、回復は水曜日に発生し、 ソースシステムはこれまで非常にうまく機能し、作成されたすべてのドキュメントは水曜日に作成されたとおりにロードされます。 一般的な場合、データソースがユーザーが必要とするすべての日付を提供せず（技術日付を使用して）エミュレートできる場合、「歴史的真理」シナリオは実行できませんが、この場合、このシナリオは1年後に文書に記述して説明する必要がありますユーザーは、月曜日と火曜日のクローズドドキュメントの数がゼロであることと、水曜日のトリプルドキュメントの数に驚くことはありませんでした。

7.「機械的な」実装

これはエラーを特定するのが最も難しいものの1つであり、実際にはETL開発者のエラーではなく、DWHアーキテクトのエラーです。 しかし、チームはプロジェクトに取り組んでおり、同僚を支援する必要もあります。



ソースシステムの開発者とアーキテクトの用語の違いにより、DWHのターゲットエンティティが誤ってモデル化されていることがあります。 ソースシステムの開発者はソースシステムの観点から考えますが、DWHアーキテクトはさまざまな統合スキーム、単一のDWHで異種ソースシステムの多くのオブジェクトを接続する方法を検討する必要があります。



この種の典型的な問題の1つとして顧客エンティティの例を説明します。データソースには一意の自然キーを持つ顧客テーブルがあり、参照整合性が整っています。 この表に基づいて、DWHで顧客エンティティが作成されました。 名前に基づいて、このテーブルの1つのレコードが1つのクライアントに対応する必要があると仮定するのは論理的ですが、実際には、同じ実際のクライアントが同じ属性で異なる自然キーを持つ複数のレコードを持つことができることが判明しました。 そして、これは、たとえば企業の顧客の総数を計算するためにこのエンティティを使用したDWHユーザーにとって不快な衝突につながります。その結果、このエンティティを「customer_record」と「customer」の2つに分割し、FKを介して比率M：1で接続することにしました。



また、ETL開発者が仕様に従ってすべてを「機械的に」実装した場合、彼は確かに無罪ではなかっただろうが、これに気付く機会があった。いずれにせよ、建築家と比較して、彼は「雲に浮かぶ」建築家とは対照的に、「地上で」と条件付きで働きます。



一般に、「機械的な」実装のいくつかの症状に言及することができます。

• ソースシステムから「継承」テーブル名
  
  
• 既存のデータストリームから新規にビジネスロジックをコピーする
  
  
• 主に「パイロット」プロジェクトジョブを使用する
  
  

「機械的な」実装のリスクを最小限に抑えるために何をすべきか：

• データの一部を潜在的に「切断」する可能性のある結合を使用して、ビジネスルールを慎重に分析します（通常、左外部結合は内部結合よりも望ましい）
  
  
• 一方、不正または不完全な条件のためにデータを「伝播」できる「疑わしい」結合をチェックします
  
  

この点を要約すると、DWHで何をロードするか、名前がコンテンツと一致するかどうかを常に理解し、必要以上のデータをロードすることはできません。

おわりに

もちろん、このリストは完全なものではありませんが、この記事がすでに期限、マイルストーン、リリース、バグ修正で汚染されている頭に何らかの秩序をもたらすことを願っています。