Data modeling is the process of designing how data is structured in a database or warehouse to support efficient storage and querying. It involves defining entities, attributes, relationships, and constraints, and choosing between approaches like dimensional modeling (star and snowflake schemas) for analytics and entity-relationship modeling for transactional systems. Normalization organizes tables to reduce redundancy and improve integrity by splitting them into smaller, related tables following normal forms (1NF, 2NF, 3NF). Denormalization deliberately reintroduces redundancy to improve read performance, and is common in data warehouses and OLAP systems where fast query performance outweighs storage efficiency.
The most common analytics schema is the star schema, in which a central fact table containing measurable metrics is surrounded by dimension tables that hold descriptive attributes. Fact tables store quantitative, measurable business metrics—called measures—such as sales amount, quantity, or duration—along with foreign keys referencing dimensions. Dimension tables store descriptive attributes used to filter, group, and label facts, such as customer details or product categories. Because star schemas are denormalized, queries need few joins and run very fast. A snowflake schema normalizes dimension tables into multiple related tables—for example, splitting a product dimension into product, category, and brand—reducing redundancy at the cost of more joins. A fact constellation (or galaxy) schema extends the idea by sharing dimension tables across multiple fact tables, enabling cross-process analytics. A factless fact table is a fact table that contains no numeric measures, only foreign keys to dimensions; it records events or coverage such as "student attended class on date" and supports questions like "how many users logged in per day?" by counting rows.
Slowly Changing Dimensions (SCDs) describe how dimension records change over time. SCD Type 0 keeps the original value forever, such as a customer's signup date. Type 1 overwrites the old value, discarding history. Type 2 adds a new row with versioning to preserve full history and is the most widely used. Type 3 stores the previous value in a separate column for limited history. Type 4 uses a separate history mini-dimension for fast-changing attributes, while Type 6 is a hybrid combining Type 1, Type 2, and Type 3 to expose both current and historical values. Dimension tables typically use surrogate keys—system-generated artificial primary keys decoupled from source natural keys—to uniquely identify rows and remain stable even when source keys change. Specialized dimensional techniques include junk dimensions (combining many low-cardinality flags), degenerate dimensions (transaction IDs stored directly in the fact table), role-playing dimensions (the same dimension, such as date, used multiple times for different roles like order date and ship date), and conformed dimensions (with the same meaning and key across multiple data marts).
Fact tables come in several flavors. Transactional fact tables record individual events as they occur, with each row representing a single business event such as a sale or click; they grow append-only and support both current and historical analysis at fine granularity. Periodic snapshot fact tables record state at regular intervals, such as end-of-day account balances, useful for tracking slowly evolving states. Accumulating snapshot fact tables track the lifecycle of a process with multiple milestone dates—like order placed, paid, shipped, and delivered—and are updated as the process advances. The grain of a fact table is the level of detail each row represents and must be declared explicitly, because mixing grains is a serious modeling error. Measures are classified as additive (sumable across all dimensions, like revenue), semi-additive (sumable across some dimensions but not others, like account balance summed across accounts but not time), and non-additive (must be recomputed from base measures, like ratios and percentages). Two major methodological traditions guide warehouse design. The Kimball approach builds dimensional data marts directly from sources and integrates them via a bus matrix of conformed dimensions, prioritizing rapid delivery. The Inmon approach builds a normalized enterprise data warehouse first and then derives subject-area marts, prioritizing data integrity and a single source of truth. Data Vault is another methodology emphasizing agility, scalability, and auditability through hubs (business keys), links (relationships), and satellites (descriptive attributes with history).