Incremental load into database

Georgiy

Dear all,

I'm looking for a better solution for incremental loading records into a database than I have. Would be appreciated for any ideas.

Case:
I have to perform a daily loading from sftp to MS SQL table.
The database includes historical records over 3 years (~10 mln rows).
External data source (sftp) has a flat file that is updated every day and includes data for 3 latest months.
Every day we need to get data from sftp and override existing records from the existing table.

As Is approach:
1. Extract the entire dataset from the database
2. Filter data to exclude 3 latest months
3. Extract data from SFTP
4. Apply union tool
5. Override table via output tool

The main limitation of the current approach is low performance due to operating under an entire dataset.

I suppose there are should be another way such as extracting records for only 3 latest month from database and appending new from sftp, but I don't fully aware of how to perform that way using Alteryx.

MarqueeCrew

@Georgiy ,

 if you loaded the database from the Alteryx workflow, I'd save a copy of the Output in the form of a YXDB file.  That will eliminate the  1st step of downloading. 

Then I was thinking that you could bulk load the keys for the updates into an "update" table, dropping all content first.  Then use sql to delete from table where key in update.   This might be a net faster choice than bulk load entire history. 

now bulk load the remaining new and updated records. 

cheers,

 mark