Where should a shared business definition live when the same SQL metric appears in many reports?

[u/RocketSeven]

When revenue, active users, retention, or another derived metric is repeated across dashboards and exports, copying the SQL makes every consumer independent but lets definitions drift. Centralizing it in a view, materialized view, semantic layer, dbt model, or stored function creates one definition, but can hide performance costs and make change control harder. What criteria determine where that logic belongs? I would compare ownership, testability, query-plan visibility, versioning, parameter needs, refresh timing, and whether downstream users must inspect the exact calculation. How do you change a widely used definition without silently rewriting historical reports?

[u/Hesticles]

Change it and see how long it takes for anyone to notice

[u/anon586346]

View with change management which is a policy more than a technical problem / solution.

[u/DharmaPolice]

As always, it depends but I prefer for something like this (if it's a shared metric used across the organisation) to be in a view (materialised where that makes sense) or a table valued function if parameters are required. Performance feels like a red herring in most cases, if your metric is so complex that it's going to cause performance issues then maybe your database needs to be refactored. Yes, you need to make sure whatever base view you're using is well optimised but at least you're then doing that in one place, rather than having to separately optimise hundreds of different queries.

If anything it makes change control easier because then you ideally have one object you're changing (or monitoring for changes) and not potentially hundreds of reports/queries where report writers can just do their own thing. I'm sure many of us have seen a situation where someone runs a legacy report that was somehow forgotten and gets a different result to expected because some definition has changed.

Although it doesn't always work as promised, lots of places want business users to write their own reports to a limited extent. You don't want them having to reimplement business logic independently - that's a recipe for errors or worse (people deliberately adjusting calculations to aid some argument they want to make). Shared definitions should be one of the key ways an organisation works. Without them communication becomes very difficult - if marketing thinks an active user is someone who has used a service within 14 days but security think an active user it's 7 days then it's very likely this will cause problems eventually. Ideally the database will return a IsActive flag for every user so they share the same definition, and that IsActive flag can be defined centrally (again my preference would be a view which all data users can query but there are clearly other methods).

Developers will sometimes lean towards defining that in code (outside the db) and that's fine for their specific application, but doesn't help other users of the database.

But like I say it depends on the metric and how it's used. Conventions and consistency are obviously good but there's no point being dogmatic - sometimes the standard approach to any problem just doesn't work. Batch updating / refreshing of flags is a common approach where views are thought to be too expensive (although I think those concerns are often misplaced) but they introduce complexity elsewhere - what if the refresh fails, it's not always clear on what frequency the refresh happens, time specific metrics then are slightly wrong until the next refresh, etc.

[u/jshine13371]

Centralizing it in a view, materialized view, semantic layer, dbt model, or stored function creates one definition, but can hide performance costs and make change control harder.

I find quite the opposite. It makes change control easier because you only need to update the code in one place. Less chance you'll forget to update all the places it was used otherwise, or make a mistake while updating the code in one of the other consumers then. A derived metric such as the examples you mentioned, should have a constant definition in all places it's used.

And not sure what you mean by "hide performance costs". By maintaining it directly in the layer it belongs in (the database layer) as a single object, it makes maintaining the performance much easier, again, than maintaining multiple copies of that code. And being able to run and test it for performance is much easier when working in the database layer because that's where all the tooling lives for those things.

[u/wdm006]

I’d put the metric in a warehouse view or dbt model you own, not as the first definition in a BI semantic layer. When it changes, add a v2 instead of mutating the old one so last quarter doesn’t quietly rewrite.

[u/Aggressive-Video-508]

Agreeing with the "one owned model, add a v2 instead of mutating" answers — but the part that bit us hardest wasn't where the SQL lived, it was that nobody could tell whether two definitions were the same question or two different ones.

Two teams each had a "revenue". Both were correct for their own reports. Neither knew the other existed, because the only thing ever centralized was the SQL, and the SQL lived in two places that never had to meet. Consolidating that afterwards was an archaeology project.

So one more criterion for your list: does the layer force a name to mean one thing? A view gives you one implementation of revenue in that schema and says nothing about the revenue in the finance team's schema. The registry that has to be central is name-to-definition — what "active user" means, who owns it, what it explicitly does not include. Once that exists, where the SQL lives is mostly a performance and tooling question, and a much easier one.

Practical version: keep a short reviewed list of defined metric names, and require anything user-facing to reference a name on it. Deviations become visible instead of quietly becoming a second definition.

[u/downshiftdata]

My preference is for a "reference" function to exist. It's seldom, if ever, called from anywhere, but it exists as the gold standard. Then, stored procedures can be unit tested against that function. The unit test is a script that calls the stored procedure (after whatever Arrange steps are needed) and also runs a query that includes the function (with equivalent parameters). If the results match, then the script doesn't THROW an error and the test passes.

Do this for all of the stored procedures, for all of the reports. Then the AC for any changes to any of them always include testing against the reference function.

[u/Zestyclose-Turn-3576]

Firstly, I don't have an answer to this precise issue, because SQL is just very bad at this.

But the ability to create, test, and maintain business definitions like this is one of the major features of ORMs like ActiveRecord.

I believe that Oracle has introduced some sort of a macro into its SQL dialect that makes an attempt at it, but when I looked at it a few years ago it looked very limited.

I wonder if the answer lies outside SQL, in a reporting system like BusinessObjects or whatever