5. Data modeling: shaping data for its readers

A data model decides which questions are easy and which are painful. You model for how the data will be read, and you choose deliberately between storing a fact once (and joining) or copying it (and risking inconsistency).

Three levels of a model

• Conceptual: the business things and how they relate. Customers place orders; orders contain products.
• Logical: entities, attributes, keys and relationships, independent of any database.
• Physical: actual tables, types, partitions, indexes and file layouts in a specific system.

Grain: the decision that matters most

The grain is what exactly one row represents: “one row per order line”, “one row per user per day”, “one row per document chunk”. State it in one sentence for every table you build.

Mixed grain is the most common cause of wrong numbers. Join a per-order table to a per-order-line table and every order total is counted once per line. In ML, the same mistake silently duplicates training examples.

Normalization versus denormalization

Normalization stores each fact exactly once. A customer’s address lives in one row of one table, and everything else refers to it by key. Updates happen in one place, so the data cannot contradict itself. This suits OLTP systems.

Denormalization copies facts into the tables that read them, so queries need fewer joins. This suits analytics and ML, where data is mostly appended rather than updated, and where scans dominate.

The principle: normalize where data is written, denormalize where it is read. Pipelines are the bridge between the two.

Dimensional modeling (Kimball)

The classic analytics model splits data into two kinds of tables.

• Fact tables record measurable events: orders, clicks, payments, model predictions. They are long and narrow, keyed by foreign keys and a timestamp.
• Dimension tables hold the context: who, what, where. Customers, products, stores, dates. They are short and wide.

A fact table surrounded by its dimensions is a star schema. Fact tables come in three common types: transaction (one row per event), periodic snapshot (one row per entity per period, such as daily account balance) and accumulating snapshot (one row per process instance, updated as it reaches milestones).

Slowly changing dimensions

Dimensions change: a customer moves city or changes plan. How you record that change decides whether history survives.

Type 2 is essential for ML. A churn model must use the customer’s plan as it was on the prediction date, not as it is today. With Type 1 data, that past value is gone forever, and every training example quietly uses today’s value: leakage.

Events versus state

• Events are immutable facts that happened: “user 42 upgraded to Pro at 14:05”.
• State is the current picture: “user 42 is on Pro”.

State can always be recomputed from a complete event history, but events cannot be recovered from state. Keep raw events whenever you can afford to. For ML this is invaluable: you can reconstruct any feature as of any past moment. Building state this way is called event sourcing.

Other modeling styles you will hear about

• One Big Table: a very wide, fully denormalized table. Columnar engines make it practical, because unused columns cost nothing to skip.
• Data Vault: hubs (business keys), links (relationships) and satellites (attributes over time). Built for auditability when integrating many enterprise sources.
• Semantic or metrics layer: metric definitions such as “monthly active users” written once in code, so every dashboard and model computes them identically.

Modeling for machine learning

A supervised training example has a standard shape: an entity ID, a prediction timestamp, the features as they were known at that moment, and a label observed afterwards.

Defining the label is a modeling decision, not an afterthought. “Churned” might mean “no purchase in the 30 days after the prediction date”. That definition fixes three windows: the history the features may look at, the prediction point, and the future window the label looks at. Features may never peek into the label window.

Modeling for LLM and RAG systems

The same discipline applies to unstructured data. A minimal, sound starting schema:

CREATE TABLE documents (
  doc_id        TEXT PRIMARY KEY,
  source_uri    TEXT NOT NULL,
  license       TEXT,
  language      TEXT,
  content_hash  TEXT NOT NULL,      -- detects changes and duplicates
  fetched_at    TIMESTAMPTZ NOT NULL,
  raw_location  TEXT NOT NULL        -- pointer to the blob in object storage
);

CREATE TABLE chunks (
  chunk_id         TEXT PRIMARY KEY,
  doc_id           TEXT REFERENCES documents(doc_id),
  position         INT NOT NULL,     -- order within the document
  text             TEXT NOT NULL,
  token_count      INT,
  chunker_version  TEXT NOT NULL,    -- which chunking logic produced it
  embedding_model  TEXT NOT NULL,    -- which model produced the vector
  embedding        VECTOR(1024)      -- pgvector type
);

The version columns are the important part. When you change the chunker or the embedding model, you can tell exactly which rows are stale and rebuild only those.

For application logs, model one row per model call: request ID, session ID, timestamp, prompt template version, model version, retrieved chunk IDs, output, token counts, latency, and any user feedback. That table becomes your evaluation and fine-tuning goldmine.

Naming as modeling

Put units and meaning in names: amount_eur_cents, created_at_utc, is_deleted. A column called “value” or “date” forces every future reader to guess, and guesses become bugs.

Questions to ask

• What is the grain of this table, in one sentence?
• Is history preserved, or are past values overwritten?
• Which questions does this model make easy, and which does it make hard?
• For a training example: what was knowable at the prediction time, and nothing more?
• If we change the embedding model tomorrow, which rows are affected?