Culvert - Compliance Reporting Pipeline from Source Extract to Signed-Off Ofgem Return
Regulatory Reporting Pipeline for a GB Energy Supplier
Designed and built a clickable regulatory reporting pipeline for a Great Britain electricity and gas supplier: pinned source extracts, versioned transformation rules, reconciliations between systems, validation of every filled return and maker-checker sign-off, with every figure traceable back to the rows that produced it.
Most of our work is under NDA. Per agreement, we can share the work but not the company details - all names and identifying information shown here have been changed.
Impact
Dashboard, returns list, return detail with 7 tabs, cell trace and reconciliations with a detail panel. Every one of the 18 return instances opens its own detail page.
A lineage graph runs from source snapshots through staging models and transformation rules to the aggregate and the template cell, with row counts in and out and the SQL of each rule.
Preparer, reviewer and approver must be different people. Submit for review stays blocked while any validation fails or a warning has no explanation.
7 report templates, 16 transformation rules with SQL, 19 validation rules evaluated live against cell values, 14 reconciliations, 5 source systems and 10 staff users.
The Problem
Hallowmere's regulatory returns were built by two analysts in spreadsheets fed by hand-written SQL, re-run each quarter and emailed for sign-off. Nobody could say which query version produced which figure, tracing one disconnections number for Ofgem took three weeks, a return was resubmitted because dual fuel accounts were counted twice, and due dates lived in a personal calendar.
Our Solution
Culvert takes a return from source extract to signed-off submission. Each run pins its extract snapshots, rule versions and template version, reconciles billing, metering, CRM and complaints data against each other, validates the filled template and routes it through preparer, reviewer and approver, so any cell can be traced back to the rows behind it.
The Challenge: Returns Nobody Could Reproduce
Ofgem collects supplier data through periodic returns and one-off requests for information, covering customer numbers, debt and disconnections, payment difficulty, the Priority Services Register, complaints and guaranteed standards. The compliance director has to sign figures that come from five systems and change with every template revision.
Figures Without a History
Each quarter the analysts re-ran queries against live billing and CRM databases. Without pinned snapshots or versioned logic, a submitted figure could not be reproduced, and answering one Ofgem query on disconnections took three weeks.
Systems That Disagree
Billing, industry registrations, CRM and the complaints tool each count customers, PSR registrations and complaints their own way. A dual fuel account counted once per fuel led to a resubmitted return, and nobody checked the sources against each other first.
Sensitive Data in the Working Set
Extracts hold customer data covered by UK GDPR and the Data Protection Act 2018, and PSR needs codes can reveal health information. Returns must be aggregates, while analysts still need to look at rows when a number looks wrong.
Rules and Deadlines That Keep Moving
Template layouts, guaranteed standards payment amounts, the complaint referral period and due dates change over time. Hard-coding any of them meant silent errors the next time the regulator or the client's own policy changed.
Our Approach: From Reporting Rules to a Clickable Pipeline
Spec and Rules Research
Mapped 9 user roles from reporting analyst to read-only auditor, 38 screens across the staff app and an auditor portal, and the data model for sources, snapshots, runs, versioned rules, templates, validations, reconciliations, sign-offs, packs and restatements. Wrote down the rules the design follows: Ofgem supply licence conditions, the Priority Services Register, Guaranteed Standards of Performance, complaints handling standards, UK GDPR and the Data Protection Act 2018, with every threshold kept as a configured value.
Clickable Demo on a Production UI Kit
Built 5 key screens on a production dashboard kit: the dashboard with due-soon returns, source freshness, role-based My work and needs-attention items, the returns list with stage pills, the return detail with cells, validation, reconciliation, commentary, adjustments, sign-off and activity, the cell trace with lineage graph and masked row sample, and reconciliations with breakdowns and explanations. Sign in as six different roles and the actions follow the user.
What a Production Build Adds
Read-only extracts from billing (such as Gentrack Junifer or ENSEK Ignition), Dynamics 365 or Salesforce and Zendesk, industry flows via ElectraLink and Xoserve, a Snowflake or BigQuery warehouse with dbt models, Dagster or Airflow with OpenLineage, rule review through GitHub pull requests, immutable packs on S3 Object Lock and Entra ID sign-on. Plus the calendar, packs, restatements, rule and template management, a hash-chained audit log and the auditor portal.
Why It Works: Every Number Has a Trail
Each Run Pins Its Inputs
The run bar shows the run id, one snapshot chip per source with its extract date, the rule set version, the template version and the configured values version. Compare with previous run sits next to it, so a figure can be reproduced or explained later.
Where Did This Number Come From?
Every cell opens a trace with the lineage graph, the rule chain in plain English and SQL, the contributing rows by region or payment method, the checks that touch it and its last 8 periods, with restated values marked.
Sources Checked Before Sign-Off
Reconciliations compare two independent sources against a tolerance taken from the client's configured policy, break the difference into A only, B only and mismatched, and can be marked explained only once the unexplained remainder is within tolerance.
Personal Data Stays Masked
Returns are aggregates. Row samples show account ids and the last 4 digits of the MPAN or MPRN, never names, addresses or PSR needs codes, and every sample view is audited. A sandbox switch marks runs that can never be packed or submitted to Ofgem.
Related case studies
View all: Data & analytics
Calima - Executive BI Dashboards with One Governed Set of Hotel and Restaurant KPIs
Designed and built a clickable executive dashboard portal for a Canary Islands hotel and restaurant group: one governed definition of RevPAR, ADR, occupancy, covers, food cost and labour cost, a daily pulse per region, a property scorecard against targets, drill-down to each hotel and restaurant, and variance commentary before the numbers reach the board.
Dokuma - Governed Data Warehouse and Pipeline Operations Console
Designed and built a clickable operations console for a direct-to-consumer home textiles brand's cloud data platform: freshness against SLAs per domain, every pipeline run with retries and a suggested fix, lineage from raw tables to marts, and column-level PII tagging with masking by default under KVKK and GDPR.
Enterprise Software Engineering - Human + AI
Contact Us
© 2026 - Copyright. All Rights reserved Oceanlab
Location
Global Headquarters
21 Merchant Road, 3rd Floor, Singapore 058267
Main corporate office and leadership operations
North America Office
548 Market Street, PMB 12345, San Francisco, CA 94104
Client operations and business development
India Office
402 Shivalik Shilp II, SG Highway, Ahmedabad 380015
Engineering and delivery center
Enterprise Software Engineering - Human + AI
Contact Us
Locations
Global Headquarters
21 Merchant Road, 3rd Floor, Singapore 058267
Main corporate office and leadership operations
North America Office
548 Market Street, PMB 12345, San Francisco, CA 94104
Client operations and business development
India Office
402 Shivalik Shilp II, SG Highway, Ahmedabad 380015
Engineering and delivery center
© 2026 - Copyright. All Rights reserved Oceanlab