遅延しがちだった、流通総額500億円／月超えの大量データ連携。処理時間を最大90%削減、エンジニアを増やさずに高速連携できるようにした過程
はじめまして。フルカイテン株式会社にて運用グループのマネージャーに就いている横井と申します。
本日は、【大量のデータを効果的に操作・分析するため、高速で複雑なクエリを効率的に設計・実行しているFULL KAITENのデータ連携が進化し続けてきた過程】について共有できればと思います。
参考：2024年8月時のデータ連携の数量
総店舗数: 9,888、総SKU数: 14,861,408、
1日の売上明細行数: 129,603,452、1日の在庫行数: 2,498,385,015
エンジニア：正社員2名、業務委託2名
※TOPSIC SQL CONTEST 成績上位者 の「hiraku」（米村）を筆頭にSQLマスターが揃っている。
以下のような方向けに弊社が直面した課題や現在の取組みをご紹介いたします。何か一つでも参考になれば幸いです。
・エンジニアの中でもお客様と近い位置で開発に携わりたい方
・社内のセールスやカスタマーサクセスと連動して課題解決や価値提供に携わりたい方
・効率的で高速なデータエンジニアを目指している方
開発の概要について
弊社の顧客は中・大手小売業になります。業種はアパレル／100均／スーパー／家電／ホームセンターなど多岐にわたり、皆さんもご存じの規模の大手企業がほとんどです。それぞれの企業が持つ商品の販売データは上述の通り想像を超える規模で、そのデータをFULL KAITENに連携していくという作業を行っています。
データ連携とは、顧客が保持しているマスタ、売上、在庫データをFULL KAITENのプロダクト側のデータ構造に落とし込んでいく開発になります。顧客のデータ構造はそれぞれ異なるため、ETL（データ抽出:Extract, 変換:Transform, 書き出し:Load）を開発して対応しています。
私がジョインする2022年当時は、体制は脆弱で開発の遅れが発生しており、障害検知の仕組みもなく、後手後手の対応に明け暮れていました。そして2024年現在、エンジニアの人数はそのままで当時の3倍を超えるアカウントを超えるデータを遅延することなく稼働させています。この２年間で私達が行った改善や施策についてご紹介します。
推進前（〜2022年上期）
私がジョインした当時はデータ連携のエンジニアは3名から4名で、新規契約アカウントに向けて専用ETL処理の開発、顧客やカスタマーサクセスからの問い合わせでデータの調査、日々運用していく中での障害対応を一手に担っていました。開発に集中したくても問い合わせや障害時には中断せざるを得なく効率的な仕事はしにくかったと思います。みんなの残業も多く疲れが見えているなと感じました。
当時はパンサー師匠（髙橋）がデータ連携の進捗管理を行うリーダー＆プロダクトのシステムエンジニア＆業務委託のマネジメントと一人何役もこなしており、本人としても満足なマネジメントが出来ていなかったと思います。
「一人でめっちゃ残業している人（パンサー師匠）が居るのですが大丈夫ですか？」と声をかけていいたら、いつの間にか自分が連携のリーダーになっていました（笑）
推進時（2022年下期〜2023年上期）
課題ばかりでしたので改善し放題です。
まずチーム作りのために朝会の実施でメンバーとの交流と作業の把握に務めました。どういうプロセスがあって何が停滞しているのか、どれだけのボトルネックがあるのかを見ていました。そこで挙げたのは次の項目です。
1. 属人的な作業の解消とプロセスの明文化
2. 障害検知の強化
3. ETL処理の高速化
4. ETL開発の効率化
2022年12月にはTOPSIC SQL CONTEST 成績上位者 のyone（米村）がジョインし、データ連携の改善を加速して進めることが出来ました。
1. 属人的な作業の解消とプロセスの明文化について
開発ドキュメントは存在していましたが、実際の手順と違っていたり内容が薄いところが沢山ありました。依頼を受けての調査方法や、障害発生時の対処手順など、エンジニアの頭の中にだけ存在していた知見を書き出し、ドキュメントの整備と拡充を行いました。
2. 障害検知の強化について
日々運用していると色々な障害が発生します。基幹システムや業務システムを開発されているようなSIerの方は入出力データ仕様がきっちり決まっていて運用が始まると間違いが起こることはないと思います。そのような方は驚くかも知れませんが、CSVファイルのカラムが変わったり、空のファイルが送られてきたり、数値なのに文字が入っていたりと割と頻繁に発生します。こういうファイルが来るのか〜とプチ盛り上がる時もあります。
当時はこういった症状を検知する仕組みが弱く、そのままデータ連携で取り込んで、後続のデータ集計処理に送り込んでいました。後処理まで障害が伝搬しますと、画面に数値が出なかったりデータ集計でとんでもない集計を行ったり、色々な問題が起きてしまい、復旧させるのにも手間取ります。
ここで障害検知の強化です。障害が発生してから対処するのではなく、事前に障害となりうる症状を検知し、Slackに通知が飛ぶようにしました。SRE (サイトリライアビリティエンジニアリング)でいうプロアクティブな状態です。この取り組みによってMTRS（平均サービス回復時間:サービスが回復するまでにかかる時間）は激減しました。
以前はデータ集計まで障害が伝播していると発覚までに数時間を要した障害が、内容によっては検知してから数分で復旧させてることができるようになりました。
3. ETL処理の高速化について
ETL処理の流れは次の通りです。
・データ抽出(Extract)：embulkを用いてCSVファイルのデータをredshiftに投入
・変換(Transform)：SQLを用いてお客様のレイアウトをフルカイテンのレイアウトに変換
・書き出し(Load)：後続のデータ集計に渡すためにS3にアンロード
速度改善を行うために様々な施策を実施しました。1つや2つの施策ではなく効果が見込める対応を次々と実施し、平均54% 最大90%の時間短縮、処理時間の例として170分から15分へと大幅な短縮を実現しました。
具体的には
・SQLスロークエリーの対処
・データ集計に渡すデータの絞り込み（毎回巨大なデータを渡していた）
・pythonユーザー定義関数の呼び出し数を極限にまで減らす（UDFの呼び出しコストはとても高い）
・Redshiftの同時実行調整
以前は処理時間を要したため、お客様がFULL KAITENを利用開始できるのは午後からでしたが、午前中からの利用が可能になりました。この改善はコスト削減効果とともに利用者のプロダクト満足度に貢献する重要な取り組みとなりました。
4. ETL開発の効率化について
ETL開発の流れとしては、まずお客様から提供して頂いたファイルのレイアウトの確認から始まります。
マスタデータ、売上、在庫データを連携して頂く場合に、お客様ごとにレイアウトが違い、外部結合キーの条件も違います。場合によっては複数のマスタデータがあり、売上や在庫も店舗とECで別れていたり拠点ごとに存在していたりと様々です。こういった変換仕様を設計シートに起こしてから開発に入ります。ここでも設計シート記載事項の見直しを行い開発にスムーズに繋げられるように務めます。
次に開発部分になります。お客様ごとにレイアウトが違うと言ってもSQL的に似たような処理を実行する部分もあり、テンプレートや一次テーブルを用意して共通化する対応を行いました。
これらの対応によって開発工数10%削減、コード重複率10%削減を達成しました。
停滞期
上記のような改善が順調に進み、苦労して停滞したような時期はありませんでした。
洗練期（2024年）
改善に終わりはなく、障害検知できるパターンの追加、パフォーマンスチューニングなどは日々行っています。
現在行なっている施策としては設計シートの改善になります。お客様にご契約して頂き2ヶ月半でFULL KAITENを使えるように設計と開発を進めています。特別な理由がなければスケジュールを順守できている状態ですが、後工程からの後戻りが発生したり、遅れを取り戻すために残業している状況はあります。（とはいえ、私の経験から感じる一般的な企業のエンジニアと比較して残業は少ないです）
前工程の成果物の品質向上
・変換仕様に認識齟齬が無いかお客様への早期フィードバック
・様々なレイアウトに対応してきたナレッジを生かしてパターン化
・開発や運用フェーズで困らないように、後工程からも設計シートにフィードバック
など品質向上に繋がる活動を注入していきます。
「新規顧客の立ち上がりがスムーズだね」とか「顧客からの問い合わせで、レスが早くて助かる」と言わせたいです。
今後について
様々な施策を実施してきましたが、現時点では、エンジニアの人数は変わらずに、3倍となったアカウントの開発と運用をスムーズに実施しています。
今後は「まだ10倍アカウントくらい増えても平気です！」と涼しい顔をして運用していたいです。
現在、大量のデータを効果的に操作・分析し、遅延なく開発ができているDEチーム内では、「こんなデータ構造を変換するのか！？」と驚くような無理難題でも楽しむカルチャーができています。 SQL自体はモダンな技術ではありませんが、大量データを扱う時代だからこそ必須な技術です。泥臭くやり切る力のある人が強いのかなと思います。
いかがでしたでしょうか。このように、私達が直面した課題や現在の取組みが、誰かの力になれば幸いです。
補足：メンバーに聞いたチームと仕事の魅力
yone（米村）の回答
【DEチームの技術力】
・SQLスキルが高い
・改修が速い
・品質が高い（正確で堅牢なコード）
・複雑な処理にも対応できる
・データクリーニング力が高い（ほとんどどんなデータでも対応できる）
【運用力が高い】
・ETLのモニタリングがしっかりしている
・回復が容易な構成になっている
コードメンテナンスと改善
・継続的なリファクタリング
・共通化と変更容易性の向上を頑張っている
hashimoto（業務委託）の回答
【保守性の高いコードや処理の性能面(速度等)を実装で意識】
・課題に対する解決のスピード感
・数年前と比べて障害検知機能が充実し、3倍以上の稼働を保守している
【メンバー＆カルチャー】
・技術力だけでなく、困りごとに対し協力できる空気感がある
・実装のみならず設計まで出来てしまうyoneさん
・データコネクトを一手に担うwatanabeさん
・視野がすごく広く細やかな気遣いができるYさん（筆者）
・複雑な顧客データを設計に落とし込む大変な作業でありながら、
見やすい設計シートにすべく配慮してくれるデータコンサルタントチーム
・迅速で丁寧で正確な対応のテクニカルサポートチーム
・お客様との調整を快く引き受けてくれるカスタマーサクセスチーム
・エラー対応、迅速に対応してくれるバックエンドエンジニア
watanabe（業務委託）の回答
【DEチームの技術力】
・移動指示アプリをお客様向けツールに組み替える技術
・既存の移動指示アプリを再構築し、お客様向けの専用ツールとして提供できる高度なプログラミングスキルとシステム設計能力
・複雑なクエリを効率的に設計・実行し、大量のデータを効果的に操作・分析するための高度なSQLスキル。
・データベースのパフォーマンスを最大限に引き出すためのクエリ最適化とインデックス設計の専門知識。
【AWSサービスの深い理解と最適化能力】
・AWSの各種サービスや現状のシステム問題点を詳細に把握し、最適なサービスへの移行や導入を実現する能力
・システムのパフォーマンスを向上させながら、運用コストの削減を実現するためのコスト分析と最適化スキル
継続的なリファクタリングによるパフォーマンスと保守性の向上
・コードの品質を常に向上させるため、パフォーマンスと保守性を考慮したリファクタリングを継続的に実施する能力。
横井（筆者）の回答
【データ連携開発力の高さ】
・顧客ごとにマスタ、売上、在庫データの構造が違い、FKのプロダクト側では同じデータ構造で扱うために ETL(（データ抽出:Extract, 変換:Transform, 書き出し:Load）を開発している
・TOPSIC SQL CONTEST 成績上位者 の「hiraku」（米村）を筆頭にSQLマスターが揃っている
・顧客ごとのデータ変換部分は複雑なクエリーになりがち。でも「こんな無茶なデータ構造を変換するのか！」という状況も楽しめる
・データベースのパフォーマンスを最大限に引き出すためのクエリ最適化とインデックス設計の専門知識をもっている
・お客様には早くサービスを使って欲しい。データ連携の初回mtgから 2ヶ月半という短い期間で開発しきる力
・FKのプロダクトでは売上や在庫の数値が重要。お客様の認識している数値になっているか妥協せずに確認する
・コードメンテナンスと改善。継続的なリファクタリングで共通化と変更容易性の向上に努めている
AWSサービスの深い理解と最適化能力
・AWSの各種サービスや現状のシステム問題点を詳細に把握し、最適なサービスへの移行や導入を実現する能力
・システムのパフォーマンスを向上させながら、運用コストの削減を実現するためのコスト分析と最適化スキル
【改修対応力の高さ】
・FKの利用が進むと、後からECのデータを取り込んだり、商品グループを見直したり、改修が必要になってくるが、爆速で対応している
【運用力の高さ】
・日々運用ではどうしても障害が発生する。ファイル未到着だったり、レイアウトが変わっていたり、データ不整合が発生したり
・モニタリングがしっかりしているので、即時検知し即座に対応している。
・回復が容易な構成になっている
・今では2年前のアカウントの3倍以上の運用を平然とこなしている
【FULL KAITENカルチャー】
・困りごとに対して、協力できる空気感、安心感
・他チームを巻き込んだり、他チームの成果に貢献するような活動も積極的に行う
この記事が気に入ったらサポートをしてみませんか？