Part 4 · 1 chapters · ~8 min

Dimensional Modelling

Kimball's facts and dimensions, choosing and declaring the grain, star and snowflake schemas, conformed dimensions across teams, slowly changing dimensions (types 1 and 2), degenerate dimensions, wide denormalised tables as a modern alternative, and metrics layers.

Facts, dimensions and grain

code

-- SCD type 2 for customers: one row per version
create table dim_customer (customer_sk bigint primary key, customer_id uuid, kyc_tier int, state text,
                           valid_from timestamp, valid_to timestamp, is_current boolean);
-- join each transfer to the customer version valid at the time
select c.kyc_tier, sum(f.amount_kobo) / 100 as volume
from fct_transfers f
join dim_customer c on c.customer_id = f.customer_id and f.created_at >= c.valid_from and f.created_at < c.valid_to
group by 1;
-- dbt snapshots build SCD2 tables automatically from a changing source

decision	guidance
grain	declare it first ("one row per completed transfer"); never mix grains in a fact table
conformed dimensions	one dim_customer used by every team, so "active customers" means the same everywhere
wide tables	with cheap columnar storage, a pre-joined wide table is often simpler for analysts; keep the star underneath
metrics layer	define "transfer volume" once (dbt semantic layer, Cube) so dashboards cannot disagree

A STAR SCHEMA FOR TRANSFERS

one fact table, surrounded by dimensions

swipe the figure sideways, or tap expand for full screen

1/4

the fact

The fact table records events at a declared grain: one row per completed transfer, with measures (amount, fee) and keys to dimensions.

measures at a fixed grainone row per transfer