One KPI, Five Answers:
A SQL Business Logic Governance Framework
When Finance, Sales and Operations each calculate the same KPI differently, the problem is not merely a formula. It is an ownership, definition and controlled-change problem.
24 October 2026 · SQL · Reporting governance
Approved lineage · Illustrative example
- Source
- SQL logic
- Report
Illustrative example. The contract links the business definition, technical object, test evidence, owner, consumers and effective date.
The hidden operating problem
Different numbers are usually rational—locally
Finance may recognise revenue when an invoice is posted. Sales may include signed orders. Operations may subtract cancellations using the latest status. Each answer can be internally consistent while still being unsuitable for an executive comparison.
The real failure occurs when those local definitions are unnamed, embedded in separate queries and presented under the same label. Meetings then become formula investigations. Teams lose time, confidence and the ability to explain historic decisions.
The governance goal is not one calculation for every purpose. It is one approved definition for each named purpose—and an explicit label when a legitimate alternative is required.
The metric contract
Define the business promise before the SQL object
A governed metric needs enough information for a business user, analyst and engineer to reach the same interpretation.
- Name and purpose Exact label, decision supported and uses that are out of scope.
- Grain and timing Transaction, customer, month or snapshot; event date and reporting cut-off.
- Rules and exclusions Statuses, cancellations, tax, intercompany, currency and late adjustments.
- Ownership Business approver, technical maintainer, deputies and review rhythm.
- Tests Known examples, reconciliation tolerance and regression checks.
- Implementation Approved schema, view, model measure or service and its dependencies.
- Consumers Reports, exports, applications and teams relying on the contract.
- Effective history Approval date, version, change reason and treatment of prior periods.
Where logic should live
Place each rule at the lowest stable reusable layer
| Location | Use when | Watch for |
|---|---|---|
| Source application | The rule controls the transaction itself and must apply before data is committed. | Changing operational behaviour merely to simplify reporting. |
| Curated SQL layer | Cleaning, conformance, deduplication or reusable row-level logic serves several consumers. | Mixing raw-source assumptions with executive definitions in one opaque object. |
| SQL metric view | A stable aggregation or classification should be reused across controlled consumers. | Treating a view as governance without an owner, contract and tests. |
| Semantic model | The measure depends on filter context, reporting relationships or analytical calculation behaviour. | Recreating the measure separately in every model. |
| Report | The calculation is genuinely local presentation logic with no reuse or enterprise meaning. | A local formula quietly becoming the de facto corporate KPI. |
Microsoft defines a SQL view as a virtual table whose rows and columns are defined by a query. Views can simplify a user’s perception of a database and provide an interface over changing schemas. Those capabilities make views useful implementation tools, but approval, meaning and ownership remain organisational decisions. [1]
Best practice
Separate source truth, conformed truth, metric truth and presentation
This layered pattern keeps evidence visible and makes change impact easier to assess.
Source
Preserve the operational facts, keys, timestamps and source status with minimal reinterpretation.
Conformed
Resolve reference values, duplicates, legal entities, currencies and agreed record-level quality rules.
Metric
Apply the approved grain, time basis, inclusions and exclusions under a stable governed name.
Presentation
Apply labels, visual comparisons and local formatting without redefining the underlying measure.
CREATE OR ALTER VIEW reporting.vw_monthly_revenue
AS
SELECT
EOMONTH(PostDate) AS ReportingMonth,
LegalEntityKey,
SUM(NetAmountGBP) AS RevenueGBP
FROM curated.InvoiceLine
WHERE InvoiceStatus = 'Posted'
AND IsIntercompany = 0
GROUP BY EOMONTH(PostDate), LegalEntityKey;
Illustrative SQL only. The correct statuses, time basis, currency treatment, grain and exclusions must come from the approved metric contract and real schema.
Failure modes
Why “centralised SQL” can still produce chaos
Duplicate approved-looking views
Several objects use credible names such as revenue_final and revenue_v2. Consumers cannot tell which one is authorised.
Undocumented exclusions
A filter removes test customers, intercompany entries or late adjustments without the business owner approving that treatment.
Historic numbers rewritten
A definition changes in place and every prior period recalculates, leaving the organisation unable to explain a previously published result.
Platform evidence
Use SQL capabilities for the job they actually do
Documentation metadata
SQL Server extended properties can be attached to supported database objects at database, schema and lower object levels. They can hold useful metadata such as a description or owner reference, but a searchable metric register should remain the primary business catalogue. [2]
Performance evidence
Query Store captures query, plan and runtime-statistics history to support performance investigation. It can show that an implementation regressed; it cannot decide whether the business definition is correct. [3]
Change history
System-versioned temporal tables can retain previous row versions and support point-in-time analysis. They may suit a structured metric registry where definition records change over time, subject to retention, storage and operating requirements. [4]
Dependency evidence
Maintain an inventory linking technical objects to reports and owners. Platform dependencies help, but declared consumers are also needed for extracts, spreadsheets and manual processes outside the database.
Operating model
Make metric change a small controlled release
Propose and assess
Record the decision problem, requested definition and affected consumers. Compare the proposed result with the current version across representative periods.
Approve and implement
The business owner approves meaning and effective date. The technical owner implements in a controlled object, adds tests and records the version.
Publish and monitor
Notify report owners, migrate consumers, reconcile the release and retire superseded objects only when remaining dependencies are understood.
Do not ask SQL developers to arbitrate unresolved policy. “Should cancelled orders count?” is a business decision. Engineering can expose the consequences, implement the approved answer and prevent accidental divergence.
Ownership and measures
Govern the definition, operate the implementation
Business metric owner
Approves purpose, inclusions, exclusions, materiality and effective date. Resolves interpretation conflicts and sponsors consumer migration.
Technical metric owner
Maintains SQL objects, tests, lineage, deployment, performance and incident evidence. Escalates ambiguity rather than encoding an assumption.
Track measures that expose control quality: percentage of priority reports using approved metrics, duplicate definitions discovered, reconciliation variance, unowned metrics, consumers on retired versions, failed regression tests, average change lead time and incidents caused by definition drift.
Interactive governance review
Can your organisation defend one important KPI?
Score each area as missing, documented or evidenced. This is a discussion aid, not an audit opinion.
Frequently asked questions
SQL business logic governance FAQs
Should every KPI be calculated in a SQL view?
No. A view is useful when logic is stable, reusable and appropriately located in the database, but some measures belong in a governed semantic model or application layer. The decision should consider reuse, grain, security, performance, change ownership and the consumers that need the metric.
Does SQL Query Store provide KPI governance?
No. Query Store captures query, plan and runtime-statistics history for performance analysis. That evidence can help operate the technical implementation, but it does not define what revenue means, approve business exclusions or identify the accountable business owner.
Who should own a governed business metric?
A named business owner should approve the meaning, permitted uses and material changes. A technical owner should implement, test and operate the SQL logic. Report owners remain responsible for using the approved metric correctly and explaining local presentation choices.
How should a business change an established KPI definition?
Treat the change as a controlled release: record the reason, assess affected reports, compare old and new results, obtain business approval, publish an effective date, update documentation and tests, notify consumers and retain enough history to explain previous figures.
Sources and scope
Verified SQL capabilities, Smart Statistics framework
Microsoft documentation was checked on 24 October 2026. Product applicability varies across SQL Server, Azure SQL, Microsoft Fabric SQL and Azure Synapse, so confirm the documentation for the actual platform and version. The revenue scenario, dashboard values and governance assessment are illustrative examples. The metric-contract and operating-model recommendations are Smart Statistics guidance.
- [1] Microsoft Learn: CREATE VIEW — view purpose, syntax, schema binding and dependencies.
- [2] Microsoft Learn: sp_addextendedproperty — metadata at supported database-object levels.
- [3] Microsoft Learn: Monitor performance with Query Store — query, plan and runtime-statistics history.
- [4] Microsoft Learn: Temporal tables — row-version history and point-in-time analysis.