{"id":4434,"date":"2026-11-08T09:35:00","date_gmt":"2026-11-08T09:35:00","guid":{"rendered":"https:\/\/dataopsschool.com\/blog\/?p=4434"},"modified":"2026-11-08T09:35:00","modified_gmt":"2026-11-08T09:35:00","slug":"warehouse-vs-source-counts","status":"publish","type":"post","link":"https:\/\/dataopsschool.com\/blog\/warehouse-vs-source-counts\/","title":{"rendered":"How One Data Team Closed the Gap Between Source and Report"},"content":{"rendered":"<p>The analytics team at a mid-sized online grocer, which I will call Larder Lane, had a standing complaint from the sales director: the weekly revenue figure in the dashboard never quite matched the figure in the finance close. Not by much. A little here, a little there. For months everyone shrugged it off as &#8220;timing&#8221;. Then one Thursday the gap was large enough to hold up a board pack, and the data engineers had to work out where it came from. This is the story of what they found, and of the small <strong>data reconciliation routine<\/strong> they adopted afterwards.<\/p>\n<h2>The setup: a pipeline everyone trusted<\/h2>\n<p>Larder Lane&#8217;s pipeline was ordinary. Orders landed in an operational database. A nightly extract copied them to cloud storage. A transformation layer cleaned and joined them into reporting tables. A BI tool read the reporting tables. Tests existed, and they passed every morning. The team felt safe.<\/p>\n<p>What the tests checked, though, was shape: no nulls in key columns, valid status values, unique order identifiers. What nobody checked was whether the count and total value arriving at the end matched the count and total value at the start. The tests asked &#8220;is this data well-formed?&#8221; and never asked &#8220;is this all the data?&#8221;<\/p>\n<h2>Day one: finding the first gap<\/h2>\n<p>The engineer who took the ticket, Tomasz, did the unglamorous thing. He picked one trading day and counted orders in three places: the operational database, the extracted files, and the final reporting table. He did the same for the sum of order value.<\/p>\n<p>The database and the extract agreed. The reporting table was short. A few hundred orders were missing from that single day, and they all shared one property: they had been edited after the nightly extract ran. Customers had changed a delivery slot or removed an item, the database row was updated, but the extract only picked up rows created since the previous run. The edited rows were never refreshed.<\/p>\n<p>That was one gap, with one cause. Fixing the extract to capture updates took an afternoon. The more interesting question was how long the problem had existed and whether anything else was drifting.<\/p>\n<h2>Day two: the other gaps nobody had looked for<\/h2>\n<p>Tomasz repeated the count for two more weeks of history and found three further mismatches, each unrelated to the first:<\/p>\n<ul>\n<li>Refunds were recorded in a separate table that the transformation layer joined with an inner join, quietly dropping refunds for orders it could not match.<\/li>\n<li>A currency conversion step used a rate table that stopped updating after a vendor changed its feed, so foreign orders were valued at stale rates.<\/li>\n<li>Cancelled orders were excluded from revenue in the dashboard but included in the finance extract, which is a definitional difference rather than a bug, yet no document recorded the choice.<\/li>\n<\/ul>\n<p>Notice the variety. One gap was an ingestion flaw, one a join mistake, one a stale reference table, one a missing definition. A single heroic fix would have solved none of them for good. What they needed was a routine that would surface the next one as soon as it appeared.<\/p>\n<h2>The routine they adopted<\/h2>\n<p>The team settled on a short, boring check that runs on a fixed rhythm. Each night an automated job, and each Monday a person, compares expected against actual at every hand-off in the pipeline.<\/p>\n<ol>\n<li><strong>Pick the control totals.<\/strong> For each hand-off choose two or three things that must survive intact: row counts, a sum of money, a count of distinct customers.<\/li>\n<li><strong>Record the &#8220;before&#8221; value at the source.<\/strong> Store it alongside the run, not in someone&#8217;s head.<\/li>\n<li><strong>Record the &#8220;after&#8221; value at each stage.<\/strong> Extract, staging, reporting.<\/li>\n<li><strong>Write the difference down.<\/strong> Zero is a fine answer. Anything else becomes a ticket with an owner.<\/li>\n<li><strong>Explain every non-zero on purpose.<\/strong> If cancelled orders are excluded by design, the check subtracts them and the definition goes into the data dictionary.<\/li>\n<\/ol>\n<p>They built the first version in a spreadsheet, then moved it into the pipeline&#8217;s own test layer once it proved useful. Teams that already use expectation-style tooling can bolt the comparison on easily; the guide to <a href=\"https:\/\/dataopsschool.com\/blog\/great-expectations-for-data-validation-in-dataops-a-practical-guide\/\">writing validation rules with Great Expectations<\/a> shows how those checks are structured, and the walkthrough on <a href=\"https:\/\/dataopsschool.com\/blog\/master-data-transformations-with-dbt-in-modern-dataops-pipelines\/\">building transformation models in dbt<\/a> covers where control totals can live inside the transformation layer.<\/p>\n<h2>What changed in practice<\/h2>\n<p>Within a month the nightly job caught a new gap. A supplier file had arrived with a changed column order, and one stage had silently misread it. The control total for that stage did not match, a ticket opened before breakfast, and the problem was corrected before anyone looked at a dashboard. Under the old arrangement it would have surfaced at month end as another unexplained difference.<\/p>\n<p>The sales director stopped asking about timing. More usefully, the finance lead and the data team started using the same numbers, because every difference had either been closed or documented as a deliberate rule.<\/p>\n<h2>The money side of the same habit<\/h2>\n<p>Larder Lane&#8217;s finance team had a parallel version of this problem on the payments side: orders billed versus settlements received from the card processor and from invoice-based business customers. They moved that matching into <a href=\"https:\/\/paymanagerpro.com\/\">PayManagerPro<\/a>, a product for sending invoices and payment links and for following each settlement through to a match, and it gave the data team a cleaner payments feed to compare against. The two routines now reinforce each other.<\/p>\n<h2>Lessons you can copy this week<\/h2>\n<h3>Match totals, not just shapes<\/h3>\n<p>A table can be perfectly well-formed and still be incomplete. Add at least one count and one sum comparison per hand-off.<\/p>\n<h3>Compare at every boundary<\/h3>\n<p>If you only compare the start and the end, you will know something is wrong but not where. Compare after each stage and the failing stage identifies itself.<\/p>\n<h3>Put definitions on paper<\/h3>\n<p>Many &#8220;gaps&#8221; are two teams using different rules. Write the rule down once, and the difference stops being a mystery.<\/p>\n<h3>Keep the cadence fixed<\/h3>\n<p>Do not run the check only when someone complains. A fixed nightly job plus a weekly human review means differences are found while each has a single recent cause.<\/p>\n<h2>The practical takeaway<\/h2>\n<p>Data records drift apart quietly. Larder Lane&#8217;s weekly revenue mismatch was never one big failure; it was four small ones accumulating unnoticed. A short, regular comparison between what the source says and what the report says would have found each of them in days rather than months. Start with one pipeline, two control totals, and a fixed time slot. Chase every non-zero while it is small, and write down every explanation. That is the whole method.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>A grocer&#8217;s dashboard never matched the finance close. A story of four quiet mismatches and the small control-total routine that now catches them early.<\/p>\n","protected":false},"author":6,"featured_media":4435,"comment_status":"","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[814],"tags":[],"class_list":["post-4434","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-dataops"],"_links":{"self":[{"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/4434","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/comments?post=4434"}],"version-history":[{"count":1,"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/4434\/revisions"}],"predecessor-version":[{"id":4505,"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/4434\/revisions\/4505"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/media\/4435"}],"wp:attachment":[{"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/media?parent=4434"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/categories?post=4434"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dataopsschool.com\/blog\/wp-json\/wp\/v2\/tags?post=4434"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}