6.2Data warehouses and analytics platforms

A database handling day-to-day OLTP (online transaction processing)—orders, inventory updates—is normalized to prioritize update consistency and response speed. But throwing an analytical query such as "aggregate sales trends by region and product category over the past three years" directly at an OLTP schema tends to multiply table joins and degrade performance. A data-platform engineer must be able to design a schema and platform (a data warehouse) separate from OLTP, tailored to the analytical purpose, and decide when and how to integrate the data into it. This section builds that design-judgment pattern.

6.2.1Star schema and snowflake schema

• A fact table is the central table holding quantitative performance data to be analyzed (sales amount, quantity, etc.). A dimension table holds the analytical perspectives—attributes such as time, store, customer, and product answering "when," "where," "who," and "what". The fact table holds the primary keys of each dimension table as foreign keys.
• Star schema is a structure where multiple dimension tables connect directly in a radial pattern around a single fact table (star-shaped). The dimension tables are denormalized (e.g., a store table redundantly holds region and prefecture names), giving fewer joins and higher analytical query performance, at the cost of increased storage from the redundant data.
• Snowflake schema further normalizes the dimension tables into a hierarchical structure (e.g., store table -> region table -> prefecture table), forming a snowflake-like shape. It saves storage but adds join levels to analytical queries, making it less performant than a star schema. It is chosen when dimensions are updated frequently or when storage needs to be minimized.