Role: Solution Architect & Data Engineer
ETL Platform: IBM Cloud Pak for Data (CP4D) DataStage
Orchestration: CP4D Pipelines
Source: Snowflake
Target: Teradata
Scale: 21 tables | Largest table ~150M records
Performance Improvement: ~4 hours → ~1 hour
Project Overview
I designed and developed an incremental ETL architecture for a large-scale Snowflake-to-Teradata data integration process implemented in IBM Cloud Pak for Data (CP4D) DataStage and orchestrated through CP4D Pipelines.
The original ETL process used a full replacement strategy, reprocessing complete source tables during every execution.
This approach worked when data volumes were smaller. However, as the tables continued to grow, ETL throughput degraded from approximately 5,000 records/second to 2,000 records/second.
With the largest table reaching approximately 150 million records, the full-refresh process took approximately 4 hours and could no longer reliably complete within the required processing window.
Rather than continuing to optimize a process that fundamentally reprocessed unchanged data, I redesigned the architecture around incremental processing.
Solution Design
The new architecture identifies and processes only records that have been added or changed since the previous successful execution.
For each of the 21 source tables, I identified and configured:
- An incremental column used to determine the dataset that needs to be processed.
- A unique raw key used to identify corresponding records already present in the Teradata target.
- A temporary Teradata structure used to hold the current incremental dataset.
The ETL flow was developed in CP4D DataStage, while CP4D Pipelines orchestrated execution and dependencies between processing steps.
Incremental Processing Architecture
1. Identify the Incremental Dataset
For each source table, a column was identified that could be used to determine which records had been added or changed.
Instead of extracting the complete source table, DataStage retrieved only the required incremental dataset from Snowflake.
2. Stage the Incremental Dataset
The incremental records were loaded into a temporary Teradata table.
This created an isolated working dataset that could be validated and processed before changes were applied to the target table.
3. Identify Existing Target Records
Each table had a defined unique raw key.
The raw key was used to compare the temporary incremental dataset with the existing Teradata target and identify records that needed to be replaced.
4. Remove Existing Records and Surrogate Keys
For records already present in the target, the existing target rows were deleted based on their unique raw keys.
As part of this process, the previously assigned surrogate keys were removed together with the existing target records.
This avoided attempting to update large target datasets in place and provided a controlled replacement mechanism for only the affected subset of data.
5. Insert the Incremental Dataset
Records from the temporary table were then inserted into the target table.
New surrogate keys were generated during the insert process, creating the refreshed target dataset without requiring a complete table reload.
The processing pattern therefore became:
Identify Delta → Stage Delta → Identify Existing Rows → Delete Affected Rows → Insert Refreshed Rows
instead of:
Delete/Replace Entire Target → Reload Entire Source Dataset
Performance Improvement
The architectural change produced a significant improvement in ETL performance.
Before
Full replacement processing
~150M records in the largest table
Throughput degraded from ~5,000 to ~2,000 records/sec
Total processing time: ~4 hours
After
Incremental extraction and processing
Only new or changed records processed
Temporary-table-based replacement
Targeted delete/insert using unique raw keys
Total processing time: ~1 hour
~75% Reduction in Runtime
The redesigned process reduced the overall execution time from approximately 4 hours to 1 hour, allowing the pipeline to complete within the required processing window.
My Role
As Solution Architect and Data Engineer, I owned both the architectural design and technical implementation of the solution.
My responsibilities included:
- Analyzing the performance degradation of the existing full-refresh process.
- Identifying the full replacement architecture as the primary scalability limitation.
- Designing the new incremental processing architecture.
- Defining incremental columns for source tables.
- Identifying unique raw keys.
- Designing temporary-table processing.
- Designing the surrogate-key replacement strategy.
- Developing CP4D DataStage ETL flows.
- Designing CP4D Pipeline orchestration.
- Developing Teradata delete/insert processing logic.
- Testing incremental datasets and validating source-to-target results.
- Optimizing the solution for large-volume processing.
- Supporting implementation of the redesigned production pipeline.
Business & Technical Impact
The project transformed a growing performance problem into a scalable incremental architecture.
By eliminating unnecessary full-table processing, the solution:
- Reduced ETL runtime by approximately 75%.
- Reduced processing from approximately 4 hours to 1 hour.
- Restored the ability to complete processing within the required time window.
- Reduced unnecessary movement of unchanged data between Snowflake and Teradata.
- Established a reusable incremental processing pattern across 21 tables.
- Supported datasets reaching approximately 150 million records.
Technologies & Engineering Concepts
IBM CP4D • DataStage • CP4D Pipelines • Snowflake • Teradata • SQL • Incremental ETL • Surrogate Keys • Temporary Tables • Delete/Insert Pattern • Pipeline Orchestration • Performance Optimization • Large-Scale Data Processing • Solution Architecture