1

I pull monthly aggregate data from a SQL database. I store the aggregated data in a separate table in the same database (so I can quickly grab numbers from six months ago, for instance).

My issue is that sometimes I am asked to pull details about the data from six months ago, but in our system the data is super dynamic and if I were to re-calculate my data from six months ago, I'd get different numbers than I did when I originally ran the data (because the underlying data has changed).

Contrived example: Number of users whose last name begins with "A". This data can change over time if a user changes their name.

I see a few high-level options:

  1. Offload the details of the data each month into an excel spreadsheet, and save it in a file system (in the example above, we'd store a UserID and LastName) like UserNames-2022-04-01.xlsx
  2. Make a timestamped copy of the critical data (Timestamp, UserID and LastName) in a separate database table, and use that for my aggregating and historical analysis
  3. Similar to #2, but import the data into a data warehouse

I think the answer here is "Data Warehouse" but I want to be sure I am not missing a better/different approach.

Is there an approach that is considered best-practice in this scenario?

Improve this question
4
  • Primarily SQL Server 2019.
    – baseball_is_life
    Commented Dec 14, 2022 at 18:26
  • 1
    If the data does not change too often, then I would use triggers in the table such as the user table, to recalculate the aggregates.
    – Rohit Gupta
    Commented Dec 14, 2022 at 19:19
  • Have you considered temporal tables and AS OF <date_time>?
    – Martin Smith
    Commented Dec 14, 2022 at 23:16
  • @Martin Smith, that is not a feature I was very familiar with til yesterday, thank you- this is leading me in the right direction (this could be the answer!)
    – baseball_is_life
    Commented Dec 16, 2022 at 17:49

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.