Run a Data Warehouse Health Check Before Funding a Rebuild

A warehouse rebuild is expensive before it is clear.

The team knows something is wrong. Loads run late. Reports miss the meeting window. Analysts work around modeled tables because they do not trust the data. Support tickets repeat the same data-quality complaints. A backlog of fixes grows, but nobody can say which issue is root cause and which issue is just a symptom.

That is the right moment for a health check.

The goal is not to modernize everything in one pass. The goal is to understand the current warehouse and ETL layer well enough to choose the next move with evidence.

Start with the business pain

Do not begin with every table.

Begin with the warehouse process, load, table, report, or decision window that hurts most. Which failure makes people miss a review? Which table do analysts avoid? Which dashboard creates the loudest trust issue? Which job fails often enough that someone watches it by hand?

The scope should include the critical path around that pain: source extract, transformation, load, table shape, dependencies, downstream report, and owner. In many environments that may involve Azure SQL, Azure Data Factory, SSIS, Teradata, Oracle, OBIEE, Informatica, Alteryx, or Python jobs.

This keeps the assessment practical. A narrow slice with evidence is more useful than a broad inventory that never reaches a decision.

Separate symptoms from causes

Warehouse issues often appear in the wrong layer.

A slow dashboard may be caused by dashboard design, a semantic model, a missing index, a grain mismatch, a late ETL job, or a source system that changed shape. A bad total may come from a filter, a join, a transformation rule, stale data, or a definition mismatch.

The health check should trace the issue end to end. Review schedules, logs, runtimes, dependencies, table profiles, model shape, naming, documentation, and source-to-target traceability. Look for nulls, duplicates, stale rows, grain mismatches, orphaned keys, and transformations nobody owns.

The output should tag findings by impact, likely root cause, effort, risk, confidence, and owner type. Confidence matters. If access is limited or evidence is incomplete, the roadmap should say that clearly.

Build a remediation roadmap

A useful assessment does not end with a list of complaints.

It separates quick fixes, sprint-sized remediation, governance needs, and larger architecture work. A quick fix might adjust a job schedule, add a missing check, document a table owner, or clean up an obvious query issue. A sprint candidate might repair one critical load path, improve a reporting model, or add reconciliation checks. Larger work may involve deeper warehouse redesign or ongoing stewardship.

The priority order should reflect business impact, not technical neatness. Fix the issues that reduce decision delay, recurring manual work, or reporting distrust first.

What a 5 to 10 day assessment looks like

A health check starts with scope, access, and evidence. The client brings the known pain points, critical tables or jobs, recent failure logs, runtime history, support tickets, available mappings, and named technical and business owners.

The first pass inventories the in-scope sources, jobs, schedules, tables, reports, owners, and service windows. Then I review recent failures, runtime patterns, manual interventions, table profiles, model shape, and traceability.

The middle of the assessment is synthesis: separate root causes from symptoms, rank findings by impact and effort, and identify where confidence is strong or weak. The final pass turns that into an executive summary, evidence appendix, and prioritized roadmap.

The handoff should make the next decision easier. Proceed with a sprint, set up a retainer for ongoing stewardship, fund a larger rebuild, or defer because the evidence does not justify the spend yet.

The useful first move

Pick the one warehouse pain point that creates the most visible business drag.

Collect the recent logs, affected reports, critical tables, and owner complaints around that slice. Once the evidence is in one place, the team can stop debating the size of the mess and start choosing the most defensible fix.