One Nightly Data Pipeline Instead of Five Spreadsheets
Sales performance, project gross margins, and field technician labor hours were tracked across five disconnected spreadsheets with manual consolidation.
4 hours → 20 minutes
Monday executive reporting
5 → 1
Sources of metric truth
<1s
Dashboard drill-down queries
Nightly
Fresh data by 06:00 Monday
01 • The Challenge
What Was Actually Broken
Pre-Build Baseline
- Four hours of every Monday consumed by consolidating five spreadsheets into one executive pack
- Sales, gross-margin and field-labour data owned by different managers, with different definitions of the same metric
- Follow-up questions answered by exporting to pivot tables, then answered again ten minutes later
- Data errors discovered in the meeting instead of before it
A multi-location services business tracked its three executive metrics — sales performance, project gross margins and field-technician labour hours — in five disconnected spreadsheets. Each department head maintained their own workbook, on their own schedule, with their own definition of terms like "realised margin" and "billable hour". Every Monday morning an analyst spent roughly four hours reconciling the five files into a single pack, and every Monday meeting opened with a quiet argument about which number was right.
The constraints were typical for a business without a data team: no warehouse engineer to hire, no appetite for a year-long data-platform programme, and the existing spreadsheet pack had to keep shipping while anything new was built. What the managing director actually wanted was not a dashboard — it was twenty minutes of Monday morning spent deciding, not reconciling.
02 • What We Shipped
The System, Piece by Piece
Architected an automated nightly ETL ingestion pipeline and role-based web dashboard with sub-second queries and scheduled executive digests.
One nightly ETL pipeline
Scheduled ingestion from the source systems into a single ClickHouse store: sales, project costs and timesheet hours land together every night with freshness guarantees instead of five manual export rhythms.
A written data dictionary
Every metric used by the dashboard is defined once, in one place, with its source field and calculation — so "gross margin" means the same thing to finance, operations and the board.
Sub-second dashboards
ClickHouse columnar queries behind a React interface with D3 visualisations: drill from company total to branch to project in under a second, without anyone exporting to a pivot table first.
Scheduled executive digests
The Monday pack arrives by email before the meeting starts — the same numbers the dashboard shows, generated from the pipeline rather than assembled by hand.
Anomaly alerts before Monday
Row-count, freshness and variance checks run after each load; a missing branch feed or a margin swing beyond threshold pings management Sunday night, not during the board meeting.
Architecture & Decisions
Stack
Python and FastAPI for orchestration, ClickHouse as the analytical store, React with D3.js for the dashboard — chosen for operational simplicity and handover to a team without specialist data-platform staff.
Deployment model
Containerised jobs and services running in the client's own infrastructure with infrastructure-as-code configuration; source data never leaves their environment.
Quality gates
Every nightly load passes freshness and completeness checks before the dashboard switches to the new snapshot; failed loads keep yesterday's numbers and alert rather than silently serving partial data.
Repository & CI
Built in the client's repository from day one with pull-request review, automated tests for transformation logic, and Friday demos of working increments.
03 • Delivery
How the Work Ran
Fixed scope, weekly working software in your repository, and a handover your team can run without us.
See the full delivery processDiscovery spent two weeks instrumenting the existing Monday ritual: where each of the four hours went, which five files fed the pack, and where the department definitions of margin and billable hours disagreed. That measurement became both the acceptance benchmark and the backlog prioritiser.
The pipeline shipped in fixed-scope milestones with a working demo every Friday: source extraction first, then the dictionary-backed transformations, then the dashboard, then alerts. For three weeks the legacy Monday pack was produced alongside the pipeline output, diffs reviewed line-by-line, before the spreadsheet ritual retired.
Handover included the data dictionary, runbooks for the nightly job and for adding new sources, recorded walkthroughs for each dashboard view, and automated anomaly alerts routed to management. The client's own team has run the pipeline unaided since.
04 • Outcome
Measured Results
Monday executive reporting dropped from roughly four hours to about twenty minutes — spent reviewing exceptions, not assembling data.
The five spreadsheets collapsed into one warehouse with a published definition for every metric the board reads.
Follow-up questions in the meeting are answered live from the dashboard, because drill-downs return in under a second instead of triggering a new export.
Data problems surface Sunday night as alerts rather than surfacing as surprises in front of the managing director.
Shipped Outcome
Monday executive reporting accelerated from 4 hours to 20 minutes
"Monday morning reporting went from half a day of frantic spreadsheet pasting to twenty minutes with coffee."
— Managing Director, Multi-Location Service SME
Handover & Runbook
Shipped with a standardized data dictionary, user walkthrough guides, and automated anomaly alerts sent directly to management.
Related capability:Data Analytics
Representative write-up of delivery work of this type — client-identifying details are protected under mutual NDA, and figures describe measured outcomes from engagements of this pattern rather than a named client's public benchmark. References within your sector can be arranged during serious evaluation.
Questions
About This Engagement
Is this a real client project?
A representative write-up of delivery work of this type — client identity, branch structure and figures that could single them out are protected under mutual NDA. Sector references can be arranged during serious evaluation.
How long does a reporting pipeline like this take?
Source-system count, metric definitions and data quality decide the schedule. Discovery maps the sources and disagreements first; the build then ships in fixed-scope milestones with a working demo every Friday, and the timeline is agreed in writing before work starts.
What happens when a feed fails or data looks wrong?
Loads are gated: freshness and completeness checks must pass before the dashboard switches snapshots. A failed load keeps the previous day's numbers and raises an alert with the failing source named — the system never silently serves partial data.
Do we need a data engineer to run it?
No. The pipeline runs on a schedule in your infrastructure, alerts route to management, and runbooks cover the two tasks your team will actually need — restarting a job and onboarding a new source. If you later hire a data engineer, they inherit documented transforms, not a black box.
Who owns the pipeline and the data?
You do — source code, schema, transformation logic and dashboards live in your repository from day one, and your data stays inside your environment throughout.
Want the full technical walkthrough? Request a case study walkthrough — arranged under mutual NDA with client consent.
Content last reviewed October 2026.
Related Services
Services That Do This Work
Each service page states what is delivered, how it is priced, and what it is a bad fit for.
- Data Pipelines & DashboardsLive dashboard + data dictionaryFirst dashboard typically 2–4 weeks after data access and scope agreement
- Cloud Setup & DevOpsInfra as code + runbookSetup typically 1–2 weeks per environment, then handover walkthrough
- Custom Software DevelopmentScope doc + tested releasesDiscovery typically 1–3 weeks; first usable release in weekly sprints after that
- Security Reviews & HardeningReview report + fixesReview typically 1–2 weeks, fixes scheduled by priority after that
- IT Consulting & Technology AdvisoryAudit report + technical roadmapAdvisory review typically 1–3 weeks, depending on system complexity
- Maintenance & Support PlansMonthly health report + proactive updatesOngoing monthly support plan with guaranteed SLA response times
TELL US ABOUT YOUR PROJECT
Tell Us What to Build, Fix or Modernize
Send a short brief. We reply within 12 hours with clarifying questions and a discovery quote — no retainers, no spam.
What Happens Next
Request a Discovery Quote
Share your goals and timeline. NDA signed first if needed.
12-hour response