Back to site
Engine

The data model

One description of what the company's numbers mean, built with help instead of by hand and defended against the join traps that quietly inflate a figure

What it is

Between the sources and the charts sits one description of what the company's numbers mean: which tables are facts and which are dimensions, how they join, what counts as revenue, what a working day is. A metric is defined there once and read from there by every surface, which is why the chart, the export and the AI answer cannot drift apart, and why an argument about a figure turns into an argument about a definition and gets settled once.

The shape is derived, not declared. Whether a relation is a fact or a dimension comes from join cardinality and not from a field somebody set, so a model stays correct when the data changes shape instead of staying correct until somebody forgets to relabel it.

Built with help, not by hand

A dataset does not have to be modelled by hand before it is useful. A scan reads it and writes down what it found:

  • What this data is - the domain it belongs to, a plain summary, the questions it is normally asked, and the gotchas somebody would otherwise learn the hard way
  • What the columns mean - business labels for names that were never meant to be read by people
  • Query patterns - the shapes of question this data actually answers, recorded once instead of rediscovered per conversation
  • Business rules - what holds true in it: what is netted off, what is excluded, what a status really means
  • Suggested metrics and dimensions for the model itself

Suggestions are accepted, not applied silently - the model everyone depends on is not edited behind anyone's back. The same knowledge is what the assistant retrieves when somebody asks a question in words, instead of inferring it on the spot; that side of it is AI in analytics.

The rest of what the assistant does with data - views, formulas, cleansing, and building a whole application's objects at once - is AI in the data layer.

Scan results are visible on the dataset, and a rescan can be run when the data or its meaning has moved on. Each run is a recorded job with its progress and outcome, so a dataset that failed to scan says so instead of quietly degrading every answer built on it.

The traps in the data model

Put sales and stock on one screen and you have walked into the oldest failure in reporting.

Both facts hang off the same dimensions - product, store, calendar. Sales has a row per line item; inventory has a row per daily snapshot. Join them the obvious way and every sales row is repeated once per snapshot it matches, so the revenue total quietly multiplies. Nothing fails, nothing warns: the number is simply wrong, and wrong in the flattering direction. That is the fan trap. Its mirror is the chasm trap - two facts reachable through a shared dimension but with no relationship to each other, returns and promotions say, whose rows get paired up although they never belonged together.

The platform handles this instead of expecting the analyst to know about it.

What the compiler does about it

A fact reachable only through a shared dimension is marked secondary and kept out of the model's default queryable slice, so it cannot be dragged into a query by accident.

Fan-out is detected while the plan is built. When a one-to-many edge would otherwise double-count a measure, the aggregate is marked symmetric: each fact is aggregated at its own grain first and only then rolled up. Sales are summed over sales rows, stock over stock rows, and the join is no longer in a position to multiply either.

What cannot survive that is refused, not approximated. Only some aggregations re-aggregate losslessly - a sum, a count, a minimum, a maximum. An average, a distinct count or a median does not, and asking for one across a multi-fact join returns a named error rather than a plausible figure. The same applies to a metric written as verbatim SQL, because an opaque expression cannot be re-aggregated safely, and to the bare row count, which has no unambiguous meaning across such a join at all.

Cross-fact ratios are limited identically on the live and the extract path, so none of this reappears when a model is moved between them.

The design decision worth naming: a refusal you can see beats a number you cannot check. The whole trap is that the wrong answer looks exactly like the right one.

The demo dataset that ships with the platform carries a deliberate fan trap on promotions and a chasm trap on returns, so you can watch the behaviour instead of taking it on trust.