0

I have the following scenario:

I have 2 source systems, one running Postgres, the other running MySQL. An ETL process (using Azure DataFactory and Azure Integration runtimes) runs the Extract for a number of Tables in each system, pulling them into an Azure SQL Database (where the Transformation and eventual load happens).

Most of the tables do not have a TimeStamp for last modified (or similar) field.

The current process involves truncating the destination tables and then bulk loading the entire table into the destination DB.

This process takes about 15-20 minutes all up, however we are looking at expanding the tables and columns we import to run some additional reporting, to the point where it might adversely affect the time the process takes.

Is there a quick or elegant way that this process can be changed to only select the new and updated rows for importation?

  • Quick and elegant way is in ALTER TABLE which adds auto-utilized timestamp column into the table structure (or, for example, ID of data renewal attempt which' attributes are stored in separate table). current process involves truncating the destination tables and then bulk loading the entire table into the destination DB Why you do not use import into newly created table followed by tables renaming? – Akina Aug 4 at 5:29
  • 1
    If you can enable track_commit_timestamp in Postgres, you could use pg_xact_commit_timestamp(xmin) to get the latest "commit timestamp" for each row – a_horse_with_no_name Aug 4 at 5:43
  • I should have added in the original question - since the Databases are the backend for paid-for systems, our ability to change the Schema is very limited - so altering the tables isn't an option (since it will put us outside of support). – TheDemonLord Aug 4 at 8:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.