Why does history get lost in your own software?

Your accounting, CRM, web shop and planning tools are built to do today's work. When something changes, they update the data. A deal moves from "quote" to "won", a stock count drops from 40 to 12, a customer gets a new account manager. The system keeps the new value and the old one is gone, or at best sits in an audit log that is hard to read.

You only notice when someone asks a question about the past. How big was the pipeline at the end of last quarter? How much was outstanding in receivables at the end of June? Which revenue belonged to an account manager last year, before their customers were reassigned? If you recorded nothing at those moments, the answer is often a reconstruction or a guess.

An ordinary refresh in Power BI does not solve this. It replaces the data with the position at that moment. That is why history belongs in a data layer of your own. Whether you need one yet is covered in data warehouse for SMEs: when you need one and when not. This article is about how to record history once you have one.

Which data needs history, and which does not?

Not everything needs the same treatment. It helps to split your data into three kinds.

  • Events. Invoices, orders, payments, booked hours. Each record has a date and stays in place. The history is already in the source. You simply fetch them, including corrections and credit notes.
  • Positions. Stock, outstanding invoices, the value of your pipeline, the number of active contracts. A position is a total at one moment and is constantly overwritten. For these you need snapshots.
  • Attributes. A customer's account manager, segment or region, a product's category, a deal's stage. These change now and then, and then you want to know what the value was on a given date. For these you keep a change log.

A simple test per item: will I later want to see what this looked like on a given date? If yes, and the system does not keep that itself, you have to record it. If not, leave it at the current position.

How do snapshots work?

A snapshot is exactly what the word says: at a fixed moment you copy the position into a table of its own, with an extra column for the snapshot date. Next time a new set of rows is added with the new date. Nothing is overwritten.

That lets you look back at any position and show trends: the pipeline per week, stock per month, how outstanding invoices develop.

Three choices shape how you set it up:

  1. How often? Daily is a safe choice for most positions. If you only look at monthly figures, a snapshot on the last day of the month can be enough. The downside: if that one run fails, you have a gap of a whole month.
  2. Which columns? Record only what you need: the key, the values and the attributes you want to filter on. Not every description and note.
  3. What time? Pick a fixed moment, for example at night, and use the local date as the snapshot date. Many APIs return times in UTC, and then a run just after midnight suddenly lands on the previous day.

Storage is rarely the problem. A daily snapshot of 500 open deals gives just over 180,000 rows a year. For a database, that is small.

How do you track changes in customer and product data?

A snapshot works for attributes too, but then you store the same value again every day while almost nothing changes. A change log is tidier: you only record a new row when something has changed.

For each customer (or product, or employee) you then keep several rows, each with:

  • a valid-from date,
  • a valid-to date, left empty on the current row,
  • a flag that marks which row is current.

When a customer's account manager changes, you close the old row and create a new one. In data jargon this is called a slowly changing dimension type 2. The variant where you simply overwrite is called type 1.

You do not need history for every column. Choose per column: account manager, segment and region yes, phone number and email address no. That keeps the table manageable and stops a typo fix in an address from counting as a real change.

In a report you then link an invoice to the customer row that was valid on the invoice date. That way last year's revenue stays with last year's account manager, even if the customer has since been handed over.

Step by step: building history in six steps

  1. Write down the questions. Which questions about the past are being asked now and not answered? For example "how does our pipeline develop per quarter?" or "what was revenue per account manager last year?"
  2. Decide the kind for each item. Event, position or attribute. Only the last two need extra work.
  3. Choose frequency and columns. Per position: how often to take a snapshot. Per attribute: which columns go into the change log.
  4. Build it into your pipeline. Taking the snapshot and updating the change log become fixed steps after fetching the data. How such a pipeline fits together is covered in what is a data pipeline (ETL), and what to build it with in ETL tools for SMEs.
  5. Monitor the runs. A missed night is a gap you can never fill. Make sure you get an alert when a run fails and a check on the number of rows per snapshot date. If you use Make.com, also read Make.com error handling.
  6. Record what you keep. A short description per table: what is in it, since when, how often and who gets alerted.

Better to start today with two positions than in six months with ten. Every week you wait is a week of history you will not get back.

An example: stock, receivables and account managers

A fictional but recognisable example. A technical wholesaler with 25 employees uses an accounting package, a CRM and a stock system. Management has three questions nobody can answer properly: how does stock value develop through the year, are outstanding invoices rising, and how much revenue belongs to which account manager now that customers have been reassigned.

The setup in the existing central database:

  1. Stock. A nightly snapshot per item: quantity, cost price and stock value, with the snapshot date.
  2. Receivables. A nightly snapshot of outstanding invoices per customer, with amount and due date. That also makes it possible to calculate ageing on any snapshot date.
  3. Customers. A change log on account manager and segment. Address and contact details are simply overwritten.
  4. Invoices. The accounting package already keeps these; they are fetched as usual.

After three months, Power BI shows stock value per week, outstanding invoices at each month end and revenue per account manager as it was at the time of invoicing. If the wholesaler uses Cirqll as its CRM, Cirqll data in your own database explains how to keep the history of your pipeline as well.

Where does history in a data warehouse go wrong?

  • Adding up positions over time. Monday's stock plus Tuesday's stock is not stock. In a report you take the last position or an average per period, never the sum across all snapshot dates.
  • Starting too late. Only thinking about history when management asks for a year-on-year comparison means you can only make that comparison a year later.
  • Gaps from missed runs. Without monitoring, you only notice weeks later that a week is missing.
  • Deleted records. If an item or deal is deleted at the source, your pipeline has to recognise that. Otherwise it either keeps showing up in your snapshots as if nothing happened, or disappears without a trace.
  • Changes at the source. If the system renames a field or changes what a status means, you want to see that before your history mixes two meanings.

Help with history in your data warehouse

Setting up snapshots, change logs and the monitoring around them is exactly what we mean by data engineering. The systems we can already connect are listed under integrations, and for the reports that make the trends visible, see dashboarding. How such a project runs in phases is described in our approach. No central database yet? Start with a database for SMEs.