A reporting data mart people can trust
A reference design for turning several operational systems into one governed source for management and regulatory reporting.
TakeawayA reported number is only as credible as its lineage, so design the checks before the dashboards.
Illustrative reference design. It shows how I approach this kind of problem and is not a record of a delivered project.
Problem and constraints
Problem
Reports are assembled by hand from several systems. Each cycle brings late fixes, drifting definitions and the question: why does this month not match last month?
Constraints
- Figures must be reproducible for any past reporting date.
- Business owners agree definitions before they are built.
- Source systems cannot change to suit reporting.
- The team running the pipelines is small.
Options considered
- Not chosen
Build each report directly on source systems
Quick for the first report, but definitions drift and every cycle repeats the reconciliation.
- Not chosen
A central warehouse with transformations only
Consistent structure, but errors surface in reports because nothing checks the boundary.
- Chosen
A governed mart with reconciliation at the boundary
More agreement up front, fewer disputes later.
Architecture
Land data unchanged, reconcile it at the boundary against control totals, then model it around a metric dictionary the business owners signed off. Any figure can be traced to its source rows.
Select any component to see what it does and how it connects. The diagram is illustrative: it shows the shape of a solution, not a specific system.
Illustrative reference architecture
Sources
Staging
Validate
Curate
Report
Select a component
Choose any block to read what it does and how it connects to the rest.
- Operational systems
- Systems of record that stay unchanged. Each has a named data owner.
- Staging layer
- Raw copies landed as received, with load timestamps, so any run can be reproduced.
- Reconciliation checks
- Row counts, control totals and rule checks. A failing check blocks publication and notifies the owner.
- Curated data mart
- Modelled around the governed metric dictionary, with history kept for past reporting dates.
- Reports and review
- Management and regulatory reports built on the curated layer, signed off by metric owners.
Decisions and trade-offs
Reconcile at the boundary, as data enters the mart.
Trade-offThe first load is slower and failures are louder, but disputes are settled once, not in every report.
Model the mart around a governed metric dictionary.
Trade-offIt costs stakeholder time up front and stops each team building its own version of a metric.
Prefer idempotent batch runs over streaming.
Trade-offData is less fresh, but any run can be repeated safely and audited.
How it would be delivered
Inventory
List the reports and write each metric definition with its owner.
Land
Copy source data unchanged into staging, then apply typed, tested transformations.
Reconcile
Run checks against control totals and block publication when they fail.
Publish
Expose a curated layer to reporting tools, documented from the dictionary.
Lessons and next steps
Current statusIllustrative reference design. It has not been implemented as described.
Lesson: the most valuable artefact is often the metric dictionary, because it gives business and engineering one vocabulary.
Next steps
- Inventory the highest-risk reports and agree an owner for each metric.
- Choose the control totals that block publication, and who is told when one fails.
- Pilot the staging-to-mart flow on a single report first.
Related work
Talk it through
If this kind of problem looks familiar, I would like to hear how it shows up for you.