Dimensional modelling and star schemas

How analytics data is modelled for reporting: facts and dimensions, star and snowflake schemas, grain, slowly changing dimensions, conformed dimensions, wide denormalised tables, pre-aggregations, and how these choices make dashboards fast and numbers consistent.

Reading is half of it. See this used in a real interview: walk through Design an Ad Click Aggregator →

Transactional databases are normalized for correct updates; analytics databases are modelled for fast, understandable queries over huge volumes. Dimensional modelling (fact tables surrounded by dimension tables, the "star schema") is the standard approach, used in warehouses and lakehouses alike. Data-heavy interview questions, such as analytics dashboards, ad reporting or business metrics, are stronger when you can sketch the analytical model, not just the pipeline.

Facts and dimensions

• Fact tables record measurable events: an order, a click, a delivery, a stream view. They contain foreign keys to dimensions and numeric measures (amount, quantity, duration). They are long (billions of rows) and narrow.
• Dimension tables describe the context: customer, product, restaurant, date, campaign, device. They contain descriptive attributes used for filtering and grouping. They are wide and comparatively short.

A query like "revenue by city and week for premium customers" joins the orders fact to the customer, location and date dimensions, filters and groups.

Star versus snowflake

• Star schema: each dimension is a single denormalized table (city, region and country all in the location dimension). Few joins, simple queries, fast.
• Snowflake schema: dimensions are normalized into sub-tables (city to region to country). Less redundancy, more joins.

Analytics favours stars: storage is cheap in columnar formats and simpler queries are faster and less error-prone. See database normalization for the opposite trade-off in transactional systems.

Grain

The most important decision for a fact table is its grain: what one row represents. "One row per order line", "one row per ad impression", "one row per user per day". State it explicitly; mixing grains in one table produces double counting. Finer grain is more flexible; coarser grain is smaller and faster. Often you keep a fine-grained fact table plus aggregated ones.

Types of fact tables

• Transaction facts: one row per event (orders, clicks).
• Periodic snapshots: one row per entity per period (daily account balances, inventory levels).
• Accumulating snapshots: one row per process instance, updated as it progresses (an order with placed, paid, shipped and delivered timestamps), good for measuring durations between steps. See Design DoorDash.

Slowly changing dimensions

Dimension attributes change: a customer moves city, a product changes category. How should old facts be reported?

• Type 1: overwrite the attribute; history reports under the new value.
• Type 2: add a new row with validity dates (and a new surrogate key); old facts keep pointing to the old version, so history reports as it was at the time.
• Type 3: keep a "previous value" column for limited history.

Type 2 is the common choice when historical accuracy matters (sales by region as the regions were). Point-in-time joins use the validity dates.

Conformed dimensions

When several fact tables (orders, deliveries, support tickets) share dimensions defined the same way (one customer dimension, one date dimension), metrics across them can be combined consistently. Inconsistent definitions across teams ("what is an active user?") are the main source of conflicting numbers; shared dimensions and a metrics layer with central metric definitions fix this. See data quality and data contracts.

Wide tables and pre-aggregation

Modern columnar engines also make one big table practical: facts pre-joined with commonly used dimension attributes, avoiding joins at query time for dashboards. Combine with pre-aggregated rollups (by hour, day, campaign) or materialised views for the most common queries, and real-time OLAP stores for sub-second dashboards on fresh data. See OLTP versus OLAP.

Where the model lives

Raw events land in the lake; transformation jobs (often SQL with tools like dbt) build cleaned facts and dimensions, then aggregates. Partition fact tables by date, cluster by frequently filtered keys, and keep dimensions small enough to broadcast in joins. See data lakes and lakehouses and MapReduce and Spark.

Example: ad reporting

• Fact: impressions and clicks at event grain, plus ad_stats_hourly aggregated by (ad, hour, country, device).
• Dimensions: ad (campaign, advertiser, creative), date and hour, geography, device, publisher.
• Billing reads hourly aggregates reconciled against raw facts; dashboards read the hourly table or an OLAP store. See Design an Ad Click Aggregator.

In the interview

When a question involves reporting, sketch it briefly: "Orders are a transaction fact at order-line grain with customer, restaurant, location and date dimensions (customer as type 2 for history); an accumulating snapshot tracks each order's lifecycle timestamps for delivery-time metrics; dashboards read daily rollups." It shows you know analytics needs its own model.

Checklist

• Facts with measures and keys; dimensions with descriptive attributes.
• Star schemas for analytics; explicit grain for every fact table.
• Transaction, periodic snapshot or accumulating snapshot facts as appropriate.
• Slowly changing dimensions chosen deliberately (often type 2).
• Conformed dimensions and central metric definitions.
• Wide tables, rollups and OLAP stores for fast dashboards; date partitioning.