Data Modelling

01 What it is

Three levels: conceptual, logical, physical

A conceptual model captures business entities and relationships with no technology in sight. A logical model adds attributes, keys and normalisation while staying platform-agnostic. A physical model realises it as tables, columns, data types and indexes for a specific engine. Modelling matters for data quality and consistency, query performance, governance, and — above all — a shared "single source of truth" understanding across teams.

02 Core concepts

The grammar of models

• Entity, attribute, relationship — a thing, its properties, and how things associate.
• Cardinality — the numeric ratio of a relationship: 1:1, 1:N, M:N.
• Keys — primary (unique row ID), foreign (a reference), surrogate (system-generated), natural/business key.
• Normalisation — 1NF atomic values, 2NF no partial dependency, 3NF no transitive dependency, BCNF stricter still.
• Denormalisation — deliberate redundancy traded for read speed.
• ERD — the entity-relationship diagram; the visual blueprint.
• Grain — the level of detail one fact row represents — decide it first, always.
• Facts & dimensions — measurable events, and the descriptive context around them.

03 Approaches

Pick the technique for the job

There is no one true model — there are techniques, each strong for a purpose. Know when to reach for each.

04 Worked examples

Normal forms, facts, dimensions & bridges — by example

The fastest way to internalise modelling is to watch one messy table get fixed. We take a single orders dataset from unnormalised, through 2NF and 3NF, then reshape it into a star schema — building the dimensions, the fact and a bridge table for a many-to-many — and finally as a Data Vault.

Second Normal Form (2NF)

Rule: be in 1NF, and every non-key column must depend on the whole composite key — no partial dependencies.

Third Normal Form (3NF)

Rule: be in 2NF, and no non-key column may depend on another non-key column — no transitive dependencies.

Building dimensions and a fact (the star)

Analytics flips the goal: instead of removing all redundancy, you denormalise descriptive data into dimensions and keep measures in a fact at one declared grain. Dimensions get a surrogate key and carry their attributes right on the row.

-- A dimension DESCRIBES: denormalised, surrogate key, SCD-ready
create table dim_product (
  product_sk   bigint primary key,  -- surrogate: stable & meaningless
  product_id   varchar,              -- natural/business key
  product_name varchar,
  category     varchar,              -- denormalised attribute
  valid_from   date, valid_to date,  -- SCD Type 2 window
  is_current   boolean
);
create table dim_date  ( date_sk int primary key, full_date date, month varchar, quarter varchar );
create table dim_store ( store_sk int primary key, store_name varchar, region varchar );

-- A fact MEASURES: foreign keys + additive measures at ONE grain
create table fct_sales (
  sales_sk    bigint primary key,
  date_sk     int    references dim_date(date_sk),
  product_sk  bigint references dim_product(product_sk),
  store_sk    int    references dim_store(store_sk),
  quantity    int,             -- additive measure
  net_amount  numeric(12,2)     -- additive measure
);  -- GRAIN: exactly one row per order line

-- "Revenue by category in Q1" — the classic star join
select p.category, sum(f.net_amount) as revenue
from fct_sales f
join dim_product p on p.product_sk = f.product_sk
join dim_date    d on d.date_sk    = f.date_sk
where d.quarter = 'Q1'
group by p.category;

Bridge tables for many-to-many

What if a sale is credited to several sales reps? You can't add multiple reps to a fact row without breaking its grain and double-counting revenue. A bridge table resolves the many-to-many with an allocation factor that sums to 1.0 per group.

-- The fact points at a GROUP, not a single rep, to keep its grain
alter table fct_sales add credit_group_sk bigint;

create table dim_sales_rep ( rep_sk bigint primary key, rep_name varchar );

create table bridge_sales_credit (
  credit_group_sk bigint,                 -- group the fact references
  rep_sk          bigint references dim_sales_rep(rep_sk),
  allocation      numeric(4,3),           -- weight; sums to 1.0 per group
  primary key (credit_group_sk, rep_sk)
);

-- Multiply the measure by allocation so nothing is double-counted
select r.rep_name, sum(f.net_amount * b.allocation) as credited_revenue
from fct_sales f
join bridge_sales_credit b on b.credit_group_sk = f.credit_group_sk
join dim_sales_rep      r on r.rep_sk = b.rep_sk
group by r.rep_name;
-- Sale 1 ($25, group 900) -> Dana $12.50, Ravi $12.50; sale 2 ($16, group 901) -> Dana $16

The same pattern handles any many-to-many: an account with several owners, a patient with multiple diagnoses, a product in several categories. The bridge holds the relationship; the allocation factor keeps sums honest.

The same data as a Data Vault

Data Vault splits everything into three parts: hubs (immutable business keys), satellites (descriptive context, fully historised) and links (relationships between hubs). It trades query simplicity for auditability and painless change.

05 Key personas

Who models data

06 Methodology

From requirement to physical table

07 Best practices

What good looks like

08 2026 trends

Modelling on the lakehouse

↻ Refreshed 2026-07-16 by the AI desk

09 In practice

Why the model is a business asset

A good model is the difference between data that answers questions in seconds and data no one trusts. Here's how enterprises apply it, what the analysts and platforms are doing, and how SCIKIQ delivers it.

What the leaders are doing

The lakehouse has become the model's home, and open formats keep it portable — here's the signal from the standard, the analysts and the platforms.

10 Developer lab

Hands-on: build a real mart

-- Star schema: grain = one row per order line
create table fct_sales (
  sales_sk    bigint,   -- surrogate key
  date_sk     int,      -- FK -> dim_date
  product_sk  int,      -- FK -> dim_product
  store_sk    int,      -- FK -> dim_store
  quantity    int,
  net_amount  numeric(12,2)  -- additive fact
);

-- Track history: a new row whenever a tracked column changes
{% snapshot dim_customer_snapshot %}
{{ config(
    target_schema='snapshots',
    unique_key='customer_id',
    strategy='check',
    check_cols=['tier', 'region']
) }}
select customer_id, name, tier, region
from {{ source('crm', 'customers') }}
{% endsnapshot %}

-- Hub: the immutable business key
create table hub_customer (
  customer_hk   binary,      -- hash of the business key
  customer_bk   varchar,     -- the business key itself
  load_ts       timestamp,   record_source varchar
);
-- Satellite: descriptive context, fully historised
create table sat_customer (
  customer_hk   binary,   load_ts timestamp,
  hash_diff     binary,   -- change detection
  name          varchar,  tier varchar
);
-- Link: a relationship between hubs
create table link_order (
  order_hk binary, customer_hk binary, product_hk binary, load_ts timestamp
);

// Paste into dbdiagram.io to see the star render as a diagram
Table fct_sales {
  sales_sk   bigint [pk]
  date_sk    int    [ref: > dim_date.date_sk]
  product_sk int    [ref: > dim_product.product_sk]
  store_sk   int    [ref: > dim_store.store_sk]
  net_amount decimal
}
Table dim_date    { date_sk int [pk]  full_date date  month int }
Table dim_product { product_sk int [pk]  sku varchar  category varchar }
Table dim_store   { store_sk int [pk]  name varchar  region varchar }

Same customer, three lenses: a Kimball SCD2 dimension for history, a Data Vault trio for auditable integration, and a DBML star you can paste into dbdiagram.io to see the ERD instantly.

11 Analyst lab

Hands-on: model the business first

12 Career path

From analyst to enterprise architect

Modelling skill compounds into architecture. Bands are indicative US figures and vary by market and sector.

13 Skill matrix

Rate yourself, then level up

Find your current row and aim one column right.

14 Glossary

Terms worth knowing

15 Test yourself

Check your understanding

16 Keep learning

Where to go next

Models need clean data flowing in and shared meaning on top. Continue the path: