One SQL query across multiple sources

Data Virtualization / Federated Queries / Enterprise Integration

Why Physical Integration Isn’t Always the Best Solution

For many years, the development of enterprise analytics has been closely linked to the creation of centralized data warehouses.

Companies used ETL to transfer information from ERP, CRM, electronic document management systems, and other sources to the Data Warehouse. This approach allows for the creation of consistent analytical models, control over data quality, and reduction of the load on operational systems during report generation.

However, it is not always optimal for every information task.

For example, an industrial holding company has several ERPs, a centralized CRM, and a corporate DWH.

The finance department generates regulated reports monthly. A proven warehouse with relevant historical data is used for this.

At the same time, the commercial department wants to create a new operational report that combines current CRM data with information on product availability in the warehouse system. For such a task, it is not necessary to create a new large ETL process.

It may be possible to obtain the necessary information directly from multiple sources through an appropriate logical layer. Another example is a company that wants to temporarily combine data from two systems in preparation for a migration.

Creating a separate DWH solely for such a short-term task may be economically unfeasible.

In these situations, Data Virtualization is a promising approach – creating a logical representation of information from independent sources without necessarily physically transferring all data to a single repository beforehand. It is important to correctly understand its capabilities.

Data Virtualization does not mean that data is never copied. Intermediate results may be created during query execution, or caching or materialization may be used.

The main difference is that prior full centralization is not a mandatory condition for every query.

For enterprises, this creates additional flexibility, but also imposes new requirements on performance, security, and management of information dependencies.

Data Federation vs. Data Virtualization: What’s the Difference

The terms Data Federation and Data Virtualization are often used together, but their meanings are not entirely identical.

Data Federation

Describes the ability to query multiple independent sources through a common interface.

For example, a single SQL query can read tables from PostgreSQL and Hadoop.

Data Virtualization

Typically encompasses a broader model.

In addition to federated access, it can include logical views, metadata management, access control, query optimization, and corresponding caching mechanisms.

Data Integration

Is a broader concept that includes ETL, ELT, APIs, CDC, stream integration, and other methods of information exchange.

Data Warehouse

Provides physical storage of prepared data for defined analytical tasks.

For most large enterprises, these approaches can complement each other.

For example, official financial reporting may remain in the DWH, while separate operational analytical queries will use federated access.

Federated Access Architecture

Consider an enterprise with multiple independent sources. ERP uses SQL Server.

CRM runs on PostgreSQL. Historical web events are stored in Hadoop Data Lake.

A suitable federated SQL engine can be used to create a new analytical view.

• SQL Server
• ERP
• PostgreSQL
• CRM
• Hadoop / Hive
• Data Lake

Connectors to sources

Metadata · authentication · access · restrictions

Federated SQL / Data Virtualization

Logical views · query planning · joins

Enterprise semantic layer

Business definitions · MDM · access rules

BI / Analytical applications

Unified logical access to defined resources

Conceptual architecture. Support for each specific source depends on connectors and the chosen platform version. Unlike traditional ETL, information does not necessarily have to pre-exist in a centralized table.

A query can access relevant sources during execution. However, it is important to consider that the corresponding operations create a load on the network and initial systems.

This is why a federated architecture requires careful selection of scenarios.

Presto in August 2019

One of the most interesting open-source tools is Presto, a distributed SQL engine for analytical queries on large datasets. It originated at Facebook and was further developed as an open-source project.

In January 2019, some of the core developers organized an independent development of PrestoSQL. In August 2019, it is important to distinguish this direction from PrestoDB.

The name Trino will appear much later, in December 2020, so it should not be used as the product name in an article dated August 2019. For this publication, we will consider PrestoDB.

As of August 13, 2019, PrestoDB version 0.224 is already available, and version 0.223.1 was released at the beginning of the month.

Main Architecture

Presto uses a coordinator and workers. The coordinator accepts an SQL query, forms the corresponding plan, and coordinates execution.

Workers perform distributed processing.

Connectors

Appropriate connectors are used for different systems.

For example, Hive, PostgreSQL, MySQL, and other supported sources.

Federated Queries

Presto can execute SQL queries that access multiple catalogs.

In-Memory Processing

Worker computational resources are used to perform relevant operations. However, this does not mean that any query is guaranteed to fit into memory.

Large JOINs and other operations may require significant resources. Therefore, it is necessary to evaluate the actual execution plan.

Practical SQL Example of a Federated Query

Suppose a company has a customer directory in PostgreSQL and prepared sales in Hive. Presto can provide access to them through appropriate connectors.

A sample query:

SELECT
    c.customer_id,
    c.customer_name,
    SUM(s.net_amount) AS total_sales
FROM postgresql.crm.customers AS c
JOIN hive.analytics.fact_sales AS s
    ON c.customer_id = s.customer_id
WHERE s.sales_year = 2019
GROUP BY
    c.customer_id,
    c.customer_name;

This example assumes that the relevant catalogs and tables are configured, and sales_year exists in the physical model.

Its purpose is to demonstrate federated SQL, not a universal ready-made query for any infrastructure.

What happens during execution

Presto receives information about the table structure. After that, it plans the reading of necessary data and corresponding join operations.

Depending on the capabilities of the connectors, some filters may be applied closer to the source. The rest of the calculations are performed in a distributed environment.

Main risk

If PostgreSQL contains millions of customer records and Hive contains billions of sales, a bad plan may require transferring a significant amount of information. Therefore, it is necessary to analyze which filters are actually applied at the source side.

It should not be assumed that all Presto connectors support the same optimization capabilities.

Predicate Pushdown: How to Reduce Transferred Data Volume

One of the important principles of federated queries is Predicate Pushdown. Its essence lies in the ability to perform part of the filtering directly at the initial source.

For example:

SELECT
    customer_id,
    amount
FROM postgresql.crm.orders
WHERE order_date >= DATE '2019-08-01';

If the relevant connector supports pushing this filter down to PostgreSQL, the engine can retrieve only the necessary rows. This reduces network traffic.

However, if the filter is not pushed down, the system may read a larger volume of data and perform filtering later.

Design

For each critical query, the actual plan must be checked.

Connectors

Pushdown support depends on the source and version.

Performance

Even correct pushdown does not eliminate all costs.

For example, a large join of two distributed sets can remain expensive.

Indexes

For operational DBMS, it is important to correctly use available indexes.

Caching

For frequently used resources, separate caching or materialization may be appropriate.

Therefore, federation should not become an excuse for uncontrolled execution of heavy SQL queries against a productive ERP.

When Data Virtualization May Be Better Than ETL

It is advisable for the enterprise to define task classes.

Operational Information Views

For example, it is necessary to combine the current state of CRM with product availability.

If a query is executed rarely and has a limited scope, federated access may be convenient.

Temporary Integration

For example, when merging two companies, it is necessary to create a common directory representation.

Full physical consolidation can occur later.

Exploratory Analytics

An analyst can test a hypothesis on data from multiple sources without creating a separate long-term ETL.

Cataloging and Discovery

Logical access can help find the required datasets.

However, for regulated reporting with large volumes and strict performance requirements, a physical DWH often remains more appropriate.

There is no universal winner.

The right solution depends on the specific business scenario.

The main problem of federation is the lack of a single historical state

Let’s consider a query that combines CRM and ERP data.

CRM is updated at 14:00.

ERP at 14:02.

The federated query starts at 14:01 and executes over a period of several minutes. As a result, different pieces of information may be read from different logical points in time.

This creates a risk of an inconsistent result.

Snapshot Consistency

It is necessary to establish whether a particular architecture can provide an appropriate consistent state.

Transaction Boundaries

A local transaction of a single source does not automatically create a global transaction across multiple independent DBMS.

Analytical Usage

For operational research, a certain time discrepancy may be acceptable.

Financial Reporting

For critical indicators, a fixed and verified set may be required.

Therefore, Data Virtualization should not be automatically used for every regulated financial transaction.

In such cases, a physical DWH with controlled loading can provide better reproducibility.

Federation and MDM: Why Identical Names Do Not Mean Identical Objects

Another problem is corporate identification.

For example, CRM uses customer code C-00184.

ERP uses 000184. A federated JOIN cannot independently establish that it concerns the same counterparty.

An appropriate Master Data Management model must be used.

Corporate ID

Agreed business object identifier.

Mapping

Local key correspondence.

Golden Record

Confirmed representation of core characteristics.

Historicity

Changes in corporate correspondences must be considered.

Data Quality

Ambiguous results must undergo appropriate verification.

For example, two enterprises may have similar names but be different legal entities. Therefore, records cannot be merged solely based on textual similarity.

In a federated architecture, MDM can remain a separate controlled system.

Its information is used to correctly connect sources.

Data Virtualization and Business Glossary

Effective use of federated queries requires more than just technical access.

For example, an enterprise creates a logical representation of ActiveCustomers.

However, it is necessary to define what activity means.

Perhaps it refers to customers with an active contract.

Or to those who have placed an order in the last 90 days. These metrics can vary.

Therefore, it is necessary to use the Business Glossary.

Business Definition

Describes the content of the term.

Data Owner

Approves the methodology.

Data Catalog

Documents physical sources and logical representations.

Data Lineage

Shows relevant dependencies.

Certified Status

Under defined conditions, the logical representation can be approved for a specific use.

However, such a status does not guarantee that the physical sources are always available.

Therefore, for federated analytics, reliability must be controlled separately.

Information Security in a Federated Architecture

One of the most important tasks is access control.

For example, an analyst gains access to a logical representation that uses client data from the CRM.

It is necessary to determine whether they can see all records or only the relevant part.

Authentication

The federated platform must correctly identify the user or technical service.

Authorization

Appropriate access rules must be ensured.

Source Permissions

Connections to PostgreSQL or SQL Server should not have excessively broad privileges.

Data Transfer

Appropriate security measures are applied to relevant channels.

Caching

If temporary copies are used, their access rights and retention periods must be defined.

Logging

Critical operations must be monitored without excessive accumulation of sensitive records.

After the GDPR comes into effect, this is particularly relevant for processes involving personal data.

It is important that the physical absence of a new permanent copy does not imply the automatic legitimacy of processing. Even a federated query can receive, transmit, and use personal information.

Therefore, its purpose and legal basis must be taken into account.

Caching and Materialization: A Trade-off Between Speed and Freshness

One practical solution is to use a cache.

For example, a company repeatedly executes the same query to the product catalog daily.

Instead of constantly reading the source, corresponding materialization can be considered.

Advantages

The load on the source is reduced.

The time to obtain the result is shortened.

Risks

The cache can become outdated.

Update rules need to be defined.

Data Freshness

Each materialized set requires a corresponding freshness characteristic.

Invalidation

It is necessary to determine when the cache becomes invalid.

Data Governance

Even a temporary copy must have a clear purpose.

Thus, Data Virtualization does not preclude physical storage.

For certain tasks, a hybrid model may be optimal, where part of the data is read directly, and another part is read from prepared datasets.

Practical Scenario: Federated Analytics of an Industrial Holding

Consider a hypothetical holding company with three ERP systems, a central CRM, a Hadoop Data Lake, and a corporate DWH.

The enterprise wants to create an operational view of customer orders and product availability.

Stage 1. Business Goal

It is determined which decisions the analytics should support.

Stage 2. Source Inventory

The team identifies the necessary tables and responsible parties.

Stage 3. Data Catalog

Information resources are documented.

Stage 4. MDM

Corporate customer and product identifiers are verified.

Stage 5. Platform Selection

Presto or other appropriate federated access tool is evaluated.

Stage 6. Connectors

Connections to supported sources are configured.

Stage 7. Logical Model

Defined analytical representations are created.

Stage 8. SQL Testing

Correctness of JOIN and other transformations is checked.

Stage 9. Performance Testing

The load on the initial systems is assessed.

Stage 10. Access Control

Appropriate access rules are established.

Stage 11. Freshness Monitoring

The status of the sources is monitored.

Stage 12. Comparison with ETL

For critical scenarios, it is assessed whether physical loading would be more cost-effective.

Stage 13. Productive Implementation

The result becomes available to the relevant users.

Stage 14. Continuous Monitoring

The company analyzes performance, errors, and actual usage.

The main thing is not to turn the federated platform into an uncontrolled way to execute arbitrary heavy queries to productive ERP.

KPI Data Virtualization

It is important for an enterprise to consider all characteristics. For example, the absence of a new physical table can reduce storage costs.

However, if a federated query regularly overloads the ERP, the total cost of the solution may turn out to be higher.

Therefore, Data Virtualization needs to be evaluated based on real workloads.

Intecracy Group’s Competencies in Data Virtualization

Building a federated information architecture requires the integration of several technological areas. Data Management IG (dmig.com.ua) specializes in ETL, MDM, Data Quality, DWH, and corporate analytics. These competencies are important for designing logical information models, aligning corporate identifiers, and determining the feasibility of federation compared to physical loading.

IQusion IT (iqusion.com) works in the areas of system integration, electronic document management, and BPM. These areas can be used to connect operational sources, align information contracts, and organize corporate access procedures. Softline IT (softline.kiev.ua) has competencies in server, network infrastructure, and storage systems. They are important for ensuring the performance of federated SQL engines, network availability, and the stability of the respective DBMS.

SL Global Service (sgs4business.com) specializes in cloud integration, virtualization, and hybrid infrastructure. Such competencies can be used to organize logical access to information resources distributed between local and cloud environments.

The choice of architecture should be determined by specific business objectives, performance requirements, and a corresponding economic evaluation.

Practical Implementation Guidelines

Data Virtualization and Federated SQL create additional opportunities for enterprise integration. They allow the use of logical views that combine information from multiple independent sources without the mandatory prior creation of a complete physical copy of all data.

Presto and corresponding commercial virtualization platforms can be used for such scenarios. However, federated access does not eliminate the need for ETL, DWH, and Data Governance.

For regulated reporting, historicity, reproducibility, and control reconciliations may remain critical. Federated queries must consider network limitations, source performance, connector support, and the correctness of information contracts.

MDM, Business Glossary, and Data Catalog play a special role.

The main principle of Data Virtualization is that the enterprise should have unified logical access to the necessary data without creating redundant physical copies where it is not beneficial, but maintaining correctness, access control, and predictable performance of corporate analytics.