月50〜100GBの売上CSVをFTPで受け取り、最小構成でPower BIに載せたい——そんな要件に対し、SynapseやDatabricksなしで「Azure SQL Database（サーバーレス対応）＋Azure Data Factory（ADF）」だけで実現する実装ガイドです。アーキテクチャ、コスト、設計、運用・監視、セキュリティまでを現場目線で具体化します。
目次
前提とゴールの整理
質問の背景
- データ特性: FTP から受信する CSV（50〜100GB／月）を取り込み、結合や整形を経て Power BI で可視化したい。
- 制約: 開発リソースが少ない。現在 Azure／SQL Server 未導入。
- 利用状況: Power BI プレミアム（PPU または容量）を 15 ユーザーで利用中。
- 要件: できるだけシンプルかつ予算を抑えた Azure 構成。Synapse など大規模向けサービスは避けたい。
最小で強い解：Azure SQL Database（サーバーレス可）＋ Azure Data Factory
扱うデータが月間 50〜100GB、更新頻度が日次〜月次であれば、DWH 専用基盤に投資せずとも PaaS の組み合わせで十分にスループットと可用性を確保できます。コアは以下の 3 サービスです。
| 役割 | 推奨サービス | 主なメリット | 概算コスト* |
|---|---|---|---|
| データベース／マート | Azure SQL Database （汎用層 or サーバーレス 2〜4 vCore） | 数十GBクラスでも十分な性能／SLA Power BI へネイティブ接続（Import／DirectQuery） サーバーレスならアイドル時に自動休止で課金最適化 | 常時稼働: 約 $180/月 サーバーレス休止活用: 約 $60〜100/月 |
| ETL／ELT | Azure Data Factory | GUIベースで低コード開発／FTPコネクタ標準搭載 従量課金で動かした分だけ支払い | 月1回100GB処理で約 $20〜40/月 |
| 可視化 | Power BI プレミアム（既存） | 増分更新（Incremental Refresh）対応、共有・配布・大規模モデルに強い | 既存契約を利用 |
*2025年10月時点、東日本リージョン相当の参考感。為替・リージョン・割引適用で変動します。
全体アーキテクチャ（シンプル構成）
外部FTP
│（FTPS/SFTP 推奨）
▼
Azure Data Factory（トリガ & パイプライン）
├─ Copy Activity：FTP → Azure Blob（ステージング）
├─ 検証/前処理：検品・解凍・スキーマ判定
├─ Copy Activity：Blob → Azure SQL（stg_ にバルクロード）
└─ Stored Procedure：SQL側でJOIN・整形（ELT）
▼
Azure SQL Database（stg → dwh/mart）
▼
Power BI（Import＋増分更新 or DirectQuery）
実装ステップ（ゼロから運用まで）
リソース設計と命名
- リソースグループ：
rg-sales-analytics-prod - ストレージアカウント：
stsales<uniq>（Blob + SFTP有効化） - Data Factory：
adf-sales-prod - SQL サーバー：
sqlsrv-sales-prod／データベース：sqldb-sales（汎用層 or サーバーレス） - タグ：
costCenter=BI,env=prod,owner=DataTeam
ネットワークとセキュリティの最小構成
- FTPは可能ならFTPSまたはSFTPでの受け取りに切替。相手都合で無理な場合でも ADF で暗号化転送を強制。
- ストレージ・SQL ともにマネージドIDで連携し、キー・パスワードの配布を廃止。
- 外部公開を絞るならPrivate Endpointとファイアウォールでアクセスを制限。
- データは既定で暗号化（TDE/At-Rest）。転送は HTTPS/TLS。
ADF パイプラインの基本形
| 順序 | アクティビティ | 役割 | ポイント |
|---|---|---|---|
| 1 | Get Metadata / List | FTP上の新着ファイル検出 | ファイル名規約（例：sales_YYYYMMDD_region.csv）で日付と地域を抽出 |
| 2 | Copy Activity (FTP→Blob) | ステージング保存 | gzip/zip を自動展開。再実行に備え version 付与（例：raw/2025/10/01/） |
| 3 | Data Flow or Script | 検品・スキーマ検査 | カラム数/区切り/UTF-8 をチェック。異常は隔離フォルダへ。 |
| 4 | Copy Activity (Blob→SQL) | バルクロード | ターゲットは stg_* テーブル。並列コピーで IOPS を活用。 |
| 5 | Stored Procedure | ELT（JOIN/整形/サロゲートキー採番） | MERGE と差分（ウォーターマーク）で高速反映。 |
| 6 | Web/Email（任意） | 結果通知 | レコード件数、成否、次回実行時間を通知。 |
スキーマ設計（スター型推奨）
分析視点が明確な売上データはスター・スキーマが最も運用しやすく、Power BI での計算も安定します。
| テーブル | 主な列 | 備考 |
|---|---|---|
f_sales（事実） | sales_id, date_key, product_key, store_key, qty, net_amount, tax_amount | 金額は税込/税抜を分け保持。発生日/計上日など日付キーを分離。 |
d_date | date_key, date, y, q, m, week_of_year, is_holiday | 固定365/366日表。Power BIの時間インテリジェンスで活躍。 |
d_product | product_key, sku, category, brand, cost, list_price | SKU変更や階層は SCD Type2 も選択可。 |
d_store | store_key, store_code, region, channel, open_date | RLSの地域制御で利用。 |
Azure SQL Database：ロードと整形の具体例
ADFの Copy Activity は Azure SQL に高速書き込みできます。ELT での整形は SQL 側のストアドに寄せるのが低コストで保守性も高くおすすめです。
-- 例）BLOBからステージングへバルクロード（SAS/マネージドIDを利用） -- 事前に資格情報と外部データソースを作成済みとする BULK INSERT stg_sales FROM 'https://<storage>.blob.core.windows.net/staging/2025/10/01/sales_20251001.csv' WITH ( DATA_SOURCE = 'MyBlobSrc', FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '0x0a', TABLOCK, CODEPAGE = '65001', MAXERRORS = 0 ); -- 差分適用（ウォーターマーク sales_date >= @_LastLoad） MERGE dwh.f_sales AS tgt USING ( SELECT /* 必要な整形 */ * FROM stg_sales WHERE sales_date >= @_LastLoad ) AS src ON (tgt.sales_id = src.sales_id) WHEN MATCHED THEN UPDATE SET tgt.qty = src.qty, tgt.net_amount = src.net_amount, tgt.tax_amount = src.tax_amount WHEN NOT MATCHED THEN INSERT (sales_id, date_key, product_key, store_key, qty, net_amount, tax_amount) VALUES (src.sales_id, src.date_key, src.product_key, src.store_key, src.qty, src.net_amount, src.tax_amount);
パフォーマンス最適化の要点
- インデックス設計：事実テーブルは Clustered Columnstore Index が第一候補。ルックアップ用のディメンションは主キーに Clustered B-Tree、検索列に非クラスタード。
- バッチサイズ：ADF 書き込みの batchSize と parallelCopies を調整（例：batch 10k、並列 4〜8）。
- 拘束整合性：ロード中は制約/トリガーを避け、整形後に検証をかける。
- サーバーレス計算：ピーク時は 4 vCore、非稼働帯は自動休止でコスト最小化。
Power BI：Import＋増分更新が基本
- 増分更新：RangeStart/RangeEnd パラメーターで直近 N ヶ月を増分、履歴はアーカイブ。更新時間と容量を大幅削減。
- モデル設計：スター型を忠実に。Measures は DAX で KPI を定義（例：粗利、客単価、販売成長率）。
- リフレッシュ：更新完了後のトリガ（Data Factory → Power BI REST API）で順番を担保。
- DirectQuery 切替：リアルタイム性が最重要のごく一部レポートのみ。一般的には Import が高速で運用しやすい。
- RLS：SQL 側または Power BI 側。営業部門 × 地域などの動的マッピングを維持。
コスト設計：現実的な見積もりと抑制テクニック
| 項目 | 設定の目安 | 想定利用 | 月額の目安 | 節約ポイント |
|---|---|---|---|---|
| Azure SQL（サーバーレス 2〜4 vCore） | 自動一時停止 1〜3 時間 | 毎日2時間の処理＋業務時間の照会 | $60〜100 | 処理時間帯にだけ自動起動。夜間/休日は休止。 |
| ADF（パイプライン/コピー） | 日次～月次スケジュール | 月50〜100GBの移送＋検品 | $20〜40 | Mapping Data Flow を多用しない（必要時のみ）。 |
| Storage（Blob） | Hot：ステージング／Cool：アーカイブ | 生データ保管（90日） | $5〜15 | ライフサイクル管理で自動クラス移行・削除。 |
| Power BI（既存） | Premium/PPU | 増分更新／共有配布 | 既存契約利用 | モデルを軽量化して容量圧縮。 |
さらに効くコスト最適化の工夫
- サーバーレス vCore 上限/下限のチューニング：昼間の参照は 2 vCore、夜間のロードは 4 vCore といったバランスに。
- データ圧縮：CSV は gzip で受領。Blob 取込時に解凍する方が総コストは安い（転送料節約）。
- ログの粒度最適化：詳細ログは7〜14日、その後は要約に圧縮。
- Azure Hybrid Benefit（任意）：既存 SQL ライセンスがあれば基本料金を低減可能。
品質と監査：現場で効く「検品」テンプレート
| 検査 | 内容 | 実装例 | 異常時の扱い |
|---|---|---|---|
| ファイル完全性 | レコード件数／終端改行／UTF-8 | ADF Data Flow の検証 or Azure Function | 隔離フォルダへ退避し、送信元へ自動通知 |
| 重複取込防止 | ハッシュ（MD5/SHA256）or ファイル名＋サイズ | Blob メタデータに記録 | 二重登録をスキップしログのみ残す |
| ビジネス検査 | 必須列のNULL、金額の下限、未来日 | SQL の CHECK / 例外テーブル | 該当行だけを stg_error に退避 |
運用・監視・セキュリティ運用
- スケジュール：ADF Trigger（時間）＋ Blob Event（ファイル到着）を併用。遅配時はリトライを 3〜5 回。
- 監視：失敗時はメール/Teams 通知。件数・処理時間・単価をダッシュボード化。
- 権限：マネージド ID を基本。SQL への接続は Azure AD 認証。秘密鍵や接続文字列を配布しない。
- RLS：営業部・地域の動的制御。SQL側にユーザー×地域のマップ表を置き、Power BI でフィルタ適用。
- バックアップ：Azure SQL の自動バックアップ（ポイントインタイム復元）。保持期間は要件に合わせ延長。
DirectQuery と Import の使い分け
| 方式 | 向くケース | 長所 | 注意点 |
|---|---|---|---|
| Import（推奨デフォルト） | 日次〜数時間更新。サマリ多め。 | 高速、DAXが自由、Premiumの増分更新が使える | メモリ容量に依存。モデルの節食設計が鍵。 |
| DirectQuery | ほぼリアルタイム参照が必要 | 最新のDBを直接参照、データ移動なし | クエリ遅延の影響を受ける。SQL側の最適化必須。 |
サンプル：月100GBの月次更新を 1 週間で立ち上げる手順
- ストレージ（Blob）を作成し、SFTP エンドポイントを有効化。取引先に鍵を配布して送信先を切替。
- ADF を作成。リンクサービス（SFTP/Blob/SQL）をマネージドIDで設定。
- パイプラインを 1 本作り、Copy（SFTP→Blob）→検品→Copy（Blob→SQL）→SP 実行の直列で構成。
- SQL に
stg_*とdwh/martスキーマを作成。インデックスと統計を作る。 - Power BI データセットを作成し、増分更新を構成。主要レポート（KPI、売上推移、地域別、チャネル別）を作る。
- 監視通知と失敗時の手順書（リトライ／ロールバック）を整備。
よくある落とし穴と回避策
- CSV の仕様ブレ：送信元がダブルクォートや改行コードを変えてくると崩れます。契約書にフォーマット規約を明文化し、実ファイルを用いた自動検品を入れて防止。
- ADF の過度な Data Flow 依存：複雑な変換は便利だがコストが跳ねがち。JOIN/集計は可能な限り SQL（ELT）へ寄せる。
- SQL のリソース不足：vCore をケチりすぎるとロード時間がのびる。処理時間帯だけ上限を引き上げるのが正解。
- Power BI モデルの無秩序化：フラットテーブルで何でも詰め込むと後悔します。スター型＆不要列/行の削減が鉄則。
セキュリティ／ガバナンスのベストプラクティス
- ゼロトラスト：公開最小限・IP制限・条件付きアクセス。
- 監査証跡：パイプライン実行ログ、SQL 監査、Power BI 操作ログを 90 日保持。
- データマスキング：個人情報・機微情報は動的マスキングまたは列暗号化。
- リソース整備：IaC（Bicep/ARM）で再現性を担保。開発・検証・本番の分離。
拡張パス（将来の増大に備える）
| シナリオ | 対応策 | 備考 |
|---|---|---|
| データが >1TB | 分析用の専用基盤へ移行（例：専用DWH） | 現行の ADF と Power BI はそのまま活用可能 |
| 機械学習の本格導入 | ノートブック基盤（例：ML プラットフォーム）を ADF から呼び出し | 推論結果を Azure SQL のマートへ反映 |
| 同時接続の急増 | 読み取り専用レプリカの活用／キャッシュ層 | Power BI の集計テーブルも有効 |
サンプル DAX／モデルのヒント
-- KPI例：客単価 Avg Ticket = DIVIDE ( SUM ( f_sales[net_amount] ), SUM ( f_sales[qty] ) ) -- 売上成長率（前年同日比） YoY Growth % = VAR SalesThis = SUM ( f_sales[net_amount] ) VAR SalesPrev = CALCULATE ( [Net Sales], DATEADD ( d_date[date], -1, YEAR ) ) RETURN DIVIDE ( SalesThis - SalesPrev, SalesPrev )
- グループ化・バケットは Power BI のフィールドパラメーターで切替可能にしてユーザー体験を上げる。
- 更新順序：SQL ロード → モデル更新（増分）→ レポート配信。失敗時は直前のパーティションのみ再処理。
稼働までのチェックリスト
- 受け取り形式はFTPS/SFTP、CSVはUTF-8/BOMなし、カンマ区切り、ダブルクォートでエスケープ。
- ファイル命名規約・送付スケジュール・再送手順を相手先と合意。
- ADF で検品NG時の隔離・通知経路を実装。
- SQL で
stg_errorを定義し、問題行のみを隔離。 - Power BI は増分更新とパラメーターを本番容量でテスト。
- コストアラートを設定（SQL vCore 秒・ADF 実行回数・Blob 容量）。
代替案と比較（採用判断の裏付け）
| オプション | 適合シナリオ | コスト感 | 複雑度 | 本構成より有利な点 | 不利な点 |
|---|---|---|---|---|---|
| 専用DWH基盤 | 1TB超・多数同時クエリ | $1,000+/月 | 高 | 並列分散で大規模処理に強い | 初期構築・運用の学習コストが重い |
| Data Lake＋ノートブック | 非構造データ中心／ML主導 | $500〜1,500/月 | 中〜高 | スキーマオンリードで柔軟 | 運用者のスキル要件が高い |
| Power BI Dataflow 中心 | 変換が軽微／ソースがSaaS中心 | 既存契約内 | 中 | Power Query資産を活用しやすい | FTP直結や大容量の継続運用は工夫が必要 |
結論
現状の「月50〜100GB／月次〜日次更新／開発リソース限定」という条件では、Azure SQL Database（サーバーレス選択可）＋ Azure Data Factoryが最も低コストかつシンプルで、Power BI との親和性も高く拡張性を確保できます。SQL での ELT と増分更新を徹底することで、処理時間・容量・料金を同時に抑えながら、経営に効くダッシュボードを安定供給できます。将来、データ量や要件が拡大しても、既存のパイプラインとモデルを活かしたまま上位基盤へ無理なくスケールアウト可能です。
付録：運用レシピ（コピー＆アダプト用）
命名規約（抜粋）
- データセット：
ds-sales-raw,ds-sales-curated - ADF パイプライン：
pl-sales-daily-load,pl-sales-monthly-close - テーブル：
stg_sales_yyyymm,f_sales,d_store,d_product
インデックス雛形
-- 事実：列ストアで圧縮と走査最適化 CREATE CLUSTERED COLUMNSTORE INDEX CCI_f_sales ON dwh.f_sales; -- ディメンション：主キーはクラスタード、探索列にNCインデックス CREATE UNIQUE CLUSTERED INDEX PK_d_product ON dwh.d_product(product_key); CREATE NONCLUSTERED INDEX IX_d_product_sku ON dwh.d_product(sku);
増分更新ポリシー例（Power BI）
- 保持期間：過去 36 ヶ月
- 増分期間：直近 3 ヶ月
- アーカイブ：月単位パーティション
リカバリー手順（要約）
- 失敗パイプラインの実行IDを確認し、Blob の当該ファイルを隔離。
- SQL の
stg_*当該日のデータをトランケート。 - 該当ファイルのみ再取り込み→SP 再実行。
- Power BI の該当パーティションを再処理。
SLA の目安
| KPI | 目標 | 補足 |
|---|---|---|
| 日次取込成功率 | ≧ 99.5% | 自動リトライ＋手動復旧で担保 |
| 日次取込処理時間 | ≦ 60 分 | 列ストアと並列コピーで短縮 |
| ダッシュボード初回描画 | ≦ 3 秒（Import） | 集計テーブル／不要列削減 |
この構成が「効率」と「安心」を両立する理由
- 潔い役割分担：移送は ADF、整形は SQL、可視化は Power BI。責務が明快で属人化しにくい。
- 費用の弾力性：使った分だけ支払う従量課金＋サーバーレス休止で月額が読みやすい。
- 段階的な高度化：複雑化の兆し（データ多様化、同時接続増）に応じて、最小限の差分で拡張可能。
コメント