Моделирование данных: star schema и не только

Data modeling — организация данных для эффективных queries. Правильная модель = 10x производительность и легкость поддержки.

Star schema

Fact tables: measurements (bets, deposits). Dimension tables: contexts (user, game, time). Denormalized dimensions. Fast queries. Best для BI и reporting. Classic Kimball methodology. Golden standard для analytics warehouses.

Snowflake schema

Normalized dimensions с hierarchies. Меньше redundancy. Slower queries из-за joins. Rarely used в modern warehouses (storage cheap, queries важнее). Star schema обычно preferred.

Data Vault

Hubs (business keys), links (relationships), satellites (attributes). Highly flexible для изменений. Auditability: full history. Complex queries. Good для enterprise DWs с many sources. Overkill для simpler cases.

iGaming schema example

Facts: fact_bet, fact_deposit, fact_session. Dimensions: dim_player, dim_game, dim_date, dim_geo, dim_device. Slowly Changing Dimensions Type 2 для player attributes history. Junction tables для многозначных связей (multi-currency, multi-account).