SQL data platform and ETL optimisation
Reporting databases grow organically until nobody trusts them. Why platforms get rebuilt, and how dimensional design and disciplined ETL restore performance and trust.
overnight runs complete and restart cleanly
reports respond at the speed of the question
one definition, traceable to source
The challenge
Reporting databases commonly grow one request at a time, with tables added for a project, views layered on views and load jobs extended by whoever was available. After a few years the overnight load runs long and fails often, reports query inconsistent snapshots, and the same measure returns different values depending on which table a report happens to join.
Analysts respond by building private extracts, which multiplies the problem and leaves no lineage and no agreed definitions. Everyone who depends on the numbers requires a platform that finishes its work overnight, answers questions quickly, and can show where any figure came from.
When it's time to act
The signs we commonly see when this initiative is due.
- The overnight load finishes at 9am, or not at all, and mornings start with a rescue.
- The same measure returns different values depending on which table a report joins.
- Month-end reporting slows the platform for everyone else.
- Analysts keep private extracts because they trust their copy more than the platform.
- Tracing where a reported figure came from takes a day of archaeology.
How we deliver it
Workload review
Profile queries, load failures and schema hot spots to target effort where it pays.
Redesign
Conformed dimensions and fact tables per Kimball, one definition per business measure.
ETL rebuild
Incremental, idempotent loads with validation gates and restart-from-failure.
Engine tuning
Indexing, partitioning and query plan fixes measured against the real workload.
Operate
Monitoring, lineage documentation and handover to the internal team.
Workload review
Profile queries, load failures and schema hot spots to target effort where it pays.
Redesign
Conformed dimensions and fact tables per Kimball, one definition per business measure.
ETL rebuild
Incremental, idempotent loads with validation gates and restart-from-failure.
Engine tuning
Indexing, partitioning and query plan fixes measured against the real workload.
Operate
Monitoring, lineage documentation and handover to the internal team.
Our approach
- Understand which reports people actually depend on and where they have lost trust in the numbers, because that is what the rebuild has to win back.
- Profile the real workload and benchmark it: which queries hurt, which loads fail, and which tables serve conflicting definitions of the same measure, assessed against your own data standards where they exist.
- Redesign the warehouse on Kimball dimensional principles, with conformed dimensions so every report draws the same site, asset and period definitions.
- Rebuild ETL as incremental, idempotent steps with validation gates, so loads either pass their checks or stop visibly rather than quietly publishing bad data.
- Tune the engine against the measured workload: covering indexes for the report patterns, partitioning for the fact tables, and query plan fixes verified by before-and-after timings.
- Document lineage from every reported figure to its source system, and hand the platform over with monitoring the internal team can run.
Tools and methods
The value it creates
- Overnight loads finish inside their window and restart cleanly from failure, instead of needing a morning rescue before anyone can report.
- Reports respond fast enough to be used in the conversation they were built for, and month-end no longer degrades the platform for everyone else.
- Every figure traces to source, which retires the private extracts and lets the business trust one set of numbers.
What changes
Views layered on views
Conformed dimensions and one definition per measure
Loads that fail silently
Validation gates that pass or stop visibly
Queries tuned by folklore
Indexing and partitioning measured against the real workload
Private extracts everywhere
One platform the business trusts
Where these initiatives fail
The failure modes we design against.
- Rebuilding the schema while keeping the old load jobs, which migrates the fragility.
- Tuning by instinct instead of profiling the workload that actually runs.
- Publishing bad data quietly: a load without validation gates fails on someone's report.
- Skipping handover, so the platform depends on whoever built it.
Common questions
Rebuild or tune: how do you decide?
The workload review decides. Where the model is sound, indexing, partitioning and query fixes buy years. Where definitions conflict at the schema level, tuning polishes a structure that cannot agree with itself, and a redesign pays back faster.
Do reports keep working during the rebuild?
Yes. The new platform runs in parallel and reports cut over in stages once their numbers reconcile, so the business never loses its reporting while the foundation changes.
Which database platforms do you work with?
PostgreSQL, SQL Server and Azure SQL are the common ones, loaded through SSIS or Azure Data Factory and reported through Power BI and Tableau. The dimensional principles are the same on each.
Key terms
Plain-language definitions from our glossary for the concepts this page leans on.
Standards and further reading
Reference points we draw on where they suit the work. We also work to client internal standards and established site practice.
Further reading
Articles and calculators on the methods behind this work.
Related projects
Facing a similar challenge?
Tell us what you are working through and we will bring the right mix of engineering, data and hands-on experience.
Contact us