Informatica Cloud - Incremental Load (Data Ingestion)

Today we will learn how to create incremental data load (ingestion) in Informatica cloud data integration

1. Create two SQL server connections in Informatica cloud administration
1. Connection for source database
2. Connection for target database
2. Create a mapping from Informatica data integration
1. Add source
1. Select source connection (point to the newly created source connection in the Informatica administration)
2. Select source type as query
3. In the query window, enter the query to get the maximum timestamp from your target table
4. The timestamp should come from the target database table here. This max timestamp will act as a filter to fetch latest records from the source
   
2. Add a sql expression task in the mapping
1. Write query to fetch records from the actual source table
2. use a where condition (where timestamp > ?<timestamp_field>? )
3. In the advance section, set "max output row count" = 0. This will fetch all the latest rows from the source (otherwise it will fetch only 500 rows set by default).
4. Go to Output Fields and create all the fields that you have listed in your query
   
   
   
   
   
   
   
   
3. Add a Filter expression task in the Mapping 
1. Filter out all the NULL records
   
4. Select Target expression transformation
1. Select the target table where you want to insert the records
2. Do the field mapping
3. Additional: In the advance section of the target, call your stored procedure (that will run some business logic after inserting all the records) in the Post SQL
   
   
   
   
   
   
   
3. Mapping is complete
4. Run the Mapping via Informatica job. You will see all the records getting inserted
5. Run the job again to test incremental records
   
6. After running the mapping again, there will be no records inserted in the target. this is because, in the source there are not new records present at the moment.