Data engineering6 min read23 September 2026

Modelling data for analysis, which is not how the application stores it

Pointing a reporting tool at a production database is the fastest way to get analytics and the fastest way to get analytics nobody trusts. The application schema is optimised for writing one row correctly; analysis needs to read millions and group them, and the two shapes are genuinely different.

Part ofData Engineering, Databases & Analytics

A normalised transactional schema is correct for its purpose: minimal duplication, referential integrity, fast single-row writes. Asking it analytical questions requires joining eight tables, and the answer depends on which join conditions the analyst happened to use — which is why two people asking the same question get two numbers.

Facts and dimensions

The distinction is old, and it survives because it maps onto how questions are actually asked: measure something, grouped by something else.

	Facts	Dimensions
Contain	Measurements and foreign keys	Descriptive attributes
Examples	An order, a payment, a session	Customer, product, date, channel
Volume	Large, growing constantly	Smaller, changing slowly
Used for	What you aggregate	What you group and filter by
Grain	One row per event, precisely defined	One row per entity

Every analytical question decomposes into the same shape: an aggregate over a fact table, sliced by dimension attributes. "Revenue by channel last quarter" is a sum over an order fact grouped by a channel dimension and filtered by a date dimension. Once the model is in that shape, most questions require one join pattern rather than an act of interpretation.

Declare the grain, in one sentence, before writing any SQL

The grain is what one row represents. Getting it wrong or leaving it implicit is the most consequential modelling error available, because every subsequent aggregate is silently affected.

text

Good grain statements — unambiguous, and one sentence each:

  fct_order_lines   One row per line item per order.
  fct_orders        One row per order.
  fct_daily_usage   One row per tenant per day.
  dim_customers     One row per customer, current attributes.

Why this matters: in fct_order_lines, the order-level discount is
repeated on every line. Summing it gives you the discount multiplied
by the number of lines.

That single error — summing an order-level value at line grain — is
probably the most common wrong number in analytics. It is invisible
in the SQL and only visible in the grain statement, which is why the
grain belongs in the table's documentation, not in someone's memory.

Attributes that change over time

A customer moves from one region to another. Should last year's orders now appear under the new region? Both answers are defensible, and the model has to commit to one rather than leaving it to whoever writes the query.

Approach	Behaviour	Use when
Overwrite	History is rewritten; only current state exists	The attribute is a correction, not a change
Add a new row with validity dates	Both states retained; joins pick the one in effect	The change is meaningful and history must hold
Keep a current and a previous column	Cheap, one step of history	You only ever need to compare against prior state
Snapshot the dimension daily	Complete history, simple queries, larger storage	The dimension is small and history matters throughout

The daily snapshot is underrated for small dimensions. It is inelegant and it makes every historical question trivially answerable without validity-range joins, which is usually worth more than the storage it costs.

Define the metrics once, in code

The most common analytics dispute is not about data quality. It is that two teams have each defined "active user" reasonably and differently, and both numbers are correct under their own definition.

Active user — acted in the period, or merely signed in? Does an API call count? What length is the period?
Revenue — booked, invoiced, recognised or collected? Net of refunds and credits, or gross?
Churn — cancelled, expired, or lapsed after a period of inactivity? Counted by account or by seat?
New customer — first signup, first payment, or first use? On which date do they land?
Session — ended by what gap? Does a background request extend it?

Each of those has several sensible answers, and the damage comes from having several in use simultaneously. The remedy is a single definition expressed once in a modelled table or a metrics layer, referenced everywhere, with the reasoning written next to it — including the edge cases it deliberately excludes.

A layering that keeps the model debuggable

Raw. Source data, unmodified, append-only. Never queried by analysts, and never edited — it is the thing you re-derive everything else from.
Staged. One model per source table: renamed to conventions, typed, lightly cleaned. No joins, no business logic.
Core. Facts and dimensions, conformed, with declared grains. This is where business logic lives, once.
Marts. Aggregates and denormalised tables shaped for specific consumption, built only from core.

The rule that makes the layering worth having is that each layer reads only from the one below. A mart querying raw data bypasses every definition in core, which is how a dashboard ends up disagreeing with the model it was supposed to be built on — and the violation is invisible unless the dependency graph is checked.

The mistakes that cause the most rework

Mistake	Symptom
Reporting directly on production tables	Analytical load on the transactional database; queries break when the app schema changes
No declared grain	Aggregates silently wrong through double counting
Business logic duplicated per dashboard	Every chart is a different definition of the same metric
Overwriting changing attributes	Historical reports move without anyone changing them
One wide table for everything	Unqueryable, and impossible to establish a grain for
No date dimension	Fiscal periods, week starts and holidays re-implemented in every query

The date dimension is the cheapest item on that list to fix and the most frequently skipped. A table with one row per day carrying fiscal period, quarter, week start, weekday flag and holiday flag removes an entire category of recurring date logic — and makes every report agree about when a week begins.

Questions

Why not just report directly on the production database?

Because the schemas are optimised for opposite things. A normalised transactional schema writes one row correctly; analysis reads millions and groups them, which means joining many tables in a way that varies by whoever wrote the query — so two people asking the same question get two answers. It also puts analytical load on the database serving your application, and every app schema change breaks reports.

What is a table's grain and why does it matter so much?

The grain is what one row represents, stated in one sentence. It matters because every aggregate depends on it: in a table with one row per order line, the order-level discount repeats on every line, so summing it multiplies the discount by the number of lines. That error is invisible in the SQL and only visible in the grain statement — which is why it belongs in documentation rather than in someone's memory.

How should I handle a customer attribute that changes?

Decide first whether it is a correction or a change. A misspelled name should be overwritten, because the old value was never true. A genuine relocation is a change, and overwriting it makes last year's regional analysis wrong — so either add a new row with validity dates, or snapshot the dimension daily, which is inelegant and makes every historical question trivially answerable.

Why do two dashboards show different numbers for the same metric?

Almost always because each defines the metric reasonably and differently — active user, revenue, churn and new customer each have several defensible definitions. When the logic lives inside a chart it is invisible, uncopyable and unversioned, so the next dashboard re-implements it slightly differently. Define each metric once in the model, with the reasoning and excluded edge cases written next to it, and let dashboards read a column.