Transformation Logic Generator

Transformation Logic Generator

Overview

This skill helps you design, write, and validate transformation logic for SAP Datasphere. Whether you're building a Transformation Flow with SQLScript or a Data Flow with Python operators, this skill provides patterns, best practices, and diagnostic tools to ensure your transformations are correct, performant, and maintainable.

When to Use This Skill

• Designing transformations from scratch: Deciding which tool and language to use
• Handling delta logic: Implementing incremental loads with watermarks
• Slowly Changing Dimensions (SCD Type 2): Tracking history of dimension changes
• Complex data cleansing: Deduplication, pivoting, date/time normalization
• Performance optimization: Dealing with large datasets or slow execution
• Troubleshooting transformation failures: SQL errors, type mismatches, operator crashes
• Data type mapping: Converting between source and target systems
• Error handling: Adding logging and validation to transformations

SQLScript vs Python: Choosing Your Tool

Use SQLScript for Transformation Flows When:

• Working with structured, tabular data from relational sources
• Needing high performance for large volumes (1M+ rows)
• Implementing delta loads with watermark patterns
• Performing set-based operations (MERGE, aggregations, window functions)
• Operating in the SAP HANA native database
• Team expertise is SQL-focused

Use Python for Data Flows When:

• Requiring complex business logic that's hard to express in SQL
• Integrating with ML libraries (scikit-learn, pandas, numpy)
• Handling unstructured data (text, JSON, images)
• Needing pandas-like data manipulation
• Working with multiple input sources in flexible ways
• Team expertise is Python-focused

SQLScript Transformations for Transformation Flows

Delta Handling with Watermarks

Watermarks track the last extracted value to enable incremental loads. Common watermark types:

-- Timestamp watermark pattern
PROCEDURE TF_LOAD_CUSTOMER_DELTA (
    IN iv_last_watermark TIMESTAMP
)
LANGUAGE SQLSCRIPT
AS
BEGIN
    -- Get current watermark (typically max of changed timestamp)
    DECLARE v_current_watermark TIMESTAMP = CURRENT_TIMESTAMP();

    -- Load only changed records
    UPSERT TARGET_CUSTOMER
    SELECT
        CUSTOMER_ID,
        CUSTOMER_NAME,
        REVENUE,
        UPDATED_AT,
        'ACTIVE' AS RECORD_STATUS
    FROM SOURCE_CUSTOMER
    WHERE UPDATED_AT > :iv_last_watermark
        AND UPDATED_AT <= :v_current_watermark;

    -- Update watermark in control table
    UPSERT WATERMARK_CONTROL
    VALUES ('CUSTOMER', :v_current_watermark);
END;