GitHub: https://github.com/liz0214/lizzy

Fully Local ETL with DuckDB: SQL Reports + Python Orchestration + Data Quality Checks (Portfolio)

この記事で伝えたいこと

ローカル完結（DuckDB）で、請求（invoices）・入金（payments）風データを生成し、SQLで「未収/延滞/月次推移/ランキング/セグメント×プランKPI」を作成、Pythonで実行・CSV出力・ログ・品質チェックまで一通り再現できるミニETLを作りました。
ポートフォリオとして、再現性（1コマンド実行）と実務っぽさ（品質チェック・ログ） を重視しています。

背景

実務で「回収管理（未収・延滞・月次推移）」系の集計を扱う場面が多く、SQLを軸に“実務っぽい成果物”を作りたいと思いました。一方、ポートフォリオは第三者が手元で再現できることが重要なので、ローカルで完結して配布もしやすい DuckDB を採用しました。

全体像（パイプライン）

generate：ダミーデータ生成（customers / plans / invoices / payments）
load：DuckDBへ投入（raw）
sql：集計SQL（未収、延滞、月次推移、ランキング、セグメント×プランKPI）
python：SQL実行・CSV出力・ログ保存
quality：欠損/重複/整合性チェック → Markdownレポート出力

イメージ

generate -> DuckDB(raw) -> SQL reports -> outputs/*.csv
                      \-> quality_check -> outputs/quality_report.md
                      \-> logs/*.log

データモデル（簡易）

raw.customers：segment（personal / business）
raw.plans：個人向け低価格・法人向け高価格（光熱費プランのようなイメージで偏りを作る）
raw.invoices：月次請求風（1顧客×1月＝最大1請求）
raw.payments：全額/一部/未入金を混在（segmentで確率差）

SQLレポート（5本）

このポートフォリオの主役はSQLです。代表的な5本を用意しました。

1) 未収一覧（Outstanding）

as_of時点の未収残（請求−入金累計）を出力します。
?のプレースホルダを使い、Pythonから --as-of の日付を渡して実行します（Parameterized SQL）。

2) 延滞一覧（Overdue）

未収のうち期日超過分を抽出し、days_overdue を算出します。

3) 月次推移（Monthly trend）

billed / paid / outstanding_eom（末日未収残）を月次で出力します。
“その月の請求と入金”だけでなく、月末時点の未収残（EOM） を出すことで実務っぽいレポートに寄せています。

4) 未収ランキング（Ranking）

未収合計Top、延滞件数、最大延滞日数などを顧客単位で集計します。

5) セグメント×プランKPI（Segment×Plan KPI summary）

個人/法人×プラン別に、未収・延滞率（件数/金額）などを集計します。

Pythonは何をしているか

Pythonは「分析ロジック」よりも、実行・出力・ログ・品質チェックを担う“運用スクリプト”的な役割です。

run_report.py：SQL実行 → CSV出力、ログ保存
quality_check.py：NULL/重複/FK/入金>請求などを検証し、Markdownレポート出力
pipeline.py：generate→reports→quality を順に実行（入口1本化）

1コマンドで再現（Windows PowerShell想定）

--as-of は YYYY-MM-DD 形式（例：2026-05-06）

.\.venv\Scripts\python.exe -u src\pipeline.py --reset --as-of YYYY-MM-DD

実行後に生成される主なファイル：

outputs/outstanding.csv
outputs/overdue.csv
outputs/monthly_trend.csv
　- ※列：ym, billed_amount, paid_amount, outstanding_eom など
outputs/monthly_trend.csv
- 例：列は ym, billed_amount, paid_amount, outstanding_eom など
outputs/ranking_customers.csv
outputs/segment_plan_summary.csv
outputs/quality_report.md
- NULL/重複/FK/入金<=請求（as_of時点）を検証

※ outputs/ と logs/ は実行時に生成されるため、リポジトリには含めていません（.gitignore で除外）。

品質チェック

最低限の信頼性担保として、以下をチェックしています。

必須列NULL
PK重複
参照整合性（FK相当）
入金合計が請求額を超えない（as_of時点）

つまずきと学び

Python環境混在（例：Anaconda/venv）で実行やデバッグが不安定になりやすい → .venv 固定が重要
Parameterized SQL（?）は、SQL側とPython側で引数数が一致しないと落ちる → 実行側で吸収する設計が便利
偏りのあるダミーデータ設計（個人/法人×プラン）を入れると、分析が“意味を持つ”

今後の拡張

invoice_lines（明細）追加で整合性チェック強化（SUM(lines)=invoice）
config化（yaml）や簡易CI
簡単なグラフやダッシュボード化

Repository / Release

GitHub: https://github.com/liz0214/lizzy
Release: https://github.com/liz0214/lizzy/releases/tag/v1.0.0

最後に

SQL最適化やデータ品質の観点を意識して作りました。