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

[Georgiy]

@MarqueeCrew thanks for sharing your idea
However, I am not sure it will be faster cause in the first step you considering an additional transformation layer (yxdb). Suppose extracting the entire dataset from MS SQL database and saving in yxdb will take a lot of time.

Do you know is it possible to override only part of the data?

I would extract only rows over 3 latest months change them if necessary, delete extracted rows and append new rows to the database table?

[MarqueeCrew]

@Georgiy ,

i think that you misunderstood me.  I'm suggesting that you maintain a copy of the database in YXDB form.  

you might want to Google ms sql upsert for some sql ideas to implement using Alteryx. 

cheers,

 mark

[Georgiy]

Have found solution by using pre sql statement in the output tool

[MarqueeCrew]

Awesome 👏 

[faiqz]

Hi @Georgiy 

can you share a sample workflow of your incremental load? I don't quite understand how to use the pre sql statement in the output tool