Every report shows a different number: metric definitions, source of truth and a reporting layer

When three systems answer the same question three different ways, the first explanation people reach for is usually the wrong one: the data is not disappearing. If August shows 1,284 orders in the store admin, 1,301 in the ERP and 1,259 in accounting, all three are probably correct on their own terms, because all three are counting different things with the same word. Consistent reporting rests on four things: every metric has a written definition and one owner, every field has one system of record, reports are built on a recomputable layer instead of operational tables, and a reconciliation check runs every day without anyone asking. That order matters. Changing infrastructure before you fix definitions hides the gap rather than closing it.

"Revenue" is not one number

Shopify's own reports are the fastest way to see the problem. Gross sales is unit price times quantity before discounts and returns, excluding tax and shipping. Net sales is gross sales minus discounts and returns. Total sales takes net sales and adds tax, shipping and fees back on. Three numbers, one screen, all correct. A refund reduces net sales in the standard reports but leaves gross sales untouched.

Then comes the second question: which day does it belong to? If an order placed in June is refunded in July, do you reduce June's revenue or post a negative line in July? Both are defensible, but one matches the accounting ledger and the other matches the marketing dashboard. If nobody wrote the choice down, two teams will read the same data differently at month end and the argument will restart every month.

Do not bury the definition inside the dashboard

Larger data teams keep a separate layer for exactly this. MetricFlow, which powers the dbt Semantic Layer, holds metric definitions in version-controlled YAML with lineage attached, so dashboards ask the layer for the definition instead of carrying their own copy of the formula. MetricFlow was open sourced under Apache 2.0 in late 2025. Cube and Looker's LookML solve the same problem from different angles.

Being a small team is not a reason to skip the idea, because the real issue is not tooling, it is how many copies of the definition exist. Keep the definition in one SQL view or one model and point every dashboard at it. The moment a formula lives inside a dashboard it starts to multiply: six months later the same revenue formula sits in six dashboards, five get updated, one does not, and the stale one is usually the board deck. A metric dictionary does not have to be elaborate either. One row per metric with the name, a plain-language definition, the formula, which records are excluded, which date field it groups by, the time zone, the refresh cadence and the owner's name is enough.

Which date are you actually asking about?

An order does not have one date. Created, payment authorized, invoiced, shipped and delivered are separate fields, and the same order can land on five different days depending on which one you pick. Accounting looks at the invoice date, marketing at the order date, operations at the ship date, finance at the day the money hit the bank. All four are right for their own purpose.

Practical rule: every report declares one field as its event date, and that declaration sits next to the report title. Other dates can be columns, just not the grouping key. Before comparing two reports, the first question is not which metric they measure but which date field they group by. In practice most of the gap shows up right there.

Your day may start at 3 a.m.

Turkey has stayed on UTC+3 year-round since the September 7, 2016 decision, with no daylight saving switch. A fixed offset makes life easier, but it comes with one trap: if your server groups timestamps in UTC, the range the report calls "yesterday" actually runs from 3 a.m. to 3 a.m. local time. Orders placed between midnight and 3 a.m. get counted on the previous day. On the last night of a campaign, that alone is enough to make the report disputed.

Google Analytics 4 has a well-known version of the same issue. In GA4, event_date is expressed in the property's time zone while event_timestamp is UTC, so querying the BigQuery export without converting compares a different 24-hour window than the interface does, and daily totals never line up. The clean setup is to store in UTC, convert in exactly one place in the reporting layer, and print the time zone on the dashboard. If you sell into the EU, remember the other side still shifts clocks, so daily totals drift in March and October.

One system of record per field

Where is stock quantity true, in the ERP or the storefront? Is the customer's current address in the CRM or on the order record? Those answers belong in writing, field by field. If two systems can write the same field independently, you do not have a reporting problem, you have a data ownership problem, and no reporting layer will fix it. How to wire the flow between systems, including ordering and delivery guarantees, is covered in the ERP and e-commerce data sync article.

Stop running report queries on the production database

A query that breaks a month down by dimension scans millions of rows, takes a while, and if the same database is serving customer traffic, the slowdown shows up on the site. The standard answer is a read replica, but a replica makes no promise about freshness. In PostgreSQL you can measure the delay with now() - pg_last_xact_replay_timestamp(), though that reading looks worse than reality during quiet periods when the primary commits nothing, so comparing LSN positions with pg_wal_lsn_diff is the more reliable signal.

The reporting consequence is concrete: a user who saves an order and immediately opens a replica-backed dashboard may not see it and will conclude the report is broken. The fix is not chasing zero lag, it is stating the expectation. A single line in the corner of the dashboard saying "data as of 09:15" removes a surprising share of the questions you would otherwise field all month. Making the query itself faster is a separate topic; for the index and query-plan side, see the slow database article.

Why last month's report changed

This question almost always has one root cause: the report is retelling the past with today's data. Flip a customer from individual to corporate and their historical orders look corporate too, so June's corporate revenue grows retroactively. Update a price list and, if old orders recompute against current prices, the same thing happens.

Two habits end it. First, copy the values that applied at the time onto the transaction line: unit price, discount applied, tax rate and exchange rate belong on the order line, not looked up from a current table. Second, version the attributes that change. The SCD Type 2 pattern from warehouse modeling does precisely this: when a customer's segment changes you do not update the row, you insert a new one, and each row carries a validity start, an end and a current-record flag. Reports join to the row that was valid on the event date, and history stops moving.

Late-arriving data: pick a policy

Refunds get entered twenty days later, bank statements arrive two days later, delivery status settles after four. So yesterday's number will be different today, next week and at month end. There are two legitimate policies here, and until you choose one, everyone assumes their own.

The first is rolling recomputation: the report rebuilds the last 30 days every night, historical numbers can move, and that behaviour is documented on the dashboard. The second is period close: once a month closes its numbers are frozen, and corrections that arrive later are posted as separate lines in the current period. That is what accounting has done for decades and it is usually the healthier choice for management reporting, because a number that has been presented never changes afterwards. Write down which one you picked. The actual mistake is running both policies side by side in the same company.

A load job that runs twice should not double your rows

Nightly jobs that feed reporting tables get triggered manually, die halfway and get retried. So running the job twice for the same day must not corrupt anything: delete and rewrite that day's partition in the target table, or upsert on a natural key, rather than appending rows. A half-finished load should not leave a half-finished day either, and writing into a staging table and swapping it in at the end is the cheapest protection there is. The full mechanics of retries and idempotency are in the background jobs and queues article.

Reconciliation is not a human's job

Having an accountant notice the gap at month end is the most expensive detection method available. Put an automated check on a daily schedule and keep the order right: compare row counts at the same grain first, then totals. Comparing only totals and finding them equal is misleading, because two errors in opposite directions cancel each other out. Alerts above the threshold should go to a channel, not to one person's inbox.

If you run dbt there is plenty off the shelf: the unique, not_null, relationships and accepted_values generic tests, equal_rowcount from dbt-utils, and source freshness checks. When you rewrite a report or move one off a legacy system, the compare_queries macro in dbt-audit-helper does a row-by-row comparison of two queries and returns a summary of how many rows are unique to each side and how many are identical. That is the package's whole reason for existing: proving a rebuilt model produces the same output as the original. Without dbt, two queries, a diff and an alert do the same job. What matters is that it runs by itself every day.

Access control belongs in the reporting layer too

A reporting copy does not carry your application's permission rules with it. A sales rep who only sees their own accounts in the app may be able to download the entire customer list from the BI tool. One spreadsheet export walks straight through an authorization model that took months to build. Define separate roles for the reporting environment, apply row-level restrictions where the tooling supports it, and log exports. For building the underlying model, the roles, RBAC and ABAC article covers the ground.

When you actually need a warehouse

Do not skip steps. For most companies the right first move is a separate reporting schema on a read replica plus a few materialized views. One PostgreSQL detail worth knowing: REFRESH MATERIALIZED VIEW CONCURRENTLY requires a unique index on the view that covers every row and contains no expressions and no WHERE clause. Without that index the refresh locks the view, and the dashboard stalls for exactly as long as the refresh takes.

Tie the warehouse decision to four signals: more than three or four source systems, a genuine need to version history, raw event volume the operational database cannot carry, and analysts who need to write SQL without waiting on a developer. If none of those apply, a warehouse creates more work than it removes. And know this going in: a warehouse does not solve the definition problem, it relocates it. The first output of a warehouse built before the definitions were written is a fourth different number on the dashboard.

The first step that fits this week

Pick the three most argued-about numbers. For each one, write on a single page the formula, which date field it groups by, the time zone, which records are excluded (test orders, cancellations, internal accounts) and who owns it. Then pull the same period from both systems at the same grain, ideally down to day and order ID, and list the differences as rows.

Sort the first twenty differences into three buckets: definition mismatch, date-boundary mismatch, actual missing data. In practice the first two buckets account for most of the gap, and they get resolved with one page of documentation and a query fix rather than a development project. The handful of rows left in the third bucket are real bugs and belong on the integration side. Projects that start without making this distinction spend most of their budget hunting a data loss that was never there.