IICS | Incremental Load Using System Variable in Informatica Cloud

Hi Friends , we will learn how to use IICS system variables to do incremental loading in IICS . in this example , we will load data from snowflake to oracle table .

system variable informatica link
https://knowledge.informatica.com/s/a...

DDL/DML used for snowflake :
CREATE TABLE "CUST"
( "EMP_ID" NUMBER(38,0),
"EMP_NM" VARCHAR2(100 BYTE),
"STATUS" VARCHAR2(50 BYTE),
"SRC_CD" VARCHAR2(50 BYTE),
"CITY" VARCHAR2(100 BYTE),
"COUNTRY" VARCHAR2(100 BYTE),
"CREATE_DATE" TIMESTAMP (6),
"UPDATE_DATE" TIMESTAMP (6)
)
inserts :
Insert into CUST (EMP_ID,EMP_NM,STATUS,SRC_CD,CITY,COUNTRY,"CREATE_DATE","UPDATE_DATE") values (10,'RAM','A','SAP','DELHI','INDIA','2023-05-01 06:10:36','2023-05-01 06:10:36');
Insert into CUST (EMP_ID,EMP_NM,STATUS,SRC_CD,CITY,COUNTRY,"CREATE_DATE","UPDATE_DATE") values (20,'STEVE','A','SAP','DALLAS','UNITED STATES','2023-05-01 09:15:20','2023-05-01 09:15:20');
Insert into CUST (EMP_ID,EMP_NM,STATUS,SRC_CD,CITY,COUNTRY,"CREATE_DATE","UPDATE_DATE") values (30,'ANGELA','A','SAP','MEXICO CITY','MEXICO','2023-06-02 10:12:45','2023-06-02 10:12:45');
Insert into CUST (EMP_ID,EMP_NM,STATUS,SRC_CD,CITY,COUNTRY,"CREATE_DATE","UPDATE_DATE") values (40,'RAVI','A','SAP','CANCUN','MEXICO','2023-06-02 11:23:35','2023-06-02 11:23:35');
Insert into CUST (EMP_ID,EMP_NM,STATUS,SRC_CD,CITY,COUNTRY,"CREATE_DATE","UPDATE_DATE") values (100,'PAT','A','SAP','CALGARY','CANADA','2023-07-13 13:24:34','2023-07-13 13:24:34');
Insert into CUST (EMP_ID,EMP_NM,STATUS,SRC_CD,CITY,COUNTRY,"CREATE_DATE","UPDATE_DATE") values (50,'DEV','A','SAP','CHENNAI','INDIA','2023-06-01 14:32:06','2023-06-01 14:32:06');
Insert into CUST (EMP_ID,EMP_NM,STATUS,SRC_CD,CITY,COUNTRY,"CREATE_DATE","UPDATE_DATE") values (70,'RAJ','A','SAP','PUNE','INDIA','2023-06-01 15:54:21','2023-06-01 15:54:21');
Insert into CUST (EMP_ID,EMP_NM,STATUS,SRC_CD,CITY,COUNTRY,"CREATE_DATE","UPDATE_DATE") values (80,'DIYA','A','SAP','INDORE','INDIA','2023-07-13 20:12:31','2023-07-13 20:12:31');
Insert into CUST (EMP_ID,EMP_NM,STATUS,SRC_CD,CITY,COUNTRY,"CREATE_DATE","UPDATE_DATE") values (90,'RANI','A','SAP','BHOPAL','INDIA','2023-07-12 23:47:41','2023-07-12 23:47:41');

Oracle DDL is same .

syntax used for Snowflake to run job when db servers is in MST/MDT timezone . please change value as per your timezone difference to GMT .

for snowflake sources :
DATEADD(HOUR,-6,TO_TIMESTAMP('$Lastruntime','YYYY-MM-DD HH24:MI:SS'))

for oracle sources :
to_timestamp('$Lastruntime','YYYY-MM-DD HH24:MI:SS')-6/24

#informatica #iics #informática #tutorial #etltesting #datawarehouse #sql