DRAFT ETL+ Load Replace Logic

Owned by Cortlandt _
Last updated: Jul 27, 2023
3 min read

DRAFT Step by step technical description of the ETL+ Load Replace process. Work in Progress

Load Replace Panel UI

Load Replace Settings / Parameters

1 = source field, 2 = target T-SQL

Variables used.

Process

  1. TargetRowCount = Obtain the number of rows in the <target-table>.
    Zero means the table has zero rows or the table has yet to be created in the DW.

  2. IF TargetRowCount = 0 AND Target T-SQL string begins with “MAX(“ THEN

    1. Run Load All

    2. End Load Replace process now.

  3. Create a T-SQL query from the T-SQL expression in Target T-SQL that results in a single value resolved from Target T-SQL.
    (In short, evaluate the Target T-SQL parameter and resolve into a single value for use in latter steps)
    Depending on the syntax of Target T-SQL the T-SQL query created is either:

    • SELECT <Target T-SQL> FROM <target-table>

    • SELECT <Target T-SQL>

  4. Save the results of the T-SQL SELECT statement into a variable named SQLResults.

  5. low-value variable used in step #6 below.
    Determine the low-value for the date type of <source-table>.source field. For instance, the low-value of the smallint data type is = -32,768.

    PSEUDO CODE
    low-value = Low_Value( Data_Type( <source-table>.source field ))
    WHERE

    1. Data_Type – a function that returns the name of the datatype of the source field/row named in the parm.

      • The datatype for all source fields is shown on the Design page.

    2. Low_Value – a function that returns the lowest value for the datatype named in the parm.

      • For instance Low_Value(T-SQL_smallint) = -32,768

  6. Calculate compare-value variable used in the step below.
    IF SQLResults = NULL AND Target T-SQL NOT = “NULL”
    THEN
    compare-value= low-value ! see NOTE below
    ELSE
    compare-value = SQLResults

  7. Filter and Extract Source Table into temp/staging table (pseudo query language)

    1. Simple Version. ! pseudo query language
      Extract INTO staging-table ! pseudo query language
      FROM <source-table>
      WHERE <source-table>.source field >= compare-value

    2. When ETL SQL Statement includes a JOIN or UNION clause. ! pseudo query language
      Extract INTO staging-table ! pseudo query language
      FROM <source-table>
      WHERE <source-table>.source field >= compare-value
      <equivalent to JOIN or UNION clause>

  8. Delete all rows in target T-SQL that meet the filter criteria

    • T-SQL: DELETE FROM target-table WHERE target-table.source-field >= compare-value
      T-SQL: DELETE FROM target-table WHERE target-table.target T-SQL >= SQLResults

      • Both the <source-table> and <target-table> tables must have a field or column as named in the source-field parameter.

  9. Add/Insert records from staging-table into target-table.

    • T-SQL: INSERT FROM staging-table INTO target-table.

  • <source-table> – source table named in the ETL SQL Statement panel.

  • <target-table> – corresponding target table. The table currently selected in the Target Objects panel.

  • Step 6 - Assume that <field> >= low-value would work consistently with all Source Object query systems/data access layers whereas <field> >= NULL might not.



T-SQL: DELETE FROM target-table WHERE target-table.target T-SQL >= SQLResults

 

 

 

 

Proposed Functions

LOADMAX Option

Concept: Create a new, ETL+ only function called LOADMAX that can be used in place of the MAX function in Target T-SQL.

Replace step # 2 above with

2. IF TargetRowCount = 0 AND Target T-SQL string begins with “LOADMAX(“ THEN

Run Load All
End Load Replace process now.

ELSE

Change “LOADMAX(" to "MAX(" in Target T-SQL string.