SQL(1)엑셀로도 충분한데, 왜 BigQuery?
방대한 데이터를 몇 줄의 코드로 분석해보자.
SCM 담당자에게 엑셀(Excel)은 필수 능력이죠. VLOOKUP, 피벗 테이블, 그리고 약간의 VBA만 있다면 대부분의 구매, 재고, 물류 데이터를 분석하고 관리하는 데 큰 무리가 없습니다.
솔직히 말해, 많은 회사의 일상적인 데이터는 엑셀로도 충분히 감당 가능한 수준일 수 있습니다.
그렇다면 왜 지금 'BigQuery'와 'SQL'을 공부해야 할까요?
그것은 '데이터의 양'이 아닌 '데이터의 복잡성'과 '미래의 확장성' 때문입니다. 특히 CDMO(위탁개발생산)와 같은 산업의 SCM 데이터는 기록보다는 더 많은 내용을 담고 있습니다. 하나의 원자재가 어떤 고객사의, 어떤 생산 배치(Batch)에, 어떤 품질 상태(QC Status)로, 언제 사용되었는지 그 이력(Lot Traceability)을 완벽하게 추적해야 합니다.
지금은 10만 줄의 데이터일지라도, 회사가 성장하고 다루는 프로젝트가 수십, 수백 개가 되면 순식간에 수천만, 수억 줄의 데이터로 불어납니다. 엑셀의 VLOOKUP이 'Calculating...' 메시지를 띄우며 멈춰버리는 순간, 우리의 SCM 역량도 함께 멈추게 될 것입니다.
오늘은 만약 SCM 데이터가 '엄청나게' 방대해진다는 가상의 시나리오를 세우고, BigQuery와 SQL을 어떻게 활용해 SCM을 한 단계 진화시킬 수 있을지 구체적인 코드와 함께 정리해 보았습니다.
1단계: 데이터를 '구조'로 바라보기 (엑셀 시트 vs 데이터베이스 스키마)
엑셀은 모든 정보를 하나의 거대한 시트에 담으려는 경향이 있습니다. (흔히 'Flattened data'라고 부릅니다.) 하지만 BigQuery와 같은 데이터베이스는 데이터를 목적에 맞게 여러 개의 테이블로 '분리'하고 '연결'하는 것(정규화)에서 시작합니다.
가장 핵심적인 구분은 'Fact(사실) 테이블'과 'Dimension(기준) 테이블'입니다.
Fact 테이블 (사건/거래 데이터): 매일같이 쌓이는 '행위' 데이터입니다. 데이터가 거대하게 쌓이는 곳이죠.
fact_Material_Consumption (자재 사용 내역)
fact_Purchase_Orders (구매 주문 내역)fact_Inventory_Transactions (재고 입출고 내역)
Dimension 테이블 (기준 정보): Fact 데이터가 무엇을 의미하는지 '설명'하는 데이터입니다. (마스터 데이터)
dim_Raw_Materials (원자재 마스터: 자재명, 카테고리, 표준원가...)dim_Suppliers (공급업체 마스터: 업체명, 국가, 등급...)dim_Projects (프로젝트 마스터: 고객사명, 생산 제품...)
이렇게 데이터를 구조화하면 '하나의 거대한 시트'를 관리할 때보다 훨씬 빠르고 유연하게 데이터를 조합할 수 있습니다. 엑셀에서 PO 리스트에 자재 카테고리를 붙이기 위해 매번 VLOOKUP을 거는 대신, 두 테이블을 'JOIN'하라는 명령 한 줄이면 끝납니다.
2단계: 데이터 간의 '연결고리' 만들기 (Primary Key & Foreign Key)
데이터를 여러 테이블로 분리했다면, 이제 이들을 다시 엮어줄 '연결고리'가 필요합니다. 이것이 바로 PK(기본 키)와 FK(외래 키)의 개념입니다.
PK (Primary Key, 기본 키): 각 테이블에서 절대로 중복되지 않는 고유한 ID 값입니다. (예: dim_Raw_Materials 테이블의 material_id)
FK (Foreign Key, 외래 키): 다른 테이블의 PK를 참조하는 값입니다. (예: fact_Purchase_Orders 테이블에 있는 material_id는 dim_Raw_Materials의 PK를 참조하는 FK입니다.)
이 연결고리가 왜 중요할까요? 이 고리들이 모여 SCM의 복잡한 질문에 답을 주기 때문입니다.
예를 들어, "A 고객사의 'P-100' 프로젝트에 사용된 'Filter-XYZ' 자재는, 어느 공급사로부터, 언제, 얼마에 구매했으며, 당시 입고 검사(QC) 상태는 어땠는가?"라는 질문을 받는다면, 엑셀로는 여러 파일을 뒤져가며 며칠이 걸릴 수도 있습니다.
하지만 데이터베이스에서는 dim_Projects → fact_Material_Consumption → fact_Inventory_Transactions → fact_Purchase_Orders → dim_Suppliers 테이블을 이 '연결고리(Key)'들로 이어 붙여(JOIN) 단 몇 초 만에 답을 찾을 수 있습니다.
3단계: 엑셀 함수가 아닌 'SQL'로 질문하기 (유용한 통계 분석)
데이터 구조를 설계하고 연결고리를 만들었다면, 이제 'SQL(Structured Query Language)'이라는 언어로 데이터에게 직접 질문을 던질 차례입니다. SQL은 데이터베이스와 대화하는 표준 언어입니다.
SCM 담당자로서 매일 고민하는 질문들을 SQL로 어떻게 바꿔볼 수 있을까요?
질문 1: "우리 핵심 공급사들의 실제 납기 준수율(OTD)은 몇 %인가?"
공급업체의 성과를 측정하는 핵심 KPI입니다. fact_Purchase_Orders 테이블의 날짜 정보와 dim_Suppliers의 업체 정보를 JOIN합니다.
SQL
SELECT
s.supplier_name,
COUNT(po.po_id) AS total_orders,
-- 실제납기일 <= 요청납기일 이면 1 (정시), 아니면 0 (지연)으로 계산하여 평균
AVG(CASE
WHEN po.actual_delivery_date <= po.expected_delivery_date THEN 1 ELSE 0 END) AS on_time_delivery_rate
FROM
`my-cdmo-project.scm_dataset.fact_Purchase_Orders` AS po
JOIN
`my-cdmo-project.scm_dataset.dim_Suppliers` AS s ON po.supplier_id = s.supplier_id
WHERE
po.po_status = 'Received' -- 입고 완료 건에 한해서
AND po.order_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY
s.supplier_name
ORDER BY
on_time_delivery_rate DESC;
질문 2: "지금 당장 생산에 투입 가능한 'Released' 상태의 원자재 재고 총액은?"
CDMO에서 가장 중요한 qc_status('Quarantine' vs 'Released')를 구분하는 쿼리입니다.
fact_Inventory_Transactions로 현재고를 계산하고, dim_Raw_Materials와 JOIN하여 재고 금액을 산출합니다.
SQL
-- WITH 절을 사용하여 재고의 현재 상태를 먼저 계산합니다.
WITH CurrentInventory AS (
SELECT
material_id,
lot_number,
qc_status,
SUM(quantity_changed) AS current_quantity_on_hand
FROM
`my-cdmo-project.scm_dataset.fact_Inventory_Transactions`
GROUP BY
material_id, lot_number, qc_status
)
SELECT
m.material_name,
m.category,
SUM(ci.current_quantity_on_hand) AS total_released_quantity,
SUM(ci.current_quantity_on_hand * m.standard_cost) AS total_released_value
FROM
CurrentInventory AS ci
JOIN `my-cdmo-project.scm_dataset.dim_Raw_Materials` AS m
ON ci.material_id = m.material_id
WHERE
ci.qc_status = 'Released' -- 사용 가능한 재고만 필터링
AND ci.current_quantity_on_hand > 0
GROUP BY
m.material_name, m.category
ORDER BY
total_released_value DESC;
질문 3: "특정 프로젝트(배치)에 투입된 원자재의 총 원가(Total Material Cost)는?"
프로젝트(배치)별 원가를 집계하여 수익성을 분석하는 데 필수적입니다.
SQL
SELECT
p.project_id,
p.client_name,
p.product_type,
SUM(c.quantity_used * m.standard_cost) AS total_material_cost
FROM
`my-cdmo-project.scm_dataset.fact_Material_Consumption` AS c
JOIN
`my-cdmo-project.scm_dataset.dim_Projects` AS p ON c.project_id = p.project_id
JOIN
`my-cdmo-project.scm_dataset.dim_Raw_Materials` AS m ON c.material_id =
m.material_id
WHERE
p.project_id = 'P-2025-001' -- 특정 프로젝트 ID로 조회
GROUP BY
p.project_id,
p.client_name,
p.product_type;
질문 4: "우리가 표준원가보다 10% 이상 비싸게 구매(PPV)하고 있는 품목은?"
표준 원가(standard_cost) 대비 실제 구매 가격(unit_price)의 차이를 분석하여 비용 절감 기회를 찾습니다.
SQL
SELECT
m.material_name,
s.supplier_name,
m.standard_cost,
AVG(po.unit_price) AS average_purchase_price,
-- (평균 구매가 - 표준 원가) / 표준 원가
(AVG(po.unit_price) - m.standard_cost) / m.standard_cost AS price_variance_ratio FROM
`my-cdmo-project.scm_dataset.fact_Purchase_Orders` AS po
JOIN
`my-cdmo-project.scm_dataset.dim_Raw_Materials` AS m ON po.material_id = m.material_id
JOIN `my-cdmo-project.scm_dataset.dim_Suppliers` AS s ON po.supplier_id = s.supplier_id
WHERE
po.po_status = 'Received'
GROUP BY
m.material_name, s.supplier_name, m.standard_cost
HAVING
price_variance_ratio > 0.1 -- 표준원가보다 10% 이상 비싸게 산 품목만 조회
ORDER BY
price_variance_ratio DESC;
결론: (뻔한 얘기지만)감보다는 데이터로 의사결정을 해야한다.
데이터를 구조적으로 설계하고, 위와 같은 SQL로 복잡한 질문을 직접 던지며, 예측 가능한 공급망을 디자인하는 전문가가 되기 위해 오늘도 노력합니다.
