Data engineering

Reporting disagreements are rarely a maths problem. They happen because the same field is computed three ways in three places and nobody can trace which one the board pack used. We build pipelines with lineage from source column to published figure, define each metric once, and keep the definitions reviewable by the people who own them. Cambridge Mutual’s regulatory reporting run went from nine days of manual reconciliation to an overnight batch with a signed lineage trail behind every number.

• 01

  One definition per metric, versioned and reviewed by its business owner, so “active customer” means the same thing in the board pack and the regulatory return.

• 02

  Column-level lineage from source system to published figure. Any number in a report can be traced back in minutes instead of reconstructed over a fortnight.

• 03

  Tests on the data, not only the code: freshness, volume, referential and distribution checks that fail the pipeline before a wrong figure reaches a report.

• 04

  Retention and minimisation enforced in the pipeline. Personal data is masked, tokenised or dropped at ingest according to its classification, rather than cleaned up afterwards.

• 05

  Reruns are deterministic. Replaying a pipeline for a past date reproduces the figures as they stood, which is what makes a restatement defensible.

1. 01

   Source and definition audit

   We trace the numbers people actually argue about back to their sources and record every place each metric is currently computed. This is the step where the disagreements get named and owned.

2. 02

   Ingest and contracts

   Change data capture or batch extraction from source systems, with a schema contract at each boundary so an upstream change is caught at ingest rather than in a report three weeks later.

3. 03

   Modelling layer

   Transformations as version-controlled, tested SQL. One definition per metric, reviewed by its owner, with the reasoning recorded next to the code instead of in a decommissioned wiki.

4. 04

   Serving and access

   Published models exposed to BI tools, downstream systems and regulatory returns, with row- and column-level access mapped to your existing data classification.

5. 05

   Operate

   Freshness and quality alerting routed to the team that owns the data, plus a documented backfill and restatement procedure for when a source system corrects history.

• Metric definition catalogue: one owner, one definition and one implementation per published figure.
• Ingest pipelines with schema contracts, using change data capture where the source supports it.
• Version-controlled transformation models with freshness, volume, referential and distribution tests.
• Column-level lineage graph from source system through to each published figure.
• Access model mapped to data classification, with masking and tokenisation applied at ingest.
• Backfill and restatement runbook, including how to reproduce a prior reporting period exactly.

• 01Do we need a warehouse, a lakehouse or a mesh?

  Whichever your volume and team shape justify, and for most organisations we work with a well-modelled warehouse is the honest answer. Architectural labels tend to arrive before the problems they solve. We recommend the smallest thing that meets your reporting, retention and access requirements, and tell you what would have to change for the larger option to pay off.

• 02Can this handle personal and special-category data?

  Yes, and the classification drives the design. Orrery Health’s clinical pipelines mask direct identifiers at ingest, pseudonymise at rest, and restrict re-identification to a single named and audited role. We build to the classification you already hold; if you do not have one, producing it is the first week of work.

• 03Our source systems are legacy and we cannot add triggers to them.

  Common, especially with mainframe and packaged ERP sources. Depending on the platform we use log-based change data capture, batch extracts against a replica, or a nightly file drop. All three are workable. The constraint changes achievable latency, not feasibility, and we agree the freshness target per source before committing to it.

• 04Who maintains the pipelines after you leave?

  Your team, and we build for that from the start. Transformations are SQL in your repository, tested in your CI, running on infrastructure you own. We pair with named engineers throughout, and the last six weeks of an engagement are deliberately run by them with us in support.

• INT

  Systems integration

  Point-to-point sprawl replaced with versioned contracts, replayable events and one place to look when a message goes missing.

  /services/systems-integration
• CLD

  Cloud migration

  Regulated workloads moved with residency, key custody and exit obligations designed in, not retrofitted.

  /services/cloud-migration
• SEC

  Security and compliance

  Controls that survive an audit because they are enforced in the pipeline, not described in a document.

  /services/security-compliance