0

Incremental load records flag type

Dear, 

I have a scenario as the below:

I'm loading a table from the database that has a creation date and update date, which will be used in the incremental load later. The target is I need to add two columns (flag columns) that will be filled with date based on the incremental load. If the record got created will fill flagA with the data of the load if the record got created will fill flagB with the data of the load.

I assume that it will be based on the data and action of the load. 

 

Select 

X,

Y, 

Z, 

$(xxxx) as flagA, 

$(yyyy) asflagB

From 

TablA;

 

Any Idea. 

 

Regards. 

6replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • If you are looking to store 1) the date a record was inserted into the Incorta table, and 2) flags indicating whether a record was inserted via full load or inserted/updated via incremental load, there are several ways to do this. Here is one way that aligns to the pattern you've described:

    Full load query (MySQL syntax):

    SELECT
        X,
        CURDATE() as flagA,
        DATE(null) as flagB
    FROM
        TableA

    Incremental load query:

    SELECT
        X,
        DATE(null) as flagA
        CURDATE() as flagB,
    FROM
        TableA
    WHERE
        update_date > ?
    Like
  • Yes, I want to trigger the records with the date it was full load but this is will happen one time on the first full load. but  how this will work in the incremental load if I want to differentiate between the new insert records and the updated records? 

    And is this a function 

    CURDATE() 
    Like
    • IncortaUser you can differentiate between insert/update within your incremental SQL query itself by using a CASE statement to apply logic. Here I am assuming your source table will have an update date equal to the create date for new records:

      CASE
          WHEN TableA.create_date = TableA.update_date THEN FALSE
          ELSE TRUE
      END AS is_updated_record

      Yes, CURDATE() is a MySQL function that returns the current date. Similar functions exist for any relational database. You should work with your DBA to create the appropriate SQL for your use case.

      Like
  • During Incremental refresh, Incorta uses the new image of the entire record to replace the old image.  The record with the same key will be updated.   It will not keeps the original value from the initial creation in a column that will not be replaced. 

    If you just want to know if a record was created and never updated and separate it from those records which were updated, only one flag is needed.  The Full query and Incremental query can use different constant values.

    Full Query:

    SELECT CUSTOMER_ID, CUSTOMER_NAME, 'Y' NEVER_UPDATED_FLAG FROM SRC.CUST

    Incremental Query:

    SELECT CUSTOMER_ID, CUSTOMER_NAME, 'N' NEVER_UPDATED_FLAG FROM SRC.CUST
    Like
  • Dears, 

    thank you for your replies. Let me give a sample:

    I have attached the full and incremental loads, so basically, when I load incrementally, I want to store the date, whether it was a new insertion or an update. 

    Like
    • IncortaUser  

      Incorta currently does not have system variable that can return the current timestamp or the current job timestamp.
       

      To get the current time in the extraction query, we will need to get the current timestamp from the source database as Dustin mentioned.

      For example, n Oracle, for Full load:

      SELECT TRX.A, TRX.B, TRX.C, NULL as flagB
      FROM SCHEMA.TRX
      

      for Incremental  load:

      SELECT TRX.A, TRX.B, TRX.C, SYSDATE as flagB
      FROM SCHEMA.TRX
      WHERE TRX.C > ?
      

      Sorry I don't understand how flagA is populated. 

      It seems that flagB for showing the latest time when the data was updated?

      Like
Like Follow
  • 3 wk agoLast active
  • 6Replies
  • 22Views
  • 3 Following

Product Announcement

Incorta 5 is now Generally Available