What Is Data Replication? Full Loads, Incremental Loads, and CDC Explained

Compare full loads, incremental loads and CDC, including when each replication method makes sense.

Key takeaways

<p>Data replication is the process of copying data from a source system to one or more destinations and keeping those copies aligned at an appropriate cadence. Organizations use replication to make operational data available in warehouses, lakehouses, reporting platforms, and downstream applications without relying on recurring manual exports.</p> <p>Most enterprise replication strategies use more than one loading method. A full load establishes the destination dataset. Incremental loads keep it current. Change data capture, or CDC, is one way to identify and capture the changes that incremental replication must apply.</p> <p>Understanding how these methods relate is essential when choosing a replication architecture.</p> <h2>TL;DR</h2> <ul> <li>A <strong>full load</strong> copies the entire in-scope dataset.</li> <li>An <strong>incremental load</strong> moves only records that are new or have changed since the previous successful load.</li> <li><strong>CDC</strong> identifies and captures insert, update, and delete events so they can be delivered incrementally.</li> <li>Incremental loading and CDC are related, but they are not synonyms.</li> <li>CDC does not automatically mean instantaneous replication.</li> <li>Most production architectures combine an initial full load with ongoing incremental replication and selective reloads when required.</li> </ul> <h2>What Is Data Replication?</h2> <p>Data replication creates and maintains copies of data across different systems, platforms, or locations. The source remains the system in which the original activity occurs, while the destination receives a copy for another operational, analytical, or continuity requirement.</p> <p>Replication can happen at several levels:</p> <ul> <li>Storage or infrastructure replication</li> <li>Complete database replication</li> <li>Schema or table-level replication</li> <li>Application data replication</li> <li>Replication into a warehouse or lakehouse</li> </ul> <p>This article focuses on analytical replication: moving selected data from operational databases and enterprise applications into destinations where it can support reporting, BI, AI, integration, or downstream business processes.</p> <p>The destination does not always need to be an identical copy of the source. An organization may replicate selected modules, schemas, business objects, tables, columns, or records. It may also apply mappings or structural changes based on the destination architecture.</p> <p>Data replication is one part of the broader <a href="/blog/what-is-data-pipeline">data pipeline architecture</a>. The pipeline manages how data is extracted, transported, processed, validated, and delivered. Replication describes the creation and maintenance of the destination copy.</p> <p>Replication is also not the same as backup. A backup preserves recoverable historical states. A replica is normally kept current, which means an incorrect update or deletion may also be copied to the destination.</p> <h2>How Does the Data Replication Process Work?</h2> <p>Data replication connects to a source, identifies the required data, moves it to a destination, applies the records, and checks that the resulting copy is complete and usable.</p> <table> <thead> <tr><th>Stage</th><th>What Happens</th><th>Control Question</th></tr> </thead> <tbody> <tr><td>Connect</td><td>The pipeline authenticates to the source and destination</td><td>Are credentials, roles, and access scopes appropriate?</td></tr> <tr><td>Select</td><td>Teams choose the required objects, tables, columns, or records</td><td>Is the replication scope documented?</td></tr> <tr><td>Capture</td><td>The system reads the complete dataset or identifies changes</td><td>How are inserts, updates, and deletes detected?</td></tr> <tr><td>Transport</td><td>Data moves through the replication pipeline</td><td>Is the data protected and recoverable in transit?</td></tr> <tr><td>Apply</td><td>Records are inserted, updated, merged, or deleted at the destination</td><td>Are operations applied in the correct order?</td></tr> <tr><td>Validate</td><td>Source and destination data are compared</td><td>Are row counts, keys, balances, and values consistent?</td></tr> <tr><td>Monitor</td><td>The platform tracks status, failures, retries, and replication lag</td><td>Can the pipeline resume safely after an interruption?</td></tr> </tbody> </table> <p>Consider a supplier table in an ERP system.</p> <p>The first replication run may copy every supplier record into a Snowflake table. Later runs identify suppliers created or updated since the previous successful load and apply those changes to the destination. If a supplier is deleted or deactivated, the pipeline must also have a defined method for representing that change.</p> <p>The exact mechanism depends on what the source exposes. A database may provide transaction logs. A SaaS application may provide timestamps, extraction dates, API cursors, change feeds, or periodic snapshots instead.</p> <h2>Full Load vs. Incremental Load vs. CDC</h2> <p>Full loads, incremental loads, and CDC solve different parts of the replication problem.</p> <table> <thead> <tr><th>Area</th><th>Full Load</th><th>Incremental Load</th><th>CDC</th></tr> </thead> <tbody> <tr><td>Data move</td><td>Entire in-scope dataset</td><td>New or changed records</td><td>Captured change events</td></tr> <tr><td>Typical role</td><td>Initial copy, rebuild, or complete refresh</td><td>Ongoing scheduled refresh</td><td>Detailed or lower-latency change replication</td></tr> <tr><td>Change detection</td><td>Not required</td><td>Timestamp, ID, hash, API token, or CDC feed</td><td>Logs, change tables, triggers, or application feeds</td></tr> <tr><td>Inserts</td><td>Included</td><td>Included if detectable</td><td>Usually captured</td></tr> <tr><td>Updates</td><td>Included</td><td>Included if detectable</td><td>Usually captured</td></tr> <tr><td>Deletes</td><td>Reflected if the target is rebuilt from the source snapshot</td><td>Require explicit detection and handling</td><td>Often captured when the source mechanism supports delete events</td></tr> <tr><td>Complexity</td><td>Lower</td><td>Moderate</td><td>Higher</td></tr> <tr><td>Source workload</td><td>Can be significant for large datasets</td><td>Usually lower than repeated full loads</td><td>Depends on the capture mechanism</td></tr> <tr><td>Latency</td><td>Determined by the full-load schedule and duration</td><td>Determined by the extraction schedule</td><td>Can be continuous, near real time, or micro-batch</td></tr> </tbody> </table> <p>The three approaches are often combined. <a href="https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Task.CDC.html" target="_blank" rel="noopener noreferrer">AWS Database Migration Service</a>, for example, supports a full-load-plus-CDC pattern in which existing data is migrated first and subsequent changes are then replicated continuously.</p> <h2>What Is a Full Data Load?</h2> <p>A full data load copies the complete in-scope dataset from the source to the destination. It is commonly used to establish a new destination, rebuild an existing table, or refresh small datasets whose changes are difficult to identify reliably.</p> <p>There are two common full-load patterns:</p> <ul> <li><strong>Initial full load:</strong> The first copy used to populate an empty destination before ongoing replication begins.</li> <li><strong>Recurring full refresh:</strong> A scheduled process that replaces or rebuilds the complete destination dataset.</li> </ul> <p>Full loads are useful when:</p> <ul> <li>A warehouse or lakehouse is being populated for the first time</li> <li>Historical data must be backfilled</li> <li>A small reference table changes infrequently</li> <li>The source does not expose reliable incremental indicators</li> <li>Previous changes may have been missed</li> <li>A destination table must be rebuilt after corruption or structural change</li> </ul> <p>Their main advantage is simplicity. Each run receives the complete current dataset and does not depend entirely on a previous checkpoint.</p> <p>That simplicity becomes expensive as the dataset grows. Repeated full loads can consume more source processing, network bandwidth, destination compute, and load-window time. A failed run may also require the entire dataset to be processed again.</p> <p>Full loads are therefore not obsolete. They are simply inefficient when used indiscriminately.</p> <h2>What Is an Incremental Data Load?</h2> <p>An incremental data load copies only records that are new or have changed since the previous successful run. This reduces repeated data movement and makes more frequent replication practical for large or fast-changing datasets.</p> <p>The pipeline needs a reliable way to determine what changed. Common incremental mechanisms include:</p> <ul> <li>Last-modified timestamps</li> <li>Increasing numeric IDs</li> <li>High-water marks</li> <li>Source API cursors</li> <li>Extraction dates</li> <li>Hash or checksum comparisons</li> <li>Snapshot comparisons</li> <li>CDC feeds</li> </ul> <p>A high-water mark records the pipeline&rsquo;s confirmed position, such as the latest processed timestamp or ID. The next run starts from that position.</p> <p>The checkpoint should advance only after the relevant load succeeds. If it advances before the data is safely applied, a failed run can create a gap that later runs will not recover.</p> <p>Incremental loading also needs protection against boundary problems. Two records may share the same timestamp. A delayed update may arrive after the apparent extraction window. Source and destination systems may interpret time zones differently. Some records may be modified without updating the field being monitored.</p> <p>Deletes require particular attention. A timestamp query can find a row that was inserted or updated, but it cannot query a row that no longer exists. Deleted records may require tombstones, an active-key extract, a source change feed, or periodic reconciliation against a current snapshot.</p> <p>Oracle&rsquo;s <a href="https://docs.oracle.com/en/cloud/saas/applications-common/25a/biacc/manage-data-extracts.html" target="_blank" rel="noopener noreferrer">Business Intelligence Cloud Connector documentation</a> distinguishes complete extracts from incremental extracts that retrieve data changed since the previous extraction. This illustrates an important point: incremental replication from a SaaS application does not necessarily require direct access to database transaction logs.</p> <h2>What Is Change Data Capture?</h2> <p>Change data capture is a set of techniques for identifying and capturing changes made to source data. Those changes can then be delivered to a warehouse, lakehouse, replica, application, event stream, or another downstream consumer. According to <a href="https://cloud.google.com/learn/what-is-change-data-capture" target="_blank" rel="noopener noreferrer">Google Cloud&rsquo;s CDC explanation</a>, the captured modifications can include inserts, updates, and deletions.</p> <table> <thead> <tr><th>CDC Method</th><th>How It Works</th><th>Main Consideration</th></tr> </thead> <tbody> <tr><td>Log-based CDC</td><td>Reads database transaction, redo, binary, or write-ahead logs</td><td>Requires supported log access and sufficient log retention</td></tr> <tr><td>Change-table CDC</td><td>Reads database-maintained tables containing captured changes</td><td>Depends on database-specific setup and retention</td></tr> <tr><td>Trigger-based CDC</td><td>Uses database triggers to write changes to a separate table or queue</td><td>Adds logic and potential processing to the source</td></tr> <tr><td>Application change feed</td><td>Uses vendor-provided events, APIs, webhooks, or delta endpoints</td><td>Coverage and latency depend on the source application</td></tr> <tr><td>Query-based detection</td><td>Polls timestamps, IDs, status fields, or hashes</td><td>May miss deletes or changes not reflected in the monitored field</td></tr> </tbody> </table> <p>Log-based CDC is widely used for operational databases because it can capture committed changes without repeatedly scanning entire tables. <a href="https://debezium.io/documentation/faq/" target="_blank" rel="noopener noreferrer">Debezium&rsquo;s</a> documentation also notes that log-based CDC can capture deletes and does not require a last-updated column.</p> <p>Oracle GoldenGate similarly <a href="https://docs.oracle.com/en/middleware/goldengate/core/21.3/understanding/oracle-goldengate-architecture.html" target="_blank" rel="noopener noreferrer">reads database transaction logs</a> to capture changes continuously.</p> <p>However, capturing a change is not the end of replication. The event must still be transported, processed, and applied correctly at the destination.</p> <p>Teams must account for:</p> <ul> <li>Capture, transport, and apply lag</li> <li>Transaction and event ordering</li> <li>Duplicate delivery</li> <li>Interrupted connections</li> <li>Insufficient source-log retention</li> <li>Schema changes</li> <li>Reprocessing after failure</li> <li>Updates that arrive before their related records</li> </ul> <p>CDC pipelines should therefore be designed for idempotency. If the same event is delivered more than once, reprocessing it should not create duplicate or contradictory destination records.</p> <h2>CDC and Incremental Loading Are Not the Same Thing</h2> <p>Incremental loading describes the outcome: move only what changed. CDC describes one family of techniques for identifying and capturing those changes.</p> <p>A scheduled query that extracts records with a new last-modified timestamp is incremental even if it never reads a transaction log or CDC table.</p> <p>Conversely, CDC may capture changes continuously but deliver them to the destination in micro-batches. The presence of CDC does not determine the delivery cadence by itself.</p> <p>The relationship is simple:</p> <blockquote>CDC can power an incremental load, but not every incremental load uses CDC.</blockquote> <p>This distinction matters when evaluating tools. A claim of &ldquo;incremental replication&rdquo; does not reveal how updates and deletes are detected, how quickly changes are delivered, or how missed events are recovered.</p> <h2>Which Data Replication Method Should You Use?</h2> <p>The right method depends on dataset size, source capabilities, acceptable latency, delete handling, and recovery requirements.</p> <table> <thead> <tr><th>Requirement</th><th>Recommended Starting Point</th></tr> </thead> <tbody> <tr><td>Populate a new destination</td><td>Initial full load</td></tr> <tr><td>Refresh a small, slowly changing reference table</td><td>Scheduled full refresh</td></tr> <tr><td>Replicate a large table with reliable update timestamps</td><td>Incremental load</td></tr> <tr><td>Capture frequent inserts, updates, and deletes</td><td>CDC where supported</td></tr> <tr><td>Extract from a SaaS application without transaction-log access</td><td>Application-supported incremental extraction</td></tr> <tr><td>Add older historical records</td><td>Full or partitioned backfill</td></tr> <tr><td>Recover changes that may have been missed</td><td>Targeted reload or full reconciliation</td></tr> <tr><td>Support a low-latency operational workflow</td><td>CDC or an application event feed</td></tr> <tr><td>Support standard daily or hourly analytics</td><td>Scheduled incremental replication may be sufficient</td></tr> </tbody> </table> <p>Most enterprise architectures use a hybrid approach:</p> <ol> <li>Run an initial full load.</li> <li>Continue with incremental or CDC-based replication.</li> <li>Validate the destination regularly.</li> <li>Reload selected tables or partitions when necessary.</li> </ol> <p>The objective is not to eliminate full loads. It is to avoid moving the entire dataset when only a small portion has changed.</p> <h2>What Are the Main Data Replication Challenges and Best Practices?</h2> <p>Reliable replication requires more than moving rows. Teams must verify that the destination remains complete, current, consistent, secure, and recoverable.</p> <table> <thead> <tr><th>Best Practice</th><th>Risk It Reduces</th></tr> </thead> <tbody> <tr><td>Define acceptable freshness</td><td>Prevents unnecessary real-time complexity or unacceptable reporting delays</td></tr> <tr><td>Use stable primary or business keys</td><td>Reduces duplicates and incorrect updates</td></tr> <tr><td>Handle deletes explicitly</td><td>Prevents obsolete records remaining active at the destination</td></tr> <tr><td>Advance checkpoints only after success</td><td>Prevents permanent gaps after failed loads</td></tr> <tr><td>Make reruns idempotent</td><td>Prevents duplicate records and repeated change application</td></tr> <tr><td>Preserve or map data types carefully</td><td>Reduces truncation, rounding, and formatting problems</td></tr> <tr><td>Monitor freshness and replication lag</td><td>Identifies pipelines that run but fall behind</td></tr> <tr><td>Reconcile source and destination data</td><td>Detects missing, duplicated, or altered records</td></tr> <tr><td>Plan for schema changes</td><td>Prevents new columns or changed types from silently breaking loads</td></tr> <tr><td>Retain sufficient change history</td><td>Allows interrupted pipelines to recover</td></tr> <tr><td>Support selective reloads</td><td>Avoids rebuilding every table after a localized issue</td></tr> <tr><td>Record operational lineage</td><td>Shows which source, run, and transformation produced destination data</td></tr> </tbody> </table> <p>A pipeline that finishes successfully can still produce an incomplete replica. Operational success and data correctness are different checks.</p> <p>Monitoring should therefore cover both system status and data outcomes. A green job status does not prove that every expected row, deletion, key, or balance reached the destination.</p> <h2>How SplashBI Replicates Enterprise Application Data</h2> <p>Replication becomes more complicated when the source is an enterprise SaaS application rather than a conventional database. Applications may restrict direct database access, expose governed business objects, and provide different mechanisms for complete and incremental extraction.</p> <p><a href="/enterprise-intelligence-platform/data-pipeline">SplashBI Data Pipeline</a> automates table-to-table replication from Oracle Fusion Cloud, Oracle EBS, and other enterprise sources into warehouses, lakehouses, reporting platforms, and downstream applications. The replication workflow covers four stages:</p> <ol> <li><strong>Connect:</strong> Select Oracle Fusion, Oracle EBS, or another supported enterprise source.</li> <li><strong>Configure:</strong> Choose the required modules, objects, destination, load method, and schedule.</li> <li><strong>Replicate:</strong> Run full, incremental, scheduled, or on-demand data movement.</li> <li><strong>Use:</strong> Make the destination data available to BI, operational reporting, AI, and other downstream workloads.</li> </ol> <p>Relevant capabilities include:</p> <ul> <li>Prebuilt Oracle extraction</li> <li>Full and incremental loads</li> <li>Table-to-table replication</li> <li>Multi-threaded incremental processing</li> <li>Preserved source structures and relationships</li> <li>Scheduling and on-demand execution</li> <li>Built-in monitoring, retries, and recovery</li> <li>Resumable processing</li> <li>Cloud and compatible on-premises destinations</li> <li>No-code configuration for standard pipelines</li> </ul> <p>The destination options include Snowflake, Databricks, Amazon Redshift, Azure SQL, Microsoft SQL Server, Oracle, and other enterprise platforms. Organizations can also combine Oracle data with information from Salesforce, Workday, UKG, and additional sources.</p> <p>Because source systems expose different change mechanisms, the replication method should fit the source architecture. An operational database may support log-based CDC. Oracle Fusion may use application-supported full and incremental extraction. Both can keep destination data current, but they do not capture changes in precisely the same way.</p> <p>The dedicated <a href="/oracle-fusion-data-pipeline-for-analytics-and-warehouses-of-your-choice">Oracle Fusion Cloud Data Pipeline</a> supports fault-tolerant, resumable incremental loads into the destination an organization already uses. SplashBI also provides a specific <a href="/oracle-fusion-to-snowflake-data-pipeline">Oracle Fusion-to-Snowflake pipeline</a> for replacing recurring file exports with automated replication.</p> <p>In one documented implementation, SplashBI supported replication across more than 55 entities and over 500 million rows from Oracle Fusion and EBS into a governed destination architecture. The point is not simply the volume moved. It is the ability to maintain structure, incremental delivery, monitoring, and access controls across a complex enterprise environment.</p> <h2>Data Replication Is a Strategy, Not a Single Load Type</h2> <p>Full loads, incremental loads, and CDC are not competing answers to the same question.</p> <p>A full load establishes or rebuilds the destination. Incremental loading reduces ongoing data movement. CDC provides a more detailed way to capture changes when the source supports it.</p> <p>The right replication strategy combines these methods according to data volume, freshness, source capabilities, and recovery needs.</p> <p><strong>Explore how the <a href="/enterprise-intelligence-platform">SplashBI enterprise intelligence platform</a> connects, replicates, governs, and prepares enterprise data for reporting, analytics, and AI.</strong></p>

FAQ

Is CDC the same as incremental loading?

No. Incremental loading means transferring only new or changed data. CDC is one method for identifying and capturing those changes. An incremental load can also use timestamps, increasing IDs, hashes, API cursors, or snapshot comparisons without using log-based CDC.

Does CDC always replicate data in real time?

No. CDC can support real-time or near-real-time replication, but changes may also be delivered in scheduled batches. Actual freshness depends on capture frequency, transport time, processing, destination performance, workload, and whether the pipeline has fallen behind.

When should a full load be used?

Use a full load to populate a new destination, rebuild a table, backfill history, refresh a small dataset, or recover when incremental changes may have been missed. Large, frequently changing datasets usually require incremental replication after the initial load.

Can incremental data replication capture deleted records?

Yes, but only when the source and pipeline provide a deletion mechanism. Options include CDC delete events, tombstone records, inactive-status fields, active-key comparisons, and periodic source snapshots. A simple last-modified timestamp query will not normally detect a row that no longer exists.