ํ”ผ๋“œ๋กœ ๋Œ์•„๊ฐ€๊ธฐ
Part 11 - Dimensions and Fact Table ๐Ÿ“Š
Dev.toDev.to
Database

Star-Schema ๋„์ž…์„ ํ†ตํ•œ Mart Layer ์ฟผ๋ฆฌ ํšจ์œจ ์ตœ์ ํ™”

Part 11 - Dimensions and Fact Table ๐Ÿ“Š

Abdelrahman Adnan2026๋…„ 4์›” 21์ผ1๋ถ„beginner

AI ์š”์•ฝ

Context

๋‹จ์ผ Staging Table ๊ตฌ์กฐ๋กœ ์ธํ•œ ๋ถ„์„ ์ฟผ๋ฆฌ์˜ ๋ณต์žก๋„ ์ฆ๊ฐ€ ๋ฐ ์„ฑ๋Šฅ ์ €ํ•˜ ๋ฐœ์ƒ. ๋ฐ์ดํ„ฐ ๋ถ„์„ ๊ฐ€๋…์„ฑ์„ ๋†’์ด๊ธฐ ์œ„ํ•œ ๋…ผ๋ฆฌ์  ๋ฐ์ดํ„ฐ ๋ถ„๋ฆฌ ํ•„์š”์„ฑ ๋Œ€๋‘.

Technical Solution

  • ๋ฐ์ดํ„ฐ ๋ถ„์„ ์ตœ์ ํ™”๋ฅผ ์œ„ํ•œ Mart Layer ๊ธฐ๋ฐ˜์˜ Star-Schema ๊ตฌ์กฐ ์„ค๊ณ„
  • ์ค‘๋ณต ์ œ๊ฑฐ๋œ ์ฐธ์กฐ ๋ฐ์ดํ„ฐ ๊ด€๋ฆฌ๋ฅผ ์œ„ํ•œ dim_station ๋ฐ dim_sensor Dimension Table ๊ตฌ์ถ•
  • ์‹ค์ œ ์ธก์ •๊ฐ’๊ณผ ๋‚ ์”จ ์ปจํ…์ŠคํŠธ๋ฅผ ํ†ตํ•ฉํ•œ fact_air_quality Fact Table ์ƒ์„ฑ
  • ์žฆ์€ Join ๋น„์šฉ ๊ฐ์†Œ๋ฅผ ์œ„ํ•ด ์ผ๋ถ€ Location ํ•„๋“œ๋ฅผ Fact Table์— ๋ฐฐ์น˜ํ•œ ์ „๋žต์  Denormalization ์ˆ˜ํ–‰
  • ๋ฐ์ดํ„ฐ ๋ฌด๊ฒฐ์„ฑ ๋ณด์žฅ์„ ์œ„ํ•ด station_id ๋ฐ sensor_id์— ๋Œ€ํ•œ Unique ๋ฐ Not Null ์ œ์•ฝ ์กฐ๊ฑด ํ…Œ์ŠคํŠธ ์ ์šฉ

์‹ค์ฒœ ํฌ์ธํŠธ

1. ๋Œ€๊ทœ๋ชจ ๋ถ„์„ ์ฟผ๋ฆฌ ์„ฑ๋Šฅ ๊ฐœ์„ ์„ ์œ„ํ•ด Fact Table๊ณผ Dimension Table์˜ ๋ถ„๋ฆฌ ๊ฒ€ํ† 

2. Join ํšŸ์ˆ˜ ๊ฐ์†Œ๋ฅผ ํ†ตํ•œ ์ฟผ๋ฆฌ ์ตœ์ ํ™”๋ฅผ ์œ„ํ•ด ๋ถ„์„ ๋นˆ๋„๊ฐ€ ๋†’์€ ํ•„๋“œ์˜ ์ ์ ˆํ•œ Denormalization ์ˆ˜ํ–‰

3. Mart Layer์˜ ๋ฐ์ดํ„ฐ ์‹ ๋ขฐ์„ฑ ํ™•๋ณด๋ฅผ ์œ„ํ•œ Key ์ œ์•ฝ ์กฐ๊ฑด ๊ฒ€์ฆ ์ž๋™ํ™” ๊ตฌ์ถ•

ํƒœ๊ทธ

#Dimension Table#Star Schema#Fact Table#Denormalization#Data Modeling
์›๋ฌธ ์ฝ๊ธฐ