{"id":1753,"date":"2026-08-13T00:01:29","date_gmt":"2026-08-13T00:01:29","guid":{"rendered":"https:\/\/vykhodtsev.dev\/?page_id=1753"},"modified":"2026-08-13T00:14:02","modified_gmt":"2026-08-13T00:14:02","slug":"snowflake-%e2%86%92-teradata-incremental-etl-pipeline","status":"publish","type":"page","link":"https:\/\/vykhodtsev.dev\/?page_id=1753","title":{"rendered":"Snowflake \u2192 Teradata Incremental ETL Pipeline"},"content":{"rendered":"\n

Role:<\/strong> Solution Architect & Data Engineer
ETL Platform:<\/strong> IBM Cloud Pak for Data (CP4D) DataStage
Orchestration:<\/strong> CP4D Pipelines
Source:<\/strong> Snowflake
Target:<\/strong> Teradata
Scale:<\/strong> 21 tables | Largest table ~150M records
Performance Improvement:<\/strong> ~4 hours \u2192 ~1 hour<\/p>\n\n\n\n

Project Overview<\/h2>\n\n\n\n

I designed and developed an incremental ETL architecture for a large-scale Snowflake-to-Teradata<\/strong> data integration process implemented in IBM Cloud Pak for Data (CP4D) DataStage<\/strong> and orchestrated through CP4D Pipelines<\/strong>.<\/p>\n\n\n\n

The original ETL process used a full replacement strategy<\/strong>, reprocessing complete source tables during every execution.<\/p>\n\n\n\n

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<\/strong>.<\/p>\n\n\n\n

With the largest table reaching approximately 150 million records<\/strong>, the full-refresh process took approximately 4 hours<\/strong> and could no longer reliably complete within the required processing window.<\/p>\n\n\n\n

Rather than continuing to optimize a process that fundamentally reprocessed unchanged data, I redesigned the architecture around incremental processing<\/strong>.<\/p>\n\n\n\n

Solution Design<\/h2>\n\n\n\n

The new architecture identifies and processes only records that have been added or changed since the previous successful execution.<\/p>\n\n\n\n

For each of the 21 source tables<\/strong>, I identified and configured:<\/p>\n\n\n\n

    \n
  • An incremental column<\/strong> used to determine the dataset that needs to be processed.<\/li>\n\n\n\n
  • A unique raw key<\/strong> used to identify corresponding records already present in the Teradata target.<\/li>\n\n\n\n
  • A temporary Teradata structure used to hold the current incremental dataset.<\/li>\n<\/ul>\n\n\n\n

    The ETL flow was developed in CP4D DataStage<\/strong>, while CP4D Pipelines<\/strong> orchestrated execution and dependencies between processing steps.<\/p>\n\n\n\n

    Incremental Processing Architecture<\/h2>\n\n\n\n
    \"arch<\/figure>\n\n\n\n

    1. Identify the Incremental Dataset<\/h3>\n\n\n\n

    For each source table, a column was identified that could be used to determine which records had been added or changed.<\/p>\n\n\n\n

    Instead of extracting the complete source table, DataStage retrieved only the required incremental dataset from Snowflake.<\/p>\n\n\n\n

    2. Stage the Incremental Dataset<\/h3>\n\n\n\n

    The incremental records were loaded into a temporary Teradata table<\/strong>.<\/p>\n\n\n\n

    This created an isolated working dataset that could be validated and processed before changes were applied to the target table.<\/p>\n\n\n\n

    3. Identify Existing Target Records<\/h3>\n\n\n\n

    Each table had a defined unique raw key<\/strong>.<\/p>\n\n\n\n

    The raw key was used to compare the temporary incremental dataset with the existing Teradata target and identify records that needed to be replaced.<\/p>\n\n\n\n

    4. Remove Existing Records and Surrogate Keys<\/h3>\n\n\n\n

    For records already present in the target, the existing target rows were deleted based on their unique raw keys.<\/p>\n\n\n\n

    As part of this process, the previously assigned surrogate keys were removed together with the existing target records<\/strong>.<\/p>\n\n\n\n

    This avoided attempting to update large target datasets in place and provided a controlled replacement mechanism for only the affected subset of data.<\/p>\n\n\n\n

    5. Insert the Incremental Dataset<\/h3>\n\n\n\n

    Records from the temporary table were then inserted into the target table.<\/p>\n\n\n\n

    New surrogate keys were generated during the insert process, creating the refreshed target dataset without requiring a complete table reload.<\/p>\n\n\n\n

    The processing pattern therefore became:<\/p>\n\n\n\n

    Identify Delta \u2192 Stage Delta \u2192 Identify Existing Rows \u2192 Delete Affected Rows \u2192 Insert Refreshed Rows<\/strong><\/p>\n\n\n\n

    instead of:<\/p>\n\n\n\n

    Delete\/Replace Entire Target \u2192 Reload Entire Source Dataset<\/strong><\/p>\n\n\n\n

    Performance Improvement<\/h2>\n\n\n\n

    The architectural change produced a significant improvement in ETL performance.<\/p>\n\n\n\n

    Before<\/strong><\/p>\n\n\n\n

    Full replacement processing
    ~150M records in the largest table
    Throughput degraded from ~5,000 to ~2,000 records\/sec
    Total processing time: ~4 hours<\/strong><\/p>\n\n\n\n

    After<\/strong><\/p>\n\n\n\n

    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<\/strong><\/p>\n\n\n\n

    ~75% Reduction in Runtime<\/h3>\n\n\n\n

    The redesigned process reduced the overall execution time from approximately 4 hours to 1 hour<\/strong>, allowing the pipeline to complete within the required processing window.<\/p>\n\n\n\n

    My Role<\/h2>\n\n\n\n

    As Solution Architect and Data Engineer<\/strong>, I owned both the architectural design and technical implementation of the solution.<\/p>\n\n\n\n

    My responsibilities included:<\/p>\n\n\n\n

      \n
    • Analyzing the performance degradation of the existing full-refresh process.<\/li>\n\n\n\n
    • Identifying the full replacement architecture as the primary scalability limitation.<\/li>\n\n\n\n
    • Designing the new incremental processing architecture.<\/li>\n\n\n\n
    • Defining incremental columns for source tables.<\/li>\n\n\n\n
    • Identifying unique raw keys.<\/li>\n\n\n\n
    • Designing temporary-table processing.<\/li>\n\n\n\n
    • Designing the surrogate-key replacement strategy.<\/li>\n\n\n\n
    • Developing CP4D DataStage ETL flows.<\/li>\n\n\n\n
    • Designing CP4D Pipeline orchestration.<\/li>\n\n\n\n
    • Developing Teradata delete\/insert processing logic.<\/li>\n\n\n\n
    • Testing incremental datasets and validating source-to-target results.<\/li>\n\n\n\n
    • Optimizing the solution for large-volume processing.<\/li>\n\n\n\n
    • Supporting implementation of the redesigned production pipeline.<\/li>\n<\/ul>\n\n\n\n

      Business & Technical Impact<\/h2>\n\n\n\n

      The project transformed a growing performance problem into a scalable incremental architecture.<\/p>\n\n\n\n

      By eliminating unnecessary full-table processing, the solution:<\/p>\n\n\n\n

        \n
      • Reduced ETL runtime by approximately 75%<\/strong>.<\/li>\n\n\n\n
      • Reduced processing from approximately 4 hours to 1 hour<\/strong>.<\/li>\n\n\n\n
      • Restored the ability to complete processing within the required time window.<\/li>\n\n\n\n
      • Reduced unnecessary movement of unchanged data between Snowflake and Teradata.<\/li>\n\n\n\n
      • Established a reusable incremental processing pattern across 21 tables<\/strong>.<\/li>\n\n\n\n
      • Supported datasets reaching approximately 150 million records<\/strong>.<\/li>\n<\/ul>\n\n\n\n

        Technologies & Engineering Concepts<\/h2>\n\n\n\n

        IBM CP4D \u2022 DataStage \u2022 CP4D Pipelines \u2022 Snowflake \u2022 Teradata \u2022 SQL \u2022 Incremental ETL \u2022 Surrogate Keys \u2022 Temporary Tables \u2022 Delete\/Insert Pattern \u2022 Pipeline Orchestration \u2022 Performance Optimization \u2022 Large-Scale Data Processing \u2022 Solution Architecture<\/strong><\/p>\n\n\n\n

        <\/p>\n","protected":false},"excerpt":{"rendered":"

        Role: Solution Architect & Data EngineerETL Platform: IBM Cloud Pak for Data (CP4D) DataStageOrchestration: CP4D PipelinesSource: SnowflakeTarget: TeradataScale: 21 tables | Largest table ~150M recordsPerformance Improvement: ~4 hours \u2192 ~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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"set","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"_zipai_imported":false,"footnotes":""},"class_list":["post-1753","page","type-page","status-publish","hentry"],"spectra_blocks_featured_image_url":null,"spectra_blocks_author_info":{"display_name":"Aleksei","avatar_url":"https:\/\/secure.gravatar.com\/avatar\/198b25ea575ebb3cbd0934cb05fd942d0f256a65d3ff388a161c857e00b092bf?s=96&d=mm&r=g","author_link":"https:\/\/vykhodtsev.dev\/?author=1","description":""},"_links":{"self":[{"href":"https:\/\/vykhodtsev.dev\/index.php?rest_route=\/wp\/v2\/pages\/1753","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/vykhodtsev.dev\/index.php?rest_route=\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/vykhodtsev.dev\/index.php?rest_route=\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/vykhodtsev.dev\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/vykhodtsev.dev\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1753"}],"version-history":[{"count":3,"href":"https:\/\/vykhodtsev.dev\/index.php?rest_route=\/wp\/v2\/pages\/1753\/revisions"}],"predecessor-version":[{"id":1758,"href":"https:\/\/vykhodtsev.dev\/index.php?rest_route=\/wp\/v2\/pages\/1753\/revisions\/1758"}],"wp:attachment":[{"href":"https:\/\/vykhodtsev.dev\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1753"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}