기초 · CH02
SQL과 데이터 모델링
데이터 엔지니어의 첫 도구는 여전히 SQL입니다. 주문(사실)과 고객·상품(차원)으로 나눈 스타 스키마로 집계하고, 윈도 함수를 쓰고, 실무에서 숫자를 틀리게 만드는 대표 함정인 조인 뻥튀기를 직접 확인합니다.
사실 테이블과 차원 테이블
사실(fact)
일어난 일 한 건이 한 행. 주문, 결제, 클릭. 행이 계속 늘어납니다.
차원(dimension)
사실을 설명하는 정보. 고객의 지역, 상품의 카테고리. 상대적으로 적고 천천히 바뀝니다.
스타 스키마
사실 테이블 하나를 가운데 두고 차원 테이블을 키로 붙이는 구조. 분석 쿼리가 단순해집니다.
1. 지역×카테고리 매출
SELECT c.region, p.category, sum(o.qty * p.price) AS revenue, count(*) AS orders
FROM orders o
JOIN customers c USING (customer_id)
JOIN products p USING (product_id)
WHERE o.status <> 'cancelled'
GROUP BY ALL ORDER BY revenue DESC LIMIT 5;│ region │ category │ revenue │ orders │
│ 경기 │ 주방 │ 19986500 │ 815 │
│ 광주 │ 주방 │ 19385000 │ 799 │
│ 경기 │ 문구 │ 17935500 │ 822 │
│ 경기 │ 음료 │ 17551000 │ 817 │
│ 부산 │ 주방 │ 17484000 │ 744 │
GROUP BY ALL은 SELECT에서 집계하지 않은 컬럼으로 묶어 주는 DuckDB 문법입니다. Snowflake, Databricks SQL에도 같은 문법이 있습니다.
2. 윈도 함수: 고객별 몇 번째 주문인가
SELECT customer_id, order_id, ordered_at,
row_number() OVER (PARTITION BY customer_id ORDER BY ordered_at) AS nth_order,
min(ordered_at) OVER (PARTITION BY customer_id) AS first_order_at
FROM orders WHERE customer_id = 42 ORDER BY ordered_at LIMIT 5;
42번 고객의 첫 주문은 11월 2일 08:18이고, 이후 주문마다 순번이 1, 2, 3…으로 붙습니다. 재구매율, 첫 구매 후 이탈 같은 지표가 모두 이 패턴에서 나옵니다.
3. 조인 뻥튀기 함정
고객 10명 중 1명꼴로 쿠폰이 2장씩 있는 coupons 테이블을 주문에 붙이고 행 수를 세 봤습니다.
| 세는 방법 | 결과 |
|---|---|
| 주문 테이블만 count(*) | 32,200 |
| 쿠폰을 LEFT JOIN한 뒤 count(*) | 35,412 |
| 쿠폰을 붙이고 count(DISTINCT order_id) | 32,200 |
고객 한 명에 쿠폰이 여러 장이면 그 고객의 주문 행이 쿠폰 수만큼 복제됩니다. 매출 합계도 같은 비율로 부풀어 오릅니다. 1:N 관계를 붙이기 전에는 키가 유일한지 확인하거나, 먼저 집계한 뒤 붙입니다. 08장에서 이 확인을 dbt 테스트로 자동화합니다.
이것만 기억하세요
사실과 차원을 나누면 쿼리가 단순해집니다. 조인한 뒤 행 수가 늘었다면 숫자를 믿기 전에 키가 유일한지부터 확인합니다.