We're in the process of building a vNext Data/Reporting Platform for capturing Web & Sales Traffic.

At a very high level we're capturing Impressions, Clicks & Sales Data which then needs to be reported on.

  • Impressions are on the order of Millions per day
  • Clicks: 100,000's per day
  • Sales: 1000's per day

My first pass design for the platform essentially aggregates data into Hourly, Daily, Weekly & Monthly structures. Each being grouped on a number of other dimensions (Client, Brand, Product, Country, Retailer etc...) with Impression Counts, Click Counts, Total Value etc...

So each hour, source data gets aggregated -> Hourly And then each day:

  • the prev 24h of Hourly Data -> New Daily
  • the prev 24h of Hourly Data -> Added to existing Weekly or create new Weekly
  • the prev 24h of Hourly Data -> Added to existing Monthly or create new Monthly

This gives us a lot of flexibility for reporting efficiently as well as monitoring trends and identifying potential problems.

One thing I'm not sure on is how to "Fix" data in the event a "discrepancy" gets into our aggregated data.

e.g. Every once in a while a customer will provide incorrect pricing information. When that occurs, the "Potential Value" on each click and impression will be wrong.

So that source data ends up in the Hourly aggregates.

If unnoticed, for even one day, that hourly data pollutes the daily, weekly & monthly aggregates. And because those aggregates are being built and added to cumulatively, you can't easily undo it. (You can't pour the blue water out of the jug of purple water).

The naive solution here would seem to be to throw away the subset of aggregated data that's been polluted, fix the source data, and then regenerate each of those aggregates that's been removed. Which one day later might mean throwing away 24 Hourly's, 1 Daily, 1 Weekly & 1 Monthly.

Or is there another approach here that I'm not thinking of?

  • We store monitoring data in a similar fashion aggregating by hour, day, month. We discarded weekly as not very useful. Main difference to you - we store original detailed non-aggregated data for at least 7 days. This detailed table has ~150M rows - not too much, 20M per day. If some monitoring data is delayed by less than 7 days, having original detailed data allows to easily and accurately recalculate all affected summaries. So, based on your expected timeframes for possible incorrect data, keep a buffer of original details. This buffer is your "undo" buffer . – Vladimir Baranov Oct 20 '17 at 13:24

In accounting practice values are never altered once written. Instead secondary compensating transactions are created. In your circumstances this would mean one new row with a value that is the negative old, incorrect value and a second new row with the new, correct value.

To work each data row will need its own datetime column so these compensating transactions can be post-processed into the correct buckets.

Averages cannot be altered in post processing. Instead the sum and count are stored and average calculated at run time.

  • Thanks Michael. It is something I'd considered, but discounted because of the need to go back and re-process historic periods... As you said, I'd need 2 dates. the date the event occured (and aggregation bucket it belongs to) and the date the data was added so that accountany-corrections which are added retrospectively could be Id'd and processed afterwards. That works at the source->hourly aggregation... but would break at the hourly->other aggregates level, unless I was to apply the fixes directly from source to each of the aggregations. cheers for the suggestion. will suss it out – Eoin Campbell Oct 23 '17 at 8:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.