Analytics Engineers: We've been Modelling the Wrong Thing

You're asked to investigate one number, then disappear into a rabbit hole of lineage and SQL you didn't write. Inherited models look tidy from far away and tangled up close. A metric disagrees with another system; you trace upstream logic and find three competing filters, two historical workarounds, and one critical assumption in a Slack thread from last year.

You can eventually explain the number.

The problem is what happens next: almost none of that understanding becomes durable.

I don't think this is a tooling failure in the narrow sense. dbt, semantic layers, and BI made analytics dramatically more reliable and collaborative.

I've started to think it's an abstraction failure.

We've become very good at modelling transformations and metric definitions. But the "model" we ship is often shallow relative to the work that produced it.

The depth lives elsewhere:

in the heads of the people who negotiated the tradeoffs
in Slack/Jira threads that don't age well
in one-off comments nobody sees until something breaks

So yes, we model data. But too often we fail to model the context that makes those outputs trustworthy.

The gap that shows up in real analytics engineering work

This rarely arrives as a request for "better data modelling." It arrives as frustration: Why don't these numbers match? Is this filter intentional? Who owns this metric? Can we change it without breaking finance?

Underneath those questions is usually the same problem: the transformation logic exists, but the reasoning around it does not.

This is the daily pain of inherited analytics work.

In mature projects, a lot of logic is technically valid but context-poor:

Why this source instead of the obvious one?
Why this exclusion filter?
Why this grain compromise?
Which edge case was intentionally accepted?
Who approved the tradeoff, and for what use case?

You can usually read the SQL. You often can't replay the reasoning that produced it.

So teams do one of three things:

Keep legacy logic because touching it feels risky.
Rewrite logic without understanding prior constraints.
Fork definitions quietly across teams until trust erodes.

That is why inherited transformations can be so frustrating in practice. The problem is not only that the SQL is complex. It's that business rules and tradeoffs are encoded into transformations without the decisions that explain them.

And when the published definition doesn't work for a team, the system often doesn't capture what happens next. Someone writes custom SQL. Someone adds a dashboard-level calculation. Someone keeps a spreadsheet adjustment alive because it matches how their team actually works.

The workaround may be reasonable. The problem is that it stays outside the model, so the next person has to rediscover the same gap.

Even fixing a definition "properly" can mean changing several official places: fact logic, a wide table, the governed semantic metric, and perhaps a customer classification built from it. Update three out of four and the organisation has not fixed the measure. It has created another reason not to trust any of them.

dbt's newer semantic models are interesting because they seem to recognise part of this problem. They introduce entities, dimensions, and metrics as a graph above the transformation layer, rather than asking every consumer to interpret tables independently. That is a meaningful abstraction, not just better documentation.

But the direction still matters. The semantic model describes a dbt model that has already been built. The relation remains the anchor; meaning is added above it so metrics can be queried consistently.

I wonder whether the stronger inversion is to make that meaning the primary artefact, then derive the fact table, wide table, metric query, and customer classification from it. Those physical shapes still matter. They just stop being separate places where the organisation has to maintain the same understanding.

That may be too ambitious, or the wrong abstraction entirely. But it feels like the question underneath the tooling: should semantics describe the models we build, or should the models we build be projections of the semantics we have agreed?

A familiar metric pattern

A few years ago I was trying to calculate average call handling time and noticed the metric appeared to include time the customer spent waiting in the queue before an agent picked up.

That might be a valid operational metric, but it wasn't the metric I needed for analysing agent handling time.

I checked with the steward. They were surprised too. We agreed the existing metric was doing something useful, but its name made it easy to misuse. So we created a clearer metric for agent handling time and re-named the existing one.

That is the real pattern: not one "correct" definition replacing a bad one, but a definition being clarified, forked, renamed, and governed over time.

MAU (monthly active users) seems like there should surely be only one way to calculate it. Product, finance, and ops may each need subtly different versions. Each can be valid; product are looking for signal so maybe they check for some minimum level of activity, while finance want to exclude those on a trial. The failure mode is pretending one definition must serve all purposes, or redefining silently without preserving why.

A good system should make this explicit: purpose, owner, approval, alternatives considered, unpublished workarounds, and fork history.

The shift I think we need

I wonder if the next abstraction is not another place to put metrics, but a better way to preserve the reasoning behind them.

The primary artefact would carry both structure and context:

what something means (entities, grain, relationships, metrics)
why it was defined that way (rationale, approvals, tradeoffs)
how it evolved (draft, published, forked, superseded)

SQL would still matter. It would remain the execution language analysts inspect and trust.

But SQL would no longer have to be the only place where business meaning is encoded.

What this changes for AEs

It doesn't make analytics engineers less important. It makes their highest-value work durable.

Instead of being the only people who can decode inherited SQL, AEs become stewards of a model that preserves both computation and intent.

That means less archaeology, fewer accidental redefinitions, and better reuse.

There is a bigger implication here, but I think it deserves its own essay. Most analytics models are built for descriptive analytics and scheduled reporting. But what if we built a richer kind of model — one that captured not only transformations, joins, and metrics, but also relationships, constraints, dynamics?

That starts to look useful beyond BI: operational workflows, machine learning, optimisation, agents with enough context to reason across complex domains.

Maybe. But the first step is simpler: stop losing the reasoning behind the models we already have.

What this is not

Not "dbt was wrong."
Not "semantic layers failed."

Those tools solved real problems in their era.

This is about the next layer: making organisational understanding explicit enough to govern, evolve, and reuse — instead of leaving it in people's heads and reconstructing it every quarter.

A simple test

Pick one critical metric in your organisation and ask:

Can we explain why it is defined this way, not just how?
Can we show who approved that definition and for which purpose?
Can we see the data limitations, tradeoffs, and edge cases already investigated?
Can we tell where the metric has worked, where it hasn't, and why?
Can we support a different legitimate purpose without silent redefinition?
Is the semantic definition primary, or is it still an annotation on a table someone must keep aligned?

If not, the bottleneck is no longer SQL productivity.

It's model memory.

I'm exploring this problem space.

If you've inherited a model where the logic runs but the reasoning is lost, I'd value comparing notes.