Perspectives · Data Operations
Practical ETL Workarounds for Mid-Market Investment Firms
Practical ways a lean investment firm can integrate new data sources with tools it already has, and what to reach for when workarounds stop scaling.
· 14 min read
Onboarding a new data source means months of ETL build before a single row of usable data lands in front of the people who need it. The cost is in time, but also in the missed opportunity, the stale analysis, and the analyst hours burned on manual workarounds, while the "proper" pipeline waits in a backlog.
For a deeper look at why onboarding a new data source turns into a months-long project, see our previous article, Onboarding a New Data Source Shouldn't Require a Project.
This piece moves past the diagnosis and into the practical. What can a lean investment firm actually do, with tools they already have, to integrate investment data without commissioning a custom build? We will look at real workarounds, some temporary, but a few that are surprisingly durable. None require a new vendor. Finally, we will look at what a firm should reach for when they need more than a workaround.
The Real Cost of "Just One More Data Source"
Mid-market investment firms live closer to the data than their larger counterparts. There is no large data engineering bench absorbing new integrations. When a new source appears, the work falls on the same small team already running the book. Research across the industry keeps arriving at the same conclusion: manual, spreadsheet-driven data handling is the norm, and it’s expensive. A study of 100 Hedge Fund executives found that 73% said their fund or department was wasting time with manual or spreadsheet-based portfolio analytics, exposing them to errors, missed opportunities, and even regulatory fines1. A separate industry survey found that 56% of asset managers still struggle with fragmented data, 46% rely on manual processes to keep product information current, and 44% continue to use spreadsheets as core infrastructure2.
The point is not that spreadsheets are bad. Rather, when every new data source requires a custom project to integrate, the gap between "we need this data" and "we can act on this data" is filled with manual effort. That is where mid-market firms lose time they don’t have.
Why the Default Answer Is Always "Build a Pipeline"
"Build a pipeline" is the default reflex, because investment data is messy. It arrives in inconsistent formats, on inconsistent schedules, from sources who define each field differently. A prime broker, a fund administrator, and a custodian do not agree on what "position" means. The conventional path is to write custom extract, transform, and load logic that normalizes each source into something usable. That work is why integrations take months to build.
Over time custom pipelines accumulate technical debt. Each one-off build becomes a piece of fragile infrastructure that someone has to maintain, often a single person who knows how the wiring works. Per industry analysis, when a data platform loses trust, the shadow spreadsheets that grew up in the meantime become "a second, worse data platform with no tests and no owner"3. One practitioner's account of tracing a broken trade-surveillance spreadsheet found a code comment that read "Fix this on Monday," signed by someone who had left the firm years earlier4.
This is the underlying structural problem. It is not that firms lack the skill to build pipelines. It is that each custom build adds to a stock of brittle, person-dependent logic that gets harder to maintain as the source count grows. Research from IHS Markit (now S&P Global Market Intelligence) found that nearly three in four private capital firms rely on spreadsheets to collect and analyze portfolio company data, and that a substantial share of those spreadsheets contain model errors, including structural errors and errors of omission5. The default answer, "build a pipeline," solves the immediate integration and creates the next maintenance problem.
Before you build, it is worth asking whether a lighter approach can get the data flowing. The workarounds below are not a replacement for a real data platform. They are ways to buy time, reduce the project backlog, and keep the team analyzing instead of waiting.
Workarounds You Can Run Without a Vendor
Standardize the intake request first
Most data onboarding projects start late and grow because the request itself is vague. "We need the new prime broker feed" is not a specification. Before any build, formal or informal, define what you are actually asking for. Create a one-page intake template that captures the essentials: the source, the delivery method (SFTP, email, API, portal download), the file format, the cadence, the fields you need, and the business question the data is meant to answer.
This is a lightweight version of what data engineers call a data contract, which is an explicit agreement on what a source provides and what you expect to consume. The value for a small firm is immediate. A clear intake request turns a fuzzy "project" into a scoped task. It surfaces the real complexity early, before anyone has written a line of transformation logic, and it makes the handoff between the person who wants the data and the person who has to integrate it far less painful. Celent's research on investment data ecosystems notes that fragmented data tools across the value chain, including ETL and data lineage, create operational complexity that hinders responsiveness, and that firms must consolidate and make deliberate trade-offs between best-of-breed and standardized capabilities6. A simple intake template is the first, cheapest step toward that standardization.
Use spreadsheets as a deliberate, documented staging layer
Spreadsheets are already the de facto integration layer at most mid-market firms. The goal is not to eliminate them. Rather, it is to stop using them as shadow IT and start treating them as a deliberate, owned staging layer.
When a new source arrives, land the raw file in a single, versioned location. A shared drive folder with a naming convention, or a structured SharePoint library, works. Then build the first-pass transformation in a spreadsheet using tools you already have. Excel's Power Query (Get & Transform) and Google Sheets' import functions can clean, filter, and reshape a CSV or text file without any custom code. The output is a standardized table that downstream analysis can reference.
The difference between this and shadow IT is governance. Assign an owner, version the file, and document the transformation logic in a comment or a companion tab, not in someone's head. Use named ranges or table objects, so downstream references don’t break when rows are added. The aim is to make the spreadsheet a visible, maintainable staging step rather than an invisible dependency. Research from Protiviti on end-user developed applications in financial institutions describes how spreadsheets and other user-built tools, maintained outside of IT governance, accumulate as a significant source of operational risk, with reduced oversight and fragmented controls that grow harder to manage as their numbers increase7. Documenting the staging layer does not eliminate that risk, but it makes the workaround visible and maintainable instead of hidden and fragile.
Lean on file-based and API "good enough" ingestion
A common reason onboarding becomes a project is the assumption that integration must mean a real-time, fully automated pipeline. For many investment data sources, a scheduled file drop is perfectly adequate.
If a counterparty can deliver a CSV to an SFTP site on a defined schedule, you can automate the pickup with a simple script or a scheduled task that moves the file into your staging location. If a vendor exposes an API, a short, scheduled job can pull the data you need on a daily or hourly cadence. The point is to match the ingestion method to the actual business need. A NAV file that arrives once a month does not require a streaming integration. A pricing feed that updates intraday might, but most mid-market firms can tolerate a scheduled pull.
This "good enough" philosophy is pragmatic, and it aligns with how mature firms actually evolve. A case study of a venture capital fund found that its data ingestion had grown into a set of manually run pipelines with inconsistent implementation patterns, using different languages and tools, with manual steps scattered across the process. The fix was not a wholesale rebuild but a standardization of the ingestion layer, so the team had one consistent way to bring data in from multiple sources8. You can apply the same logic at a smaller scale. Pick one consistent ingestion pattern, even a simple one, and reuse it for every new source.
Build a lightweight canonical mapping in a shared data dictionary
The hardest part of integrating investment data is not moving the file. It is reconciling what each source calls things. One counterparty's "security identifier" is another's "ISIN." One administrator reports cash in a single column; another splits it across three. Without a shared understanding, every new source means re-deciding the mapping from scratch.
The workaround is a shared data dictionary. This is a single, maintained document that defines your firm's canonical fields and how each source maps to them. It does not need to be sophisticated. A spreadsheet with three columns, canonical field, source field, and transformation rule, is enough. The discipline is in keeping it current and making it the single reference whenever a new source arrives.
This is where a lot of mid-market firms go wrong. The mapping lives in the head of one analyst, or in a tab only that analyst knows about. When that person leaves, the mapping leaves with them. A documented, shared dictionary makes the firm's data vocabulary explicit and transferable. It also makes the next onboarding faster, because the new source only needs to be mapped to fields you have already defined, rather than inventing a new schema each time.
Templatize and reuse transformations
Once you have a canonical mapping, the next lever is reusable transformation logic. The instinct at small firms is to build each transformation fresh. A better habit is to write each transformation once, in a way that can be reused.
If you are working in SQL, write your transformations as parameterized views or stored procedures that accept a source identifier, so the same logic can apply to multiple sources. If you are working in a spreadsheet, build a template workbook with the transformation logic in place and copy it for each new source, changing only the input reference. The goal is to stop solving the same problem twice. A case study of a private equity firm found that onboarding a new portfolio company into consolidated reporting took six to eight weeks of analyst time under a manual process, because each company brought its own data schema and required bespoke extraction and reconciliation. The improvement came from building a source-specific adapter that handled the translation once, so downstream logic did not need to change for each new company9.
You do not need new technology to apply that lesson. You need the discipline to write transformations that are modular and reusable rather than one-off. Every transformation you can reuse is a future onboarding you do not have to project-manage.
Put a sunset date on every workaround
The final practice is the one that keeps workarounds from becoming permanent problems. Every makeshift integration should have an explicit owner, a review cadence, and a sunset date.
This is governance stripped to its essentials. The owner is the person responsible for the workaround working. The review cadence is how often someone checks that it still does, say monthly for a critical feed, quarterly for a less urgent one. The sunset date is the point at which the firm decides whether to formalize the integration, replace it, or accept it as permanent. Without a sunset date, a workaround quietly becomes infrastructure. Industry commentary on Hedge Fund operational platforms finds that inadequate systems force teams into manual oversight, and the knowledge of those workarounds often "resides with a limited few," creating key-person risk that is "a material drag on cost, efficiency, and the ability to scale"10.
A sunset date does not solve that risk, but it makes it visible. It forces the conversation about whether the workaround is still good enough, or whether it is time to invest in something more durable.
Without a sunset date, a workaround quietly becomes infrastructure.
When the Workarounds Stop Scaling
Every workaround in this article works, up to a point. The intake template handles the tenth new source as well as the first. The shared data dictionary keeps the mapping consistent. The reusable transformations cut the per-source build time. The sunset dates keep the inventory honest.
However, the underlying math does not change. Each new source still requires someone to map it, stage it, transform it, and maintain it. The workarounds reduce the effort per source, but they do not eliminate the fact that there is effort for each new source. As the source count grows, the maintenance load grows with it, and the person-dependence that made the workarounds fast in the first place, becomes the thing that makes them fragile.
This is the point where a firm has to decide whether it is still in the business of building and maintaining integrations, or whether it wants to get back to the business of investing. Celent's research frames the industry as being at an inflection point, where firms must "re-tune the old and embrace the new simultaneously," adopting pragmatic approaches to evolve technology where it makes sense while leveraging legacy assets where it does not11. The workarounds are the "re-tune the old" part. At some point, a firm ready to grow needs the "embrace the new" part.
That is also where the ambition shifts. The workarounds solve the immediate problem, which is to get the data integrated without a months-long project. However, the larger goal that starts to matter as a firm scales is something different. It is the goal of having all investment data accessible, actionable, and ready for whatever comes next, whether that is AI, a new strategy, or a new allocator asking harder questions.
How Aquata Closes the Gap
This is where Aquata comes in. The workarounds in this article are useful. However, when the maintenance load starts to outweigh the savings, the question becomes whether there is a way to integrate investment data that does not start from scratch for every source.
Aquata's answer is built around exactly that pain. It ships with over 100 pre-built connectors to the sources mid-market firms actually use, including prime brokers, administrators, custodians, counterparties, and vendors. Alongside the connectors, Aquata provides no-code pipeline and transformation blocks that handle the mapping and normalization work without custom code. With Aquata, connecting a new source is a configuration step rather than a build project. The result is a firm can connect to a new source in days, not months, and without an ETL build.
For a mid-market firm, the value is not just speed. It is the removal of the per-source build tax that makes every workaround eventually break down. Aquata's core proposition is straightforward: transform fragmented data into action. The spreadsheets, the manual staging, the one-off transformations, these are all ways of coping with fragmentation. Aquata consolidates that fragmented investment data into a single, standardized layer, so the team spends time on analysis and alpha, not on data wrangling. It does so in a way that scales toward the larger aspiration of making all your investment data accessible, actionable, and AI-ready.
The workarounds in this article are a legitimate way to keep moving when a full integration project is not realistic. Aquata is what you reach for when you are ready to stop building workarounds and start acting on the data instead.
Sources
- 1.Hedge funds admit they spend too much time on spreadsheets, Hedgeweek hedgeweek.com.
- 2.Spreadsheets aren’t strategy: the hidden risks in your legacy fund data workflow, FE fundinfo fefundinfo.com.
- 3.Data Platform and Warehouse Debt Guide, TechDebt.fail techdebt.fail.
- 4.The Terminal Permanence of the 'Quick Fix' and Process Rot, Fill Gun fillgun.com.
- 5.Private Market Spreadsheet Reliance (Risky Business infographic), S&P Global Market Intelligence (formerly IHS Markit) ihsmarkit.com.
- 6.Investment Data Ecosystems: Solutions and Competitive Constellations, Celent celent.com.
- 7.Managing EUDA Risks in Financial Institutions, Protiviti protiviti.com.
- 8.Standardising Data Ingestion for a Leading India-Based VC Firm, Technogise technogise.com.
- 9.High-Throughput Data Engineering for a PE Firm, Contra contra.com.
- 10.Why hedge funds need the right OMS/PMS to scale with confidence, Hedge Funds Club hedgefundsclub.com.
- 11.Embracing NextGen Invest and Risk Tech (Asset Manager Edition), Celent celent.com.
