In this lesson, you will learn how to establish a "canonical source of truth" for your metrics to ensure that every stakeholder in your organization calculates business performance using the exact same logic.
The Problem of "Metric Drift"
In large analytical environments, it is common for different departments to define "Total Revenue" in conflicting ways. The Sales team might exclude taxes, while the Finance team includes them, and the Marketing team might attribute revenue to the date of lead generation rather than the date of the transaction. When these definitions live only in individual SQL scripts or BI dashboards, "metric drift" occurs, leading to fractured reporting and loss of trust in your data platform.
The Canonical Source of Truth
To prevent this, you must treat your analytical definitions as core documentation. A canonical definition isn't just a comment in your code; it is a centralized contract that dictates how a metric is derived from your Fact tables.
If you are using a tool like dbt or a centralized SQL view layer, your documentation should live alongside your transformations. Instead of repeating logic, you define the calculation once and point all stakeholders to that specific layer:
-- Metric: Net_Revenue_USD
-- Definition: Sum of transaction_amount minus tax_amount,
-- where transaction_status = 'Completed'.
-- Source: fact_sales_transactions
CREATE VIEW v_canonical_metrics AS
SELECT
date_key,
SUM(transaction_amount - tax_amount) AS net_revenue
FROM fact_sales_transactions
WHERE transaction_status = 'Completed'
GROUP BY date_key;
Documentation as Code
By documenting these definitions in a central repository, you ensure that every query—whether generated by an analyst writing manual SQL or a BI tool rendering a report—references the exact same v_canonical_metrics view. When the business logic changes (for example, a change in tax law), you update the documentation and the underlying SQL view once, automatically propagating the change to every report in the organization. This creates a single, immutable source of truth that survives schema evolutions and team rotations.
Key Takeaway: A canonical source of truth requires that business-facing metrics be defined and documented in a centralized layer to ensure consistent reporting across the entire organization.