DE//AGENT
Explore
Pattern community intermediate

Dimensional Model (star schema)

Analysts re-derive the same joins and disagree on metric definitions.

Reference architecture canvas Sources feed processing stages; a data contract sits between processing and destinations; a failure path routes invalid output to a dead-letter zone. OLTP DBs cdc Event log stream Processing transform · model idempotent writes versioned code Data contract schema · grain · SLA validated at boundary Warehouse analytics Feature/API agents · apps Dead letter quarantined output

At a glance

What Analysts re-derive the same joins and disagree on metric definitions.
When BI-heavy consumption; Stable, known query patterns
Where Data Warehouses

Problem

Analysts re-derive the same joins and disagree on metric definitions.

Solution

Facts (events/measures) surrounded by conformed dimensions (context). fct_orders (grain: one row per order line) ├─ dim_customer (SCD2 for history) ├─ dim_product └─ dim_date Declare the fact grain explicitly. Conform dimensions across facts so they can be combined. Use slowly-changing-dimension type 2 when an attribute's history must be preserved at the point of the fact.

When not to use

  • Rapidly changing source schemas (consider Data Vault)
  • Pure event/append analytics

Security scan

Last sweep 2026-10-24: 0 finding(s), 0 marked as examples.

Context

A warehouse serving BI and analytics with recurring query patterns.

Forces

  • Analysts need simple, fast queries
  • Definitions must be consistent
  • History matters for some attributes

Tradeoffs

Choose this when Costs & caveats
fast, intuitive, consistent; BI-friendly less flexible for rare queries; ETL to maintain SCDs

Diagram

dim_customer ─┐
dim_product  ─┼─▶ fct_orders ─▶ marts
dim_date     ─┘

Registry stats

Verification community · Sep 20, 2026
Completeness 75
Trust multiplier ×1.25
Composite 34.75 · adequate
Freshness 34 days since last source check
Difficulty intermediate
Version 1.0.0
License CC-BY-4.0
Security flags none