Two databases can be connected successfully while still disagreeing about what their records mean. One system’s customer may be a billing account, another’s may be a delivery location, and both may contain a field called status. A query that combines them can return a tidy result without resolving those differences.
Data federation provides a way to query information across sources through a combined access layer, often without first consolidating every record into a central store. It can make distributed information more accessible, but access and integration are separate achievements.
A useful federated design addresses semantics, performance, freshness and failure together. The aim is to establish which combined questions can be answered reliably, which qualifications readers need and which workloads deserve a different arrangement.
Separate location transparency from shared meaning
A federated interface can hide where data resides. A reader may see familiar tables or views while the system sends work to remote sources. This simplifies access, particularly when the underlying systems remain independently owned.
Hiding location does not make definitions identical. A field named order_date may represent creation time, customer confirmation or formal acceptance. Joining and comparing values without reconciling those meanings can create false delays or misleading performance measures.
This is an illustrative example. A sales system records an order when a quotation is accepted. A production system creates its corresponding order only after engineering review. Subtracting the dates may measure the review interval, rather than a delay in transferring records.
Write the intended business question before choosing fields. “How long between acceptance and production release?” is precise enough to guide mapping. “Compare order dates” leaves the interpretation unresolved.
Maintain a definition for the combined result, including the source meaning of each important attribute. The federated layer should make those meanings clearer, even if it hides the physical network arrangement.
Establish the identity being joined
Identifiers often have meaning only within their source. Customer 104 in one application need not be the same customer as 104 in another. A shared data type and matching value do not establish shared identity.
Use an explicit cross-reference where source identifiers differ. Record the source system, source identifier and corresponding shared identity, together with any effective dates or mapping qualifications required by the business process.
Mapping may be one-to-many or many-to-one. A corporate customer can have several billing accounts and many delivery sites. Decide which level belongs in the question before joining records across those levels.
This is an illustrative example. A service database records visits against equipment locations, while an invoicing database records charges against billing accounts. Joining through a corporate customer can associate every visit with every invoice for that customer unless the relationship is narrowed to the intended grain.
Test for unmapped and ambiguously mapped records. An inner join can silently remove unmatched data, making the result look cleaner and more complete than it is. Preserve exception counts and choose a deliberate treatment for those records.
Reconcile values as well as field names
Renaming two columns to a common label does not harmonise their values. Status codes, units, timezones and classification schemes can differ even where the apparent subject is the same.
A status called closed may mean completed in one system and administratively archived in another. A quantity may describe individual pieces, packs or kilograms. A date may lack a timezone or represent a local business day rather than an instant.
Create mappings based on documented meaning. Some source states have no exact counterpart, in which case an explicit “not directly comparable” state may be more honest than forcing them into a shared category.
Avoid hiding conversion assumptions inside a long query expression. Record the conversion basis, owner and version so another analyst can explain why the combined value differs from the source display.
Preserve source values where they are useful for investigation, subject to appropriate access controls. A normalised result plus its provenance is easier to reconcile than a transformed value whose original meaning has been discarded.
Define what simultaneous means across sources
Each source may provide a consistent local read while the combined query observes different moments. One database can be read before a business change and another after the corresponding change has propagated.
This is an illustrative example. An order is marked dispatched in a fulfilment system before its shipment record reaches a reporting source. A federated query can temporarily show a dispatched order with no shipment details, even though both systems are behaving according to their own rules.
A local transaction around the federated query does not automatically establish one globally coordinated snapshot across independent systems. The actual guarantees depend on the access mechanism and participating sources.
Decide whether temporary mismatches are acceptable for the use. An operational investigation may benefit from seeing current source differences. A formally reconciled period report may need controlled extracts or a defined completeness boundary instead.
Expose source observation times or progress markers where they help interpretation. Do not collapse several different source ages into one refresh timestamp unless the resulting label accurately describes the combined dataset.
Understand where the query work occurs
A federated query may execute filters, joins or aggregations at a remote source, at the coordinating system or through a combination of both. That placement affects network traffic and load.
Pushdown means performing an operation closer to the source rather than transferring all candidate data for local processing. It can reduce movement substantially when the remote source can apply a selective filter or useful aggregation.
However, not every operation can be pushed down safely or efficiently. Data types, functions, collations, permissions and the federation mechanism can affect eligibility. A query that looks compact may still transfer a large intermediate result.
PostgreSQL’s foreign-data wrapper documentation describes remote query planning, transferred conditions and related configuration for external PostgreSQL servers. Those facilities illustrate why execution plans and remote behaviour must be inspected rather than inferred from the local SQL text. PostgreSQL foreign-data wrapper documentation.
Measure the rows and bytes transferred, remote execution time and local processing time. A network bottleneck, an expensive remote scan and a poor local join require different remedies even if users experience the same slow report.
Protect operational sources from analytical demand
Federation can make a transactional database available to more users and more varied queries. That convenience can introduce workloads the source was never sized or indexed to support.
An analyst may request a broad historical join during the same period that staff are entering orders. Even a read-only query can consume processor time, memory, storage bandwidth and connections needed by operational work.
Define workload boundaries with the source owner. Consider approved views, query limits, concurrency controls and suitable reporting windows. The precise measures depend on the platform, but the responsibility should not disappear because access looks like an ordinary table.
Test the expensive cases, including missing filters and unexpectedly broad parameter values. A query that performs well for one customer can behave very differently for all customers or for a customer with unusually large history.
Where analytical demand is sustained, a replicated or extracted reporting dataset may provide better isolation and reproducibility. Federation remains one option within the architecture rather than a requirement to query every source live.
Decide how partial failure appears
A combined query depends on several components. A source can be unavailable, a credential can expire or a remote query can exceed its time budget. The application needs a policy for the resulting incomplete answer.
For some uses, the correct response is to fail the whole query. For others, a clearly labelled partial result can still support useful work. The dangerous case is presenting partial data as a complete business total.
This is an illustrative example. A service dashboard combines three regional systems. If one is unavailable, reporting the sum from the other two as the national workload understates the result. Showing the available regions and explicitly identifying the missing region preserves the limited value of the data.
Do not convert a failed source into an empty table without carrying the failure state. “No matching records” and “records could not be retrieved” are different outcomes, even if both initially produce zero rows.
Test timeout and retry behaviour. Retrying every remote request immediately can increase load on a struggling source. Set bounded recovery behaviour that fits the report’s purpose and the source owner’s operational expectations.
Preserve access rules through the combined layer
A federated interface creates another place where access is granted and interpreted. A user may have permission to see a summary but not the source detail from which it was derived.
Decide whether access uses each reader’s identity or a service identity. A service account can simplify connectivity while concentrating privileges. The combined layer must then enforce the intended reader-level restrictions correctly.
Combining permitted datasets can also reveal information that neither dataset exposes alone. Review the result’s sensitivity, not only the permissions attached to each input. Cross-source identifiers can make previously separate records linkable.
Keep credentials out of query text, exported definitions and routine diagnostic logs. Source connection management should follow the application’s established secrets and access procedures.
Audit useful events with enough context to identify the reader, purpose and accessed source where required. Avoid recording complete sensitive result sets merely to demonstrate that a query occurred.
Manage source changes as interface changes
Federated queries depend on schemas and definitions controlled by other systems. A source owner can rename a column, change a code or alter a business rule without changing the connection itself.
An obvious schema failure is often easier to detect than a semantic change that leaves the same field names intact. If closed starts including cancelled orders, a report may continue running while its meaning shifts.
Agree on a change process for exposed datasets. Record owners, definitions, supported fields and notice expectations. Where practical, expose stable source views rather than allowing every report to depend directly on internal application tables.
Use contract checks that verify types, expected relationships and important value ranges. Add business-level reconciliation for changes that structural checks cannot detect.
Version transformations when definitions change. Historical outputs need enough provenance to explain which mapping and source interpretation produced them. This is particularly useful when a report is rerun after a source application upgrade.
Compare federation with consolidation deliberately
Consolidation copies data into a managed common store. It introduces extraction, transformation, storage and refresh work, but can provide a controlled reporting population and reduce repeated pressure on operational sources.
Federation can reduce unnecessary copying and make independently managed data available sooner. It retains dependencies on source availability, query capability and network performance at the time of access.
Neither arrangement automatically resolves inconsistent definitions. Moving data into one database does not harmonise customer identity or status meaning. Leaving data distributed does not prevent careful semantic integration.
This is an illustrative example. A small exception report that compares current open jobs across two systems may suit federation. A repeatedly issued multi-year performance report may benefit from a controlled consolidated dataset with preserved versions. The appropriate choice follows workload and reproducibility needs.
A hybrid can be useful: retain live access for investigation while maintaining approved extracts for recurring reports. Define which path supports which claim so readers do not confuse current operational views with reconciled historical figures.
Validate a combined question end to end
Before validation, specify the provenance that must travel with an answer. At a minimum, a reviewer may need the contributing source, relevant source key, observation boundary and transformation version. Not every reader needs every field on screen, but the system should retain enough information to explain a disputed row.
This is an illustrative example. A combined backlog report classifies a job as overdue using a promised date from one source and a completion status from another. When a planner challenges the result, the investigation needs both original values and the mapping rule used at the time. A single final overdue flag is insufficient to distinguish a source correction, a stale read and an incorrect calculation.
Consider reproducibility explicitly. A live federated query rerun tomorrow may produce a different answer because each source has moved on. If the original answer must be recoverable, retain an authorised result snapshot or the source versions and process needed to reconstruct it. Merely saving the SQL preserves the question, not the historical state that answered it.
Keep provenance retention proportionate to purpose and sensitivity. A small reconciliation record may suffice where retaining full source copies would introduce unnecessary duplication. Define who can inspect that record and how long it remains useful under the organisation’s approved information practices.
Choose one valuable question and trace it through every source. Start with a small known population whose records can be inspected independently. Confirm identifiers, grain, units, exclusions and timing before optimising the query.
Include unmatched records, duplicate mappings, changed classifications and source failures in the validation set. These cases reveal whether the combined result depends on assumptions that ordinary matching rows conceal.
Reconcile counts and important measures against each source under the same definition. Explain differences instead of forcing agreement through filters that remove inconvenient exceptions. A legitimate difference can expose a useful business-process boundary.
Measure performance with representative volumes and concurrent source activity. Record where work executes and which limits protect the source. Repeat only when changed data, queries or source behaviour justify another assessment.
Provide readers with the result’s definition and known limits. They should be able to tell what population is included, how current it is and whether any source was unavailable. Those details are part of the answer, not merely technical administration.
Federation succeeds when it gives people a dependable way to ask a combined question while preserving the distinctions that matter. Shared access becomes useful integration only after identity, meaning, timing and failure have been addressed explicitly.
Source basis: the integration, consolidation, federation and metadata discussions in Data Strategy (2005), supplied in the collection. The comparison and scenarios are original synthesis. The linked PostgreSQL documentation was checked for a contemporary example of remote query execution.
