Incremental load records flag type
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.
$(xxxx) as flagA,
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 > ?
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.
SELECT CUSTOMER_ID, CUSTOMER_NAME, 'Y' NEVER_UPDATED_FLAG FROM SRC.CUST
SELECT CUSTOMER_ID, CUSTOMER_NAME, 'N' NEVER_UPDATED_FLAG FROM SRC.CUST