Skip to content

Data & Analytics

Data warehousing: keep what was true then

Operational systems keep the present. When a customer moves territory or a price list changes, the old value is overwritten, and every past report quietly changes with it. A warehouse keeps history, every version with the dates it was true and every day's snapshot, in one place built for reporting across all your systems. So last year's report still says what it said last year, and trends compare like with like.
Discuss your systemSee three kinds of history ↓
Jun 3 · Acme Properties moves from Northside to EastsideOperational system · now onlyWarehouse · history keptAcme PropertiesNorthsideNorthsideEastsideAcme Properties · NorthsideJan 1 – nowJan 1 – Jun 2Acme Properties · EastsideJun 3 – nowQ1 revenue · Northsiderun in April$182,400rerun in July$171,900changed: Acme now counts as EastsideQ1 revenue · Northsiderun in April$182,400rerun in July$182,400same: Q1 kept as Northside

When you need a warehouse, and when you don't

Reports join three or more systemsA shared, modelled home pays for itself
Last year's numbers change when you rerun themYou need history, not just the current state
Heavy reports slow down the system people work inAnalysis should run somewhere else
One system and a few reportsReport from that system. A warehouse can wait

Four layers, each with a job

Data moves up one layer at a time, and each layer can be rebuilt from the one below it.

  1. 01

    Raw

    As delivered, kept

    Every load stored exactly as it arrived, so any later layer can be rebuilt.

    raw data that gets “cleaned” in place.

  2. 02

    Staging

    Cleaned and renamed

    One spelling for each status, one format for each date, one name for each field.

    business rules creeping in here instead of the model.

  3. 03

    Modelled

    Facts, dimensions, history

    Jobs, invoices and customers at a stated grain, with versions kept when they change.

    a customer table that only holds today's values.

  4. 04

    Marts

    Shaped for one team

    Small, fast tables for finance, operations or marketing, built from the modelled layer.

    marts with their own definitions of shared numbers.

Three kinds of history

Each answers a different kind of question about the past.
customerterritoryvalid
Acme PropertiesNorthsideJan 1 – Jun 2
Acme PropertiesEastsideJun 3 – now

Q1 revenue for Northside?

Counts Acme's Q1 work, because it was Northside then.

What a warehouse hands off

In

Raw loads
from every source
Change records
what changed, and when
Model rules
facts, dimensions, history
Warehouse
raw → staging → modelled → marts

Out

Reports
same answer on any day
Point-in-time answers
what was true then
One home
for BI, dashboards, AI analytics

Worth building when the past has to stay put

Good fit

  • Reports combine several systems.
  • Numbers for closed periods must not change.
  • You ask questions about trends, not just today.

Not the right fit

  • One system's built-in reports answer what you ask.
  • Very little data. A few modelled views in your existing database may be enough.

Where it's the home

Next to warehousing

Questions about data warehousing

Do we need Snowflake or BigQuery?

Often not. PostgreSQL holds a modest business's history comfortably; larger warehouses make sense when volumes or many concurrent users demand them. Starting candidates, not requirements.

How long should history be kept?

As long as decisions look back, often several years for finance, less for raw web events. We set it per table, with you.

Is it real time?

Usually updated on a schedule, often overnight or hourly. Live screens belong in the operational system.

What does it cost to run?

For most small and mid-sized businesses, a database server and scheduled jobs. We size it to your data before recommending anything larger.