What's the Best Way to Join On-Premise Data with Cloud Data?

Join locality and operational burden decide this architecture, not total dataset size. What matters is where the join executes and how much data has to cross the boundary to get there.

Most teams inherit this decision rather than make it. A nightly CSV export from on-premise SQL Server into S3 and onward into Snowflake was a reasonable choice under constraints that no longer hold, and it keeps running until a failed transfer blocks the morning reports.

Three architectures are available. Each one spends a different resource: source capacity, network transfer, or operational attention.

TL;DR

• Join locality and operational burden decide this architecture; total dataset size rarely does.
• Live federation fits when data must stay in place, and pushdown keeps the result set small enough to cross the network.
• Batch replication fits repeated analytics that tolerate hour-level freshness and rewards a predictable extraction window.
• CDC replication earns its operational cost only when dashboards, applications, or downstream data products need second-level freshness.
• Boundary rules outrank performance preferences, so map every transfer path, including query results, logs, and spill storage, before comparing approaches.

Try Airbyte Flex

What Are Your Three Options for Hybrid Data Integration?

The three options for hybrid data integration are live federation, batch replication, and CDC replication. Federation leaves data in place and queries it across the boundary, batch replication copies it on a schedule, and CDC replication streams committed changes continuously.

The table below sets the three against the constraints that usually decide between them.

The practical distinction is where computation happens: federation spends source and network capacity at query time, while replication moves that cost into scheduled or continuous pipelines. Federation makes the bill most visible, because every query charges it directly to the system you are least willing to disturb.

When Should You Use Live Federation?

Live federation queries on-premise data directly from cloud tools without maintaining a replicated copy of the source tables. You can query the latest source-visible values, subject to transaction isolation and caching, while avoiding the storage costs and data governance complexity of a replicated copy. Query results, caches, spill data, and metadata may still cross the boundary, and every query depends on your source system's performance and network connectivity.

Performance depends primarily on how much data crosses the network after predicate and aggregation pushdown. A selective filter can make federation practical over a large table because only a small result crosses the network. Cross-source joins, unsupported aggregations, or expressions the connector cannot push down may instead transfer gigabytes of intermediate data to the cloud query engine. Join pushdown often requires both tables to reside in the same catalog, so inspect the query plan to confirm whether the connector executes the join at the source.

Concurrency matters as much as one-query latency, because repeated scans compete for WAN bandwidth, database connections, CPU, memory, and I/O on the source. That load can interfere with transactional workloads, especially during business hours. Use read replicas when policy permits them, cap federated-query concurrency, apply statement timeouts and workload-management rules, and test whether the connector supports parallel extraction or relies on a single JDBC stream.

When regulations forbid replicating data off-premises but allow approved query results to leave the source boundary, federation can be a suitable choice. Power BI Gateway connecting to on-prem SQL Server for current dashboard queries illustrates this approach.

If representative query plans show repeated scans or poor pushdown, test replication before committing production workloads to federation.

When Is Batch Replication the Right Choice?

Batch replication moves data in scheduled chunks during off-peak hours. You extract full or incremental datasets, load them into cloud storage, and run data-shaping jobs to prepare analytics-ready tables. The cloud copy stays as fresh as your last sync.

This method handles large or processing-heavy datasets where repeated federated scans or continuous streaming would be cost-prohibitive. It delivers consistent, query-ready data on an hours-latency schedule with a predictable work scope, though runtime still varies with bytes read, source throughput, network capacity, and destination compute.

Incremental extraction needs a trustworthy watermark, and an application-managed updated_at column is rarely one. It can miss rows committed by long-running transactions, miss rows the application forgot to stamp, and give you nothing at all for hard deletes. Prefer commit-ordered change versions, database log positions, or source-supported change tracking where available, capture deletes explicitly with tombstones or periodic reconciliation, and, when timestamps are the only option, overlap extraction windows and deduplicate records.

Independent table watermarks can also produce joins that represent no single point in time. Use snapshot isolation when the source supports it, record a common extraction boundary, and expose table-level freshness so you can detect skew.

Destination cost deserves the same attention. MERGE operations may scan or rewrite affected partitions, and copy-on-write and merge-on-read tables require different compaction strategies. Partition pruning, clustered keys, larger batches, and scheduled compaction keep upsert costs from erasing the savings of incremental extraction.

A nightly incremental sync from Oracle to BigQuery with automatic schema detection illustrates this approach. If extraction boundaries are inconsistent or deletes go uncaptured, joined analytics can be invalid even when the load succeeds.

How Do You Implement CDC Replication With Second-Level Freshness?

CDC tracks configured row-level inserts, updates, and deletes by reading transaction logs and streaming those events to downstream systems. After the initial snapshot, the stream primarily carries change events, along with any required control, schema, heartbeat, or replay records.

Start With a Consistent Snapshot

Production CDC begins with a consistent initial snapshot. It then reads changes from the exact log position that marks that snapshot. Long snapshots can retain old database rows or log segments, so measure snapshot duration and storage headroom before you start the stream. Schema changes also need coordination because logical decoding may not emit DDL events, and primary-key changes can invalidate assumptions used for destination upserts.

Control Log Retention and Replica Identity

Treat log retention as an operational control. Monitor inactive logical replication slots and available storage. Because PostgreSQL versions expose different columns in pg_replication_slots, inspect the version-specific view schema before running this example. Where your version exposes these fields, monitor slot activity, retained WAL, safe capacity, and invalidation state:

<pre><code>SELECT
  slot_name, slot_type, active, wal_status,
  pg_size_pretty(safe_wal_size) AS safe_wal_size,
  inactive_since, invalidation_reason,
  pg_size_pretty(
    pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
  ) AS retained_wal
FROM pg_replication_slots
WHERE restart_lsn IS NOT NULL
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC;</code></pre>

Configure one replica-identity option for each table that publishes updates or deletes. The following commands are alternatives, so choose the setting that matches the table and connector requirements. Run the single command for that setting:

<pre><code>ALTER TABLE mytable REPLICA IDENTITY DEFAULT;
ALTER TABLE mytable REPLICA IDENTITY FULL;
ALTER TABLE mytable REPLICA IDENTITY USING INDEX ui;</code></pre>

Design for Replay, Lag, and Failover

Monitor source-log growth, connector lag, snapshot progress, rejected records, schema mismatches, destination apply lag, and the age of the oldest unacknowledged event.

Test database and connector failover together. A promoted replica may preserve a synchronized slot or lose it and restart from the current WAL position, depending on the database version and connector configuration. Test the exact configuration so operators know whether they must recreate capture jobs and slots.

Operational dashboards that require fresh metrics depend on this continuous synchronization. Microservices also use it to react to data changes within seconds, while event-driven applications use it across on-premise and cloud infrastructure. CDC can also keep retrieval stores and AI data products current by reprocessing only records whose source text changed, which requires propagating updates and tombstones to every derived index.

Streaming order updates from on-prem PostgreSQL to a cloud data warehouse for current inventory management illustrates this approach. Choose CDC only when second-level freshness justifies continuous responsibility for snapshots, log retention, schema changes, failover, and destination apply lag.

What Security Considerations Matter Most?

Secure hybrid integration requires controlled network paths, verified encryption, least-privilege identities, and data-flow governance. Apply those controls to source reads, transferred records, temporary storage, metadata, logs, and query results.

Secure Network Paths

Private VPN connections encrypt traffic over untrusted or public networks, while dedicated connectivity can improve latency consistency. Private endpoints primarily reduce public-network exposure. Include steady CDC traffic, batch peaks, redundancy, and cross-region transfer when estimating connectivity and data-transfer costs.

Protect Data and Identities

Encrypt data in transit using TLS 1.2 or higher for all connections. CISA's joint hardening guidance recommends TLS 1.3 where both endpoints support it. Require certificate verification explicitly in database-client settings. Protect data at rest with server-side encryption using cloud KMS or customer-managed keys. Where separation of duties or sovereignty requires it, keep key custody in an external HSM or customer-controlled key service, and test how pipelines behave when the key service denies access.

Automate certificate rotation to prevent pipeline failures from expired credentials. Implement least-privilege access using service accounts with specific database permissions. Prefer short-lived workload identities or certificate-based federation over stored cloud access keys, and separate extraction, orchestration, and destination-write roles.

Integrate cloud IAM with on-premise directory services to maintain consistent access policies. Follow event logging practices to track all data movement and access patterns, including identity issuance, source reads, destination writes, key use, and administrative changes.

Govern Residency and Regulation

Consider data residency requirements that may restrict where your organization stores or processes information. One concrete sovereignty pattern keeps Apache Iceberg data files in on-premises S3-compatible storage. It exposes tables through a REST catalog, whose open specification lets the catalog vend short-lived, table-scoped credentials to cloud compute. The data files stay under customer control, while metadata and query results may still cross the boundary, so include them in transfer assessments and access policies.

A federated query can still constitute an international transfer when it returns personal data to a cloud endpoint. Include batch files, CDC logs, temporary spill storage, backups, and observability payloads in data-flow maps and retention policies.

The EU transfer rules set out in Chapter V of GDPR may require an adequacy mechanism or Article 46 safeguards. The EU financial-sector resilience regulation known as DORA has applied since January 2025, and covered financial entities must maintain oversight and mapping of ICT services that support critical functions.

Under the federal security standards codified in the HIPAA Security Rule, transmission and at-rest encryption remain addressable safeguards. Encryption is still the recommended technical posture.

How Do You Choose the Right Approach?

Evaluate the following questions before selecting an approach. Base the decision on measured workload and boundary requirements.

1. Can data leave on-premises? Map every transfer path, including query results, temporary storage, logs, backups, and subprocessors. Compliance restrictions may require federation or an architecture that keeps data files under customer control.
2. What freshness can you tolerate? Use CDC when the freshness SLA justifies continuous log retention, monitoring, reconciliation, and destination apply costs. Use batch when an hourly or daily copy meets the business requirement.
3. How much data crosses the network after pushdown? Base the evaluation on the volume of shipped intermediate results. Selective filters and same-source joins can make federation viable over large datasets, while a smaller cross-source join may perform poorly if gigabytes must move through a single stream.
4. Where do joins execute? Same-catalog joins with predicate and aggregation pushdown favor federation. Cross-catalog joins and processing-heavy pipelines favor replication.
5. What concurrency can the source absorb? Low-concurrency exploration may suit federation. Repeated dashboards and production data processing can compete with OLTP workloads and are usually safer on a replicated copy.
6. What's your complexity tolerance? Batch generally has the lowest steady operational burden among replication approaches. CDC adds log-retention, snapshot, schema-change, ordering, duplicate-delivery, and failover responsibilities.

Total dataset size alone provides insufficient basis for this decision. Join locality and operational constraints matter more, and both of them point back to where you are allowed to run the pipeline in the first place.

How Does Airbyte Flex Support Hybrid Joins Across Your Boundary?

Airbyte offers deployment patterns that match different on-premise-to-cloud connectivity and sovereignty requirements. Airbyte Self-Managed runs entirely inside your environment, connects directly to local databases, and writes to cloud warehouses over encrypted connections, which keeps processing and temporary data within infrastructure you control.

Airbyte Flex splits those responsibilities differently. Airbyte operates the control plane while the data plane, along with your records, credentials, keys, and compute, runs in your environment. However, some metadata such as cursor and primary-key values sits in the control plane. Source connectors reach local systems through that customer-controlled data plane, so extraction stays inside the boundary without your team running the orchestration layer.

The same replicated records are what an in-boundary retrieval stack indexes, so agent reads inherit the boundary and access controls the pipeline already enforces. Airbyte moves 26 billion records daily for more than 7,000 companies, including 18% of the Fortune 500.

The capabilities that matter most for hybrid joins cluster around extraction breadth and unattended operation:

• 700+ connectors covering enterprise databases such as Oracle, SQL Server, and PostgreSQL
• Incremental sync and CDC to hold transfer volumes down
• Automatic detection of schema changes in on-premise systems
• Retries for interrupted transfers, with errors recorded for operator review
• An open-source foundation that keeps deployment portable across hybrid patterns

None of that removes the need to measure. Connector breadth decides what you can reach, while boundary rules and the source's spare capacity still decide which of the three architectures you should reach with.

What Should You Do Next?

You can test the decision before it becomes permanent. A proof-of-concept run against real joins, real concurrency, and at least one deliberate failure will tell you more than any comparison table, including the one above, because the constraint that breaks the design is usually the source system rather than the network.

Airbyte covers all three architectures on one platform, with CDC, incremental sync, and scheduled batch loads sharing the same connectors and the same operational surface. Where the boundary is the binding constraint rather than the freshness target, Airbyte Flex keeps the data plane in your environment while the control plane runs elsewhere.

Get a demo to see how the deployment models align with your residency and freshness requirements.

Frequently Asked Questions

Can You Run Federation and Replication Against the Same Source?

Yes, and mature deployments usually do. Cap federated-query concurrency separately from the replication connector's, so exploratory queries cannot starve the extraction window, and monitor both as one combined load on the source rather than two independent budgets.

Does Federation Avoid a Data Transfer Assessment?

No. Query results, cached result sets, spill storage, and connector metadata can all leave the source boundary even though no table is copied. Scope the assessment to what actually crosses the boundary, not to whether a replica exists.

How Do You Prevent Duplicate CDC Records?

Treat CDC delivery as at-least-once unless every component provides compatible exactly-once behavior. Deduplicate by source log position or event identifier and make destination writes idempotent.

What Happens to a CDC Pipeline During a Planned Source Upgrade?

Logical replication slots do not always survive a major-version upgrade, and a dropped slot restarts capture from the current log position rather than the last acknowledged one. Plan a reconciliation pass or a fresh snapshot into the maintenance window, and confirm the behavior on your exact database version and connector configuration beforehand.