MES·DB 제조데이터허브 — Medallion 파이프라인 · KPI API

<aside> 🧰

기술 스택 — Python · FastAPI · SQLAlchemy(async) · Pydantic · Alembic / PostgreSQL(JSONB + GIN) · MSSQL(읽기 전용) / pymssql · openpyxl · httpx / Medallion(Bronze→Silver→Gold) · KS X 9101 제조 데이터 표준 · 어댑터 패턴 / Docker Compose

</aside>

<aside> 🎯

한 줄 요약 — 자동차 부품사(동광기연)의 MES(MSSQL)와 엑셀 원천 데이터를 표준 제조데이터허브(PostgreSQL)에 자동 적재하고, 자재 수불 · 과부족 KPI를 REST API로 산출하는 데이터 파이프라인. 최종 목표는 수작업 엑셀(CONSOL) 계산의 제거. 백엔드/데이터 파이프라인 단독 설계·구현.

</aside>

1. 두 단계로 진화한 구조

	1단계 — 로컬 Mock (선행 검증)	2단계 — 멀티테넌트 허브 (현재)
성격	고객사 전용 1회성 파이프라인	범용 제품, 고객사별 ETL은 어댑터로 격리
스키마	`bronze_` / `silver_` / `gold_*` 자체 명명	KS X 9101 표준 테이블 (ItemMaster, GoodsReceipt, Inventory …)
Bronze	원천별 13개 테이블 (payload JSONB)	`RawData` 단일 테이블 (`topic` • `payload JSONB`)
Silver / Gold	SQL VIEW 14개	Silver는 실체 테이블, Gold 테이블 없음 — API 호출 시 실시간 계산
KPI 제공	DB 직접 조회	REST API (`/kpi/{tenant}/inventory-flow`, `/shortage`)
자재 출고	생산실적 × BOM 추정	스캔앱 실측 (GoodsIssue)

<aside> 💡

왜 바꿨나 — Mock은 한 고객사만을 위한 SQL 뷰 더미였다. 허브는 KS X 9101 표준을 뼈대로 다른 고객사도 같은 테이블·API로 수용하고, 고객사별 특수 로직(엑셀 파싱·필드 매핑)만 adapters/<고객사>/에 격리한다. 새 고객사는 어댑터 폴더 하나만 추가한다.

</aside>

2. 아키텍처

graph LR
    subgraph src["원천"]
        M["MES (MSSQL, 읽기 전용)"]
        X["엑셀 (재고대장·BOM·소요계획)"]
        S["스캔앱 (출고 실측)"]
    end
    subgraph hub["제조데이터허브 (FastAPI + PostgreSQL)"]
        R["RawData<br>Bronze · topic + payload"]
        subgraph silver["Silver — KS X 9101 표준 테이블"]
            T1["ItemMaster / BOM"]
            T2["Inventory"]
            T3["GoodsReceipt / GoodsIssue"]
            T4["ProductionPlanning"]
        end
        A["KPI API<br>실시간 계산"]
    end
    C["3D 모니터링 (Builder)"]
    M -->|WinForms 중계기 폴링| R
    S -->|앱 → REST| R
    R -->|어댑터 자동 매핑| silver
    X -->|업로드 API| silver
    silver --> A --> C

데이터 성격별 적재 경로 분리

Master/State (품목·BOM·재고) — 어댑터 → Silver 직행. 상태 스냅샷이라 원본 보존 가치가 낮다.
Event (입고·출고) — RawData(Bronze) 경유 → 매핑 잡 → Silver. 대량·연속이고 매핑 규칙이 바뀌어도 재처리할 수 있어야 한다.

3. 1단계 — Mock에서 검증한 것

MES 증분 폴링 (5분 주기)

워터마크 증분 — 테이블별 마지막 값 이후만 조회. 증분 키 타입 3종(identity / datetime / datestring) 지원.
부모-자식 조인 증분 — 시각 컬럼이 없는 상세 테이블은 부모 헤더의 워터마크로 WHERE IN (...).
부트스트랩 — 1,100만 행 실적 테이블은 1년 컷오프를 미리 심어 풀 폴링 방지.
5만 행 청크 스트리밍 → COPY BINARY → 임시 테이블 → ON CONFLICT 머지. per-row INSERT 대비 수십~수백 배.
content_hash(MD5)로 변경 없는 행은 스킵. LoadMode 3종(history / latest / append)으로 테이블별 UNIQUE와 충돌 처리 분기.

엑셀 적재 — 폴더 감시 → 3초 디바운싱 → 시트명·폴더·파일명 3단계 매칭 → Bronze 적재 → Archive/Error 이동.