데이터웨어하우스(DW)를 구축할 때 **정보시스템 테이블(=소스 시스템 테이블)**을 설계·정리하는 요령

 

 

1. 테이블 작성 전 수행 사항

항목                              요령 및 체크리스트 

사업 목표 정의	DW가 지원할 핵심 사용 사례(예: 매출 분석, 공급망 최적화)를 먼저 도출하고, 이를 충족할 데이터만 선별
스테이크홀더 참여	재무·마케팅·운영팀 등이 실제로 분석할 데이터 항목·단위·빈도를 사전에 인터뷰하여 요구사항 정리
소스 시스템 식별	CRM, ERP, 외부 SaaS, 로그 파일 등 데이터 위치·포맷·업데이트 주기를 한 눈에 정리

---

2. 정보시스템 테이블 작성 요령

2-1. DDL 작성 규칙

• IF EXISTS → DROP → CREATE 패턴으로 재실행 가능하게 작성
   

  IF OBJECT_ID('bronze.crm_customer', 'U') IS NOT NULL
      DROP TABLE bronze.crm_customer;
  GO
  CREATE TABLE bronze.crm_customer (
      customer_id       VARCHAR(50)  NOT NULL,
      customer_name     NVARCHAR(100),
      gender_code       CHAR(1),
      birth_dt          DATE,
      created_at        DATETIME2,
      updated_at        DATETIME2,
      source_file       VARCHAR(200)
  );

• 소스별 스키마 분리
  • bronze: 원본 적재(Landing)
  • silver: 정제·표준화
  • gold: 최종 분석용(Star Schema)
• 모든 컬럼에 메타데이터 확장
  • source_file, load_ts, md5_hash 등 데이터 리니지 및 중복 제거에 활용

2-2. 데이터 타입 및 제약

원본  특성                                             권장                                             변환이유

날짜 문자(‘20240101’)	DATE or DATETIME2	연산·인덱싱 최적화
숫자 문자(‘001234’)	INT / DECIMAL	저장 공간 절감, 연산 속도 ↑
코드(‘M’, ‘F’)	CHAR(1) or TINYINT	카드널리티 낮아도 정규화 필요
Nullable PK	NOT NULL + 기본값 설정	이후 Fact Join 시 오류 방지

---

3. 데이터 적재·검증 고려사항

단계                       주요 고려사항

적재	- BULK INSERT + FORMAT = 'CSV' + FIRSTROW = 2

• 클라우드 스토리지(Azure Blob/S3) → EXTERNAL DATA SOURCE 연결 | | 검증 | - PK 중복·Null 체크
  • 문자열 TRIM → 불필요한 공백 제거
  • 날짜 유효성 → TRY_CAST 또는 정규식으로 필터링 | | 스토리지 전략 | - 핫/콜드 데이터 분리 → 최근 6개월=핫, 나머지=콜드(저가 스토리지)
• 압축 → Parquet, ORC 사용 시 용량 60~90 % 절감 |

---

4. 보안·거버넌스 반영

• Role-Based Access Control(RBAC)
  • bronze: ETL 팀만 R/W
  • silver: 데이터 엔지니어 R/W, 분석가 R
  • gold: BI/분석팀 R, 쓰기 금지
• 민감정보 마스킹·암호화
  • PII(이메일, 주민번호) → SHA256 해시 또는 동형암호화
• 데이터 카탈로그·메타데이터 문서화
  • 테이블·컬럼 정의, lineage, owner 정보를 Data Catalog(예: DataHub, Alation)에 등록

---

5. 확장성 고려 설계

• 파티셔닝
  • 대용량 팩트 테이블은 날짜/지역별 파티션 → 쿼리 3~10 배 성능 향상
• Star vs Snowflake
  • Star: 쿼리 속도 ↑, 모델 단순 → 대부분 BI용
  • Snowflake: 저장 ↓, 정규화 ↑ → 다차원 속성이 많을 때만 사용
• 모듈형 ETL 파이프라인
  • 새 소스가 추가돼도 기존 코드 변경 최소화 → dbt 모델 단위로 관리

---

요약 체크리스트

1. 사업 목표 → 필요 테이블·컬럼만 선별
2. bronze/silver/gold 계층 DDL 작성
3. 타입·제약·파티션·압축 명시
4. 적재 전 중복·Null·형식 검증
5. RBAC·암호화·카탈로그로 거버넌스 확보
6. 확장 가능한 Star Schema + 모듈형 ETL 설계