1. Introduction: The growing complexity of enterprise data access
Enterprise data has never been more distributed.
Customer information may reside in a CRM platform. Financial transactions are maintained in core banking or ERP systems. Orders and inventory are managed by operational applications. Documents are stored in content repositories. Business processes span multiple SaaS platforms, databases, APIs and cloud services.
This distribution is a natural consequence of enterprise technology evolution. Organisations adopt specialised applications to address business needs, modernise individual systems at different times, acquire companies with different technology stacks, and increasingly operate across hybrid and multi-cloud environments.
However, the business rarely sees these systems as independent entities.
A relationship manager wants a consolidated view of a customer. A service representative needs to see order history, payment status and support interactions together. A risk management application needs to correlate information from multiple systems. An enterprise AI assistant needs access to relevant business context that may be spread across databases, APIs and document repositories.
The business requirement is unified access to information. The underlying technology landscape, however, remains fragmented.
Traditionally, enterprises have addressed this problem by moving data from source systems into centralised repositories using ETL and ELT pipelines. These pipelines extract information, transform it into a common structure and load it into data warehouses, data lakes or other storage platforms.
This approach remains essential for many workloads, particularly historical reporting, large-scale analytics, data science and machine learning.
But it also raises an important architectural question:
Do we need to move and replicate data every time an application or user needs to access information across multiple systems?
For many operational and interactive data-access requirements, the answer is no.
Data federation provides an alternative. It allows data to remain in its original systems while a unified access layer connects to those systems, presents a logical view of the information and executes queries across them.
The opportunity is not to eliminate data movement. It is to make data movement a deliberate architectural decision rather than the automatic response to every data-access requirement.
2. Why enterprises move data in the first place
Before examining data federation, it is important to understand why data movement became such a fundamental part of enterprise architecture.
Different business systems maintain information in structures optimised for their own applications. They use different databases, schemas, formats, APIs and access mechanisms.
Combining information from these systems for reporting and analysis requires a mechanism to collect, transform and consolidate it.
ETL and ELT are two widely used approaches.
- ETL (Extract, Transform, Load): Data is extracted from source systems, transformed into the required structure and then loaded into a target repository.
- ELT (Extract, Load, Transform): Data is first loaded into the target platform and transformations are performed there, taking advantage of its processing capabilities.
These approaches commonly feed centralised analytical repositories.
2.1 The traditional data integration architecture
A typical enterprise data integration architecture looks like this:
Data lakes, data warehouses and lakehouses serve different purposes, although their capabilities increasingly overlap.
A data lake can retain raw data and support subsequent processing. Curated zones may contain cleansed, transformed and structured information. A data warehouse provides a managed environment optimised for analytical querying and reporting. A lakehouse combines aspects of data lake storage with capabilities required for analytical workloads.
Query engines such as Amazon Athena and Trino can execute SQL directly against supported data stored in data lakes. Apache Spark and platforms such as Databricks support distributed data processing, transformations, analytics and machine learning.
The important point is that these technologies primarily address analytical data processing and the management of analytical datasets. They do not automatically solve every operational data-access requirement.
2.2 Where traditional data integration becomes expensive
Data integration is not inherently problematic. It becomes an architectural burden when pipelines are created for requirements that could be served more directly.
Several challenges emerge as the number of systems and data consumers increases.
Data duplication
The same business information may be copied into multiple repositories to support different applications, reports and integrations. Over time, organisations must manage multiple representations of the same data.
Data freshness
Once data is copied, the target repository reflects the source only as recently as the last successful extraction, transformation and loading cycle. Streaming and change-data-capture mechanisms can reduce this delay, but they introduce additional infrastructure and operational requirements.
Pipeline proliferation
Every new consumer or data requirement can result in additional pipelines, transformations, mappings and dependencies. Maintaining these becomes increasingly difficult as the enterprise grows.
Integration complexity
Different systems expose different data structures, APIs and semantics. Transformations and reconciliation logic must be maintained as source systems evolve.
Governance and security overhead
Data replicated across additional locations creates additional copies that must be protected, governed, audited and retained appropriately.
Infrastructure and operational cost
Storage, processing, orchestration, monitoring and maintenance costs accumulate across the integration landscape.
The issue is not that data integration is the wrong approach. It is that organisations sometimes use it as the default solution even when the actual requirement is simply to access information across systems.
3. Not every data-access problem is an analytics problem
This is perhaps the most important distinction in modern enterprise data architecture.
Analytical processing and operational data access may use the same underlying business information, but they have fundamentally different requirements.
Consider two scenarios in a financial services organisation.
Scenario A: Historical analytical reporting
A business analyst wants to understand customer transaction patterns over the last five years, identify trends across regions and evaluate changes in product performance.
This requires access to large volumes of historical data, extensive filtering, aggregation, transformation and analytical processing.
A data warehouse, lakehouse or data lake supported by an analytical query engine is well suited to this requirement.
Scenario B: An operational customer view
A relationship manager opens a customer profile and expects to see the customer's current account balances, recent transactions, active loans, credit exposure, service requests and relevant risk information.
The information is distributed across several operational systems.
The application needs to retrieve a relatively small, relevant subset of information, ideally within a few seconds, using the latest information available from the respective sources.
Building and maintaining a separate data pipeline for every such application requirement may introduce unnecessary duplication and freshness challenges.
This is where data federation becomes relevant.
3.1 Analytical processing versus operational data access
| Dimension | Analytical processing | Operational data access |
|---|---|---|
| Primary purpose | Discover patterns and insights | Support business operations |
| Typical data volume | Large datasets | Selective subsets |
| Query characteristics | Scans, joins and aggregations | Lookups and selective joins |
| Performance objective | Throughput and analytical efficiency | Responsive application interaction |
| Data freshness | Depends on ingestion and refresh strategy | Can query current source data |
| Typical consumers | Analysts, BI, data scientists | Applications, business users, services |
| Common technologies | Warehouses, lakes, Spark, Athena, Databricks | Operational databases, APIs, caches, federation |
These are architectural tendencies, not absolute rules. Modern analytical engines can support interactive queries, and operational systems can perform complex analytical work. The distinction is about workload characteristics and service expectations.
The architectural mistake is not using a data warehouse. It is assuming that every requirement to access information across multiple systems must first be converted into an analytical data integration problem.
4. What is data federation?
Data federation provides a unified access layer across multiple heterogeneous data sources without requiring all the underlying data to be physically consolidated first.
A federation platform connects to different systems, understands their structures and capabilities, and exposes their data through a common logical representation.
Applications and users can then access information through a consistent interface without having to understand the specific connection and query mechanisms of every underlying source.
This is often associated with data virtualization, although the terms are not always used identically.
Data virtualization generally emphasises the logical abstraction of data across sources. Data federation additionally emphasises the coordination and execution of queries across those sources.
A federation platform typically provides several capabilities:
- Source connectivity: Connect to databases, files, APIs and other supported systems.
- Metadata discovery: Understand available tables, fields, schemas and source capabilities.
- Logical data abstraction: Present source information as virtual tables or other consistent logical entities.
- Query planning: Determine how a query should be decomposed and executed across multiple sources.
- Query optimisation: Push suitable operations down to source systems and minimise unnecessary data retrieval.
- Result processing: Combine and return the results through a unified interface.
- Security and governance: Apply appropriate access controls and auditing across the federated access layer.
4.1 A federated data access architecture
The federation layer does not necessarily store the underlying business datasets. Instead, it maintains the metadata, logical definitions, connection information, policies and execution capabilities required to access those datasets.
This distinction is important: the platform provides a unified way to access distributed information without making centralised data storage a prerequisite.
5. How query pushdown makes data federation practical
One of the most important technical mechanisms in data federation is query pushdown.
Without query pushdown, a federation engine could retrieve large volumes of data from multiple source systems and perform much of the filtering, projection and aggregation itself.
That would introduce unnecessary network traffic, increase processing requirements within the federation layer and potentially affect query performance.
Query pushdown addresses this by executing suitable parts of a query as close to the data as possible.
Instead of transferring large datasets to the federation engine for processing, the engine translates and delegates supported operations to the underlying data sources.
The source system performs the operations it can execute efficiently and returns a smaller result set.
5.1 A simple example
Imagine an enterprise has the following systems:
- A customer database containing millions of customer records.
- A transaction database containing hundreds of millions of transactions.
- A loan management system containing active loan records.
An application needs to retrieve the recent transactions and active loans for a specific customer.
A simplified logical query might look like this:
SELECT
c.customer_id,
c.customer_name,
t.transaction_date,
t.amount,
l.loan_account,
l.outstanding_balance
FROM virtual_customers c
LEFT JOIN virtual_transactions t
ON c.customer_id = t.customer_id
LEFT JOIN virtual_loans l
ON c.customer_id = l.customer_id
WHERE c.customer_id = 100245
AND t.transaction_date >= CURRENT_DATE - INTERVAL '30 days';
The virtual tables represent data in different underlying systems.
A naïve implementation could retrieve millions of customer, transaction and loan records into the federation engine and then perform the filtering and joins centrally.
A more efficient federation engine analyses the query and determines which operations can be pushed down.
For example:
- The customer database receives a lookup for customer
100245. - The transaction database receives a request filtered by customer ID and transaction date.
- The loan system receives a request filtered by customer ID and, if supported, active-loan status.
- The federation engine combines the returned results where the joins cannot be delegated to a common source.
The result is that the federation layer transfers only the relevant information required to produce the response, rather than retrieving entire datasets.
5.2 How the query pushdown process works
5.3 What operations can be pushed down?
The operations supported depend on the underlying data source and its connector.
Common examples include:
Predicate pushdown
Filters are executed at the source.
For example:
WHERE customer_id = 100245
Instead of retrieving all customer records and filtering locally, the federation engine requests only the relevant records from the source.
Projection pushdown
Only required columns are retrieved.
For example, if the application needs customer ID and account balance, there is no reason to retrieve every column in the account table.
Aggregation pushdown
Operations such as COUNT, SUM, AVG, MIN and MAX can be executed by the source when supported and semantically safe.
This avoids transferring large numbers of individual records when the application needs only an aggregate.
Join pushdown
When the relevant data resides within the same source, or the source supports the required operation, joins can be delegated to that source.
Cross-source joins generally require additional planning. A federation engine may execute part of the join remotely and combine the results itself, or use another suitable execution strategy.
Limit and ordering pushdown
Where query semantics permit, operations such as LIMIT and ORDER BY can be delegated to the source to reduce the volume of returned data.
5.4 Cross-source query execution
Not all operations can be pushed down.
For example, a customer table may reside in PostgreSQL, transaction records in Oracle and loan information behind a REST API.
The federation engine cannot necessarily ask any one of these sources to execute a join across all three systems.
Instead, it can:
- Push source-specific filters and projections to each system.
- Retrieve the reduced result sets.
- Perform the required cross-source joins or other operations within the federation execution layer.
- Return the consolidated result.
The objective is not to push everything down at any cost. It is to determine the most efficient execution strategy based on the capabilities of each source, the query and the expected data volume.
5.5 Query pushdown is not a guarantee of low latency
Query pushdown is an important optimisation mechanism, but it does not automatically guarantee that every federated query will be fast.
Performance depends on several factors:
- The response time and capacity of the underlying systems.
- Network latency between the federation engine and the sources.
- The selectivity of filters and the volume of matching records.
- The availability of appropriate indexes and source-side optimisations.
- The complexity of cross-source joins and aggregations.
- Concurrent query load and source-system resource utilisation.
- API rate limits and the response characteristics of external services.
A well-designed federation platform must therefore provide query planning, execution controls, observability and appropriate mechanisms for handling expensive or long-running operations.
Caching and selective materialisation may also be useful for frequently accessed or computationally expensive queries. Federation does not mean that every request must always be executed against every source from scratch.
6. Data federation vs ETL vs ELT: Choosing the right approach
The choice between data federation, ETL and ELT should be based on the workload rather than a preference for one technology.
| Consideration | ETL / ELT | Data Federation |
|---|---|---|
| Data location | Data is copied to a target | Data generally remains at source |
| Primary purpose | Consolidation and transformation | Unified access across sources |
| Historical analysis | Well suited | Possible, but not its primary purpose |
| Operational data access | Depends on refresh or streaming strategy | Can query sources when requested |
| Data freshness | Depends on ingestion and refresh | Can reflect source data at query time |
| Data transformation | Extensive transformation pipelines | Logical transformations and query-time operations |
| Cross-system access | Through consolidated datasets | Through virtualised and federated queries |
| Data duplication | Usually creates additional copies | Can reduce unnecessary replication |
| Large-scale processing | Well suited to dedicated analytical platforms | Depends on query complexity and source capabilities |
When should enterprises move data?
ETL and ELT remain appropriate when:
- Historical data must be retained independently of operational systems.
- Data must be cleansed, standardised or transformed extensively.
- Large-scale aggregations and analytical processing are required.
- Source systems cannot support the required query workload.
- Data must be made available to downstream systems in a stable, curated form.
- The business requires a durable analytical dataset or a point-in-time snapshot.
When should enterprises consider federation?
Data federation is worth considering when:
- Applications need information from multiple existing systems.
- Business users require a consolidated view of distributed information.
- Data freshness is important.
- Creating and maintaining another pipeline would add unnecessary complexity.
- Data must be accessed without routinely replicating it.
- Different consumers need a common access interface over heterogeneous sources.
Can both approaches coexist?
Absolutely.
In many enterprises, the most effective architecture uses both.
ETL and ELT support analytical repositories and large-scale processing. Data federation supports operational and interactive access across distributed systems.
The objective is to select the right mechanism for each workload rather than forcing every requirement through one architectural pattern.
7. Security and governance in a federated architecture
A common concern about data federation is whether information can be accessed securely when it remains distributed across multiple systems.
The fact that data remains at its original location does not automatically make it secure. A federation platform must provide appropriate security and governance mechanisms, while respecting the controls of the underlying sources.
Important capabilities include:
Authentication and authorisation
Applications and users must be authenticated, and their permissions must determine which data and operations they can access.
Object-level access control
Access to virtual tables, source objects and other logical entities should be governed through explicit permissions.
Row-level security
Users may be allowed to access only records relevant to their organisation, business unit, geography or role.
Column-level security
Sensitive fields such as personal identifiers, financial details and confidential attributes may need to be restricted or masked.
Operation-level permissions
Access policies should distinguish between operations such as reading, inserting, updating and deleting data, where those operations are supported.
Auditability
The platform should provide visibility into who accessed which data, when it was accessed and through which application or interface.
Source-level security
The federation layer should work with the authentication and authorisation capabilities of underlying systems rather than assuming that a single central policy automatically replaces source-level controls.
Federation can reduce unnecessary data duplication, but governance must extend across the complete data-access path.
8. Data federation and enterprise AI
Enterprise AI applications and agents need access to relevant business context.
That context is rarely confined to one database. It may be spread across customer systems, financial applications, operational databases, REST services, documents and vector stores.
A common approach is to extract information from these systems and consolidate it into a central repository or vector database for retrieval.
This is appropriate for many use cases, particularly document search, knowledge retrieval and applications where indexed representations are useful.
However, not every AI requirement needs a new copy of every dataset.
Consider an AI assistant supporting a customer service representative. It may need to retrieve:
- Customer profile information from CRM.
- Current account status from an operational database.
- Recent transactions from a transaction system.
- Open service requests from a support platform.
- Relevant policy documents from a document repository.
A governed federation layer can provide a common access mechanism to supported structured sources and services, allowing the AI application to retrieve relevant information without requiring all structured business data to be consolidated first.
8.1 Federation as part of the enterprise AI architecture
This architecture does not mean that an LLM should receive unrestricted access to enterprise data. The AI application must operate through controlled tools and interfaces, enforce user permissions and retrieve only the information needed for a particular task.
It is also important to distinguish between different AI workloads:
- Live structured-data access.
- Document retrieval and RAG.
- Vector similarity search.
- Historical analytical queries.
- Model training and large-scale data preparation.
Each may require a different combination of technologies. Federation can provide an important part of the architecture, but it is not a replacement for every data engineering or AI infrastructure component.
9. What a modern enterprise data architecture should look like
The discussion should not be framed as a choice between a centralised data architecture and a federated one.
Enterprises need both analytical and operational data capabilities.
The architectural objective should be to support each workload through the mechanism best suited to its requirements.
9.1 A dual-path data architecture
This model allows organisations to retain the benefits of centralised analytical platforms while providing a more direct mechanism for accessing information across distributed operational systems.
It also avoids making data replication the mandatory first step for every new application or data consumer.
10. Introducing Kiwi Fabric: A unified approach to distributed data access
Kiwi Fabric is designed to address the challenge of accessing and working with enterprise data distributed across heterogeneous systems.
Rather than requiring all data to be moved into a central repository, Kiwi Fabric provides a federation layer through which supported data sources can be accessed using a consistent logical model and unified interfaces.
Its capabilities include:
- Connectivity across supported relational databases, files, REST services and vector data sources.
- Virtual tables that provide logical representations of underlying data.
- Federated query execution across distributed sources.
- Query planning and pushdown optimisation to reduce unnecessary data retrieval.
- PostgreSQL wire protocol, REST and Apache Arrow Flight interfaces.
- SDKs for Java, C#, Python and Node.js, along with the Kiwi Unified CLI.
- Fine-grained access controls across objects, rows, columns and operations.
- AI-assisted generation of queries, virtual tables and SAGA workflows.
The objective is to make distributed data easier to access and use without forcing applications to manage the complexity of every underlying source.
For developers, this means working through consistent interfaces rather than implementing and maintaining separate connectivity and data-access logic for every system.
For enterprise architects, it provides another architectural option for meeting data-access requirements without automatically introducing additional pipelines and copies.
For organisations adopting AI, it can provide a governed access layer across supported enterprise data sources.
Kiwi Fabric is not intended to replace every ETL platform, data warehouse, lakehouse or analytical query engine. Those technologies remain important for their respective workloads.
Its purpose is to complement the existing enterprise data landscape by addressing a different problem: unified access to distributed data.
11. Choosing the right architecture: A practical decision framework
Before introducing another data pipeline or repository, architects should ask a few fundamental questions.
| Question | Architectural consideration |
|---|---|
| Is the requirement primarily historical analysis? | Consider ETL/ELT and an analytical repository. |
| Does the workload require extensive transformation? | Consider a dedicated data engineering pipeline. |
| Does an application need current information from multiple systems? | Evaluate data federation. |
| Can the source systems support the expected query workload? | Assess source capacity, indexing and concurrency. |
| Can filters, projections and aggregations be pushed down? | Evaluate connector and source capabilities. |
| Is the same information repeatedly requested? | Consider caching or selective materialisation. |
| Does the requirement involve complex cross-source joins? | Evaluate query planning, data movement and execution costs. |
| Are there strict security or data residency constraints? | Assess source-level and federation-level governance. |
| Is the requirement suitable for a stable, curated analytical dataset? | Consider a warehouse or lakehouse. |
The best architecture is rarely determined by one technology or design philosophy. It emerges from understanding the nature of the workload, the characteristics of the underlying systems and the operational requirements of the business.
12. Conclusion: Should data movement really be the default?
Enterprise data will continue to be distributed.
Applications will evolve independently. Organisations will adopt new platforms, integrate acquired businesses, modernise legacy systems and operate across increasingly diverse infrastructure environments.
Centralised analytical repositories will remain essential. ETL and ELT will continue to play a critical role in data transformation, consolidation, historical analysis and machine learning.
But these technologies should not automatically become the answer to every requirement involving data across multiple systems.
When an application needs a current view of information distributed across enterprise systems, the first question should not always be how to extract, transform and load that information into another repository.
It should also be:
Can we access and use this data effectively without moving it at all?
Data federation makes that architectural choice possible for suitable workloads. With effective query optimisation, pushdown, security and governance, it can provide a practical way to access distributed enterprise information while reducing unnecessary data movement and integration complexity.
The future of enterprise data architecture is not about choosing between centralisation and federation.
It is about knowing when to use each.
About the Author
Ajay Jain is the Founder and Managing Director of Kiwify Labs, with 30 years of experience in enterprise technology, digital transformation, cloud modernisation and distributed systems. His experience includes leadership roles at Deloitte, ITC Infotech, Tech Mahindra and Dell.
About Kiwify Labs
Kiwify Labs is a deep-technology product company focused on distributed systems and developer platforms. Its Kiwi product portfolio addresses challenges across enterprise data federation, workflow orchestration and distributed execution.
Website: https://kiwifylabs.com
Frequently Asked Questions
What is data federation?
Data federation is an approach that provides unified access to data distributed across multiple systems without requiring all the underlying data to be physically consolidated. A federation engine connects to the sources, creates a logical view and coordinates queries across them.
What is the difference between data federation and data virtualization?
Data virtualization focuses on presenting distributed data through a unified logical abstraction. Data federation commonly includes the query planning and execution mechanisms used to retrieve and combine data from multiple sources. The terms overlap and are sometimes used interchangeably.
Is data federation better than ETL?
Neither is universally better. ETL is appropriate for data consolidation, transformation and analytical processing. Federation is useful when applications or users need unified access to distributed data without first creating additional copies. Many enterprise architectures benefit from both.
What is query pushdown in data federation?
Query pushdown is an optimisation technique in which a federation engine delegates supported query operations, such as filtering, selecting columns and aggregating data, to the underlying source systems. This reduces the amount of data transferred to the federation layer.
Does data federation provide real-time data?
Federation can query supported sources when a request is made, allowing results to reflect the data available at those sources at query time. Actual freshness depends on the source systems and their own data-update mechanisms. Federation does not guarantee that every source is synchronously updated.
Is data federation suitable for large datasets?
It can be, depending on the workload, source capabilities, query complexity and optimisation strategy. Query pushdown, selective retrieval and parallel execution are important. Large-scale historical processing and extensive transformations may still be better suited to analytical platforms.
Can data federation replace a data warehouse?
No. A data warehouse remains valuable for historical analysis, curated datasets, complex transformations and analytical workloads. Federation complements it by providing access to distributed data without requiring every use case to depend on centralised storage.
How does data federation support enterprise AI?
A federation layer can provide governed access to structured information across databases, APIs and other supported sources. AI applications can use that access to retrieve relevant enterprise context. Document retrieval, vector search, model training and live structured-data queries may still require different supporting technologies.