We are loading data from an Oracle database into Snowflake using Alteryx. The full load process is working seamlessly. However, the issue arises when we attempt to implement the incremental load. Our source data contains a "LAST_UPDATE_DATE" column, and our goal is to compare this column in the target table to identify new records and updated records, then only update these specific records in the target table, achieving an incremental load.
We've gone through the Alteryx documentation, forums, and online resources to find a solution, but we have not been successful in resolving this matter.
DIL_SUPPLIER_DS in oracle
SUPPLIER_KEY | LAST_UPDATE_DATE | COMPANY |
DSP01 | 03/02/2002 | ABC |
DSP02 | 04/02/2002 | ABCD |
DSP03 | 08/05/2005 | ABCDE |
DSP04 | 08/08/2005 | DELTA |
SO USING FULL_INCREMENTAL LOAD I LOAD THE SAME TABLE INTO SNOWFLAKE, AS DIL_SUPPLIER_D.
DIL_SUPPLIER_D IN SNOWFLAKE
SUPPLIER_KEY | LAST_UPDATE_DATE | COMPANY |
DSP01 | 03/02/2002 | ABC |
DSP02 | 04/02/2002 | ABCD |
DSP03 | 08/05/2005 | ABCDE |
DSP04 | 08/08/2005 | DELTA |
NOW, I ADDED ONE ROW AND UPDATE DSP05 ROW AND CHANGED THE COMPANY NAME TO GIGA(DSP02) IN THE SOURCE.(DIL_SUPPLIERS_DS)
SUPPLIER_KEY | LAST_UPDATE_DATE | COMPANY |
DSP01 | 03/02/2002 | ABC |
DSP02 | 08/08/2023 | GIGA |
DSP03 | 08/05/2005 | ABCDE |
DSP04 | 08/08/2005 | DELTA |
DSP05 | 08/08/2023 | BELTA |
WE TO COMPARE : SELECT * FROM DIL_SUPPLIER_DS WHERE LAST_UPDATE_DATE>(SELECT MAX(LAST_UPDATE_DATE ) FROM DIL_SUPPLIER_D)
AFTER COMPARING THE ABOVE CONDITION, WE HAVE UPDATED THE DSP02 ROW AND DSP05
So, two Rows are to be Updated in target based on condition.
Thank you for your attention to this matter. We eagerly await your response, and we are committed to working collaboratively to overcome this challenge.
