A data warehouse with a memory, for a real estate group

Where it started

The group ran with information spread across the management system, several departmental spreadsheets and manual exports. Each area had its own reports and its own definitions, so two documents covering the same period could show different numbers with nobody able to say which was right.

The most expensive problem was not the time spent assembling reports, considerable as it was, but the absence of history: when a value changed in the source system, what it said before disappeared. There was no way to answer "how did this look in March" other than hoping somebody had saved a copy.

What we did

We started from the business questions, not the tables. Together with management we defined the set of indicators that had to be answerable every month, and only then designed the dimensional model that supports them.

• Dimensional model on PostgreSQL with slowly changing dimensions of type 2, so every modification preserves the previous version with its validity dates.
• Scheduled load processes from the source systems, with quality validation and traceability back to the origin of each figure.
• Laravel backbone for orchestration, load control and the data access layer.
• Database server administration: PostgreSQL in Docker containers, backups with a tested restore, and automated deployment for every new load.
• Second exploitation phase: per-area dashboards built on the model, with each metric’s definition written next to the number.

How it was structured commercially

The project was proposed and closed end to end as a technical and commercial proposal in two phases: first the model and historical load, then the exploitation layer. That let the client validate the value of phase one before committing to phase two.

What changed

• Month-end stopped being an argument about which number is correct.
• Any question about the past is answered from the history, without depending on stray copies.
• New questions from management are answered with a query against the model instead of a bespoke development.