From fragmented files to a controlled operating data layerFrom fragmented files to a controlled operating data layer

Investment firms rarely begin with a clean data architecture. They accumulate administrator files, broker statements, custodian records, bank reports, investor documents, market-data extracts and internal spreadsheets. Each source may be useful. The problem appears in the spaces between them.

When teams spend time searching for the latest file, copying values between templates or explaining why two reports differ, the firm does not necessarily need another enterprise platform. It needs a controlled operating data layer.

What an operating data layer isWhat an operating data layer is

An operating data layer is the dependable connection between source systems and the workflows that consume their information. It does not have to replace the portfolio system, administrator, data warehouse or Excel. Its role is to make recurring operational use of data more consistent.

At minimum, it should answer:

• Where did this data come from?
• Which fund, entity, account and period does it belong to?
• Has it passed the required checks?
• Which version is approved?
• What changed since the previous version?
• Which report or workflow consumed it?
• Who reviewed or amended it?

Begin with the workflow, not the databaseBegin with the workflow, not the database

Data programmes often start by listing systems and fields. That is necessary but insufficient. The more useful starting point is a recurring business workflow—for example, daily cash oversight, monthly NAV review or investor reporting.

For that workflow, identify:

1. The decisions or deliverables it produces.
2. The data required for each step.
3. The current sources and formats.
4. The transformations and manual adjustments.
5. The validation and approval points.
6. The evidence that must be retained.

This keeps the architecture tied to an operating outcome and reduces the risk of building a large data store that employees do not use.

Separate source, control and presentationSeparate source, control and presentation

Many operational spreadsheets combine three roles: source data, transformation and control logic, and final presentation. That makes change difficult. A safer design separates them.

Source layerSource layer

Retain the original files or system extracts with source, timestamp and version metadata. Do not silently overwrite the evidence.

Controlled data layerControlled data layer

Map fields into a consistent structure, apply validation, identify exceptions and retain adjustment history. This is where the firm establishes a common operating definition.

Presentation layerPresentation layer

Deliver the approved information through Excel, a web report, a portal or another tool suited to the user. Excel can remain valuable without being the only system of record.

Design around exceptionsDesign around exceptions

A data layer should not merely move information faster. It should make problems visible.

Useful exception categories include:

• missing file or incomplete record;
• unexpected schema or format change;
• duplicate or stale data;
• cash, position or transaction mismatch;
• value outside an agreed tolerance;
• invalid mapping to fund, account or entity;
• late approval or unresolved amendment.

Each exception needs an owner, status, ageing and evidence. Otherwise automation simply produces a faster queue of ambiguous problems.

Preserve human interaction where it is usefulPreserve human interaction where it is useful

Operational teams often prefer to review and amend information in Excel. Removing that interface can create resistance without improving control.

A practical model is:

1. Source data is stored and versioned centrally.
2. Approved datasets populate a controlled range or report.
3. Employees review, comment or amend only defined fields.
4. Validation runs before changes are accepted.
5. The approved result and adjustment history are written back.

This preserves a familiar working surface while strengthening the underlying control model.

Prepare for AI by improving the evidencePrepare for AI by improving the evidence

AI cannot compensate for unclear ownership, unidentified versions or missing source evidence. Before introducing an AI agent, the firm should be able to identify:

• approved source locations;
• permitted data and user access;
• business rules and tolerance thresholds;
• expected output structure;
• examples of acceptable and unacceptable results;
• review and escalation requirements.

Once those foundations exist, AI can help classify documents, extract fields, draft explanations, prioritise exceptions or prepare recurring outputs. The data layer makes those actions more dependable and easier to review.

A practical implementation sequenceA practical implementation sequence

1. Select one recurring workflow1. Select one recurring workflow

Choose a process with visible manual effort, repeated data sources and a clear output.

2. Inventory inputs and transformations2. Inventory inputs and transformations

Capture the actual files, fields, versions, owners and manual adjustments—not only the documented process.

3. Define a minimum common model3. Define a minimum common model

Standardise the identifiers and fields required for the selected workflow. Avoid designing the entire enterprise model on day one.

4. Implement validation and exception ownership4. Implement validation and exception ownership

The first useful release should expose problems, not only produce a cleaner report.

5. Connect the user interface5. Connect the user interface

Deliver information through the tools employees can use effectively, including controlled Excel where appropriate.

6. Add automation and AI selectively6. Add automation and AI selectively

Automate stable steps first. Introduce AI where variation or unstructured information makes conventional rules insufficient, with review thresholds defined in advance.

The operating principleThe operating principle

A controlled operating data layer is not another abstract platform. It is the practical structure that lets a firm trust, trace and reuse information across recurring workflows.

Sources and further readingSources and further reading

• EY on a thin layer of automated data governance: Source
• Clearwater Analytics on unified investment data: Source
• Alter Domus on services, technology and preferred-system integration: Source