Star Schema

Table of Contents

  1. Definition
  2. Relation to marketing
  3. How to calculate
  4. How to utilize
  5. Comparison to similar approaches
  6. Best practices
  7. Future trends
  8. Related Terms

Definition

A star schema is a dimensional data model used for analytics in which a central fact table (quantitative measures at a defined grain) links to multiple denormalized dimension tables (descriptive attributes) via surrogate keys. The layout resembles a star: one fact at the center with radiating dimension “spokes.” It optimizes query performance and simplifies business logic for reporting and BI.

Back to Top

Relation to marketing

Marketing data commonly follows a star schema to analyze campaigns, channels, audiences, and outcomes. Facts capture measures such as impressions, clicks, spend, leads, opportunities, and revenue at a chosen grain (e.g., campaign–ad group–day). Dimensions provide context—campaign, channel, creative, audience, geography, device, customer, and time—enabling fast slice-and-dice analysis, cohorting, and KPI tracking (CPA, ROAS, LTV).

Back to Top

How to calculate

  • Grain selection: Clearly define the atomic level (e.g., ad_id × date, customer × month, order line). All measures must be additive at this grain.
  • Additivity: Prefer additive or semi-additive metrics (e.g., spend sums; daily active users are semi-additive across time). Use snapshot facts for point-in-time measures (e.g., pipeline value by day).
  • Surrogate keys: Generate integer keys for dimensions to ensure stable joins despite source system key changes.
  • SCD handling: Implement Type 2 for history (new row per change) when analyzing performance against historical attributes (e.g., campaign owner at the time of spend).
  • Storage and cost sizing (baseline):
    • Fact rows ≈ (# business entities at grain) × (# time buckets) × (# sources).
    • Fact size ≈ rows × (sum of numeric column bytes + FK bytes + overhead).
    • Dimension size is typically small relative to facts; SCD Type 2 increases rows by change frequency.
Back to Top

How to utilize

  • Model facts for core processes: Acquisition (ad interactions), engagement (site/app events), conversion (orders, subscriptions), and retention (renewals).
  • Create conformed dimensions: Shared customer, product, time, and channel dimensions support cross-domain reporting (e.g., aligning ad spend with revenue).
  • Build snapshots where needed: Daily opportunity or subscription snapshots support pipeline and MRR trend analysis.
  • Expose via a semantic layer: Publish certified metrics (CPA, CAC, ROAS, LTV, churn rate) mapped to star tables for consistency across BI tools.
  • Optimize queries: Use partitioning (e.g., by date), clustering on foreign keys, and pre-aggregations for common rollups (campaign → channel → region).
Back to Top

Comparison to similar approaches

AspectStar SchemaSnowflake SchemaThird Normal Form (3NF)Wide Table (One Big Table)Data Vault
StructureCentral fact with denormalized dimensionsDimensions normalized into sub-dim tablesHighly normalized entities & relationshipsSingle denormalized tableHubs, links, satellites
Primary goalFast analytics, simple joinsSpace saving on dimensions, some normalizationOperational integrity, OLTP fitSimplest consumption for fixed queriesAgile ingestion and historization
Join complexityLowMedium (more joins)HighNone (for many queries)Medium to high
Change historySCD patterns (Type 1/2)SCD with more tablesTracked via audit tablesOften flattened; history harderBuilt-in via satellites
Best useBI/metrics dashboards, ad hoc slicingLarge/text-heavy dimensionsOperational systems, MDMPrototyping, ML extractsStaging, enterprise historization layer
Marketer fitHighModerateLowModerate for exportsIndirect (feeds dimensional layer)
Back to Top

Best practices

  • Fix the grain first: Document it in the fact table name (e.g., fact_ad_performance_daily).
  • Use conformed dimensions: Reuse the same time, customer/account, product, and channel dimensions across facts.
  • Prefer integer surrogate keys: Improve join speed and decouple from source keys; include natural keys for traceability.
  • Apply SCD intentionally: Type 2 for historical analysis; Type 1 for corrections; hybrid where needed.
  • Model numeric measures cleanly: Separate counts (clicks), amounts (spend), and ratios (CTR) with ratios computed in the semantic layer to avoid double-aggregation.
  • Design a rich time dimension: Include fiscal periods, ISO weeks, holiday flags, and marketing calendars to simplify date logic.
  • Handle many-to-many carefully: Use bridge tables (e.g., customer ↔ segment memberships) with weighting when aggregations would otherwise overcount.
  • Secure PII: Keep sensitive attributes in restricted dimensions or tokenize; expose only necessary attributes in shared marts.
  • Test and document: Enforce uniqueness of surrogate keys, referential integrity (all FKs resolve), valid ranges, and null checks; publish data dictionaries.
  • Performance tuning: Partition facts by date, cluster on high-cardinality FKs, and precompute aggregates for heavy dashboards.
Back to Top
  • Metrics/semantic layers: Central definitions for KPIs reduce duplication and guard against ratio misaggregation.
  • Open table formats in lakehouses: Star schemas implemented over Iceberg/Delta/Hudi with ACID guarantees and time travel.
  • Streaming + incremental modeling: CDC and event streams maintain near-real-time fact tables and SCD dimensions.
  • Automated lineage and testing: Column-level lineage, contract checks, and anomaly detection embedded in pipelines.
  • Dimensional + entity modeling hybrids: Combining stars for consumption with vault/contract models for ingestion and governance.
  • Privacy-preserving joins: Consent-aware identity keys and clean rooms for cross-platform campaign measurement.
Back to Top
Back to Top