Data Consistency Across Reports

Why this matters

As a BI Analyst, stakeholders expect the same metric to match across dashboards, finance reports, and ad-hoc analyses. Inconsistency erodes trust and wastes time. You will often be asked to explain why yesterday's revenue in the Executive Dashboard differs from the Finance P&L or why customer counts do not match between Marketing and Product reports. Mastering consistency means faster decisions and fewer escalations.

• Real task: reconcile KPI mismatches across dashboards before a leadership meeting.
• Real task: define and document a single source-of-truth for key metrics.
• Real task: prevent double counting from joins or pre-aggregations.

Concept explained simply

Data consistency across reports means the same metric yields the same value for the same business question, timeframe, and filters—no matter which report you open.

Mental model: the Metric Contract

Think of every metric as having a contract. If two reports obey the same contract, they match.

• Definition: exact formula and inclusions/exclusions.
• Scope: source tables/views and data freshness.
• Time: timezone, calendar (fiscal vs calendar), time grain.
• Filters: default filters, segments, status flags.
• Dimensions: join keys, slowly changing dimensions behavior.

Contract match = consistent numbers.

Common causes of inconsistencies

• Different formulas (gross vs net, handling of refunds, cancellations, taxes, fees).
• Different sources (orders vs invoices; live vs snapshot; pre-aggregated marts).
• Join duplications (many-to-many joins; non-unique dimension keys).
• Different filters (status IN/OUT, cohorts, geos, product lines).
• Time differences (UTC vs local; fiscal vs calendar; month-end boundary).
• Time grain mismatch (daily vs monthly aggregation first).
• Currency conversion timing (rate date mismatch or missing FX table join).
• Late-arriving data or partial refresh windows.
• Slowly changing dimensions (current vs as-of attributes).

Worked examples

WITH promo_one AS (
  SELECT order_id FROM promotions GROUP BY order_id
)
SELECT SUM(o.amount)
FROM orders o
LEFT JOIN promo_one p ON p.order_id = o.order_id;

Practical checklist

• [ ] Does every report use the same metric definition and formula?
• [ ] Are source tables and data freshness identical or explicitly aligned?
• [ ] Are timezone and calendar rules consistent?
• [ ] Is time grain (daily/weekly/monthly) aligned before aggregation?
• [ ] Are default filters the same and visible?
• [ ] Do joins preserve row-level uniqueness? Check keys.
• [ ] Are currency conversions using the same rate date and table?
• [ ] For point-in-time metrics, is there an as-of snapshot rule?

How to self-check

1. Run a small-scope reconciliation: pick one week and one segment. Numbers should match there first.
2. Trace lineage: list each transformation from source to report for both reports. Compare step-by-step.
3. Create a "side-by-side" query that computes both methods in the same SQL to isolate differences fast.

WITH a AS (
  -- Method A
  SELECT DATE_TRUNC('day', created_at) d, SUM(amount) revenue
  FROM orders
  WHERE status IN ('paid','shipped')
  GROUP BY 1
),
b AS (
  -- Method B
  SELECT DATE_TRUNC('day', posted_at) d, SUM(net_amount_usd) revenue
  FROM invoices
  WHERE invoice_status = 'posted'
  GROUP BY 1
)
SELECT coalesce(a.d,b.d) d, a.revenue rev_a, b.revenue rev_b, (coalesce(a.revenue,0)-coalesce(b.revenue,0)) diff
FROM a
FULL OUTER JOIN b USING (d)
ORDER BY d;

Exercises

Do these hands-on tasks. Then compare with the provided solutions.

Exercise 1 — Reconciling revenue across Orders vs Invoices

You have last month numbers:

• Sales Dashboard (Report A): 1,200,000 using orders.amount on order_created_at (UTC), status IN ('paid','shipped'). Refunds not subtracted.
• Finance P&L (Report B): 1,150,000 using invoices.net_amount_usd on invoice_posted_at (local time), invoice_status = 'posted', excludes marketplace orders, uses daily FX.
• Operational facts: 200 refunded orders totaling 50,000; 120 late invoices posted on the 1st of this month totaling 30,000; marketplace orders last month totaled 15,000.

Task: Identify likely causes for the 50,000 difference and propose a reconciliation plan. Write:

• A numbered list of root causes.
• A short plan to align the reports.

-- Example alignment using invoices (net, posted, exclude marketplace)
SELECT DATE_TRUNC('month', posted_at AT TIME ZONE 'UTC') m,
       SUM(net_amount_usd) revenue
FROM invoices
WHERE invoice_status='posted'
  AND is_marketplace = false
GROUP BY 1;

Exercise 2 — Active customers definition alignment

Two reports:

• Report A (Product): Active customers = users with an order in the last 90 days. Timezone: UTC. Excludes test accounts.
• Report B (Growth): Active customers = users with subscription_status='active' as of month-end snapshot. Timezone: local. Corporate accounts can have multiple child users.
• Numbers for last month-end: A=12,400; B=11,950.

Task: List the top 3 drivers of mismatch and propose a single standard definition for "Active customers" and how to implement it. Include:

• Your chosen definition
• Time basis (snapshot vs rolling)
• Deduplication or hierarchy handling

-- Use month-end subscription snapshot and count distinct billing accounts
SELECT month_end,
       COUNT(DISTINCT billing_account_id) AS active_customers
FROM subscription_snapshot
WHERE as_of_ts = month_end
  AND subscription_status='active'
GROUP BY 1;

Common mistakes

• Assuming matching names mean matching definitions. Always verify the contract.
• Aggregating after a many-to-many join. Ensure uniqueness first.
• Ignoring calendar and timezone differences when comparing months.
• Mixing gross and net values.
• Using different currency rate dates across reports.
• Comparing live tables to snapshots without an as-of rule.

Mini challenge

Pick one key metric in your org. In a single paragraph, write its Metric Contract: definition, source, time, filters, and dimensions. Then, list two reports that must use it and note what you need to change for alignment. Keep it short but precise.

Who this is for

• BI Analysts ensuring trusted dashboards.
• Analytics Engineers building semantic layers.
• Data Scientists validating experiment metrics.

Prerequisites

• Comfort with SQL joins and aggregations.
• Basic understanding of data models (facts/dimensions).
• Familiarity with reporting tools and filters.

Learning path

1. Master metric contracts and documentation.
2. Learn reconciliation techniques (side-by-side SQL, sampling).
3. Standardize time and currency logic.
4. Harden joins and uniqueness guarantees.
5. Automate consistency checks in your BI layer.

Practical projects

• Create a metric contract doc for the top 5 KPIs and embed it in dashboard headers.
• Build a reconciliation query that compares two revenue pipelines daily and flags variance > 1%.
• Add a semantic layer view that enforces timezone and calendar consistently.

Next steps

• Adopt a naming convention for metrics and snapshots.
• Schedule periodic variance checks.
• Educate stakeholders on definitions via short notes in dashboards.

Quick test

The quick test is available to everyone; only logged-in users will have progress saved.