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.

6.2.2ETL/ELT and OLAP

• ETL (Extract, Transform, Load) extracts data from multiple operational systems such as OLTP, transforms it to fit the analytical schema (deduplication, unit normalization, handling missing values, etc.), and then loads it into the data warehouse—transformation is completed on a dedicated ETL server before loading. ELT (Extract, Load, Transform) loads the raw data first after extraction, then performs the transformation afterward using the processing power of the destination (the DWH or data lake)—an approach that has spread alongside the popularity of large-scale parallel cloud processing platforms.
• OLAP (Online Analytical Processing) performs interactive analysis of multidimensional data (axes such as time, region, product). Drill-down digs into aggregated data toward finer granularity (year -> quarter -> month). Roll-up conversely aggregates detailed data to a coarser granularity. Slice and dice refers to cutting out a specific dimension (slice) or switching perspective to view the data from multiple angles (dice).

6.2.3Data mining and big data / data lakes

• Data mining is a set of techniques for discovering statistical correlations, patterns, and regularities that are hard for humans to notice manually from large volumes of data (association-rule analysis/market-basket analysis, clustering, regression analysis, etc.). Whereas OLAP involves an analyst slicing data multidimensionally with a hypothesis in mind, data mining differs in that it discovers patterns from the data without a prior hypothesis.
• Big data refers to data characterized by volume, variety (a mix of structured, semi-structured, and unstructured data), and velocity (real-time nature) that traditional RDBMSs struggle to handle. A data lake is a repository that stores structured, semi-structured, and unstructured data alike in its raw form, without transformation, at scale. Whereas a data warehouse is "schema-on-write" (the schema is decided before storing), a data lake is "schema-on-read" (the schema is applied at read time), giving high flexibility at storage time.

Suppose a data-platform engineer at an apparel retailer receives a request from management to "quickly analyze sales trends by store, product category, and season over the past five years, from multiple angles." Sending analytical queries directly at the OLTP database (a normalized order/inventory system) would require multi-level joins across many tables, respond slowly, and strain the operational update workload—judged unsuitable. So the engineer builds a dedicated data warehouse, centering a fact table holding sales amount and quantity, surrounded by dimension tables for store, product, time, and customer. Since management wants "fast multidimensional analysis" and query performance matters more than storage footprint, the engineer chooses a star schema with denormalized dimension tables (had the store dimension instead updated frequently, or had storage needed to be tightly constrained, a normalized snowflake schema would have been considered instead). For data integration, since data must be gathered from multiple operational systems (POS, inventory, membership) and the transformation rules (deduplication, unit normalization) are complex, the engineer adopts ETL, completing the transformation before loading. Once operating, if management asks to "look at the annual trend first, then drill into the underperforming quarter down to monthly detail," that is handled with the OLAP drill-down operation. Furthermore, if the goal goes beyond simple aggregation to discovering, without a prior hypothesis, "which products tend to be purchased together," that is where data mining (market-basket analysis) comes in. If unstructured data such as social-media posts or call-center transcripts needs to be incorporated in the future, the engineer would consider accumulating the raw data in a data lake that does not fix the schema in advance, processing it schema-on-read as each analytical need arises. Discerning "the purpose of analysis (speed vs. storage priority)," "the complexity of data integration," and "structured vs. unstructured" and choosing the schema, integration method, and platform accordingly is the essence of this practice.

6.2.4Section summary

• Fact table = quantitative results, dimension table = analytical perspectives. Star schema = denormalized, higher performance; snowflake schema = normalized, storage-efficient
• ETL = transform then load; ELT = load then transform. OLAP performs multidimensional analysis via drill-down/roll-up/slice and dice
• Data mining discovers patterns without a prior hypothesis. Big data and data lakes use schema-on-read to accommodate unstructured data as well