Incremental Data Loading in Oracle with PL/SQL

In today’s data-driven world, businesses cannot afford long downtime during ETL (Extract, Transform, Load) processes. Traditional full data reloads are time-consuming and resource-intensive. That’s why incremental data loading has become the backbone of modern Oracle Data Warehouse environments.

Instead of reloading the entire dataset, incremental loading brings in only the new or changed data. When paired with PL/SQL scripts, this approach provides speed, efficiency, and reliability—three key ingredients for high-performing warehouses.

In this guide, we’ll explore incremental data loading strategies in Oracle Data Warehouse using PL/SQL with detailed steps, best practices, and code examples.

What is Incremental Data Loading?

Incremental loading is the process of capturing and loading only the modified data since the last ETL run. Instead of truncating and reloading millions of rows, we only process changes (inserts, updates, and sometimes deletes).

For example:

A sales fact table with 200M rows only gets 50K new rows daily.
Full reload = process all 200M rows → high cost and time.
Incremental load = insert only 50K new rows → quick and efficient.

Why Use Incremental Loading in Oracle Data Warehouse?

Here are the top reasons why enterprises prefer incremental strategies:

Performance – Reduces ETL job runtime drastically.
Resource Efficiency – Saves CPU, I/O, and storage.
Near Real-Time Insights – Enables faster availability of data.
Cost-Effective – Less cloud/DB compute charges.
Scalability – Handles large enterprise data seamlessly.

Key Strategies for Incremental Data Loading

There are several ways to implement incremental loading in Oracle DW using PL/SQL. Let’s explore the most effective strategies.

1. Timestamp-Based Incremental Loading

This method relies on a column such as LAST_UPDATED_DATE or CREATED_DATE.

Steps:

Capture the maximum timestamp from the target table.
Fetch records greater than this timestamp from the source.
Insert them into the target.

PL/SQL Example:

DECLARE
   v_last_load_date DATE;
BEGIN
   -- Get last load timestamp from control table
   SELECT MAX(load_date)
   INTO v_last_load_date
   FROM etl_control
   WHERE table_name = 'SALES_FACT';

   -- Insert only new records
   INSERT INTO sales_fact_dw (sale_id, product_id, amount, created_date)
   SELECT sale_id, product_id, amount, created_date
   FROM sales_fact_src
   WHERE created_date > v_last_load_date;

   -- Update control table
   INSERT INTO etl_control (table_name, load_date)
   VALUES ('SALES_FACT', SYSDATE);
   
   COMMIT;
END;

✅ Best for: Systems where source tables always have reliable timestamps.
⚠️ Watch out: Late arriving data may be missed if timestamps are not accurate.

2. CDC (Change Data Capture) using Oracle GoldenGate

Oracle provides Change Data Capture (CDC) with GoldenGate or Streams to capture committed changes directly from redo logs.

Pros: Real-time, reliable, low latency.
Cons: Licensing cost, setup complexity.

If budget allows, pairing PL/SQL with GoldenGate is one of the most robust solutions for incremental loads.

3. Trigger-Based Incremental Load

You can use triggers on source tables to log changes into a staging table.

Example:

CREATE OR REPLACE TRIGGER trg_sales_fact_log
AFTER INSERT OR UPDATE OR DELETE ON sales_fact_src
FOR EACH ROW
BEGIN
   INSERT INTO sales_fact_stage
   (operation_type, sale_id, product_id, amount, created_date, log_date)
   VALUES
   (CASE
        WHEN INSERTING THEN 'I'
        WHEN UPDATING THEN 'U'
        WHEN DELETING THEN 'D'
    END,
    :NEW.sale_id,
    :NEW.product_id,
    :NEW.amount,
    :NEW.created_date,
    SYSDATE);
END;

Your ETL job then processes only the staged changes.

✅ Best for: Medium workloads where real-time replication isn’t available.
⚠️ Watch out: Can add overhead to source OLTP system.

4. Surrogate Key + Batch Identifier

In some systems, rows carry a batch ID or surrogate key. Using the highest ID loaded last time, you fetch only newer rows.

PL/SQL Snippet:

DECLARE
   v_last_id NUMBER;
BEGIN
   SELECT MAX(last_loaded_id)
   INTO v_last_id
   FROM etl_control
   WHERE table_name = 'CUSTOMER_DIM';

   INSERT INTO customer_dim_dw
   SELECT * FROM customer_dim_src
   WHERE customer_id > v_last_id;

   UPDATE etl_control
   SET last_loaded_id = (SELECT MAX(customer_id) FROM customer_dim_src)
   WHERE table_name = 'CUSTOMER_DIM';
   
   COMMIT;
END;

✅ Best for: Tables with increasing IDs.
⚠️ Watch out: Fails if IDs are not sequential or reused.

5. MERGE (UPSERT) Approach for Incremental Updates

Sometimes incremental loading is not only about inserts but also updates. Oracle’s MERGE statement simplifies this.

Example:

MERGE INTO customer_dim_dw tgt
USING (SELECT * FROM customer_dim_src WHERE updated_date > SYSDATE-1) src
ON (tgt.customer_id = src.customer_id)
WHEN MATCHED THEN
   UPDATE SET tgt.customer_name = src.customer_name,
              tgt.email = src.email
WHEN NOT MATCHED THEN
   INSERT (customer_id, customer_name, email, updated_date)
   VALUES (src.customer_id, src.customer_name, src.email, src.updated_date);

✅ Best for: Handling mixed inserts and updates in one go.
⚠️ Watch out: Not efficient on extremely large datasets without indexing.

Best Practices for Incremental Loading in Oracle

Maintain ETL Control Tables – Always track last load date, batch ID, or key.
Partition Large Tables – Partition fact tables for faster incremental inserts.
Use Bulk Collect in PL/SQL – Speeds up large inserts.
Log Errors & Exceptions – Implement structured PL/SQL exception handling.
Validate Data Consistency – Compare row counts between source and target.

Real-World Example Workflow

Here’s a simplified end-to-end incremental ETL flow for a SALES table:

Identify new/changed rows (based on timestamp).
Load into a staging table.
Apply business transformations in PL/SQL.
Use MERGE to upsert into warehouse fact table.
Update ETL control table.
Log job status for monitoring.

This hybrid approach ensures data accuracy, high performance, and reliability.

Benefits to Business Users

For analysts and decision-makers:

Faster Reports: Data available in near real-time.
Cost Saving: Reduced infrastructure costs.
Agility: Respond quickly to changes in market trends.
Scalability: Supports growing data without massive re-engineering.

Conclusion

Incremental data loading is no longer a luxury—it’s a must-have strategy in modern Oracle Data Warehouses. Whether you choose timestamp-based, CDC, triggers, or merge strategies, implementing them with PL/SQL gives your ETL processes a strong foundation.

If you are working with complex SQL, PL/SQL, or Power BI scenarios, feel free to discuss them with fellow professionals in our SQL Queries Community Forum.

For additional reading on Oracle’s incremental strategies, check out Oracle GoldenGate documentation — a great reference to extend your ETL knowledge.

By adopting incremental loading best practices, you ensure efficiency, scalability, and performance in your Oracle Data Warehouse environment—leading to faster insights and better business outcomes.