Medallion Architecture - 3 — Silver Layer

Bronze Layer의 복잡한 중첩 구조/지저분한 데이터를 Flatten하여 타입을 맞추고, 분석하기 좋은 원재료 형태로 가공하는 레이어

---

전체 흐름

Bronze S3 (Parquet)
    ↓
Athena 외부 테이블 (raw_bronze_tbl)
    ↓  SELECT + Flatten
Silver 테이블 (CTAS or INSERT)
    ↓
Silver S3 적재

Airflow DAG가 Bronze 완료 시점 이후 스케줄로 Athena SQL을 실행하여, Bronze 데이터를 정제 후 Silver 테이블/S3에 기록하는 구조.

---

1. Bronze 외부 테이블 구성

Silver 작업 전 선행 조건 — Bronze S3 데이터를 Athena에서 조회할 수 있는 테이블이 필요함.

기존 bronze_tbl 문제점

• Firehose에서 Parquet 대응으로 생성한 테이블 → Glue의 Location이 Firehose를 바라봄
• show tables;로 존재는 확인되지만, 실제 조회 시 결과 0건 (껍데기 상태)
• S3를 바라봐야 데이터 조회 가능

테이블 생성 방법 3가지

방법	특징
UI 직접 구성	Athena 콘솔에서 수동 생성
Glue Crawler	자동 스키마 탐지. Flat 구조엔 정확하나 중첩 구조에서 스키마 누락/오류 발생 가능
SQL 직접 작성	정확하고 제어 가능 → 이 방법 사용

SQL로 Bronze 외부 테이블 생성

CREATE EXTERNAL TABLE IF NOT EXISTS `de_ai_04_ma_bronze_db`.`raw_bronze_tbl` (
  `event_id` string,
  `event_time` timestamp,
  `source_ip` string,
  `user_agent` string,
  `data` struct<
    user_id: string,
    item_id: string,
    price: int,
    qty: int,
    store_id: string
  >,
  `ingested_at` timestamp
)
PARTITIONED BY (
  `year` string,
  `month` string,
  `day` string,
  `hour` string
)
STORED AS PARQUET
LOCATION 's3://de-ai-04-827913617635-ap-northeast-2-an/medallion/bronze/'
TBLPROPERTIES (
  'classification'='parquet',
  'projection.enabled' = 'true',
  'projection.year.type' = 'date',
  'projection.year.range' = '2024,2026',
  'projection.year.format' = 'yyyy',
  'projection.month.type' = 'integer',
  'projection.month.range' = '1,12',
  'projection.month.digits' = '2',
  'projection.day.type' = 'integer',
  'projection.day.range' = '1,31',
  'projection.day.digits' = '2',
  'projection.hour.type' = 'integer',
  'projection.hour.range' = '0,23',
  'projection.hour.digits' = '2',
  'storage.location.template' = 's3://de-ai-04-827913617635-ap-northeast-2-an/medallion/bronze/year=${year}/month=${month}/day=${day}/hour=${hour}/'
);

• CREATE EXTERNAL TABLE — S3를 직접 바라보는 외부 테이블. 데이터는 S3에 존재하고 Athena는 메타데이터만 관리
• struct<...> — Bronze의 중첩 JSON(data 필드)을 구조체로 명시적 정의. Silver에서 data.user_id, data.price 형태로 Flatten 가능
• PARTITIONED BY — year/month/day/hour 4단계 파티션. 시간 범위 쿼리 시 불필요한 파일 스캔을 방지하여 비용/속도 최적화
• projection.enabled = true — Partition Projection 활성화. Glue 카탈로그에 파티션을 등록하지 않아도 S3 경로 패턴만으로 파티션 자동 인식
• projection.*.type / range / digits — 각 파티션 컬럼의 데이터 타입, 범위, 자릿수 지정. digits = '2'로 01, 02 형태의 제로패딩 대응
• storage.location.template — 파티션 경로 패턴. ${year}/${month}/${day}/${hour} 변수가 projection 설정과 매핑되어 S3 경로 자동 생성

---

2. Glue Crawler 사용 시 참고 절차

SQL 작성이 어려운 경우 Crawler로 자동 생성 가능.

AWS Glue > Crawlers > Create Crawler
  이름     : de-ai-04-ma-bronze-crawler
  Source   : S3 선택 → s3://.../medallion/bronze/
  IAM      : AWSGlueServiceRole-de-ai-04-S3FullAccess (최초 1회 생성)
  Target DB: de_ai_04_ma_bronze_db
  Prefix   : de_ai_04_ma_crawler_
  → Run Crawler 실행

• S3 데이터를 스캔하여 스키마 파악 후 테이블 자동 생성
• Flat 구조 → 자동 탐지 정확도 높음
• 중첩 구조(nested JSON, parquet) → 스키마 누락/오류 발생 가능
• 생성 후 Glue > Databases > Tables에서 Location이 S3를 바라보는지 반드시 확인

---

3. Silver DAG 구성

Silver DB : de_ai_04_ma_sliver_db

스케줄링 전략

# cron 표기 (분 시 일 월 요일)
10 * * * *

• Bronze 수집이 매시 정각~완료되므로, 완료 후 약 10분 여유를 두고 매시 10분에 실행
• 핵심 고민 포인트 : Bronze Layer에 데이터가 떨어지는 정확한 시점 파악 필요. 센서 기반 감시 또는 고정 스케줄 중 선택

방식 ① CTAS 기반 ETL DAG

참고 파일 : 11_aws_ma_silver.py

• CTAS(Create Table As Select) — SELECT 결과를 새 테이블로 바로 생성
• 매 실행 시 테이블 삭제 후 재생성 → 항상 최신 데이터로 전체 교체
• Bronze 테이블 SELECT → Flatten/정제 → Silver 테이블에 CTAS로 세팅

방식 ② 증분 적재 DAG

참고 파일 : 11_aws_ma_silver_increment.py

• 테이블 생성(CREATE)과 데이터 삽입(INSERT)을 별도 Task로 분리
• 테이블은 최초 1회만 생성
• 매시 10분 INSERT 수행 → 시간 단위로 Silver 데이터가 S3에 누적 적재

두 방식 비교

구분	CTAS DAG	증분 DAG
테이블 생성	매번 삭제 후 재생성	최초 1회만 생성
데이터 적재	전체 교체 (스냅샷)	누적 INSERT
Task 구조	단일 CTAS	CREATE + INSERT 분리
적합한 경우	항상 최신 상태만 필요	이력 누적/시계열 분석 필요

---

4. 작업 완료 기준

단계	완료 조건
1차	Athena에서 Bronze 외부 테이블 생성 → Parquet 데이터 조회 성공
2차	DAG 스케줄 가동 → Silver 테이블에 데이터 적재 확인
최종	데이터 생성 → Bronze → Silver → S3 적재까지 End-to-End 확인

---

요약

구성 요소	역할	핵심 포인트
raw_bronze_tbl	Bronze S3를 바라보는 외부 테이블	Partition Projection으로 파티션 자동 인식, struct로 중첩 구조 정의
Glue Crawler	테이블 자동 생성 (대안)	Flat 구조에 적합, 중첩 구조는 오류 가능
CTAS DAG	Bronze → Silver 전체 교체	매번 삭제 후 재생성, 최신 스냅샷 유지
증분 DAG	Bronze → Silver 누적 적재	CREATE/INSERT Task 분리, 시간 단위 데이터 누적

Bronze 외부 테이블은 반드시 S3 Location을 바라봐야 하며, Partition Projection을 활용하면 파티션 등록 없이 시간 단위 자동 조회가 가능함. Silver DAG는 CTAS(전체 교체)와 증분(누적 INSERT) 중 데이터 활용 목적에 맞게 선택.