Data Modeling for Analytics

Dimensional modeling

Dimensional modeling is a way of designing analytical tables around business processes: measurements go into fact tables, and the context used to filter and group them goes into dimension tables. The result is the star schema, where the terms fact table and dimension table are explained with a small example from the web shop. The method was worked out by Ralph Kimball and his colleagues, and their four design steps are still the best way to start:

1. Select the business process: something that happens and is measured, such as meters reporting readings or KBS sending invoices. Not a department, not a report.
2. Declare the grain: what one row of the fact table is.
3. Identify the dimensions: the who, what, where and when that describe each row at that grain.
4. Identify the facts: the numeric measurements, true at that grain.

The order matters. Teams that start from the report ("Margriet wants kWh per tariff per month") build one table per report, and each new question needs a new table. Starting from the process and its finest grain gives tables that can answer the next question too.

Dimensional models are deliberately denormalized (see Denormalization): the tariff name is repeated in every contract row so that queries need few joins. That is safe here because the tables are written only by the pipeline, never edited by applications.

Declare the grain

The grain of a fact table is the exact meaning of one row. Kimball's group puts it as: "The grain establishes exactly what a single fact table row represents." For Kestrel's readings the grain is one meter, one day. Every design question is then answered against that sentence: a customer dimension fits (each meter-day belongs to one customer), a per-invoice amount does not (an invoice covers a month).

Choose the atomic grain, the finest level the process records, when you can. You can always add up daily rows to months; you can never split a monthly total back into days. A grain declaration is also a test: if the key columns of the grain are not unique in the data, something upstream is wrong.

-- Declared grain: one row per meter per day. Does the source respect it?
SELECT count(*)                                  AS rows,
       count(DISTINCT (meter_id, reading_date))  AS meter_days
FROM daily_readings;

-- Build the fact table at that grain, with a date key in YYYYMMDD form
CREATE TABLE fact_daily_reading AS
SELECT DISTINCT
  CAST(strftime(reading_date, '%Y%m%d') AS INTEGER) AS date_key,
  meter_id,
  kwh_import,
  kwh_export
FROM daily_readings;

SELECT count(*) AS rows, min(date_key) AS first_day, max(date_key) AS last_day
FROM fact_daily_reading;

┌───────┬────────────┐
│ rows  │ meter_days │
│ int64 │   int64    │
├───────┼────────────┤
│   360 │        359 │
└───────┴────────────┘
┌───────┬───────────┬──────────┐
│ rows  │ first_day │ last_day │
│ int64 │   int32   │  int32   │
├───────┼───────────┼──────────┤
│   359 │  20260901 │ 20260930 │
└───────┴───────────┴──────────┘

360 rows but only 359 distinct meter-days: one row breaks the grain (meter M-30005 appears twice on 12 September). After deduplication the fact table has 359 rows, while 12 meters over 30 days should give 360. The grain check finds duplicates; a missing day (meter M-30010 on 17 September) needs a separate completeness check. Both problems are planted in the sample data; see Data Types and Data Quality.

Facts and measures

Kimball distinguishes four kinds of fact table:

How a measure may be added up depends on what it means. An additive fact can be summed across every dimension: kWh imported adds up over meters, customers, tariffs and days, and so do invoice amounts. A semi-additive fact can be summed across some dimensions but not across time: balances and counts at a moment, such as the open amount owed at month end or the number of active contracts. A third group is non-additive: ratios and percentages, such as the share of invoices paid on time, which must be recalculated from their parts rather than added or averaged.

-- Periodic snapshot: active contracts at each month end, per segment
CREATE TABLE fact_contract_snapshot AS
SELECT m.month_end, cu.segment, count(*) AS active_contracts
FROM (SELECT last_day(d::DATE) AS month_end
      FROM generate_series(DATE '2026-06-01', DATE '2026-09-01',
                           INTERVAL 1 MONTH) t(d)) m
JOIN contracts c ON c.start_date <= m.month_end
                AND (c.end_date IS NULL OR c.end_date >= m.month_end)
JOIN customers cu USING (customer_id)
GROUP BY m.month_end, cu.segment;

-- Adding up across segments is fine: one total per month end
SELECT month_end, sum(active_contracts) AS active_contracts
FROM fact_contract_snapshot
GROUP BY month_end
ORDER BY month_end;

-- Adding up across time is not
SELECT sum(active_contracts) AS summed_over_four_months,
       sum(active_contracts) FILTER (WHERE month_end = DATE '2026-09-30')
                             AS at_end_of_september
FROM fact_contract_snapshot;

┌────────────┬──────────────────┐
│ month_end  │ active_contracts │
│    date    │      int128      │
├────────────┼──────────────────┤
│ 2026-06-30 │               11 │
│ 2026-07-31 │               11 │
│ 2026-08-31 │               11 │
│ 2026-09-30 │               11 │
└────────────┴──────────────────┘
┌─────────────────────────┬─────────────────────┐
│ summed_over_four_months │ at_end_of_september │
│         int128          │       int128        │
├─────────────────────────┼─────────────────────┤
│                      44 │                  11 │
└─────────────────────────┴─────────────────────┘

Kestrel had 11 active contracts with a known customer at each month end, not 44. For a quarter, report the value at the end of the period, or the average over the period, and say which. BI tools need to be told this explicitly: a measure defined as a plain sum will happily add up month ends (see Business Intelligence Tools).

Surrogate and natural keys

A natural key is the identifier a source system already uses for a thing: KBS's customer number C-10003, a meter's 18-digit EAN code. In a warehouse it is kept as an ordinary column, so people can search and trace by it, but it does not join the fact to the dimension. A surrogate key is a meaningless integer that the warehouse assigns to each dimension row and stores in the fact rows. Kimball's advice is "simple integers, assigned in sequence, starting with the value 1". The general idea is on The Relational Model; in analytics it matters for specific reasons:

• History. A type 2 dimension (below) has several rows for one customer. They cannot all have C-10003 as their key.
• Several sources. When a second billing system is merged in, its customer numbers can overlap with KBS's.
• Source changes. Source systems renumber, merge duplicates and reuse codes. The warehouse's keys stay stable.
• Missing references. A fact whose source key matches nothing can point to a special "unknown" member instead of disappearing.

The last point is easy to get wrong. Kestrel's sample data contains a contract for customer C-10099, who does not exist in KBS's customer table. An inner join to the customers drops that meter's readings without a word. The common practice is a dimension row with key 0 for "unknown", and a fact load that falls back to it:

-- Customer dimension with surrogate keys, plus one "unknown" member
CREATE TABLE dim_customer AS
SELECT 0 AS customer_key, 'C-UNKNOWN' AS customer_id,
       'unknown' AS segment, 'unknown' AS city
UNION ALL
SELECT row_number() OVER (ORDER BY customer_id), customer_id, segment, city
FROM customers;

-- Fact rows look up the surrogate key; no match means key 0, not a lost row
CREATE TABLE fact_daily_reading AS
SELECT DISTINCT
  r.reading_date,
  r.meter_id,
  coalesce(d.customer_key, 0) AS customer_key,
  r.kwh_import
FROM daily_readings r
JOIN meters m USING (meter_id)
JOIN contracts c USING (contract_id)
LEFT JOIN dim_customer d ON d.customer_id = c.customer_id;

SELECT d.segment, count(DISTINCT f.meter_id) AS meters, sum(f.kwh_import) AS kwh
FROM fact_daily_reading f
JOIN dim_customer d USING (customer_key)
GROUP BY d.segment
ORDER BY d.segment;

┌───────────┬────────┬───────────────┐
│  segment  │ meters │      kwh      │
│  varchar  │ int64  │ decimal(38,3) │
├───────────┼────────┼───────────────┤
│ business  │      3 │      3213.790 │
│ household │      8 │      2114.174 │
│ unknown   │      1 │       221.324 │
└───────────┴────────┴───────────────┘

The total still matches the source (minus the one duplicate row), and the 221.324 kWh without a known customer is visible as its own line, where someone will ask about it, instead of missing from every report. The LEFT JOIN with coalesce is the pattern: never let a fact load depend on an inner join to a dimension.

The date dimension is the usual exception to "meaningless integers": a date key such as 20260912 (YYYYMMDD, as in the grain example) is readable and sorts correctly, and dates never change their meaning. A real dim_date has one row for every calendar day, with columns such as weekday, ISO week, month, quarter and Dutch public holiday, so that "weekend consumption" is a filter rather than a calculation in every query.

Conformed dimensions

Kestrel's readings and invoices are two business processes with two fact tables at two grains. They can still be compared, as long as both use the same dimension tables for what they share. A conformed dimension is a dimension used with identical keys, column names and values by several fact tables, so that results from those facts can be put side by side. In Kimball's words, this is "the essence of integration" in a warehouse.

The planning tool for this is the bus matrix: business processes as rows, dimensions as columns, a mark where a process uses a dimension. Each column with more than one mark is a dimension that must be conformed.

Three more dimension patterns turn up in almost every model:

• Role-playing dimension: one physical dimension used several times in a fact for different roles. An invoice has a billing period and a due date, both pointing to dim_date.
• Degenerate dimension: an identifier kept in the fact table with no dimension table of its own. The invoice number INV-40028 has no attributes beyond what the fact row holds, but people search by it.
• Junk dimension: one small table combining unrelated low-cardinality flags (paid by direct debit, paper invoice, estimated reading), so the fact table does not need a column per flag.

Slowly changing dimensions

Dimension attributes change: customers move, change tariff, or turn from household into business. A slowly changing dimension (SCD) is a dimension whose attributes change over time, together with the rule for how those changes are stored. The rule decides whether old facts are reported with the old value or the new one. The introduction covers types 1 and 2; Kimball's full list, in short:

Types 1 and 2 cover most needs; choose per attribute, not per table. A corrected typo in a name is type 1; a change of tariff, segment or city, which changes how facts should be grouped, is type 2. In Kestrel's sample data, customer C-10003 had a fixed contract until 30 June 2026 and a dynamic one from 1 July. Here is a version of dim_customer that tracks the tariff as a type 2 attribute, and her invoices reported both ways:

-- dim_customer as type 2: a new row whenever the customer's tariff changes
CREATE TABLE dim_customer AS
SELECT
  row_number() OVER (ORDER BY cu.customer_id, c.start_date) AS customer_key,
  cu.customer_id,                                   -- natural key from KBS
  cu.segment,
  c.tariff,
  c.start_date                                      AS valid_from,
  coalesce(c.end_date, DATE '9999-12-31')           AS valid_to,
  c.end_date IS NULL                                AS is_current
FROM customers cu
JOIN contracts c USING (customer_id);

SELECT * FROM dim_customer WHERE customer_id = 'C-10003';

-- Lotte's invoices: tariff as it was (type 2) and as it is now (type 1 view)
SELECT i.invoice_id, i.period, i.amount_eur,
       was.tariff AS tariff_as_was,
       cur.tariff AS tariff_as_is
FROM invoices i
JOIN contracts c USING (contract_id)
JOIN dim_customer was ON was.customer_id = c.customer_id
                     AND i.period BETWEEN was.valid_from AND was.valid_to
JOIN dim_customer cur ON cur.customer_id = c.customer_id AND cur.is_current
WHERE c.customer_id = 'C-10003'
ORDER BY i.period;

┌──────────────┬─────────────┬───────────┬─────────┬────────────┬────────────┬────────────┐
│ customer_key │ customer_id │  segment  │ tariff  │ valid_from │  valid_to  │ is_current │
│    int64     │   varchar   │  varchar  │ varchar │    date    │    date    │  boolean   │
├──────────────┼─────────────┼───────────┼─────────┼────────────┼────────────┼────────────┤
│            3 │ C-10003     │ household │ fixed   │ 2022-06-01 │ 2026-06-30 │ false      │
│            4 │ C-10003     │ household │ dynamic │ 2026-07-01 │ 9999-12-31 │ true       │
└──────────────┴─────────────┴───────────┴─────────┴────────────┴────────────┴────────────┘
┌────────────┬────────────┬───────────────┬───────────────┬──────────────┐
│ invoice_id │   period   │  amount_eur   │ tariff_as_was │ tariff_as_is │
│  varchar   │    date    │ decimal(10,2) │    varchar    │   varchar    │
├────────────┼────────────┼───────────────┼───────────────┼──────────────┤
│ INV-40001  │ 2026-06-01 │         92.40 │ fixed         │ dynamic      │
│ INV-40004  │ 2026-07-01 │         41.75 │ dynamic       │ dynamic      │
│ INV-40016  │ 2026-08-01 │         49.30 │ dynamic       │ dynamic      │
│ INV-40028  │ 2026-09-01 │         66.95 │ dynamic       │ dynamic      │
└────────────┴────────────┴───────────────┴───────────────┴──────────────┘

With type 1 (only the current row), her June invoice of €92.40 would be reported as dynamic-tariff revenue, although she was on a fixed tariff then: "billed per tariff" for June would be wrong, and would change again every time a customer switches. Type 2 keeps June under fixed. Two details from the example are worth copying: the open-ended row gets a far-future valid_to (9999-12-31) instead of NULL, so BETWEEN works without special cases, and the natural key stays in the dimension so the customer's rows can be found together. In a real warehouse the fact load looks up the right customer_key once, when the fact row is written, so queries need no date logic at all. dbt's snapshots build exactly this kind of table from a changing source (see Warehouses, Lakes and Lakehouses).

The snowflake schema

A snowflake schema is a star schema whose dimension tables are normalized into further tables: dim_contract would hold a tariff_key pointing to a separate dim_tariff, which might point to a dim_tariff_group. Each level saves a little repetition and adds a join to every query that needs the attribute.

Prefer the plain star unless there is a concrete reason. Analysts and BI tools write simpler and faster queries against it, and columnar storage compresses repeated tariff names to almost nothing (see Snowflake schema). Good reasons do exist: a large shared hierarchy (a product catalog used by many dimensions), or an attribute set maintained by a different team. Do not confuse the pattern with Snowflake, the cloud data platform: the name is a coincidence of shape.

Defining metrics once

The hardest part of modeling is not tables, it is words. Ask "how many active contracts did we have on 30 September?" and Kestrel's sample data gives three defensible answers:

-- "How many active contracts did we have on 30 September?" Three answers:
SELECT
  count(*) FILTER (WHERE end_date IS NULL)                       AS no_end_date,
  count(*) FILTER (WHERE end_date IS NULL
                     AND start_date <= DATE '2026-09-30')         AS started_and_running,
  count(*) FILTER (WHERE end_date IS NULL
                     AND start_date <= DATE '2026-09-30'
                     AND customer_id IN (SELECT customer_id FROM customers))
                                                                 AS with_known_customer
FROM contracts;

-- "Churn in 2026 so far": contracts that ended vs customers who left
SELECT
  (SELECT count(*) FROM contracts
    WHERE end_date BETWEEN DATE '2026-01-01' AND DATE '2026-09-30') AS contracts_ended,
  (SELECT count(*) FROM customers cu
    WHERE NOT EXISTS (SELECT 1 FROM contracts c
                      WHERE c.customer_id = cu.customer_id
                        AND (c.end_date IS NULL OR c.end_date > DATE '2026-09-30'))
  ) AS customers_without_contract;

┌─────────────┬─────────────────────┬─────────────────────┐
│ no_end_date │ started_and_running │ with_known_customer │
│    int64    │        int64        │        int64        │
├─────────────┼─────────────────────┼─────────────────────┤
│          13 │                  12 │                  11 │
└─────────────┴─────────────────────┴─────────────────────┘
┌─────────────────┬────────────────────────────┐
│ contracts_ended │ customers_without_contract │
│      int64      │           int64            │
├─────────────────┼────────────────────────────┤
│               1 │                          0 │
└─────────────────┴────────────────────────────┘

13 is what Lucía Romero's old spreadsheet KPI pack counted: every contract without an end date, including one that only starts on 1 October. 12 is only a step on the way: started and not ended, but the contract of a customer who does not exist is still allowed. 11 follows Femke Jansen's approved definition, "has started, has not ended on that date, and belongs to a known customer", and is the number that goes into Margriet's pack. Churn is just as slippery: one contract ended this year (CT-20003), but its customer switched to a dynamic contract and never left, so customer churn is zero. Both numbers are correct; they answer different questions. And is customer C-10012, who signed up on 25 September for a contract that starts on 1 October, a new customer of September or of October?

The fix is organizational first, technical second:

1. Write the definition down, in business language, with the edge cases: "A contract is active on day D when its start date is on or before D, it has no end date or an end date on or after D, and it belongs to a known customer." Femke Jansen, as owner of the billing data, approved it (see Data Governance and Privacy).
2. Implement it once, as a column or view in the gold layer (an is_active flag per contract per day), or as a measure in the BI tool's semantic layer, and let every report use that.
3. Name things precisely: "contract churn" and "customer churn" are two metrics, not one with two values. This is the analytics version of the ubiquitous language from domain-driven design.