Healthcare
Biopharma data warehouse replaces nested views with incremental tables
Data processing now fits a 4–5-hour overnight window, so Customer 360 dashboards are ready when the working day starts
Data integration and warehousing

Who we worked with
Biopharmaceutical company
A biopharmaceutical company wanted Customer 360 dashboards on its data warehouse, which was built mostly on views and was slow to query and fragile to change. We restructured the warehouse.
What we did
From a web of views to tables loaded overnight
Views on views, and requests that could take days
The warehouse was built mostly on views. One view could feed several others, so the dependencies formed a dense network. A single ordinary request could take up to 2 days, and it could block access to the database completely. Maintenance was fragile for the same reason. Change one view and the whole process could be disrupted.
Tables, incremental loads and parallel loading
We moved the warehouse from views to tables and introduced incremental loads. Data now loads in parallel, and the final data set is assembled from separate components. Apache Airflow schedules the overnight loads and runs them in order, and the loading code is Python. One source can change without putting the other loading processes at risk. Tables are no longer built on the fly at query time.
A stable database and Customer 360 dashboards
The database on AWS is stable, and the heavy processing runs in the overnight load, before anyone opens a dashboard. On the restructured data warehouse, our client builds and uses Customer 360 dashboards. Each one shows a client's profile across live calls, email marketing, advertising and events, refreshed by the start of the working day.

Why tables
Doing the work before anyone asks
Slow requests and fragile maintenance had one cause, the dependency network, so that is what we removed.
What a network of views costs at query time
A view holds no data of its own. It is a saved query that runs whenever something reads it, so when views read other views, one request sets off the whole chain beneath it and every step is computed on the fly.
Tables that load in increments
An incremental load brings in only what changed since the last run. A materialized view would store its result too, but refreshing it still runs the chain of views beneath it, so the dependency network stays. Tables loaded this way have no chain behind them. A dashboard reads data that is already there.
After the restructure
4–5 hours
Overnight processing, done by morning
The stack
PostgreSQL, Python and Apache Airflow on AWS
PostgreSQL
SQL
Python
Apache Airflow
AWS
Reports waiting on a chain of views?
Tell us how your warehouse is built today and which reports wait on it. We reply to every enquiry within 24 hours.



