1

I had an issue with updating my historical table in db. I have 3 steps in ETL job:

  1. Detect changes - Matillion ETL finds differencies between two tables and writes it into tmp table
  2. Close current - this step updates all rows with current_flag = 'Y' to 'N' and sets end_date to dateadd(day,-1,current_date)
  3. Insert rows - all rows from tmp table in step 1 (filtering only the ones with N and C indicator) are being appended to Historical table with start_date equal to dateadd(day,-1,current_date) and end date equal to '2099-01-01'

WHat happened is that step 2 and 3 were executed manually by mistake with data from yesterday (from tmp table) and after 15minutes job with all 3 steps was executed again as supposed to with new data. I have different update_timestamps (2021-03-19 01:59:02 and 2021-03-19 02:11:57) which can be used to fix the issue. But i am having difficulties on fixing data that were updated manually (having start_date='2021-03-18' and end_date='2021-03-18' and current_flag='N'), I believe those should be updated again to start_date='2021-03-18' and end_date='2099-01-01' and current_flag='Y') but I am not sure what to do with new job data (newer timestamp - that executed correctly) as it was done on wrong data. Should I delete it and rerun after fixing start_date='2021-03-18' and end_date='2021-03-18' and current_flag='N' manually? Also, I feel like i'm missing some steps here?

Thanks

John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
Anchee
  • 43
  • 3
  • SELECT * from MY_TABLE dsa where start_date='2021-03-18' and end_date='2021-03-18' and update_timestamp_new = '2021-03-19 01:59:02' --357 rows – Anchee Mar 19 '21 at 11:16
  • SELECT * from MY_TABLE dsa where update_timestamp_new = '2021-03-19 01:59:02' --29926 + 357 (from above) = 30283 That update timestamp is the "wrong" one. The one that's finished regularly is 2021-03-19 02:11:57 --2288 rows – Anchee Mar 19 '21 at 11:18

0 Answers0